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!

Joining Cells in Excel

This article discusses options when trying to join together cell values in Excel.

Many times in Excel you may want to combine several cells into one cell. For example, if your sheet has dates scattered across different cells, you may want to bring them all together into one comma-delimited list of dates. Excel has several formulas that can do this, and here they are:

Excel FormulaHow Good Is It?
CONCAT
TEXTJOIN
CONCATENATERANGE

Let’s discuss each of these formulas one by one. The CONCAT formula replaces the old CONCATENATE formula. CONCAT improves upon CONCATENATE because you can use it to join a range of cells rather than just cells. For example, CONCAT(A1:A3) brings together all cells between A1 and A3. However, one drawback of CONCAT is that there is no way to specify a delimiter, such as a comma (,), to separate each value within the result. Another drawback of CONCAT is that the values will not be formatted, which can be critical if you are working with dates, for example.

The TEXTJOIN formula is better than CONCAT, because you have the option to specify a delimiter. So, TEXTJOIN(“,”, A1:A3) results in a comma-delimited list of the values drawn from A1 thru A3. There is a problem with TEXTJOIN however – the cells being joined will not be formatted in the result.

That brings us to the ultimate solution – the CONCATENATERANGE formula. The CONCATENATERANGE formula does everything the TEXTJOIN and CONCAT formulas do, in addition to formatting the result using the individual cell formats within the range. The other big plus of CONCATENATERANGE is that it works with ALL versions of Excel.

If you’d like to use the CONCATENATERANGE formula, you’ll need to download it from its creator, Tumbling Wave Software. Here is a direct link to download the CONCATENATERANGE formula.

So, I hope you’ve found this article useful. To all my Excel friends:

Office Code Readability

This post discusses ways to ease your ability to read someone else’s code.

Did you ever look at someone’s Office macro and think to yourself, “Boy, this is hard to read”? I know I have, and it’s particularly bad when he (or she) is using line numbers. Though line numbers help in identifying errors during debugging, they become more of an irritant when you are trying to read the code for some other purpose. Of course, we want to be able to debug, and we want to be able to read – can we have it both ways? – yes, we can!

To make the code more readable, we could go thru every line of code and manually delete each line number, but that would be quite a chore. Or, you could print out (or save) a version of the code before adding-in the line numbers. Because code changes so often, managing both versions seems like an impractical solution as well.

To make the code more readable, we could go thru every line of code and manually delete each line number, but that would be quite a chore. Or, you could print out (or save) a version of the code before adding-in the line numbers. Because code changes so often, managing both versions seems like an impractical solution as well.

The best way I’ve found to quickly remove line numbers is to use this handy little tool:

VBA Line Numbers with CodeLiner

It’s pretty cool – once installed, you will see a new toolbar in your VBA Editor, whether it’s Excel, Access or Word. The toolbar has a button that simply hides all line numbers in your project! With all the line numbers hidden, the code has instantly become much more readable. Thankfully, there is another button on that same toolbar that will re-insert the line numbers, when you want to go back to debugging your code.

So, code happy with readable code, everybody!