code line numbers VBA

[iknowu99]

Hi All reading this,

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

Gracias

 

[Ron Coderre]

Hi All reading this,

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

Gracias

The short answer is: No, there is no line number "feature" in Excel VBA

 

[Kenneth Hobson]

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.

Sub t()
  GoTo 5
  Exit Sub
5:
  MsgBox "5"
  GoTo Five
  Exit Sub
Five:
  MsgBox "Five"
End Sub

 

[Nalani]

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)

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]

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

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]

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]

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]

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.

 '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.

'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]

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?



Thanks.

 

[xenou]

Not sure but possibly you need to allow trusted access to the Visual Basic Project (this is an option under your macro settings).