The answer to this question is undoubtedly “YES!!!”

You may be thinking, “Is VBA even still a thing?”

“Is it a dead programming language?”

“Why should I even bother learning VBA?”

Hear me out, and Iet me explain why the language is still worth learning.

Today’s Need

In today’s world, almost no job is done without a computer. In typing, only a computer is used. Some jobs demand repeating the same process at a regular frequency.

Examples:

1. Preparation of the same type of reports on a daily/weekly/monthly basis.
2. An accountant’s job involves the analysis and compiling of several financial transactions that need to be cross-checked.
3. Performing a series of actions in an Excel document to prepare some kind of dashboard.

To do any of the above repetitively, it is way better to automated them. Once the task is automated, it can be run with the click of a button. An automation tool is generally required to automate action(s) on the computer. But, if one knows VBA, no other tool is required to achieve this, since VBA is already integrated with the Microsoft Office package.

For those who already know a little of what a scripting language is, Visual Basic for Applications comes in handy as it is just a part of the Microsoft Office application. For those who are completely new to the development of code/scripting, it is still not a problem. It does not take much time to learn it.

Benefits of Learning VBA

1. Routine, repetitive tasks can be automated thereby reducing manual work.
2. Formulas can be avoided wherever required because they can be managed in the code.
3. If the code is logically built, it can produce expected results considering conditions, etc.,
4. Anyone can do a task in a person’s absence if they’re aware of the task. Eventually, it reduces dependency on individual people.
5. The language is portable as it is just a part of the document and requires only the Microsoft Office package to be installed. VBA is available as a part of the Microsoft Office applications. (Excel, Word, Outlook, Access, etc.)
6. Code can be protected to avoid copyright issues/damage to the tool.
7. Better worksheet hiding can be done using VBA. It works better than the manual hiding of worksheets.
8. It is easier than any other programming language.
9. File handling is possible using VBA.

Some Real-Life Examples

In order to show you the wide array of stuff you can do with VBA, let me show you a few example programs and applications.

The idea here is that you can easily apply the language to many real world problems and scenarios.

Preparation of Report Card for Every Term in Schools

This can be automated using sub procedures in VBA. The steps to do so are as follows:

1.  All that the teacher needs to do is to store the data of class, grades, student names, subjects, and other different information in different sheets of Microsoft Excel or tables in Microsoft Access database documents. Both Access and Excel can be used in combination with SQL queries to achieve the outcome mentioned in Point 2.
2. Write VBA script to calculate and produce reports on all this.
3. Sum of marks for each student.
4. Average mark of the class/student (s).
5. Sort and move data to the main report (file handling is involved).
6. Provide a number as a rank based on the sorting result.
7. List a set of students who will win awards for high scores/centum in subjects.
8. List the teachers who have produced “Nil failures”/“More students with centum marks” and add them in another report to award them.

And many more… 😊

Once these are all prepared, they can be achieved in a single click after every terminal examination. Isn’t that reducing the effort and time taken by all teachers in the school? Of course, yes!!!

Excel, Access screens for reference:

Checking Who is Older and Displaying a Message

Here is a small program that receives two inputs (age of father and age of uncle) from the user and finds who is the elder of the two.

Sub compare_demo()

' declare variables
Dim fath_age, uncl_age

' assign values
fath_age = InputBox("Enter your father's age :")
uncl_age = InputBox("Enter your uncle's age :")

' age of uncle and father are being compared here
If fath_age > uncl_age Then
    MsgBox "Your father is elder than your uncle. "
ElseIf fath_age = uncl_age Then
    MsgBox "You father and your uncle are of the same age. "
Else
    MsgBox "Your uncle is elder than your father. "
End If

End Sub

Program to Find if Baggage Weight is Within a Limit

Sub chk_weight()

' declare variables
Dim allwed_wt, current_wt, flag

' assign values in kg
allwed_wt = 10
flag = 0

'set a label to start again if weight is not within limits
' CInt is used to convert the string to a number
current_wt = CInt(InputBox(" Please enter the weight of your baggage"))

' check if the weight is within limits
' display appropriate message to the passenger
If current_wt <= allwed_wt Then
    MsgBox "The weight of your baggage is within the permitted limit. No further action is required."
Else
    MsgBox "The weight of your baggage is more than the permitted limit. Please remove some baggage now as the current weight will be asked again."
    'Goto the block of code that gets input and validates the weight of baggage
    GoTo 89
End If

End Sub

Screenshot of the two programs mentioned above:

Calculation of Simple Interest in Banking

Here is a simple program that calculates simple interest based on the age of the customer as per the bank’s policy. Senior citizens are offered better rates of interest

Sub simple_interest_calculation()

' declare all required variables
Dim Prin, no_of_years, cut_age, roi, simple_interest, mat_amt

' Receive necessary inputs from the end user
Prin = InputBox("Enter the Principle amount")
no_of_years = InputBox("Enter the number of years")
cut_age = InputBox("Enter the cut_age of the customer")

' Set rate of interest depending on the cut_age of the customer ( varies for senior citizens )
If cut_age > 59 Then
    ' senior citizens
    roi = 10
Else
    ' non- senior citizens
    roi = 8
End If

' Calculate the simple interest and maturity amount
simple_interest = (Prin * no_of_years * roi) / 100
mat_amt = simple_interest + Prin

' Display the calculated output
MsgBox "The interest amount is " & simple_interest & vbCrLf & "The maturity amount is " & mat_amt

End Sub

The same program can also be modified to calculate the interest for several customers in an Excel sheet. Every row of data should indicate one customer. The first few columns should have the input data and the calculated interest can be displayed in the last column of each row.

Though the same can be achieved using formulas, there are chances for the Excel formula references to get corrupted. But if the values are calculated using VBA and displayed, there are no chances for reference errors to occur.

Sub simple_interest_calculation_dynamic()

' declare all required variables
Dim Prin, no_of_years, cut_age, roi, simple_interest, mat_amt

For i = 1 To 100
If Sheets("Simple Interest").Cells(i, 1) <> "" Then

' Receive necessary inputs from the end user
Prin = Sheets("Simple Interest").Cells(i, 2)
cut_age = Sheets("Simple Interest").Cells(i, 3)
no_of_years = Sheets("Simple Interest").Cells(i, 4)

' Set rate of interest depending on the cut_age of the customer ( varies for senior citizens )
If cut_age > 59 Then
    ' senior citizens
    roi = 10
Else
    ' non- senior citizens
    roi = 8
End If

' Calculate the simple interest and maturity amount
simple_interest = (Prin * no_of_years * roi) / 100
mat_amt = simple_interest + Prin

'Print the calculated values back to the sheet.
Sheets("Simple Interest").Cells(i, 5) = simple_interest
Sheets("Simple Interest").Cells(i, 6) = mat_amt
End If
Next


' Display the calculated output
MsgBox "The interest amount is " & simple_interest & vbCrLf & "The maturity amount is " & mat_amt

End Sub 

The screen with the input and output of the above program:

Program for Supermarket Offers

The program below suggests the supermarket admin team/the customer on the offers or discounts that can be provided to the customer based on the bill amount.

Please try the program and play around the values to understand the use of VBA.

In this program:

• If the bill amount exceeds 2500, then the customer gets a flat discount of 50.
• If the bill amount is less than or equal to 2500 and the customer has purchased milk, then the customer gets 200gm curd for free.

In this way, several conditions can be added to make the program a user-friendly/self-help app.

Sub supermarket_offers()

' declare variables
Dim prodlist, billamt

' receive values through inputboxes
prodlist = InputBox(" Enter the list of items purchased ")
billamt = InputBox(" Enter the total bill amount ")

' Offers based on multiple conditions using "and" and "or" keywords

'Flat 10 percent discount on the total bill if the bill amount is more than 10000
If billamt > 10000 Then
    billamt = billamt * (90 / 100)

' if soap is a part of the product list and if billing amount is more than 2500
ElseIf InStr(prodlist, "soap") <> 0 And billamt > 2500 Then
    ' offer flat 50
    billamt = billamt - 50
' if milk is a part of the shopping list and the customer has not got any other offer
ElseIf InStr(prodlist, "milk") And billamt <= 2500 Then
    'offer free curd
    prodlist = prodlist & ", curd - 200 gm"
End If

'Print final list and bill amount
Debug.Print "With all the offers included the bill amount is " & billamt
Debug.Print "The final list of products is " & prodlist

End Sub

Sample input:

Output:

Conclusion

So, what are you waiting for? Just start with coding simple tasks in VBA and become a pro on the go! You will never regret it. Start automating your tasks and let the system work on your behalf so that you can rest at ease. 😊

Initially things will look like a magic to you. Later you will understand what magic you did to your systems.