code line numbers VBA

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
Hi All reading this,

Can't figure out if there is a way to see line numbers in Modules/code?

Gracias
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
The feature is in vb.net which I like. Is there a reason why you need the numbers?

You can copy and paste to a sheet if you need a quick view of them.

You can use Goto x where x is a number. The number is just a label though. Labels must have a colon directly after them.

e.g.
Code:
Sub t()
  GoTo 5
  Exit Sub
5:
  MsgBox "5"
  GoTo Five
  Exit Sub
Five:
  MsgBox "Five"
End Sub
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
I don't know if this will help, or answer your question, but:

If you look at the top of your module, to the right of all the icons, there is a white space that will tell you where your cursor is.

If the first line is (for example)
Code:
Option Explicit
And your cursor is to the Left of the O, it will read Ln 1, Col 1.

If the cursor is to the Right of O, it will read Ln 1, Col 2.

And if it's at the end of the whole statement, it will read, Ln 1, Col 16.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804

ADVERTISEMENT

This will add or remove line numbers from a code module.
It is not recommended for a class module or itself.

Code:
Sub AddLineNumbers()
    Dim i As Long, j As Long, lineN As Long
    Dim procName As String
    Dim startOfProceedure As Long
    Dim lengthOfProceedure As Long
   
    With ThisWorkbook.VBProject.VBComponents("Userform1").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
                        .ReplaceLine lineN, CStr(lineN) & ":" & RemoveOneLineNumber(.Lines(lineN, 1))
                    Next j
                End If
            End If
        Next i
    End With
End Sub


Sub RemoveLineNumbers()
    Dim i As Long
    With ThisWorkbook.VBProject.VBComponents("Userform1").CodeModule
        For i = 1 To .CountOfLines
            .ReplaceLine i, RemoveOneLineNumber(.Lines(i, 1))
        Next i
    End With
End Sub

Function RemoveOneLineNumber(aString)
    RemoveOneLineNumber = aString
    If aString Like "#:*" Or aString Like "##:*" Or aString Like "###:*" Then
        RemoveOneLineNumber = Mid(aString, 1 + InStr(1, aString, ":", vbTextCompare))
    End If
End Function
 

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
The short answer is: No, there is no line number "feature" in Excel VBA

i thought so...looked at all the menus in Office 2010


Nalani, are you running an addon to see what you are seeing, or what menu did you click?




mikerickson...had to change trust settings sub to function and figure out what is what in your code, it ran slow but still amazing!! :))
This VBProject.VBComponents reminds me of a movie I recently saw...you ever try to write macros that write macros that write macros, inception style:))
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047

ADVERTISEMENT

Nalani, are you running an addon to see what you are seeing, or what menu did you click?

I don't think it has anything to do with an Addon. I just logged onto my company server which runs Office 2003 and It's there also. I run 2007 at home.

Hit Alt F11 then open a Module and place your curor somewhere in the Module Code. There is a little white box at the top of the VBE window (similar to the Font drop down window in Excel). It is just to the right of the Blue and White Help question mark.

This is where I see what line I'm on in my code. Columns depicted are measured by keystrokes.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804
This problem intrigued me and I got a little happy.
The new routine is a bit faster and a bit more robust (handles existing line labels and continuation characters)

Open a new workbook. Insert a normal module, named Module1 and put this code in it.
Insert a userform, named Userform1 and insert the code below in its code module.
Run the MakeUF module and you can add and remove line numbers from any non-class code module of any open, unhidden workbook.
Code:
 'in normal Module1
Option Explicit

Sub MakeUF()
    With UserForm1
        .Tag = "Choose a code module"
        .Show
    End With
End Sub

Sub AddLineNumbers(wbName As String, vbCompName As String)
    Dim i As Long, j As Long, lineN As Long
    Dim procName As String
    Dim startOfProceedure As Long
    Dim lengthOfProceedure As Long
    Dim newLine As String
   
    With Workbooks(wbName).VBProject.VBComponents(vbCompName).CodeModule
        .CodePane.Window.Visible = False
        
        For i = 1 To .CountOfLines
            procName = .ProcOfLine(i, vbext_pk_Proc)
            
            If procName <> vbNullString Then
                startOfProceedure = .ProcStartLine(procName, vbext_pk_Proc)
                lengthOfProceedure = .ProcCountLines(procName, vbext_pk_Proc)
                
                If startOfProceedure + 1 < i And i < startOfProceedure + lengthOfProceedure - 1 Then
                    newLine = RemoveOneLineNumber(.Lines(i, 1))
                    If Not HasLabel(newLine) And Not (.Lines(i - 1, 1) Like "* _") Then
                        .ReplaceLine i, CStr(i) & ":" & newLine
                    End If
                End If
            End If
        
        Next i
        .CodePane.Window.Visible = True
    End With
End Sub

Sub RemoveLineNumbers(wbName As String, vbCompName As String)
    Dim i As Long
    With Workbooks(wbName).VBProject.VBComponents(vbCompName).CodeModule
        For i = 1 To .CountOfLines
            .ReplaceLine i, RemoveOneLineNumber(.Lines(i, 1))
        Next i
    End With
End Sub

Function RemoveOneLineNumber(aString)
    RemoveOneLineNumber = aString
    If aString Like "#:*" Or aString Like "##:*" Or aString Like "###:*" Then
        RemoveOneLineNumber = Mid(aString, 1 + InStr(1, aString, ":", vbTextCompare))
    End If
End Function

Function HasLabel(ByVal aString As String) As Boolean
    HasLabel = InStr(1, aString & ":", ":") < InStr(1, aString & " ", " ")
End Function
This code assumes that Userform 1 has no controls.

Code:
'in Userform1 code module
Option Explicit
Public WithEvents aListBox As MSForms.ListBox
Public WithEvents butOK As MSForms.CommandButton
Public WithEvents butCancel As MSForms.CommandButton
Public WithEvents butRemove As MSForms.CommandButton

Dim promptLabel As MSForms.Label

Private Sub aListBox_Click()
    butOK.Enabled = True
    butRemove.Enabled = True
End Sub

Private Sub butCancel_Click()
    Me.Tag = vbNullString
    Unload Me
End Sub

Private Sub butOK_Click()
    With aListBox
        If .ListIndex <> -1 Then
            Call AddLineNumbers(.Value, .Text)
        End If
    End With
    butOK.Enabled = False
    butRemove.Enabled = True
    aListBox.SetFocus
End Sub

Private Sub butRemove_Click()
    With aListBox
        If .ListIndex <> -1 Then
            Call RemoveLineNumbers(.Value, .Text)
        End If
    End With
    butRemove.Enabled = False
    butOK.Enabled = True
    aListBox.SetFocus
End Sub

Private Sub UserForm_Activate()
    Dim oneWorkbook As Workbook
    Dim oneComponent As VBComponent
    Dim oneCodeModule As CodeModule
    Dim sizeLabel As MSForms.Label
    Dim fontName As String, fontSize As Long
    fontName = "Arial": fontSize = 12

    Set promptLabel = Me.Controls.Add("Forms.Label.1")
    With promptLabel
        With .Font
            .Name = fontName: .Size = fontSize + 2
        End With
        .BorderStyle = fmBorderStyleNone
        .Top = 5
        .Left = 10
        .Width = 400
        .Caption = Me.Tag
        .AutoSize = True
        .WordWrap = True
        .Width = 400
    End With

    Set aListBox = Me.Controls.Add("Forms.ListBox.1")
    With aListBox
        .Top = promptLabel.Top + promptLabel.Height + 10
        .Left = promptLabel.Left
        .Width = 400
        .Height = 100
        .ColumnCount = 2
        .BoundColumn = 1: .TextColumn = 2
        With .Font
            .Name = fontName
            .Size = fontSize
        End With
    End With
    Set sizeLabel = Me.Controls.Add("Forms.Label.1")
    With sizeLabel
        With .Font
            .Name = fontName
            .Size = fontSize
        End With
        .AutoSize = True
        .Visible = False
    End With

    For Each oneWorkbook In Application.Workbooks
        If oneWorkbook.Windows(1).Visible Then
            For Each oneComponent In oneWorkbook.VBProject.VBComponents
                If Not ((oneWorkbook.Name = ThisWorkbook.Name And oneComponent.Name = "UserForm1") _
                            Or (oneWorkbook.Name = ThisWorkbook.Name And oneComponent.Name = "Module1")) Then
                    If oneComponent.Type <> vbext_ct_ClassModule Then
                        aListBox.AddItem oneWorkbook.Name
                        aListBox.List(aListBox.ListCount - 1, 1) = oneComponent.Name
                        sizeLabel.Caption = sizeLabel.Caption & vbCr & "X"
                    End If
                End If
            Next oneComponent
        End If
    Next oneWorkbook

    aListBox.Height = sizeLabel.Height
    Me.Controls.Remove sizeLabel.Name

    Set butOK = Me.Controls.Add("Forms.CommandButton.1")
    With butOK
        With .Font
            .Name = fontName
            .Size = fontSize + 2
        End With
        .Default = True
        .AutoSize = True
        .Caption = "Add line labels"
        .AutoSize = False
        .Height = .Height - 4
        .Top = aListBox.Top + aListBox.Height + 16
        .Left = aListBox.Left + aListBox.Width - .Width
    End With

    Set butRemove = Me.Controls.Add("Forms.CommandButton.1")
    With butRemove
        With .Font
            .Name = fontName
            .Size = butOK.Font.Size
        End With
        .Caption = "Remove"
        .Width = butOK.Width
        .Height = butOK.Height
        .Top = butOK.Top
        .Left = butOK.Left - .Width - 20
    End With

    Set butCancel = Me.Controls.Add("Forms.CommandButton.1")
    With butCancel
        With .Font
            .Name = fontName
            .Size = butOK.Font.Size
        End With
        .Caption = "Close"
        .Height = butOK.Height
        .Width = butOK.Width
        .Top = butOK.Top
        .Left = butRemove.Left - .Width - 20
    End With

    With Me
        .Width = 2 * aListBox.Left + aListBox.Width
        .Height = butOK.Top + 2 * butOK.Height + 10
    End With
    butOK.Enabled = False
    butRemove.Enabled = False
    aListBox.SetFocus
End Sub
 

vicsar

New Member
Joined
May 5, 2013
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
Hello mikerickson,

What references should be enabled in order for this to work? While running it tells me that vbext_pk_Proc is not defined. If I remove the Option Exsplicit then Excel tells me: ByRef argument type mismatch and points back to vbext_pk_Proc. How should vbext_pk_Proc be decalre what type of variable is it, string?

:confused:

Thanks.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,705
Office Version
  1. 2019
Platform
  1. Windows
Not sure but possibly you need to allow trusted access to the Visual Basic Project (this is an option under your macro settings).
 

Watch MrExcel Video

Forum statistics

Threads
1,113,749
Messages
5,543,978
Members
410,587
Latest member
kaloybulan
Top