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

 

[xld]

There is a builtin variable called Erl that shows the line number, but it only works if you have line numbers in your project (I don't).

I prefer to store pass the procedure name to the error handler, bit manual but it works.

 

[IkeMike]

I already pass the procedure name to the error handler, but that is not the information i`m searching for. I really want the exact function or line number that threw the error. Since i have some problems with some vba functions that are obviously not supported at all workstations, and i want to know which one create the problem.

i will check out the Erl variable. But what do you mean with "having line numbers in your project". How can i enable line numbers in my project?

 

[Colin Legg]

Example like this:

Sub example()
10        On Error GoTo ErrorHandler
 
          'let's raise an error
20        Err.Raise 9, , "There was an error"
 
30        Exit Sub
 
ErrorHandler:
 
40        MsgBox "Line:" & Erl & vbLf & _
                    "Error Number:" & Err.Number & vbLf & _
                    "Error Description:" & Err.Description
 
End Sub

There are tools which can help with maintaining line numbers, such as MZ-Tools which is free and highly recommended.

 

[IkeMike]

thanks a lot!
i will give it a try!

 

[IkeMike]

ok i tried but not really satisfied with the result something does not work as i would have expected it.

the line number is always zero and the error description is ""

i raise an error with Err.Raise 9, , "There was an error" , or i tried to raise an error in line 1040. i have not set the X variable before, there it raises an error.

here you can see the code

could somebody tell me why the line number (Erl) is always zero?

        'let's raise an error
'1040    Application.Worksheets(X).Protect Password:=getTheMainSheetProtectPWD
1050      Err.Raise 9, , "There was an error"

1060    Application.Worksheets(a).Protect Password:=getTheMainSheetProtectPWD
1070    calculateWBSNumbers = 1
1080      Else
1090    calculateWBSNumbers = 0
1100      End If
    
1110      Exit Function

ErrHandler:
1120  Application.Cursor = xlDefault
1130  Application.ScreenUpdating = True
'protect workbook
1140  ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=getTheMainSheetProtectPWD
'protect sheets
1150  protectAllSheets

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

 

[IkeMike]

ah yes.. fiy.. i m using mz tools to generate the line numbers. very helpful tool!

 

[IkeMike]

anybody an idea!?!?! this is so strange....

 

[Colin Legg]

Please post your protectAllSheets procedure, I expect it is being reset in there.

 

[IkeMike]

hmm.. i do not set the erl variable manually.. but here it is

Public Function protectAllSheets()
          Dim ws As Worksheet
          Dim i As Integer
10        i = 1
          
20        For Each ws In Sheets
30            If i < 17 And i <> 9 And i <> 16 And i <> 15 Then ws.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:=getTheMainSheetProtectPWD
40            i = i + 1
50        Next
End Function