A student asked me the other day if you can display line numbers on your VBA code. That got me to thinking about the fact that I don’t rely on line numbers any more. Back in the day, anytime a user called to report an error, the first question was always “What line number is the error on”. That was the gold standard for finding your errors. And yet, VBA doesn’t put line numbers on the code.
I realized that there are a couple of reasons why I don’t rely on line numbers any more. First, with VBA, there is a great set of debugging tools available which help you to locate the line that is having a problem. Whenever an error pops up, you can just click the “Debug” button to see which line is having a problem. You’ve also got breakpoints that you can use to help locate the problem, and a host of tools to evaluate your variables and properties. So finding the source of the problem isn’t as difficult as it used to be.
Second, VBA programs tend to be comprised of small blocks of code, rather than one behemoth program. Back when I was writing code in COBOL, for example, a single program could be hundreds of lines long. In VBA, that is certainly possible, but there is a tendency instead to break the program down into smaller components. You then call the components as you need them. This makes it a lot easier to find and fix your errors. By creating these little pieces, you can write, debug and test each individual unit before putting all of them together. It’s much easier to find your bugs when you are only working with a couple dozen lines of code in a single procedure. This tendency to write code in bite-sized pieces makes those line numbers less necessary.
That said, it’s still a good idea to add some location information to any error handling that you write for your programs. In each error handling routine, consider mentioning the name of the procedure. That might look something like this:
Sub MyWonderfulProcedure()
On Error Goto ErrHandler
‘ Lots of useful code here
Exit Sub
ErrHandler:
Msgbox “Error occurred in procedure MyWonderfulProcedure.” & vblf & _
Err.Description
End Sub
The only problem with this approach is that if you are fond of copying and pasting your error handling code (as I am), you have to be careful to remember to change the name of the procedure.
There is another alternative that goes back to those line numbers. Although VBA won’t put line numbers on the code for you, you can manually add line numbers, and then use the “erl” function to retrieve them. Here’s what that would look like:
Sub MyWonderfulProcedure()
On Error Goto ErrHandler
100: ‘ Lots of useful code here
Exit Sub
ErrHandler:
Msgbox “Error occurred in procedure MyWonderfulProcedure.” & vblf & _
Err.Description & vblf & _
“Line number: “ & Erl
End Sub
The first thing I had to do was create a line number. I did that by typing in “100:”. Notice the colon after the number? That makes this a label. That helps VBA to understand that it shouldn’t try to execute that part of the line. Take note also of the use of the “erl” object in the error handler. That object returns the last line number for you. Notice I said the “last” line number? That means you don’t have to number every line. If you want, you could assign a different “line number” to each procedure. Or you could decide to only put line numbers on lines that you think are likely to run into problems. Or maybe you’ll just put a number approximately every 10 lines, so that you’ll at least be in the ballpark.
So if you decide you just can’t live without the line numbers, here’s a way to get them back.
Need help with VBA? Set up a free consultation with me to review the issues you're having. I'll offer free tips if possible, or give you a quote for consulting services. https://www.northportsolutionsllc.com/book-online
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business 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, 2017. All rights reserved.
Photo Credit