Lines Numbers in Office Macros

This is an article to demonstrate how to show line numbers in your Office macros.

Why would you want to show line numbers in your Office VBA code?

  • Most importantly, line numbers help in debugging your code.

By default, it’s pretty difficult to find the location of an error in an Office macro because Office doesn’t keep track of the line number that has caused the error. Interestingly enough, Office provides us with a VBA function called “Erl” that is supposed to indicate the line where the error has occurred, but that function always returns zero (0) by default. Why? Because the “Erl” function requires line numbers to be present at the start of every line in your code. Argh!

So, what can you do to show line numbers? The most obvious solution is to manually type-in the line numbers. If manually typing hundreds of line numbers sounds tedious, that’s because it is, as experienced by the gentleman on the right.

A better solution would be to use a third-party tool to automatically show line numbers for you. One such tool is called:

VBA Line Numbers with CodeLiner

This tool, made by Tumbling Wave Software, will show (and hide) line numbers in your Excel, Access and Word files. It installs a toolbar in the VBA Editor, so you can just toggle the line numbers on and off. Once you have the line numbers showing, you can actually use the “Erl” function as it was intended – to find the line number of your error!

If you want to find out more about VBA Line Numbers with CodeLiner, you can download a copy on the Tumbling Wave Software website. I wish everyone happy coding!