Find Error Line Number in VBA

Question

I'm trying to find the line number where my code crashes but many explanation on this site seems to complicated for my level.

My code is basically as below and I have no idea where it's breaking.

Sub1   
    Call function1  
    Call function2  
End Sub  

Other answers on this website seems to be just a short function. But I don't know where to call the function in my code or how to get a popup message. If I'm meant to put my sub1 code into their function, I don't know where either. Beginner here.

Answer 1

If your code doesn't have line numbers, then VBA has no way of giving you line numbers.

You can write VBA and make it look 1980-like to do this:

Sub1
On Error GoTo 100
10   Call Function1
20   Call Function2
90   Exit Sub
100  Debug.Print Err.Message & " on line " & Erl
End Sub

But you don't want to do that. Really, you don't need a line number.

You need smaller functions that handle runtime errors.

On Error GoTo ErrHandler

When a runtime error occurs, execution jumps to the line label called ErrHandler.

     ...
     Exit Sub
ErrHandler: '<< the line label is denoted with a colon

What goes in that handler? If you're debugging, you might want to just Stop execution there and inspect your locals:

    Stop

Then add Resume on the next line, and press F8 to step into it. Resume will return to the call that caused the error. If that's a function call, then you need to handle runtime errors in that function.

Make sure you never leave Stop and Resume instructions in production code:

Sub WhenWillThisEnd()
    On Error GoTo ErrHandler
    Debug.Print 42/0
    Exit Sub
ErrHandler:
    Resume 'jumps back to the line that caused the error
    Resume Next 'resumes execution on the line right after the one that went boom
End Sub