Why Is VBA Still Useful as a Language?

VBA is an easy programming language that comes packed with Microsoft Office applications as a developer-friendly gift. It is 95% similar to Visual Basic 6 with all its built-in functions, form components, structure, code editor style, and so forth It is user-friendly because this also has the Microsoft Office app as its backend.

For example, let us assume an Excel document has some data that keeps changing daily. Data is loaded/pasted from some flat file that we receive. Using that data, we are expected to use several formulas and prepare a report. There is a catch here: a lot of logical thinking is required for the task that cannot be finished easily using standard templates with formulas.

Now, in this case, VBA comes to our rescue as it is already a part of the Excel document. An Excel document with VBA macros is stored as a “.xlsm” file which is “macro” enabled.

The VBA macros can be used with any Microsoft office application. I have used them widely with Excel and Access and found that they reduce much of my day-to-day manual work.

Let us take a close look at some features provided by the VBA macros, which may be possible only if we use licensed software. Also, there is no guarantee that all software offers these facilities:

1. Automation of Repetitive Tasks, Especially Within Word, Excel, and Access.

Examples:

• Calculation and preparation of reports.
  • Usage of a set of queries to update data in tables in Access
• • Repeated use of a systematic conditional formatting.Preparation of graphs, charts, repeated tasks of standard alignmentsPetty tasks like insertion of rows / cols in Excel based on some criteria . If this needs to be done on a huge range like 2000 rows or more, then macros are a blessing.
  • Convert the “countifs” formulas to a kind of Pivot tables. i.e. the filtered list to appear/the list to get filtered from the selected number just as in a pivot table.

Reference:

2. Usage in business

• On another note, VBA can also be in projects for small clinics, shops, tuition centers, home schools, manpower agencies, and many such small organizations that do not have branches.

• They ease the working by projecting the stored information whenever required and doing the required updates when we input any small change.

For example, a catalog can be displayed to a customer for purchase in a shop. At the same time, once the customer purchases something that can be recorded just once by the end user, a macro can be run to update the purchase details in all other data tables like stock, invoice, customer details,  and many more.

Reference:

3. Preparation of PowerPoint Slideshows for Business Meetings.

• Correction/common updates in all the prepared slides can be done in no time using VBA macros.

• A well-versed person can also prepare the entire slideshow using VBA macros from any Excel report if required. This saves a lot of time for the user.

Reference:

4. Handling of Files and Folders Within the System

• Organizing files, folders appropriately.
  • Searching for a specific file ( with specific data)
• • Import/export data into the Microsoft Office application
• • Conversion of file formats
  • Working on other files.

Reference:

5. VBA for Outlook

• Send out mass emails but still one by one based on conditions
  • It is possible to prepare an Excel sheet with the receiver’s name, email id, attachment to be used, and subject line to be used for each and connect the document to Microsoft outlook to send out the emails (one by one) because the attachment like a promotion letter may not be the same for all.
  • Let us imagine sending “the exam booked” email. Assume that a mass of 200 candidates applied for three different exams based on their levels. In that case, outlook VBA can use a conditional statement to choose from three different templates and attachments to send the correct email to each receiver/candidate.
  • Filter and sort the emails into folders
  • Ignore a set of emails which a specific content/subject line.

Though several features are already available in out for some of the points highlighted above, VBA adds some icing on top of the existing features, as we can always customize them and make the “impossible” possible!  

Reference:

Ten years ago, I tried my first VBA code on Outlook to send out my wedding reception invitation through outlook connected to Gmail. Do you think that’s crazy? Take a look at the images of the emails sent.

And you know what? Many called me back/responded to my email stating that they would definitely make it for the wedding since it was a personal invite!

But then, I told them that it was through VBA macros 😊. That was when I realized that it was a hobby for me.

Conclusion

The tasks cited in this article are only some commonly performed tasks using VBA macros. VBA’s capacity is immense in reducing our day-to-day mundane tasks on the system. It is a blessing for administrators and people who handle finance as they use the Excel application to the maximum extent.

And these are why people like me still cling to our favorite VBA, and I still keep writing articles on it to spread knowledge on the same. 😊