What's the point of adding line numbers to VBA code? Only one good reason in my opinion … error handling. With the (undocumented) Erl keyword you can get the line on which an error occurred which, obviously, makes investigating and fixing errors in your code much simpler. Having said that, you need to implement error handling to make this work … a good blog on error handling is here https://excelmacromastery.com/vba-error-handling/.
In that blog, the author Paul Kelly notes that "adding line numbers to your code manually is cumbersome … however there are tools available that will allow you to easily add and remove line numbers to a Sub".
He's right. There are such tools … and, of course, VBE_Extras is one such tool. In fact, VBE_Extras goes much further than that, actually allowing you to add/update or remove line numbers to any individual procedure (be it a Sub or a Function), Property, Module or to the entire VBA Project … with a single command.
Features of line numbering with VBE_Extras include:
Options to set the initial line number and the 'increment' for each subsequent line number
An option to set whether line numbering (when adding/updating line numbers to an entire Module or Project) resets for each procedure / Property or continually increases throughout each Module
Correctly handles line numbers that are 'references' … for example, if you have a statement 'On Error Goto 100' where 100 is an actual line number then using VBE_Extras to remove line numbers will not remove line number 100 (as it is required for your code to compile) ... and adding/updating line numbers will ensure that if the 100 is updated then it is also updated in the 'On Error Goto' statement (keeping your code compile-able)
Is 'attribute friendly' in that any existing VBA attributes will be preserved when line numbers are add/updated or removed (not familiar with VBA attributes? ... read this blog post)
Will clean up negative line numbers (it is possible to add them though you can't just type them in normally … but you really, really shouldn't)
In these screen snippets, the code is shown before any line numbers are automatically added by VBE_Extras (but note the two line numbers that are present in the code are both 'referenced') ...
... then line numbers are added to the entire Module (the Module consists only of these two small 'nonsense' procedures for demo purposes) which adds (and updates the existing) line numbers to the appropriate values ...
... then line numbers are removed (excepting for the two 'referenced' line numbers which VBE_Extras leaves in place).
Comments