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

398 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

  234. Pingback: generic tadalafil daily

  235. Pingback: cialis trial

  236. Pingback: ciprofloxacin dosage uti

  237. Pingback: cialis overdose

  238. Pingback: uses for cialis

  239. Pingback: tadalafil 10mg

  240. Pingback: sildenafil from india

  241. Pingback: cheapest viagra 100mg

  242. Pingback: sildenafil recall

  243. Pingback: how long does viagra work

  244. Pingback: can i buy viagra at walgreens

  245. Pingback: Zofran

  246. Pingback: when should you take cialis

  247. Pingback: keto diet drinks

  248. Pingback: no 1 canadian pharmacy reviews

  249. Pingback: regcialist.com

  250. Pingback: buy viagra near me

  251. Pingback: canadian pharmacy without a prescription

  252. Pingback: acyclovir stephenson blom

  253. Pingback: where to buy chloroquine walmart

  254. Pingback: amlodipine and weight gain

  255. Pingback: can you just stop taking atorvastatin?

  256. Pingback: how long for prilosec to work

  257. Pingback: zoloft vs paxil

  258. Pingback: quetiapine depression

  259. Pingback: lyrica blood pressure

  260. Pingback: lexapro and wellbutrin

  261. Pingback: xanax and cymbalta

  262. Pingback: sildenafil citrate

  263. Pingback: hydrochlorothiazide diuretic

  264. Pingback: cheap viagra

  265. Pingback: viagra cheap

  266. Pingback: lipitor and dementia

  267. Pingback: best generic viagra

  268. Pingback: female viagra

  269. Pingback: where do you buy cialis

  270. Pingback: lexapro side effects first week

  271. Pingback: order generic cialis online

  272. Pingback: brand cialis

  273. Pingback: cialis samples

  274. Pingback: cialis discount generic

  275. Pingback: viagra coupon

  276. Pingback: sildenafil generic

  277. Pingback: 1

  278. Pingback: online pharmacy viagra

  279. Pingback: cymbalta for back pain

  280. Pingback: cialis alternative

  281. Pingback: gay claw dating

  282. Pingback: ivermectin for head lice oral

  283. Pingback: 60 mg sildenafil

  284. Pingback: sildenafil 100mg canada

  285. Pingback: viagra or cialis

  286. Pingback: viagra online usa

  287. Pingback: viagra alternative

  288. Pingback: cialis for sale

  289. Pingback: sildenafil online

  290. Pingback: cialis 5mg best price canada

  291. Pingback: ivermectin usage

  292. Pingback: buy viagra online united states

  293. Pingback: buy cialis pakistan

  294. Pingback: side effects of ivermectin overdose

  295. Pingback: viagra 800 mg

  296. Pingback: stromectol for humans antiparasitic

  297. Pingback: cialis pills

  298. Pingback: cost of ivermectin for humans

  299. Pingback: ivermectin for canaries

  300. Pingback: how to get ivermectin prescription

  301. Pingback: viagra 100mg tablet price in india online

  302. Pingback: viagra connect leaflet

  303. Pingback: oral ivermectin for demodex rosacea

  304. Pingback: where to find viagra online

  305. Pingback: ivermectin kidney damage

  306. Pingback: how does ivermectin kill scabies

  307. Pingback: can you buy heneric viagra online legally

  308. Pingback: cialis works

  309. Pingback: research tadalafil

  310. Pingback: zithromax online overnight

  311. Pingback: viagra price

  312. Pingback: buy viagra direct

  313. Pingback: sildenafil avis

  314. Pingback: sildenafil 20mg

  315. Pingback: over the counter pills for ed

  316. Pingback: buying viagra online

  317. Pingback: tadalafil and dapoxetine in india

  318. Pingback: generic cialis cheap online

  319. Pingback: how to buy cialis online

  320. Pingback: buy z pack over the counter

  321. Pingback: viagra online pharmacy

  322. Pingback: get azithromycin over counter

  323. Pingback: red viagra tablets

  324. Pingback: generic without doctor viagra

  325. Pingback: genuine viagra without a doctor prescription

  326. Pingback: compra viagra

  327. Pingback: viagra for men

  328. Pingback: viagra bestellen

  329. Pingback: buy amoxil 1000mg

  330. Pingback: viagra sans ordonnance

  331. Pingback: lasix without a rx

  332. Pingback: buy neurontin online

  333. Pingback: plaquenil 200

  334. Pingback: prednisone no rx

  335. Pingback: walgreens viagra

  336. Pingback: dapoxetine online

  337. Pingback: modafinil australia

  338. Pingback: stromectol brand

  339. Pingback: albuterol tablets 2mg

  340. Pingback: zithromax 500 mg

  341. Pingback: price of lasix

  342. Pingback: online viagra cheap

  343. Pingback: viagra price

  344. Pingback: pharmacy

  345. Pingback: prednisone 50 mg

  346. Pingback: dapoxetine price usa

  347. Pingback: how does viagra work

  348. Pingback: pfizer viagra price

  349. Pingback: viagra connect

  350. Pingback: low price viagra

  351. Pingback: overwatch sex games

  352. Pingback: viagra no prescription

  353. Pingback: viagra pill

  354. Pingback: hims sildenafil

  355. Pingback: purchase oral ivermectin

  356. Pingback: fildena

  357. Pingback: zithromax liquid

  358. Pingback: meritkiing

  359. Pingback: elexusbet

  360. Pingback: Eurocasino

  361. Pingback: madridbet

  362. Pingback: meritroyalbet

  363. Pingback: eurocasino

  364. Pingback: eurocasino

  365. Pingback: what is minocycline 50 mg used for

  366. Pingback: meritking

  367. Pingback: meritroyalbet

  368. Pingback: meritroyalbet

  369. Pingback: cialis shelf life

  370. Pingback: viagra cialis

  371. Pingback: cialis price walmart

  372. Pingback: sildenafil citrate 20mg

  373. Pingback: how long before sex to take viagra

  374. Pingback: canada pharmacy generic cialis

  375. Pingback: womens viagra

  376. Pingback: tadalafil vs viagra

  377. Pingback: is cialis time released?

  378. Pingback: cialis professional vs cialis super active

  379. Pingback: latisse eyelash

  380. Pingback: clomid buy online

  381. Pingback: mulnupiravir

  382. Pingback: pill to treat covid

  383. Pingback: tamoxifen 40 mg

  384. Pingback: pharmacist international

  385. Pingback: sumycin

  386. Pingback: zithromax contraindications

  387. Pingback: ciprofloxacin capsule

  388. Pingback: biaxin

  389. Pingback: fish ciprofloxacin

  390. Pingback: augmentin sirop

  391. Pingback: metronidazole creams

  392. Pingback: Google

  393. Pingback: Watermelon haupia

  394. Pingback: Bubblegum Haupia Strain

  395. Pingback: Bubble hash

  396. Pingback: Moroccan hash

  397. Pingback: Litto disposable

  398. Pingback: Bubble hash

Leave a Reply

Your email address will not be published.