Code lines added automatically to VBA module

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

233 thoughts on “Code lines added automatically to VBA module

  1. Pingback: Adding VBA code lines automatically | My Excel Database

  2. Pingback: visit this page

  3. Pingback: Integrated Risk Management solution

  4. Pingback: purchase adderall for sale online with next day shipping discreetly

  5. Pingback: mơ người chết đánh đề con gì

  6. Pingback: cach soi keo

  7. Pingback: buy hydromorphone online no script use for pain anxiety overnight delivery

  8. Pingback: danh de online uy tin

  9. Pingback: de kep

  10. Pingback: ti le chap banh

  11. Pingback: mơ thấy rắn thì đánh con gì

  12. Pingback: game poker đổi thưởng

  13. Pingback: คอนโดเงินเหลือ

  14. Pingback: fausse rolex

  15. Pingback: our website

  16. Pingback: pinewswire

  17. Pingback: eatverts

  18. Pingback: The Natural Penguin

  19. Pingback: french bulldog puppies for sale near me in CA ON MA CO OH PA SC MS TN FL UT NH VA AL TX

  20. Pingback: Bitcoin Evolution

  21. Pingback: 안전놀이터

  22. Pingback: oxicontin for sale

  23. Pingback: lacnejšia IT správa

  24. Pingback: bitcoin evolution review

  25. Pingback: Immediate Edge Review 2020

  26. Pingback: Bitcoin Era Review 2020

  27. Pingback: w88 lite

  28. Pingback: mơ thấy gạo đánh con gì

  29. Pingback: mơ thấy con cá đánh đề con gì

  30. Pingback: mini labradoodles for sale

  31. Pingback: theweedtube

  32. Pingback: La Licorne Beauté abonnement

  33. Pingback: these details

  34. Pingback: fake Breitling Colt

  35. Pingback: SSCN BKN 2021 Kabupaten Sambas

  36. Pingback: devops

  37. Pingback: Rock Island Armory VR80

  38. Pingback: mơ bắn nhau đánh con gì

  39. Pingback: chiem bao thay lua

  40. Pingback: ngủ mơ thấy rắn hổ mang

  41. Pingback: mơ thấy gấu đánh con gì

  42. Pingback: trò chuyện với người nổi tiếng

  43. Pingback: mơ cá chép đánh con gì

  44. Pingback: nằm mơ chuyển nhà

  45. Pingback: nam mo thay ngo

  46. Pingback: mơ thấy vợ đánh con gì

  47. Pingback: vr80 shotgun

  48. Pingback: stiiizy carts online / where to buy stiiizy carts online

  49. Pingback: xếp hạng 12 chòm sao ai thông minh nhất

  50. Pingback: mơ thấy bị đòi nợ đánh con gì

  51. Pingback: mơ thấy quả mít đánh con gì

  52. Pingback: essay writing help for students

  53. Pingback: phd thesis search

  54. Pingback: customessaywriterbyz.com

  55. Pingback: help in writing essays

  56. Pingback: essay writting service

  57. Pingback: dissertation support

  58. Pingback: write my paper for me

  59. Pingback: where to buy a research paper

  60. Pingback: thesis in writing

  61. Pingback: cialis pills

  62. Pingback: cialis without a doctor prescription

  63. Pingback: how long for cialis to peak

  64. Pingback: generic cialis

  65. Pingback: over the counter viagra substitute walgreens

  66. Pingback: is viagra government funded

  67. Pingback: canadian viagra

  68. Pingback: how long does viagra take to work

  69. Pingback: buying drugs canada

  70. Pingback: canadian pharmacy online

  71. Pingback: erectile dysfunction

  72. Pingback: prescription drug

  73. Pingback: Cialis Super Active

  74. Pingback: viagra

  75. Pingback: cialis original online

  76. Pingback: cialis cheep

  77. Pingback: cialis black to buy in the uk

  78. Pingback: where can i find viagra in london

  79. Pingback: cheap cialis from australia

  80. Pingback: iztuwqqn

  81. Pingback: how to get viagra without prescription

  82. Pingback: wat gebeurt er als een vrouw viagra gebruikt

  83. Pingback: how does zithromax work

  84. Pingback: will humana cover cialis

  85. Pingback: cialis 80 mg dosage

  86. Pingback: viagra generic over the counter

  87. Pingback: viagra en madrid

  88. Pingback: essay writing services cheap

  89. Pingback: how do i write a research paper

  90. Pingback: write my essays online essay writing service

  91. Pingback: help write essay for me

  92. Pingback: business ethics essay

  93. Pingback: amoxicillin 875 mg tablet

  94. Pingback: lasix 40 mg iv

  95. Pingback: azithromycin erythromycin

  96. Pingback: where can i buy oral ivermectin

  97. Pingback: albuterol 8.5 g

  98. Pingback: doxycycline spectrum

  99. Pingback: prednisolone and exercise

  100. Pingback: clomid or femara

  101. Pingback: la dapoxetine

  102. Pingback: diflucan and alcohol

  103. Pingback: synthroid 150

  104. Pingback: thesis editing services

  105. Pingback: help in thesis writing

  106. Pingback: merck propecia coupons

  107. Pingback: neurontin 300mg capsules

  108. Pingback: metformin hcl

  109. Pingback: adderall and paxil

  110. Pingback: plaquenil and meloxicam

  111. Pingback: buy cialis 5mg daily use

  112. Pingback: kate england viagra brother

  113. Pingback: viagra online purchase india

  114. Pingback: cheapest generic viagra australia

  115. Pingback: online sildenafil canada

  116. Pingback: is generic levitra real

  117. Pingback: cipro amoxicillin

  118. Pingback: amoxicillin tooth abscess

  119. Pingback: azithromycin 250 mg used for

  120. Pingback: side effects for celecoxib

  121. Pingback: meloxicam versus celebrex

  122. Pingback: cephalexin can treat h pylori

  123. Pingback: is keflex a sulfa drug

  124. Pingback: duloxetine another name

  125. Pingback: is cymbalta

  126. Pingback: cheap viagra 50mg

  127. Pingback: cheap viagra fast shipping

  128. Pingback: hims cialis australia

  129. Pingback: sildenafil 25 mg india

  130. Pingback: purchase cialis for daily use

  131. Pingback: marley generics viagra reviews

  132. Pingback: sildenafil mexico price

  133. Pingback: generic tadalafil cheap

  134. Pingback: acheter cialis 80 mg

  135. Pingback: cost of generic levitra

  136. Pingback: cialis otc in panama

  137. Pingback: generic cialis name australia

  138. Pingback: sildenafil cost canada

  139. Pingback: comprar viagra express

  140. Pingback: where can i buy cialis pills

  141. Pingback: cialis online amazon australia

  142. Pingback: sildenafil for sale uk

  143. Pingback: cialis 20mg pills

  144. Pingback: sildenafil 100mg cheap

  145. Pingback: levitra reliable sause to buy

  146. Pingback: mom son viagra south africa

  147. Pingback: buy cialis online next day delivery

  148. Pingback: cialis generic dosage australia

  149. Pingback: 469 area code tinder

  150. Pingback: what is the active ingredient in viagra

  151. Pingback: real cialis on line ordering

  152. Pingback: lds dating site free

  153. Pingback: cialis without a script

  154. Pingback: cialis online free shipping

  155. Pingback: vet pharmacy online

  156. Pingback: what does cialis treat

  157. Pingback: canadian pharmacy ed medications

  158. Pingback: canadadrugs

  159. Pingback: cialis vs viagra which is better

  160. Pingback: cialis goodrx

  161. Pingback: viagra women

  162. Pingback: tadalafil dosage bodybuilding

  163. Pingback: walmart viagra price

  164. Pingback: tadalafil side effects

  165. Pingback: generic sildenafil usa

  166. Pingback: cialis cost comparison

  167. Pingback: indian viagra

  168. Pingback: cheap generic viagra

  169. Pingback: gabapentin classification

  170. Pingback: tadalafil cost walmart

  171. Pingback: levitra 5 mg

  172. Pingback: rush limbaugh viagra

  173. Pingback: amlodipine doses

  174. Pingback: atorvastatin 20mg

  175. Pingback: warnings for meloxicam

  176. Pingback: metoprolol medication

  177. Pingback: losartan 25mg

  178. Pingback: viagra goes generic

  179. Pingback: cialis copay card

  180. Pingback: levitra online purchase

  181. Pingback: duloxetine 30 mg

  182. Pingback: prednisone taper

  183. Pingback: amitriptyline dosage

  184. Pingback: duloxetine hcl dr

  185. Pingback: hydrochlorothiazide 25

  186. Pingback: metformin dosage guide

  187. Pingback: what is mirtazapine

  188. Pingback: bupropion 75 mg

  189. Pingback: buspar 5 mg

  190. Pingback: celexa 200 mg

  191. Pingback: zanaflex for headaches

  192. Pingback: wellbutrin 150 mg

  193. Pingback: what is diclofenac

  194. Pingback: clonidine medication

  195. Pingback: cost of finasteride

  196. Pingback: carvedilol tablet

  197. Pingback: metronidazole gel treatment

  198. Pingback: levitra 10mg tablets

  199. Pingback: totally free online dating sites in usa

  200. Pingback: donepezil hcl interactions

  201. Pingback: azithromycin 500 mg tablet

  202. Pingback: interactions for cefdinir

  203. Pingback: cephalexin for uti

  204. Pingback: zithromax antibiotic

  205. Pingback: cialis price

  206. Pingback: viagra logo

  207. Pingback: cialis professional samples

  208. Pingback: online cialis sales

  209. Pingback: tadalafil 20 mg

  210. Pingback: generic viagra 2017

  211. Pingback: sildenafil 50 mg generic

  212. Pingback: 360 viagra

  213. Pingback: female viagra

  214. Pingback: sildenafil pills online

  215. Pingback: amlodipine 10 mg pill

  216. Pingback: levitra generic price

  217. Pingback: metformin hcl 1000mg tablet

  218. Pingback: best tadalafil

  219. Pingback: amoxicillin 500 mg

  220. Pingback: doxycycline monohydrate

  221. Pingback: lasix

  222. Pingback: xenical cost nz

  223. Pingback: dapoxetine 60mg

  224. Pingback: proscar 5 mg

  225. Pingback: careprost eye drops

  226. Pingback: clomid in india

  227. Pingback: fluconazole treatment for dogs

  228. Pingback: motilium vs domperidone

  229. Pingback: tamoxifen retinopathy eyewiki

  230. Pingback: prednisolone dosage cat ibd

  231. Pingback: naltrexone addiction

  232. Pingback: valtrex side effects forums

  233. Pingback: zanaflex bad dreams

Leave a Reply

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