Adding VBA code lines automatically

I think turning on error handling make sense when code lines are added to our code. Unfortunately VBA console not predict this kind of solution. Following code allowed add code lines automatically.

Original code has been posted by mikerickson.  I’ve customized it a little bit. As it was originally assumed code adds lines in whole module, but according to second source some exclusions have been added.

To add lines name of module has to be pasted to lines code. Macro starts normally as test your own macro.

I suggest to use code from this post “Code lines added automatically to VBA module”. There is an code update.

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

Adding VBA code lines automatically:

' Attention!!!! Following reference has to be addded to project
' Microsoft Visual Basic for Applications Extensibility 5.3.

' 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()
Dim i As Long, j As Long, lineN As Long
Dim procName As String
Dim startOfProceedure As Long
Dim lengthOfProceedure As Long
Dim ModuleName As String
Dim ReplaceJump, LineValue, PrevLineValue, LenLine, YesNo

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

YesNo = MsgBox("Would you like to add code lines to module:   '" & ModuleName & "'?", vbQuestion + vbYesNo, "QUESTION...")
If YesNo = 6 Then
    With ThisWorkbook.VBProject.VBComponents(ModuleName).CodeModule
        For i = 1 To .CountOfLines
            procName = .ProcOfLine(i, vbext_pk_Proc)
            If procName <> vbNullString Then
                startOfProceedure = .ProcStartLine(procName, vbext_pk_Proc)
                If i = startOfProceedure Then
                    lengthOfProceedure = .ProcCountLines(procName, vbext_pk_Proc)
                    For j = 2 To lengthOfProceedure - 2
                        lineN = startOfProceedure + j
                        
                        ' ----------------------------
                        ' EXCLUSION
                        
                        LineValue = .Lines(lineN, 1)
                        PrevLineValue = .Lines(lineN - 1, 1)
                        
                        If Len(Trim(.Lines(lineN, 1))) = 0 Then
                            GoTo ReplaceJump
                        End If
                        
                        If Right(PrevLineValue, 1) = "_" Then
                            .ReplaceLine lineN, "    " & vbTab & vbTab & .Lines(lineN, 1)  'ori
                            GoTo ReplaceJump
                        End If
                        
                        If Right(LineValue, 1) = ":" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 4) = "Case" Then
                            .ReplaceLine lineN, "    " & vbTab & vbTab & .Lines(lineN, 1)  'ori
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 6) = "Public" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 7) = "Private" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 3) = "Sub" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 8) = "Function" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 12) = "End Function" Then
                            GoTo ReplaceJump
                        End If
    
                        If Left(Trim(LineValue), 3) = "Debug" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 7) = "End Sub" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 1) = "'" Then
                            .ReplaceLine lineN, vbTab & vbTab & .Lines(lineN, 1)     'ori
                            GoTo ReplaceJump
                        End If
                        
                        ' ----------------------------
                        ' ADDING LINE CODE

                        If lineN < 100 Then
                            .ReplaceLine lineN, CStr(lineN) & ":" & vbTab & vbTab & .Lines(lineN, 1)
                        Else
                            .ReplaceLine lineN, CStr(lineN) & ":" & vbTab & .Lines(lineN, 1)
                        End If
ReplaceJump:
                    Next j
                End If
            End If
        Next i
    End With
    MsgBox "Code lines has been added.", vbInformation, "CONFIRMATION..."
Else
    MsgBox "Canceled."
End If
End Sub

Code lines deleting works similar to mentioned above adding lines code. Module name where code lines should be added has to pasted to following code. When we start macro depends from line counter number of signs is removing. Be sure before running removing code that lines numbers have been added previously.

Removing VBA code lines automatically:


' Attention!!! Following reference has to be added:
' Microsoft Visual Basic for Applications Extensibility 5.3.

' 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 RemoveLineNumber()
' REMOVING CODE LINES

Dim i As Long, j As Long, lineN As Long
Dim procName As String
Dim startOfProceedure As Long
Dim lengthOfProceedure As Long
Dim ModuleName As String
Dim ReplaceJump, LineValue, PrevLineValue, LenLine, YesNo

ModuleName = "YourModuleName" 'Paste module name where code lines has to be removed

YesNo = MsgBox("Would you like to remove lines numbers from:   '" & ModuleName & "'?", vbQuestion + vbYesNo, "QUESTION...")
If YesNo = 6 Then
    With ThisWorkbook.VBProject.VBComponents(ModuleName).CodeModule
        For i = 1 To .CountOfLines
            procName = .ProcOfLine(i, vbext_pk_Proc)
            If procName <> vbNullString Then
                startOfProceedure = .ProcStartLine(procName, vbext_pk_Proc)
                If i = startOfProceedure Then
                    lengthOfProceedure = .ProcCountLines(procName, vbext_pk_Proc)
                    For j = 2 To lengthOfProceedure - 2
                        lineN = startOfProceedure + j

                        ' ----------------------------
                        ' EXCLUSION
                        
                        LineValue = .Lines(lineN, 1)
                        PrevLineValue = .Lines(lineN - 1, 1)
                        
                        If Len(.Lines(lineN, 1)) = 0 Then
                            .ReplaceLine lineN, .Lines(lineN, 1)
                            GoTo ReplaceJump
                        End If
                        
                        If Right(PrevLineValue, 1) = "_" Then
                            .ReplaceLine lineN, Right(.Lines(lineN, 1), Len(.Lines(lineN, 1)) - 8)
                            GoTo ReplaceJump
                        End If
                        
                        If Right(LineValue, 1) = ":" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 4) = "Case" Then
                            .ReplaceLine lineN, Right(.Lines(lineN, 1), Len(.Lines(lineN, 1)) - 8)
                        End If
                        
                        If Left(Trim(LineValue), 6) = "Public" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 7) = "Private" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 3) = "Sub" Then
                            GoTo ReplaceJump
                        End If
    
                        If Left(Trim(LineValue), 3) = "Debug" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 8) = "Function" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 12) = "End Function" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 7) = "End Sub" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 7) = "" Then
                            GoTo ReplaceJump
                        End If
                        
                        If Left(Trim(LineValue), 1) = "'" Then
                            .ReplaceLine lineN, Right(.Lines(lineN, 1), Len(.Lines(lineN, 1)) - 8)
                            GoTo ReplaceJump
                        End If
                        
                        ' ----------------------------
                        ' REMOVING LINE'S NUMBER
                        
                        .ReplaceLine lineN, Right(.Lines(lineN, 1), Len(.Lines(lineN, 1)) - 8)
    
ReplaceJump:
                    Next j
                End If
            End If
        Next i
    End With
    MsgBox "Line number has been removed.", vbInformation, "CONFIRMATION..."
Else
    MsgBox "Canceled."
End If
End Sub

100 thoughts on “Adding VBA code lines automatically

  1. Pingback: Code lines added automatically to VBA module | My Excel Database

  2. Pingback: Adult Phone Chat

  3. Pingback: binary options market

  4. Pingback: Mossberg Guns for Sale

  5. Pingback: mo thay ran danh de con gi

  6. Pingback: mơ nhà sập

  7. Pingback: mơ sông nước đánh đề con gì

  8. Pingback: mo thay loi nuoc

  9. Pingback: mơ thấy gấu đen

  10. Pingback: mo thay an bap

  11. Pingback: vipbandarq

  12. Pingback: 먹튀없는사이트

  13. Pingback: easyweb login

  14. Pingback: undetected rust hacks 2018

  15. Pingback: vr80

  16. Pingback: stiiizy carts / buy stiiizy carts online

  17. Pingback: fake glo carts

  18. Pingback: bảng xếp hạng 12 chòm sao

  19. Pingback: cây trồng trong phòng làm việc

  20. Pingback: song ngư nam khi yêu

  21. Pingback: mơ thấy ác mộng

  22. Pingback: giải mã giấc mơ thấy người yêu cũ

  23. Pingback: mo thay co

  24. Pingback: choi game tien len mien nam truc tuyen

  25. Pingback: tennis truc tuyen

  26. Pingback: tai game ban ca sieu thi cuamobi

  27. Pingback: tai game dua ngua

  28. Pingback: cat tay chay mau

  29. Pingback: out mix

  30. Pingback: mơ chồng ngoại tình

  31. Pingback: https://maxiextermination.com/pest-control-swedeborg-mo/

  32. Pingback: situs judi online

  33. Pingback: dumps shop 2020

  34. Pingback: like instagram

  35. Pingback: Gym workout music 2020

  36. Pingback: uk webcam girls

  37. Pingback: cheats multiplayer

  38. Pingback: mơ thấy nấu cơm

  39. Pingback: mơ thấy chuối chín

  40. Pingback: mơ thấy bốc mộ

  41. Pingback: nam mo thay rung toc

  42. Pingback: chup hinh thay ma

  43. Pingback: DUI Attorney Near Me

  44. Pingback: Real estate Agent in Suffolk VA - Home Purchase

  45. Pingback: Tyson vs Jones jr live stream

  46. Pingback: free dumps site

  47. Pingback: garage door spring replacement ottawa

  48. Pingback: vwzhrrqu

  49. Pingback: what would happen if a woman took a viagra

  50. Pingback: wanneer viagra innemen

  51. Pingback: what is a 250mg tablet of zithromax "converted" into 0.5g po?

  52. Pingback: free cialis

  53. Pingback: buying viagra online legally

  54. Pingback: viagra barata

  55. Pingback: canada essay writing service

  56. Pingback: writing the name of a research artile in my paper

  57. Pingback: essay writing in english my favourite teacher

  58. Pingback: help me write my college essay

  59. Pingback: corporate social responsibility and business ethics essay

  60. Pingback: amoxicillin uk price

  61. Pingback: furosemide cost usa

  62. Pingback: zithromax 250 mg pill

  63. Pingback: stromectol medication

  64. Pingback: ventolin 108 mcg

  65. Pingback: doxycycline dog

  66. Pingback: prednisolone and fioricet

  67. Pingback: clomid pregnancy

  68. Pingback: dapoxetine and flomax

  69. Pingback: diflucan antifungal

  70. Pingback: synthroid coupons

  71. Pingback: propecia withdrawal

  72. Pingback: neurontin en espanol

  73. Pingback: wiki paxil

  74. Pingback: plaquenil and arava

  75. Pingback: buy cialis toronto

  76. Pingback: how much does cialis cost with insurance

  77. Pingback: Zakhar Berkut hd

  78. Pingback: buy cialis philippines

  79. Pingback: taking soft tabs

  80. Pingback: psy

  81. Pingback: psy2022

  82. Pingback: projectio freid

  83. Pingback: viagra retinal

  84. Pingback: How to unsubscribe from tinder gold android

  85. Pingback: payday loans virginia

  86. Pingback: kinoteatrzarya.ru

  87. Pingback: video

  88. Pingback: using viagra

  89. Pingback: online dating free site

  90. Pingback: walmart cialis price

  91. Pingback: cheap cialis 10mg

  92. Pingback: canadian cialis 20 mg

  93. Pingback: top

  94. Pingback: buying cialis without prescription

  95. Pingback: walgreens pharmacy store location

  96. Pingback: canadian pharmacy meds coupon

  97. Pingback: elevit online pharmacy

  98. Pingback: buy cialis non prescription

  99. Pingback: soderzhanki-3-sezon-2021.online

  100. Pingback: podolsk-region.ru

Leave a Reply

Your email address will not be published. Required fields are marked *