I’ve change a little bit original code to add code lines to VBA module automatically. I think code looks much better and is simpler.

In previous post “Adding VBA code lines automatically” a lot of original code has been left. In following code a lot of elements have been cut – Microsoft Visual Basic for Applications Extensibility 5.3 reference doesn’t has to be add to VBA project.

In my opinion code lines are needed when error handling (On Error GoTo…) is turned on in procedures. Thanks to variable ERL user or admin knows where is error, if happened. Of course code will work without error handling, but when error happened debug mode will be showed. Debug mode is not a bad solution when VBA code is writing, but final user should not see this mode – therefor error handling is required.

In order to run code module name where code lines should be added has to be pasted to following code by clicking F5 or F8.

Adding code lines:

' Source:
' https://www.mrexcel.com/forum/excel-questions/576449-code-line-numbers-vba.html
' https://windowssecrets.com/forums/showthread.php/172507-line-numbers-in-VBA-code


Private Sub AddLineNumbers()
' ADDING CODE LINES IN VBA MODULE

Dim i As Long, j As Long
Dim ModuleName As String
Dim ReplaceJump, LineValue, PrevLineValue, YesNo

ModuleName = "YourModuleName" 'Paste module name where code lines should be added

YesNo = MsgBox("Would you like to add code lines to module:   '" & ModuleName & "'?", vbQuestion + vbYesNo, "AddLineNumbers...")
If YesNo = 6 Then
    With ThisWorkbook.VBProject.VBComponents(ModuleName).CodeModule
    
        j = 1
        For i = j To .CountOfLines
            LineValue = .Lines(i, 1)
            If Left(Trim(LineValue), 6) = "Public" Or Left(Trim(LineValue), 7) = "Private" Or _
                    Left(Trim(LineValue), 3) = "Sub" Or Left(Trim(LineValue), 8) = "Function" Then
            
                    For j = i + 1 To .CountOfLines
                        LineValue = .Lines(j, 1)
                        If Left(Trim(LineValue), 7) = "End Sub" Or Left(Trim(LineValue), 12) = "End Function" Or _
                            Left(Trim(LineValue), 6) = "Public" Or Left(Trim(LineValue), 7) = "Private" Or _
                            Left(Trim(LineValue), 7) = "Declare" Or Left(Trim(LineValue), 1) = "#" Then
                            Exit For
                        End If
                        
                        ' ----------------------------
                        ' EXLUSIONS
                        
                        PrevLineValue = .Lines(j - 1, 1)
                        
                        If Len(Trim(LineValue)) = 0 Then
                            GoTo ReplaceJump
                        End If
                        
                        If Right(PrevLineValue, 1) = "_" Then
                            If j < 100 Then
                                .ReplaceLine j, "    " & vbTab & vbTab & LineValue
                            Else
                                .ReplaceLine j, "    " & vbTab & LineValue
                            End If
                            GoTo ReplaceJump
                        End If
                        
                        If Right(LineValue, 1) = ":" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 4) = "Case" Then
                            If j < 100 Then
                                .ReplaceLine j, "    " & vbTab & vbTab & LineValue
                            Else
                                .ReplaceLine j, "    " & vbTab & LineValue
                            End If
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 3) = "Debug" Then
                            GoTo ReplaceJump
                        End If
                       
                        If Left(Trim(LineValue), 1) = "'" Then
                            .ReplaceLine j, vbTab & vbTab & LineValue     'ori
                            GoTo ReplaceJump
                        End If
                        
                        ' ----------------------------
                        ' ADDING CODE LINE

                        If j < 100 Then
                            .ReplaceLine j, CStr(j) & ":" & vbTab & vbTab & LineValue
                        Else
                            .ReplaceLine j, CStr(j) & ":" & vbTab & LineValue
                        End If
ReplaceJump:
                    Next j
            End If
        Next i
    End With
    MsgBox "Code lines has been ADDED." & Chr(10) & "Lines quantity: " & j & ".", vbInformation, "CONFIRMATION..."
Else
    MsgBox "Canceled."
End If 'If YesNo

End Sub

Removing code lines:

Attention: Code will be work properly only when code lines has been added by adding procedures from this post.

' Source:
' https://www.mrexcel.com/forum/excel-questions/576449-code-line-numbers-vba.html
' https://windowssecrets.com/forums/showthread.php/172507-line-numbers-in-VBA-code

Sub RemoveLineNumber()
' MODULE CODE LINES REMOVING

Dim i As Long, j As Long
Dim ModuleName As String
Dim LineValue, PrevLineValue, YesNo

ModuleName = "YourModuleName" 'Paste module name where code lines should be added

YesNo = MsgBox("Would you like to REMOVE code lines from module:   '" & ModuleName & "'?", vbQuestion + vbYesNo, "RemoveLineNumber...")
If YesNo = 6 Then
    With ThisWorkbook.VBProject.VBComponents(ModuleName).CodeModule
    
        j = 1
        For i = j To .CountOfLines
            LineValue = .Lines(i, 1)
            If Left(Trim(LineValue), 6) = "Public" Or Left(Trim(LineValue), 7) = "Private" Or _
                Left(Trim(LineValue), 3) = "Sub" Or Left(Trim(LineValue), 8) = "Function" Then
            
                For j = i + 1 To .CountOfLines
                    LineValue = .Lines(j, 1)
                        If Left(Trim(LineValue), 7) = "End Sub" Or Left(Trim(LineValue), 12) = "End Function" Or _
                            Left(Trim(LineValue), 6) = "Public" Or Left(Trim(LineValue), 7) = "Private" Or _
                            Left(Trim(LineValue), 7) = "Declare" Or Left(Trim(LineValue), 1) = "#" Then
                            Exit For
                        End If
                    
                    ' ----------------------------
                    ' REMOVING CODE LINES
                    
                    If Left(Trim(LineValue), 1) Like "#*" Or Left(Trim(LineValue), 2) Like "##*" Or _
                         Left(Trim(LineValue), 3) Like "###*" Or Left(Trim(LineValue), 4) Like "####*" Then
                             .ReplaceLine j, Right(LineValue, Len(LineValue) - 8)
                    Else
                         PrevLineValue = .Lines(j - 1, 1)
                        
                         If Right(PrevLineValue, 1) = "_" Then
                             If Left(LineValue, 8) = "        " Then
                                 .ReplaceLine j, Right(LineValue, Len(LineValue) - 8)
                             Else
                                 ' nothing happened
                             End If
                         End If
                         
                         If Left(Trim(LineValue), 4) = "Case" Then
                             If Left(LineValue, 8) = "        " Then
                                 .ReplaceLine j, Right(LineValue, Len(LineValue) - 8)
                             Else
                                 ' nothing happened
                             End If
                         End If
                         
                         If Left(Trim(LineValue), 1) = "'" Then
                             If Left(LineValue, 8) = "        " Then
                                 .ReplaceLine j, Right(LineValue, Len(LineValue) - 8)
                             Else
                                 ' nothing happened
                             End If
                         End If
                    End If
                Next j
            End If
        Next i
    End With
    MsgBox "Code lines has been REMOVED.", vbInformation, "CONFIRMATION..."
Else
    MsgBox "Canceled."
End If 'If YesNo

End Sub