line numbers in VBA code

Topic

New Reply

I am trying to debug my excel macro but i am having a hard time because the “Erl” feature doesn’t work unless code line numbers have been added. Is there a quick way to add code line numbers (aside from typing them in manually)? Thanks for any responses!

Reply | Quote

Replies

Mike,

Welcome to the Lounge as a new poster. :cheers:

Take a look at this macro.

I would recommend using this on a COPY of your workbook so you can maintain the original un-numbered code.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Mike,
By default, I don’t think there’s any quick way to add line numbers. There are code-editing items out there that can do that sort of thing if you google it, one is called codeliner or something like that. Other than that, you could try writing a macro that will do it by using the ReplaceLine method of the CodeModule object. I hope this helps!

Reply | Quote

Mike,

Vince is correct. The following code will add the line numbers directly into the code without copy/pasting. Replace the module or form and procedure names (in blue) with those in your project. It will take into consideration where the procedure is located and any declarations in the declarations section of the module. It will exclude any lines that cannot have a line number and add only one line number to a code line than spans multiple lines.

HTH,
Maud

Sample code prior to running “AddLineNums”:
42718-Mike1

Sample code after running “AddLineNums”:
42719-Mike2

Code:

Public Sub AddLineNums()
[COLOR=”#008000″]’————————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim Procedure As CodeModule, Code As String, Module As String, Proc As String
    Dim StartLine As Long, EndLine As Long, LineNum As Integer, I As Long
    [COLOR=”#0000FF”]Module = “Module1”[/COLOR] [COLOR=”#008000″]’CHANGE TO MODULE NAME[/COLOR]
    [COLOR=”#0000FF”]Proc = “UpdateStats” [/COLOR] [COLOR=”#008000″]’CHANGE TO PROCEDURE NAME[/COLOR]
    Set Procedure = ThisWorkbook.VBProject.VBComponents(Module).CodeModule
    StartLine = Procedure.ProcBodyLine(Proc, vbext_pk_Proc)
    EndLine = Procedure.ProcCountLines(Proc, vbext_pk_Proc) – StartLine
    LineNum = 10
[COLOR=”#008000″]’————————————–
‘CYCLE THROUGH LINES OF CODE[/COLOR]
    For I = StartLine To EndLine
[COLOR=”#008000″]’————————————–
‘EXCLUSIONS[/COLOR]
        If InStr(1, Procedure.Lines(I, 1), “Sub”, vbTextCompare) > 0 Then GoTo NextLine
        If InStr(1, Procedure.Lines(I, 1), “Function”, vbTextCompare) > 0 Then GoTo NextLine
        If I > 1 Then
            If Right(Procedure.Lines(I – 1, 1), 1) = “_” Then GoTo NextLine
        End If
[COLOR=”#008000″]’————————————–
‘ADD LINE NUMBER[/COLOR]
        Procedure.ReplaceLine I, LineNum & Chr(9) & Procedure.Lines(I, 1)
        LineNum = LineNum + 10
NextLine:
    Next I
[COLOR=”#008000″]’————————————–
‘CLEANUP[/COLOR]
Set Procedure = Nothing
End Sub

Reply | Quote

Mike,

I forgot to mention that you have to reference the Microsoft Visual Basic for Applications Extensibility 5.3 library.

Alt-F11 to open VB editor > Tools > Reference… > scroll down to Microsoft Visual Basic for Applications Extensibility 5.3 library > Tick the checkbox next to it > OK.

Maud

Reply | Quote

I’ve used MZ-Tools 3 (free) for several years and have just purchased MZ-Tools 8 for vb/vba. It may be more than you want — it does a lot more than adding and removing line numbers, and works within the vbe.

Reply | Quote

I will second the MZ-Tools (Click Here) recommendation. Many VBA developers I know say it is a MUST HAVE for anyone working in VBA (Word Macros).

The great thing about MZ-Tools is that is a VBE add-in which allows it to work in ALL Office applications that use the VBE editor for VBA code. It even works across multiple version of Office on the same PC.

Reply | Quote