Macros vs. Visual Basic Modules
Requirement: Visual Basic Knowledge
File to download: None
Related Articles: Use Input Form for Parameter Query, Use Embedded SQL Query for better efficiency and security
Introduction
If you are a frequent Excel user, you certainly have encountered some repetitive tasks. Those tasks are tedious and depressing for sure. Luckily, Microsoft Office offers two ways to automate some of those repetitive tasks by using Macros or Visual Basic Modules.
Using Macros or Visual Basic Modules are not just convenient. They offer unmatched accuracy and efficiency than human hands. Some of the operations and calculations are so complicated that it will take a skilled person long time to learn them and to gain expertise to master them. Even after that, it may still take great pains to maintain them and to ensure the correctness of it. With Macros or Visual Basic Modules in place, a rookie can just press a button to get an ultra-complicated task accomplished in no time with perfect accuracy, provided that the Macros or Visual Basic Modules are written correctly.
Difference between Macros or Visual Basic Modules
Microsoft inherited the name of macros and some of its functionalities from Lotus 1-2-3. Today the terms Macros and Visual Basic (VB) Modules are used interchangeably by most people, even though they are not quite the same thing. In fact, Microsoft’s founder, Bill Gate, once wanted to advocate the name VB Module to replace the Macros, but most users still prefer calling any automation as Macros. Consequently, Microsoft yielded to the public and continued to using the name until now.
In Excel and Word, Microsoft uses the terms Macros and Visual Basic (VB) Modules interchangeably. However, Microsoft does make a difference when it comes to Access. You can see from the left illustration that Macros are separated from (VB) Modules because they are really two different things. When you right click one of the macros and choose the “Design” view, you can see that the macro is actually just a sequence of commands and steps to automate a simple task (below left illustration). If you click on one of the modules (below right illustration), it will show you the VB codes that automate a far more complicated task. The VB module is the entity that contains the VB codes. VB codes are essentially a programming language.
What are Macros and Visual Basic Modules
Macro is a record of keystroke sequence. The keystroke sequence comprises of mainly commands and functions. If you used Lotus 1-2-3 macros before, you know that a macro may look like this:
/rff2~~
It stands for the following keystroke sequence.
“/” to active the top-line menu
“r” stands for the command “range”
“f” first “f” stands for “format”, second “f” stands for “Fixed”
“2” is the number of decimal places for a fixed number format.
“~” is the ENTER keystroke
After you build the macro, you assign a trigger key stroke to activate such macro, for example, ALT+F3 key.
Using Macro is a great way to automate simple tasks but it lacks functionalities and flexibilities. Macros just save time on some repetitive keystrokes. If you want to do something more complicated, you need to learn to use Visual Basic Module. VB Module is thousands times more powerful. VB Modules allow you to do looping, calling subroutines, manpulating objects, querying data, creating conditions, handling errors, and many many more. VB Module can essentially do things that only up to your imagination.
Visual Basic is a programming language. It was developed from the BASIC language, an early popular programming language. However, I think Visual Basic is more like a mix of BASIC and PASCAL. Some of the VB features like calling subroutines and defining variables are not originated from the BASIC language. Microsoft has been investing a lot to developing the Visual Basic language for many years and the VB language is now one of the most powerful computer languages.
In addition, VB codes are way more efficient than queries in Microsoft Access, especially concerning Parameter Query. Parameter Query is a query that prompts the user to input a parameter before the query will be run. Below is an example how a Parameter Query looks like.
There are two ways to build the Parameter Query. The easier and more popular one is to prompt the user to input the parameters through a pop-up screen. The alternative way is to build a form and let the user input the parameters in a form. There are two advantages to use the second way. The first one is to allow the user to input multiple parameters in one form instead of multiple pop-up screens. Another advantage is that the user can re-use some of the parameters without typing them over and over again in the pop-up screens because the parameters are stored in a table. The parameter table and the other tables are joined together to be run by the query. However, there is a price coming with the second method: it dramatically slows down the query. The query can run ten times more slowly. To overcome such problem, VB Modules come in rescue. We can convert the query into SQL statement and embed such SQL statement into the VB Codes. The parameters will be the variables of the SQL statement. The result is about the same speed (or even a bit faster) as a pure query itself.
How to create Macros and Visual Basic Modules
Excel offers a very similar approach as Lotus 1-2-3 and makes it much easier to build the keystroke sequence by allowing you to record the keystrokes. First of all, you need to click the “Developer” on the ribbon.
If your Excel does not have the Developer tab on the ribbon, you need to follow these steps to make it show up:
Click the Office Button, and then Click the Application (Excel, Access, Word, etc.) Button;
The Excel Option window will pop up. Click the Popular button at the left. Check the “Show Developer tab” in the Ribbon option Under Top Option for Working with Excel. Click Ok button to finish editing.
Now you have the Developer tab. You can do something about the Visual Basic Module. You can record a macro, edit it, run it, and manage it.
The best way for a new learner of writing Visual Basic codes is first to record the code and later edit it. I have been using Visual Basic codes for many years but I am still using the record function all the time. It saves me a lot of time and enhances the correctness of the codes. After I record a macro, I usually add certain codes, such as OFFSET, DIM, looping, create and call subroutines, and error handlers, etc., to make it work as what I want. It’s challenging but it’s a lot of fun. You gain a sense of great accomplishment when you can automate certain “boring” tasks and make it do what you want to do.
I am not going into details how to write basic Visual Basic codes. I would prefer to spend time on intermediate and advanced areas rather than the basic stuffs. There are some good and free tutorials on the internet that you can learn from them. Please try this website: https://channel9.msdn.com/Series/Visual-Basic-Development-for-Absolute-Beginners.
All the downloadable Excel files in my website are in XLSX or XLSM format. XLSX files have no VB modules. XLSM files contain one or multiple VB modules in them. You need to enable the macros to use the functionalities in the VB modules. I highly recommend you scan all the files for virus infection first after you download but before you open them. I will not be liable to any virus infection in your computer that is caused by my files.
Important considerations when creating Macros or Visual Basic Modules
I used to work with a consultant from a prestige consulting company which does not exist anymore now. Maybe he wanted to justify his high consulting price to the hiring company or he just enjoyed to impressing people. He created some Visual Basic Modules that were totally unnecessary. Some Excel functions can readily replace his VB Modules and do the tasks in a much better way. Apparently he was not very good at writing VB Modules either. His VB Modules took painfully long time to run and contained errors in his logic. Unfortunately, he was also an arrogant person that he took all comments as offenses and humiliations to his intelligence. Eventually, we had to let him go and scraped all the work that he did.
This is a true experience. I wanted to point out a few considerations when you try to create Macros or VB Modules:
- If there is a simpler way of doing the same thing, use the simpler way. If a formula can be used to do the same job, please try to refrain from using Macros or VB Modules.
- Creating unnecessary Macros or VB Modules does not make you look smarter, especially if your boss or your clients know more than you. Don’t underestimate how much your boss and your clients may know. Be warned that your intention can be backfired.
- Macros or VB Modules increase the difficulty of maintenance and significantly reduce the number of people who can maintain them. Unless your job security is your highest priority, Macros or VB Modules should always be the last resort to go to.
- Writing Macros or VB Modules should use the simplest logic. I have seen people write hundreds of lines of codes that can be replaced with just a few lines. Convoluted and redundant coding will only lead to problems. If your VB Modules take a long time to run, please check your coding.
- Wisely use Looping and Subroutine Calling to reduce redundant coding.
- Insert enough comments to make the coding more easily to be read. It’s not just for the people who will take over your work, but also for yourself. You will be amazed to find out how puzzling some of the coding you did just a few months ago when you did not put down enough comments to explain your own logic.
- Protect your codes with passwords or save your MS Access files into MDE or ACCDE file type.
- Test your codes thoroughly and extensively. Imagine what might happen to your codes when users insert a column or delete a row. Insert necessary error handlers to avoid errors like division-by-zeros and no data.
