Remove line numbers to VBA and replace with spaces Ms Access Gurus

through sharing, we all get better

Home Articles Code Tools

Remove Line Numbers in VBA from all classes and modules within the current database file

What use is good code if you can’t copy and paste it to somewhere else? Did you get a database with line numbers and you don't have a tool to remove them? Here is code, written by Geoff Griffith to effectively remove line numbers in VBA.

Make a Copy of the application to remove line numbers in, because the application may need the line numbers to function properly.

Remove Line Numbers from VBA and replace with spaces

Quick Jump

Download VBA Reference Backstory Share Tutoring Donate

Goto the Very Top  

Download

Download zipped BAS file you can import into your Access projects: mod_RemoveLineNumbers_GG__BAS.zip

Remember to UNBLOCK files you download to remove the Mark of the Web. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Goto Top  

VBA

Standard module 

'module: modRemoveLineNumbers_GG (Geoff Griffith)
'*************** Code Start ***********************************************
' Purpose  : remove the line numbers and replace with spaces
'              from all classes and modules within the current database file
' Author   : Geoff Griffith
' Code List: https://msaccessgurus.com/code.htm
' This code: https://msaccessgurus.com/VBA/module_RemoveLineNumbers.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Mark your changes. Use at your own risk.
'-------------------------------------------------------------------------------
'           RemoveLineNumbers_GG
'-------------------------------------------------------------------------------
Public Sub RemoveLineNumbers_GG() 
On Error GoTo HandleErrors 

 ' NOTE: This code requires a reference to:
    '       Microsoft Visual Basic for Applications Extensibility
    '       for early binding -- late binding recommended by Jack Stockton
    '
    Dim vbProj As Object  'VBIDE.VBProject
    Dim vbComp As Object  'VBIDE.VBComponent
    Dim modCurrent As Object  'VBIDE.CodeModule

    Dim iLine As Long 
    Dim sLineText As String 
    Dim sFirstWord As String 
    Dim sReplacement As String 
    Dim sNewText As String 
    
    Set vbProj = Application.VBE.VBProjects(1) 
    For Each vbComp In vbProj.VBComponents 
        
        Set modCurrent = vbComp.CodeModule 
        For iLine = 1 To modCurrent.CountOfLines 
            
            sLineText = Trim$(modCurrent.Lines(iLine,1)) 
            If Len(sLineText) > 0 Then 
                If InStr(1,sLineText, " ") > 0 Then 
                    sFirstWord = Trim$(Left$(sLineText,InStr(1,sLineText, " ") - 1)) 
                    If IsNumeric(sFirstWord) Then 
                        sReplacement = String(Len(sFirstWord), " ") 
                        sLineText = modCurrent.Lines(iLine,1) 
                        sNewText = Right$(sLineText,Len(sLineText) - (InStr(sLineText,sFirstWord) + Len(sFirstWord) - 1)) 
                        sNewText = Left$(sLineText,InStr(sLineText,sFirstWord) - 1) & sReplacement & sNewText 
                        modCurrent.ReplaceLine iLine,sNewText 
                    End If 
                End If 
            End If 
                
        Next iLine 
        
    Next 
    
    MsgBox  "Done removing line numbers",, "Done"
    
ExitMethod: 
    Exit Sub 
HandleErrors: 
    MsgBox  "Error: " & Err.Description,vbCritical, "Error " & Nz(Err.Number, "") 
    Resume ExitMethod 
End Sub 
'*************** Code End *****************************************************
Code was generated with colors using the free Color Code add-in for Access

Goto Top  

Reference

Help: VBIDE -- Objects (Visual Basic Add-In Model)

Help: String function

Help: For Each...Next statement

Help: IsNumeric function

Goto Top  

Backstory

I downloaded the new Northwind database templates. To my dismay, there are line numbers in the VBA code (but happily, there is VBA code!) and better documentation.

Numbering lines in VBA isn't a built-in feature, and there are commercial tools you can buy. Maybe not everyone can do that.

I thought about writing code to at least remove line numbers, and then asked Geoff Griffith since I figured he could do it fast, and he did! Thank him for this code ~

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/VBA/module_RemoveLineNumbers.htm

or in old browsers:
http://www.msaccessgurus.com/VBA/module_RemoveLineNumbers.htm

Get Tutoring with Access

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. And you'll get links to great resources.

Do you want to step up your application? Let's connect, I can help you make it better. If I don't know the answers you need, I'll ask other experts like Geoff. Email me at training@msAccessGurus.com

If you want someone to write programs for you and don't want to learn how to do it yourself, email Geoff instead of me.

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top