VBA Error handling 2

[bartekR]

HI All,

I am trying to create an error handler that would automatically inform administrator by e-mail about unexpected erorrs occured.

I am using Err.Description and Err.Number methods, however they often don't provide exhaustive explanation of a problem.
WHat i would like to do is to include additional information in the error message that would identify (and show) the line of code that created an error, in other words
- On error copy\store active line of code and add it to the error message.

Could anyone drop me a line of code that would do that?

thank you

 

[TopJack]

I don't believe VBA can record the line number when an error occurs but you could create a rough breakpoint flag that will give you a clue of its origin.

I've used this crude code to identify key blocks of code that could potentially have an unexpected error.

[COLOR=blue]
Sub force_error()
Dim checkpoint As String
On Error GoTo handle_error  [COLOR=green]'if an error occurs send to location in routine[/color]

checkpoint = "Block 1 in routine ""force_error"""   [COLOR=green]'define stage of routine[/color]

ActiveSheet.Range("A66000").Select                  [COLOR=green]'deliberately force an error[/color]

checkpoint = "Block 2 in routine ""force_error"""   [COLOR=green]'redefine stage of routine[/color]

Exit Sub                                            [COLOR=green]'end the routine normally[/color]

handle_error:   [COLOR=green]'this code should only run if an error occurs[/color]
my_error_string = "Error: " & Err.Number & " (" & Err.Description & ")" & _
                  " after checkpoint " & checkpoint

[COLOR=green]'typically my_error_string would look something like this :-
'******************************************************************
' Error: 1004 (Application-defined or object-defined error)
' after checkpoint Block 1 in routine "force_error"
'******************************************************************

'now send an e-mail to administrator with my_error_string[/color]

End Sub[/color]

You could of course record date, time, user, key variable values (be careful with this because thats where your error could be from), etc.

Hope this helps,
Jack.

 

[Andrzejek]

You can use MZTools (free from MZTools.com) for VBA to include line numbers in your code, and then capture line number (Erl)with your error.

Have fun.

---- Andy