VBA macros for Excel: How to improve structure and speed
Back to basics
VBA stands for Visual Basic for Application in Excel. This is a powerful built-in programming language that allows you to write your own functions or commands in an Excel spreadsheet.
When you use the VBA programming language to write out a task, it is called a macro. VBA macros carry allow you to automate tasks in Excel.
Once you have established a functional macro, there are usually some possibilities to improve its structure and speed.
Want to learn more about VBA for Excel? Read the following articles:
Ready for Improvements
Depending on the macro you created, making adjustments to its structure can impact its overall accuracy and speed. And some improvements are more easily accomplished than others. Consider the following example:
If you write a VBA macro that produces lots of on-screen action, by simply turning off screen updating, you can significantly speed it up.
To do so, execute this statement:
Now, if your macro uses a custom dialog box, it's important to make sure to turn screen updating back on before displaying the UserForm. Otherwise, moving the dialog box on the screen leaves an ugly trail.
The following are general macro tricks (methods) you can apply to improve your VBA macros structure and speed. But remember, how you improve your macro should be dependent on what you want it to accomplish.
Exit statements
Exact Lookup functions in Excel stop looking for matches once they have returned the first match. This is done to reduce the calculation time of a Lookup function. Why would you keep looking for a match when you have already found one?
For this reason, it is possible to build an Exit in your loops. If you want to find a name in a table, there is no point in continuing to search once you have found the name, right? This will only increase the runtime. So, adding an Exit statement can improve the structure and runtime of your macro.
- Adding an Exit statement: The way to exit a loop depends on the type of loop you are using. Check out the table below to see the different Exit statement.
Consider the following Exit statements and see the following image to see the difference between the two: For loop -> Exit For and Do loop -> Exit Do.
As you can see, the blue blocks show an Exit Do in a Do loop and the orange blocks show an Exit For in a For Next loop.
- If the Exit argument is coupled to an If...Then function in the loop, during each run of the loop, Excel checks whether the condition is met. If it is, it exits the loop.
- You can use an Exit statement when working with loops. However, keep in mind that it is often possible to create an Exit statement in the condition of the loop. For example, the Exit statement ‘For x = 1 to 10’ can easily be omitted by replacing it with ‘For x = 1 to 5’.
Watch your variables with the Watch window
Variables help you keep your code organized and make it easy to follow. They also help you to perform some neat actions such as loops and triggers. All in all, they are very useful. However, in longer pieces of code there might be numerous variables and it can be hard to keep track of them all. If your code doesn’t work, the chances are that there is something wrong with your variables.
To improve your VBA macros’ structure, use the Watch window. This lets you watch the variables you are interested in, even if they show up as "out of context" which means that they are not active at that moment.
To add a variable to the Watch window, right click it in the Code window and choose Add Watch. At the Add Watch menu you can adjust the different options, or you can directly click OK. The Watch window will automatically appear, and it will show the variable you have just added. See the following animation for an example.
Closing the Watch window (x) will not remove any Watches; they’ll still be present when you reopen the window. In order to delete a watch, right click it in the Watch window and select Delete Watch.
Remember that when your code creates and error, you have to debug.
Warp speed
In general, simplifying the structure of your macro (or making it more precise) not only improves its structure but its speed as well. If you're looking for your macros to go warp speed, consider the following tips:
- Turn off ‘Automatic Calculations’: This prevents calculations from occurring while executing or running macro. This allows you to wait until a set of actions have been completed and instruct calculations to update at the end once, rather than after every update.
- Disable Events: This will help prevent or stop endless loops while executing or running macros, especially if you have worksheet or workbook event
- Edit Recorded Macros by removing Select: The Select method can cause macros to be slow and prone to runtime errors because it is explicitly referring to one or more specific ranges, sheets, or shapes. So, its good practice to review the code for Select and change them to Range references.
Go with the flow
By judiciously applying any the above improvement tricks (methods), you can ensure your macro has the best structure and is running at top speed. Continue learning through our extensive VBA track with over 70 Challenges. Sign-up at 5miles for a free two-week trial now.