Tips for Writing VBA in Excel
Learn how to write VBA code fast and efficiently
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
Tips for Writing VBA in Excel
Listed below are nine tips for writing VBA in Excel. VBA for Excel introduces countless possibilities for custom calculations and automation. Often, code can run into the hundreds or even thousands of lines. As the complexity and size of code increases, it becomes increasingly important to write fast and efficient code that is easily understood by colleagues. Doing so will make you an invaluable asset to your team in maintaining and developing Excel VBA models.
1. Comment your code to improve readability
Have you ever opened a colleague’s VBA model and spent the next hour trying to work out exactly what their code was doing? With that in mind, imagine how professional you’d look if your code was crystal clear to anyone who read it, even if you weren’t there to explain it.
Writing comments is the most important element of writing VBA code, and it will ensure that you can easily understand your own code a few months down the line. You’ll also impress colleagues and quickly gain a reputation for preparing outstanding documentation, thus reducing training time for future users.
2. Name variables and functions using clear and relevant names
Writing VBA variable and function names that can be clearly interpreted will save users huge amounts of time and allow them to clearly follow the structure and flow of your code. Variable or function names such as “test1” or “first_integer” will create countless headaches for those who try to read your VBA code.
The routine below pastes the US inflation rate into the active cell if it’s available. Notice how much clearer the second example is than the first.
Bad example:
Good example:
3. Plan your work to help create simple, efficient VBA code
It’s not uncommon to get halfway through writing a code project before realizing that you should’ve structured it differently or created a separate function for specific elements of your code. Planning the structure of your VBA, what you want it to achieve, what loops you’ll need etc. will save you time in the long run. It also gives you time to think about potential errors that may arise along the way and how you plan to deal with them.
4. Use macros to quickly work out the syntax
For most people, macros offer the first glimpse into the world of VBA and coding. Even for experienced coders, macros are a great way to quickly understand the VBA syntax needed to execute a particular function or method.
Suppose you didn’t know how to save a file using VBA. By pressing Record Macro, you can carry out the steps manually, and the VBA editor will record those steps in code. Magic! Now, you can reuse and adapt the code in your own routines.
5. Learn to write your own code; don’t rely on macros!
As mentioned above, macros are incredibly helpful in learning how certain pieces of code are written. It’s important to recognize, however, that macros are inherently unreliable and not future-proof because they are hard-coded and cannot adapt to future changes in your worksheet structure.
Suppose you’ve recorded a macro that performs various formatting changes to your worksheet. Part way through the code, it selects column I, and then deletes it. Suppose then that next month, the file includes an additional column; now your macro will delete the wrong column. Learning how to code yourself will allow you to create loops and search for the correct column before correctly deleting it.
6. Use Option Explicit to avoid missing values
Using “Option Explicit” at the start of a VBA code module is a valuable way to avoid spelling errors in your code.
In the left example below, Option Explicit is not enabled. The code runs with no errors, but it always returns the answer of zero. Why? The reason is that the variable MonthIncme is spelled incorrectly, and therefore references nothing. Nothing divided by something is always zero.
When the code in the second example is run, Option Explicit detects that we’ve undefined variables in our code and returns a Compile Error. It highlights the undeclared variable and informs us of the problem. You should always use Option Explicit at the top of each code module before any of your routines.
7. Improve speed by keeping object selections to a minimum
If you’re new to VBA and are wondering why your code is running very slowly, excessive use of the .Select method is a prime suspect. Object selection should be avoided unless absolutely necessary, especially when dealing with large amounts of data or loops. Selecting objects in VBA is incredibly slow and is usually not necessary. Recorded macros often include a lot of .Select methods, so it’s important to learn how to avoid them.
In the first example below, we are trying to find the running total of 1,000 cells in our worksheet. Each time we reference a new cell, we select it, and then take its value. However, the selection of the sheet or cell is not necessary at all. We can simply get the cell value. A timer was used to record the time taken to run each routine, with the second example being 50 times quicker!
8. Re-use common functions to save time
Keeping your most common VBA routines (subs) in one place is a handy way to reduce the time you spend coding or model building. Here’s an example from our “Top 10 VBA Routines” article, which takes the selected cells, unmerges them, and applies the same headings to each cell. You can even assign a keyboard shortcut to the macro to make it lightning fast to use.
Look out for our blog on re-using code (coming soon), which will focus on add-ins and personal code modules.
9. Test your code regularly to avoid run-time errors
The tip above probably applies to most projects at work, but it is particularly relevant to coding and, therefore, VBA for Excel. Testing your code ensures that mistakes and errors are detected and that exceptions that may arise at run-time can be captured with appropriate error catching (see our article on errors in VBA here).
Testing should start with you as the code developer. You should test the code itself, and then test it within a non-live or non-impactful environment.
Once you’re satisfied with it, you should let a colleague test your Excel model, ideally without giving them any clues at all as to what buttons they should press or what inputs they should enter. Your model should speak for itself. The test will be a good indication of what will happen when your code goes live.
Additional Resources
Thank you for reading our tips for writing VBA in Excel. CFI is the official provider of the global Commercial Banking & Credit Analyst (CBCA)™ certification program, designed to help anyone become a world-class financial analyst. To keep advancing your career, the additional CFI resources below will be useful:
