vba

Label VBA code with line numbers


Is there a way to quickly label VBA code with line numbers such as shown below?

Sub sample()

     Dim i As Long

10   Debug.Print "A"
20   Debug.Print "B"
30   Debug.Print "C"
40   Debug.Print "D"    
50   MsgBox "Done."

End Sub

Solution

  • As you can see in this answer Log what line error occurs: vba, there are two ways to do that:

    1. Either manually (not fast, so doesn't answer your question)
    2. Or with some add-in, like the one mentioned in the linked answer
    3. Or write your own add-in using VBA Extensibility to do that.

    Edit: I never worked with VBE extensivbility library so I would suggest consulting other sources, for example here: http://www.cpearson.com/excel/vbe.aspx
    Repurposing code written by Chip Pearson I would try the following, although this I haven't tested it:

        With VBComp.CodeModule 'VBComp is VBIDE.VBComponent
            For N = 1 To .CountOfLines
                If Trim(.Lines(N, 1)) = vbNullString Then
                    ' blank line, skip it
                ElseIf Left(Trim(.Lines(N, 1)), 1) = "'" Then
                    ' comment line, skip it
                Else
                    .Lines(N, 1) = N & " " & .Lines(N,1)
                End If
            Next N
        End With
    

    Keep in mind, that you need to make sure that you check for various cases, for example for the lines that are already numbered.