Even experienced VBA developers struggle to foresee issues that may come up at a future point. Whether during development, or after a program is rolled out to users, bugs and glitches can lead to headaches and awkward explanations. To help avoid these issues and ensure our VBA projects run as intended, I’ve put together a list of 10 assumptions which should be avoided while coding in VBA.
A Range’s Address Will Not Change
If you’re coding in VBA, it’s likely that you are making use of the Range object. This permits VBA to read, write, and update values on a spreadsheet. The easiest way to reference the Range object is through a range address, such as “B5”. However, even with proper planning, range addresses may change as the project progresses. Something as innocent as inserting a row at the top of a spreadsheet will move that “B5” range down to “B6”, taking its address with it. Your “B5” range reference in VBA now reads a blank value.
One way to avoid this error is to utilize the Range object’s Name property.
We Can Assign ActiveWorksheet Scope to Everything
Whenever a VBA procedure interacts with the Excel application, it’s usually through the Range and Worksheet objects. Worksheet scope is used to narrow down where the impact occurs. As an alternative to naming a specific worksheet, VBA permits the use of “ActiveWorksheet” to specify worksheet scope as the current active (selected) worksheet. While this provides some flexibility and may work in most instances, the use of ActiveWorksheet may backfire as your project grows.
Let’s say that you start your VBA project with a single worksheet, named “Data”, where a VBA procedure moves some cell values. The procedure is activated with a button control on the same sheet, and data is moved by specifying ActiveWorksheet along with the range addresses.
Over time, the project grows and you decide to move all user-facing controls to a new worksheet, named “User Interface”. You move the button which activates the “data move” script to this spreadsheet. This is a great idea because it provides the user with central control area for all VBA functionality.
However, the “User Interface” now hosts the button, forcing the “User Interface” sheet to be the active worksheet when the operation runs. This would have the unintended result of the procedure attempting to move data on the “User Interface” sheet instead of the “Data” sheet. Like all things with VBA, the undo button will not help you.
Read more about assigning worksheet scope.
This Loop is Going to Work / I Don’t Need to Save
It can be risky to assume that your code will run well on the first try. Whether it be a misspelled variable or the infamous never-ending loop, there’s a good chance a critical error exists in your code.
The never-ending loop creates a particular pain, as it often makes Excel crash, causing you to lose progress in the project. This occurs frequently when building a Do loop dependent on a counter, but failing to include the actual counter.
Solution: save often, especially before testing. Regardless of how many minor changes are made, it’s possible that something was left out.
Very Hidden is Secure
Many novice Excel users make the assumption that hiding worksheets is a useful way to store confidential information. I’ve seen many Excel files sent from HR and Finance colleagues containing “hidden” workbooks with salary data. In some cases, other users have uncovered it. Such unintended disclosure of sensitive information has the potential to cause conflicts within your department or organization.
VBA permits access to the constant xlVeryHidden, which some users believe is more protective than the normal “Hide Worksheet” functionality. It’s useful for preventing users from unhiding the sheet through the worksheet toolbar (it won’t show up in the list of sheets to unhide). However, once the file gets to a user who knows how to open the VBA editor, it only takes a simple change to worksheet properties to make the worksheet to appear on the application side.
Bottom line: never store confidential or sensitive information in an Excel workbook. No matter how clever you think you are, someone will figure out how to get to it.
Worksheet Passwords Are Secure
Worksheet passwords are about as secure as the password “123456”. A simple google search for “Brute Force Excel Password” will return countless 3rd party programs to crack an Excel worksheet or workbook password. But you don’t even need a 3rd party program to crack an Excel password — a few lines of code in VBA will do this as well.
Worksheet passwords are great for preventing users from inadvertently overwriting formulas, modifying layout, or otherwise messing with spreadsheet functionality. It’s not a securing mechanism, however, but rather a social contract. For the most part, users will not attempt to crack a password unless they have reason to believe it’s restricting core functionality, or there’s some confidential information hidden in some hidden workbooks (see above). Some users may also believe they can improve on your spreadsheet by unprotecting a worksheet, only to create errors that require later cleanup.
While worksheet protection can help you maintain the structure of a worksheet, it’s important to let users know that it exists to protect the integrity of the worksheet, not to hide information.
Read more about worksheet passwords.
Users Will Enter the Correctly Formatted Data
There’s no shortage of rules that add up to 100, and a professor in a C++ course I took a few years back had one that is quite useful — he called it the 30/70 rule. In programming, 30% of your time is spent writing the framework for a program, while 70% of your time is spent writing code to manage user errors. Spreadsheet developers of various levels—whether a beginner using the application side of Excel, or an experienced developer coding in VBA—need to dedicate time to manage how users interact with a spreadsheet.
Even well intentioned users may accidentally type a letter in a number field, or put down a 9 digit zip code when you only want 5 digits. Incorporating validation can prevent these entries and provide users with the information they need to correct an error.
In Excel, there are two primary ways of incorporating validation features. The application-side has its own data validation tool, displaying an error message whenever incorrect data formats or types are inputted. Validation can also be done with VBA scripts, where constraints can be coded dynamically — e.g. acceptable values are based on data points which exist elsewhere.
Users Will Easily Understand Your Program
One of the most difficult things to do as a developer is to think about a program from a user’s perspective. Having spent hours and hours tinkering around with a program in development, we become the ultimate experts in how to use it. It’s easy to lose sight of how the program will appear when first viewed by a user. While you clearly understand its key functionality and how to operate it, a user will see an elaborate Excel file with input areas, buttons, and other controls.
It’s extremely important to provide proper documentation on any complex Excel spreadsheet being sent out to users. This goes for Excel files with VBA/macros, and those without. Better yet, embedding tooltips or instructions near each input or button may further extend its ease of use.
Your Project Will Run Efficiently in Production
Another issue one may experience is the failure to assess program efficiency under real-world conditions. Typically, in VBA, programs will be written in incremental steps with a small sample of testing data to try out various scenarios. However, once the program goes to the user, thousands of lines of data may be loaded for use. A number of issues may arise from this situation. You may realize those variables you declared as “Integer” should have been declared as “Long”. The spreadsheet that took under a second to process 20 rows of data may take many minutes to process a few thousand rows of data. The spreadsheet may crash with every use.
Need to speed up your project? See a comparison of VBA methods for effectively interacting with a spreadsheet.
I Don’t Need Comments in Code
Possibly more difficult than coding itself: adding comments to your own code. A well seasoned developer can easily assume that his/her code is clear and easy to understand, but any outsider will tell you otherwise. Even if an experienced coder eventually deciphers your code, it will take time to review and understand. If provided with clear and concise comments, future viewers of the code will be able to more easily pick up where you left off to make updates or modifications.
In many organizations, hesitation to embrace VBA centers around the transferability of knowledge after a developer leaves the organization. One way to mitigate this is to make VBA projects easily transferable to others with some knowledge of VBA. Comments in code is one of the best things you can do to make projects transferable and support capacity building within the organization.
This Spreadsheet Will Not Break
It’s easy to become overconfident about your VBA project. Be careful about sending projects out for testing and review before testing all features yourself. Nothing is more annoying to a user than encountering a major issue, glitch, or debug warning after trying a basic feature. If you’re sending out a work-in-progress file for basic testing, make sure you disclose to the testers which features you expect them to test. Let them know that the other features are still being built and may not work as intended.
It’s also important to support your VBA project after release. At some point in the future, changes to data formats or software upgrades may prevent the project from working properly. A common example I see relates to changes in accounting formats. Will your financial model still work if an organization decides to change their accounting category codes from 4 digits to 6 digits? If you originally declared the category codes as Integers (maximum number of digits is 5, up to 32,767), users of your VBA project will encounter a critical error.