Archive

Please reload

Tags

Please reload

VBA Line Numbers

March 29, 2017

 

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.

 

 

© North Port Solutions, LLC, 2017. All rights reserved.

 

Photo Credit

Timer: CC Image "Numbers" courtesy of morebyless on Flickr (CC BY 2.0)

 

 

 

 

Please reload

Recent Posts

August 28, 2017

April 18, 2017

Please reload

978-582-9550

522 Burrage St
Lunenburg, MA 01462
USA

  • Facebook Social Icon
  • LinkedIn Social Icon

©2020 by North Port Solutions, LLC. Proudly created with Wix.com