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

 

[RoryA]

You need to set a reference to the Microsoft Visual Basic for Applications Extensibility library.

 

[Rick Rothstein]

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

And the long answer is: Yes , VBA supports line numbers, but not automatically... you have to type them in and adjust them, when necessary, manually. These are not "labels" as defined in the help files as they do not require a colon after them, but they act as labels because you can GoTo them, On Error GoTo them, etc. if desired. They are more for "looks" (especially if you are an old-time BASIC language programmer) and have no real practical value, but they are tolerated for backward compatibility. Copy/Paste this code into a module and run the code in Debug mode so you can watch the progress line-by-line...

Sub Test()
10  Dim X As Long, Msg As String, Temp
20  On Error GoTo 200
30  GoTo 50
40  MsgBox "This line will not display anything as it is being skipped over"
50  For X = 1 To 3
60    If X = 2 Then
70      Temp = 5 / 0
80    Else
90      MsgBox "No errors - Iteration #" & X
100    End If
110  Next
120 Exit Sub
200 MsgBox "Divide by zero error! - Iteration #" & X
210 Resume Next
End Sub

 

[RoryA]

They are more for "looks" ... and have no real practical value

They do have one benefit - you can use Erl in an error handler to tell you which line the error occurred on.

 

[Rick Rothstein]

They do have one benefit - you can use Erl in an error handler to tell you which line the error occurred on.

Erl... wow, that takes me back! Thanks for the reminder on it Rory, I had forgotten all about that function... and apparently so did Microsoft as there appears to be no help files for it (not that the function is all that complicated to use). For those who might be interested, here is the code I posted earlier with the Erl function thrown it (highlighted in red)...

Sub Test()
10  Dim X As Long, Msg As String, Temp
20  On Error GoTo 200
30  GoTo 50
40  MsgBox "This line will not display anything as it is being skipped over"
50  For X = 1 To 3
60    If X = 2 Then
70      Temp = 5 / 0
80    Else
90      MsgBox "No errors - Iteration #" & X
100    End If
110  Next
120 Exit Sub
200 MsgBox "Divide by zero error on Line #" & Erl & "! - Iteration #" & X
210 Resume Next
End Sub

Thanks for the reminder

 

[mikerickson]

vbext_pk_Proc requires the Microsoft Visual Basic for Applications Extensibility library.

 

[vicsar]

You need to set a reference to the Microsoft Visual Basic for Applications Extensibility library.

vbext_pk_Proc requires the Microsoft Visual Basic for Applications Extensibility library.

Priceless mikerickson, thanks for this jewel.

RoryA thanks for your reply.

 

[JayKilleen]

Thanks MikeRickson! I am just using this code now.

I have added:

aListBox.Height = sizeLabel.Height 
   If aListBox.Height > 300 Then aListBox.Height = 300
    Me.Controls.Remove sizeLabel.Name

As the size of the listbox was outside my screen as I have many worksheets and modules going.

Thanks again.

 

[pgc01]

These are not "labels" as defined in the help files as they do not require a colon after them, but they act as labels because you can GoTo them, On Error GoTo them, etc. if desired. They are more for "looks" (especially if you are an old-time BASIC language programmer) and have no real practical value, but they are tolerated for backward compatibility.

Hi Rick

I always considered them labels.

- if they were like line numbers they should be ordered ascendingly
- there's no problem if you want to add the colon

I believe that the colon is just for the labels that begin with a letter, so that there's no confusion that it is a label and not some statement.

Ex.:

Sub test()

30 MsgBox 1
35 MsgBox 2
31 MsgBox 3

GoTo 37

40: MsgBox 4
34 MsgBox 5
33 MsgBox 6
37: MsgBox 7
32 MsgBox 8

End Sub

They seem like labels.

 

[pgc01]

For the Erl it will get the number at the beginning or if it is just letters it returns 0:

Sub test()

On Error GoTo err_hndl
12 label1: MsgBox 1
15 label2: MsgBox 2 / 0
c: MsgBox 3

Exit Sub

err_hndl:
i: MsgBox "Divide by zero error on Line #" & Erl & "! - Iteration #"
j: Resume Next

End Sub

 

[Jimbo66]

my code for line numbers, after row 7 (I have a Worksheet with headings) paste your code anywhere in column A on a worksheet. Create command buttons or run it as a macro.

Option Compare Text

Private Sub AddLineNumbers_Click()
 
Set sh = Sheet4
ctr = 0
                                        
For i = 7 To 1000
    If sh.Cells(i, 1) = "" Then GoTo Next_i ' find start of code
    If sh.Cells(i, 1) = "End Sub" Then Exit For
    If sh.Cells(i, 1) = "End Function" Then Exit For
    ctr = ctr + 1
    fi = Format(ctr, "000")
     sh.Cells(i, 1) = "L" & fi & ": " & sh.Cells(i, 1)
Next_i:
Next i

sh.Cells(7, 1).Activate
   
End Sub
 

Private Sub RemoveLineNumbers_Click()

Set sh = Sheet4

For i = 7 To 1000
    If sh.Cells(i, 1) = "" Then GoTo Next_i ' find start of code
    If sh.Cells(i, 1) = "End Sub" Then Exit For
    If sh.Cells(i, 1) = "End Function" Then Exit For
    If Mid(sh.Cells(i, 1), 1, 1) = "L" Then
        sh.Cells(i, 1) = Mid(sh.Cells(i, 1), 7)
    End If
Next_i:
Next i

sh.Cells(7, 1).Activate
    
End Sub