get function that created the error

[IkeMike]

Hi folks,

i implemented some error handling in my vba project. when an error occurs i catch it and present some msg-box to the user.

now i wanted to know whether it is possible to get the function that created the error. or the line number where the error occured would also help.

thus simple question: is it somehow possible to trace back the error to get to know where the error occured?

kind regards
tom

 

[IkeMike]

holy cow.. you are right

i commented out the lines before the error message and now it works!

it works this way round:

ErrHandler:

1110  Application.Cursor = xlDefault
1120  Application.ScreenUpdating = True

1130  MsgBox "An unexpected error occured. Please note the message of the next message box and submit it to your pm-tool IT support team."
1140      MsgBox "Line:" & Erl & vbLf & _
                    "Error Number:" & Err.Number & vbLf & _
                    "Error Description:" & Err.Description & vbLf & _
                    "Internal Error Code: 001" & vbLf & _
                    "Application.Version " & Application.Version & vbLf & _
                    "Application.OperatingSystem " & Application.OperatingSystem

'protect workbook
1150  ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=getTheMainSheetProtectPWD
'protect sheets
1160  protectAllSheets

thank you so much!!
do you know why the erl line is reseted in the protectAllSheets procedure?

 

[Colin Legg]

Okay... good work, so what is getTheMainSheetProtectPWD?

 

[IkeMike]

it just returns the password for the workbook. one line of code.

Private Function getTheMainSheetProtectPWD() As String
getTheMainSheetProtectPWD = "thepassword"

End Function

 

[Colin Legg]

I'll walk you through a technique so you can determine why Erl is being cleared - adding a watch to Erl.

To show the watch window go to View | Watch Window.

In your procedure (the old one with the problem, not the amended one!) on line 1170, right click on Erl and choose Add Watch. In the context frame change the scope to (All Procedures) and (All Modules). In the watch type frame choose the "break when value changes" options - this will pause code execution each time Erl changes value.

Now when you run your procedure it should break on line 1140 (as a result of Erl changing to 1050). Then press F5 to resume code execution. It will break again when the Erl value changes again. At this point you need to examine the line prior to the one highlighted by the debugger.

HTH

 

[IkeMike]

ok obviously the error line (erl) is reseted in this code line. i just tried it out..

ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=getTheMainSheetProtectPWD

it seems like the function "ActiveWorkbook.Protect" resets the erl variable!?

edit: this post is not a reply to the previous post. i will try this watch stuff afterwards..

 

[IkeMike]

ok this is the reply to the watch-variable post:

the erl variable is obviously reseted by the calls of

On Error Resume Next And
On Error GoTo 0

this could make sense.. what do you think?

ah yes.. here is the getTheMainSheetProtectPWD which i m using and which i did not posted before. because i removed exactly the two calls that resulted in the behaviour and which are not very usefull. at least in this method..

Private Function getTheMainSheetProtectPWD() As String
    On Error Resume Next
        getTheMainSheetProtectPWD = "thepassword"
    On Error GoTo 0
End Function

 

[Colin Legg]

ok this is the reply to the watch-variable post:

the erl variable is obviously reseted by the calls of

On Error Resume Next And
On Error GoTo 0

this could make sense.. what do you think?

Good work and yes - that makes perfect sense.

But those two lines aren't in any of your previous posts, so where do they come from?

 

[IkeMike]

hehe.. yes you are right

i removed them before posting the source since i thought these two lines make no sense and i did not wanted to post it..

funny that exactly these two lines resulted in the behaviour we were searching for.. my fault ..

have a nice day!

 

[Colin Legg]

ok this is the reply to the watch-variable post:

the erl variable is obviously reseted by the calls of

On Error Resume Next And
On Error GoTo 0

this could make sense.. what do you think?

ah yes.. here is the getTheMainSheetProtectPWD which i m using and which i did not posted before. because i removed exactly the two calls that resulted in the behaviour and which are not very usefull. at least in this method..

Private Function getTheMainSheetProtectPWD() As String
    On Error Resume Next
        getTheMainSheetProtectPWD = "thepassword"
    On Error GoTo 0
End Function

Right... so On Error Resume Next clears the err object and erl. That is what is causing the problem. What you can do to avoid the issue, is store the relevant values in variables before any further action. That way you can be sure you won't lose them prematurely.

As a follow up to this thread, you might find the following to be interesting and informative:

http://www.cpearson.com/excel/ErrorHandling.htm
http://www.fmsinc.com/TPapers/vbacode/Debug.asp
http://www.keysound.com/html/line_numbers.htm


Hope that helps...

 

[Colin Legg]

hehe.. yes you are right

i removed them before posting the source since i thought these two lines make no sense and i did not wanted to post it..

funny that exactly these two lines resulted in the behaviour we were searching for.. my fault ..

have a nice day!

No worries! The upside is that we got to use the watch window, so hopefully you'll find that technique helpful in the future.



Edit: By the way, you can delete the watch by right clicking on the expression in the watch window and choosing Delete Watch.