Error Handling - Excel VBA [RESOLVED]

[Mitch_s_s]

What i want to be able to do is the following:

i want be able to extract the line of code that is running that cause the error

e.g if you had on line 32
average = 32/0

this statement would give you an error, is there anyway i could extract this line number.
What i then want to do is to display a message box saying
"Please contact your administrator quoting L32 and " & Err

where L32 is the line number, and Err is obviously the error number.

Thanks
Mitch

[Shuja Ali]

To get the Line numbers, you need to add the line numbers in the code. A search here would have just shown you what you have to use

Public Sub ErrorLineNumber()
    On Error GoTo ErrHandler
1:     Dim x As Integer
2:     Dim y As Integer
3:     x = 0
4:     y = 10
5:     Dim z As Integer
6:     x = y / x
8:     MsgBox "Succeeded"
9:     MsgBox "Just another msgbox"
    Exit Sub

ErrHandler:
    MsgBox "Error Occured at Line Number " & Erl
End Sub

[Mitch_s_s]

Erl returns 0

[Mitch_s_s]

i removed the numbers!
so all my subs need their lines to be numbered?

no other way no?

thanks by the way!

[si_the_geek]

You must number each line for it to work.. tho you can use an addin like MZTools (link in my signature) to automatically add them for you.

[Mitch_s_s]

does this add anything to processing time?

bit of a pain doin that to my already created sub

might check out the add in

[si_the_geek]

does this add anything to processing time?

Not noticeably.

bit of a pain doin that to my already created sub

might check out the add in

The addin will do it instantly, for the entire project if you like.

[Mitch_s_s]

hmm interesting,
it's just an excel add in then, that writes to the vbproject?

[si_the_geek]

It a VBA (or VB) addin, which has lots of useful features.. when I first got it, it saved me about 25% of my coding effort.

[Mitch_s_s]

cheers man, might install it at home

tight security in work, so they may not allow me to install it there!

Thanks for this!