How can I automatically populate the VBA Editor with line numbers?

Question

I want to have line numbers in my VBA code for debugging reasons. That will allow me to know where a particular error occurred.

Is there an automatic feature for this (such as an option in settings)? Or do I need to write my own macro?

If I need to write my own macro to accomplish this task, how would I go about doing such a thing?

Answer 1

You don't want line numbers.

Not for "debugging reasons", not for anything. Line numbers are deprecated for a reason: they're a relic of an ancient time before procedures even existed, and GOTO the only way to get anywhere.

Erl only returns the last encountered line number before an error was raised. This can mean misleading error logs, if you're logging errors:

Sub DoSomething()
10 On Error GoTo 50
   Debug.Print 42 / 0
30 Exit Sub

50 Debug.Print "Error raised on line " & Erl 'returns 10
End Sub

Also, line numbers only have Integer resolution (a VBA module can have up to 65,535 lines, which is twice that resolution), and will silently fail and happily report wrong line numbers if you don't keep that in mind:

Sub DoSomething()
99997 On Error GoTo 99999
99998 Debug.Print 42 / 0
99999
      Debug.Print Erl   'Prints 34462 - and which line is that?
End Sub

Any serious VBA application will use structured error handling instead.

Write small, specialized procedures (i.e. write code that follows modern-day best practices), and the line number becomes utterly meaningless.

Line numbers are a pain in the neck to maintain; they clutter up the code and make it overall harder to read (and therefore to debug).

---

That said, IIRC MZ-Tools 3 had such a functionality.

Keep in mind, that BASIC looked like this when line numbers were a thing:

10 GOSUB 100
20 GOSUB 1000
99 END
100 REM CLEAR SCREEN
110 PRINT CHR$(147)
120 RETURN
200 REM MODULO
210 LET MOD% = V%-INT(V%/FB%)*FB%
220 RETURN
1000 REM INIT VARIABLES
1010 LET FIZZ$ = "FIZZ"
1011 LET BUZZ$ = "BUZZ"
1020 LET FIZZ% = 3
1021 LET BUZZ% = 5
1030 LET MIN% = 1
1031 LET MAX% = 15
1100 PRINT FIZZ$ + ":" + STR$(FIZZ%)
1101 PRINT BUZZ$ + ":" + STR(BUZZ%)
1102 PRINT FIZZ$ + BUZZ$ + ":" + STR$(FIZZ%*BUZZ%)
1105 PRINT
2000 REM ACTUAL FIZZBUZZ LOOP
2010 FOR X = MIN% TO MAX%
2015 LET RESULT$ = STR$(X)
2020 LET FB% = FIZZ%*BUZZ%
2021 LET V% = X
2024 GOSUB 200
2025 IF MOD%=0 THEN LET RESULT$=FIZZ$+BUZZ$ : GOTO 2050
2030 LET FB% = FIZZ%
2031 GOSUB 200
2035 IF MOD%=0 THEN LET RESULT$=FIZZ$ : GOTO 2050
2040 LET FB% = BUZZ%
2041 GOSUB 200
2045 IF MOD%=0 THEN LET RESULT$=BUZZ$ : GOTO 2050
2050 PRINT RESULT$
2090 NEXT X
2099 RETURN

The above is a working Commodore 64 BASIC 2.0 fizzbuzz program. VBA has fabulous backward-compatibility. With only slight modifications, it runs in VBA:

Sub Main()
10     GoSub 100
20     GoSub 1000
99     End
100     Rem CLEAR SCREEN
110     'Debug.Print Chr$(147) 'Chr$(147) was a special character on C64
120     Return
200     Rem MODULO
210     Let Modulo% = V% - Int(V% / FB%) * FB%
220     Return
1000     Rem INIT VARIABLES
1010     Let FIZZ$ = "FIZZ"
1011     Let BUZZ$ = "BUZZ"
1020     Let FZZ% = 3
1021     Let BZZ% = 5
1030     Let Min% = 1
1031     Let Max% = 15
1100     Debug.Print FIZZ$ + ":" + Str$(FZZ%)
1101     Debug.Print BUZZ$ + ":" + Str(BZZ%)
1102     Debug.Print FIZZ$ + BUZZ$ + ":" + Str$(FZZ% * BZZ%)
1105     Debug.Print
2000     Rem ACTUAL FIZZBUZZ LOOP
2010     For X = Min% To Max%
2015     Let RESULT$ = Str$(X)
2020     Let FB% = FZZ% * BZZ%
2021     Let V% = X
2024     GoSub 200
2025     If Modulo% = 0 Then Let RESULT$ = FIZZ$ + BUZZ$: GoTo 2050
2030     Let FB% = FZZ%
2031     GoSub 200
2035     If Modulo% = 0 Then Let RESULT$ = FIZZ$: GoTo 2050
2040     Let FB% = BZZ%
2041     GoSub 200
2045     If Modulo% = 0 Then Let RESULT$ = BUZZ$: GoTo 2050
2050     Debug.Print RESULT$
2090     Next X
2099     Return
End Sub

Don't write 1980's code, we're 40 years later.