I wrote a post several years ago about adding line numbers to your VBA code. To this day, it remains my post popular post ever. Why? It’s because they can be a useful tool in your debugging arsenal.
Taking a walk down memory lane, old-school computer programmers will remember that line numbers were crucial back in the day. Whenever there was a problem with your program, you were always referred to a particular line number, identifying the command that was failing, which gave you a starting point for your debugging efforts.
Line numbers have become obsolete in modern languages like VBA. They are no longer generated by the code editor, and even when available, they are often not referenced in error messages. Without them, how do you know where your code is failing?
In VBA, by default, error messages are displayed in a dialog box that includes a “Debug” button. If you see that button, click it. Doing so will open the code window, where you’ll see a line highlighted in yellow. That’s the line of code that’s failing, and thus, you’ve got your starting point for troubleshooting.
That behavior can be overridden, though. Some VBA programmers use an “On Error” statement, which tells the program what to do when an error occurs. Using this statement circumvents the aforementioned dialog box, so you won’t have that handy “Debug” button in this case. Now the onus is on you to communicate any information that is necessary to help with debugging. There are lots of ways to accomplish this task, including:
Manually add line numbers, and reference them in your error handling routine. See my previous blog post for further info.
Include multiple error handlers, each with a different error message. Let’s stay I’ve got a routine that executes three steps: open a text file, add a line to that file, and close the file. I could create three separate error handers for this code. One would execute if there is an error in the open step; another would execute when I’m trying to add the line; and the third would execute when errors occur in the close step. Here’s an example of what that might look like:
Add a variable, and use it to identify the section of your code. In the example I mentioned above, I’d set the variable to “Open”, and then “Add Line”, and then “Close”, respectively. In my error handling routine, I’d display this variable as part of my error message. Here’s a sample of that:
These are just a couple of ideas of how you can debug your program without using line numbers. Personally, I prefer not to use line numbers, because they are a manual effort to maintain, and because I’ve mistakenly used the same line number more than once, which causes confusion.
One final thought on debugging: short procedures are easier to debug than long ones. If you keep each procedure short, and include error handling in each, you’ll make it easier to debug.
I love working on Excel and Access VBA project, so reach out to me via email or phone if you'd like to discuss yours.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2022. All rights reserved.