If you use line numbers in the code (and MZTools -- www.mztools.com -- can
do this with one mouse click), you can use the Erl function to get the line
number on which the error occured. For example,

'[Code Without Line Numbers]
Sub AAA()
Dim X As Long
Dim Y As Long
Dim Z As Long
On Error GoTo EndProc:
X = 1
Y = 0
Z = 0
Z = X / Y '--- cause an error
Exit Sub
EndProc:
MsgBox "Error On Line: " & Erl '--- displays "Error On Line 0"
End Sub

'[Code With Line Numbers]
Sub AAA()
Dim X As Long
Dim Y As Long
Dim Z As Long
On Error GoTo EndProc:
10 X = 1
20 Y = 0
30 Z = 0
40 Z = X / Y ' -- cause an error
50 Exit Sub
EndProc:
60 MsgBox "Error On Line: " & Erl '--- displays "Error On Line 40"
End Sub

MZTools, which does far far far more things than just line numbers, is a
"must have" for serious VBA development. Versions are available for VBA,
VB6, and VBNET.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)











"Barb Reinhardt" wrote in message
...
I generally try to build my code so that I use "On Error" only when
absolutely necessary, otherwise, it can take some time to find the
problem.

Sub Macro1()

Dim myWS As Worksheet

On Error Resume Next
Set myWS = Worksheets("Sheet4")
On Error GoTo 0

If myWS Is Nothing Then
MsgBox ("Worksheet doesn't exist")
Exit Sub
End If

'Continue execution if the worksheet exists

End Sub

In your current situation, can you temporarily turn off the Resume next
line
to see where it errors out?

--
HTH,
Barb Reinhardt



"Robeyx via OfficeKB.com" wrote:

A simple problem. If an OnError routine is invoked, how can you tell
which
instruction caused the error?

There must be an easy way, but I can't find it. Single-stepping is not
what
I mean, I just want there to be a field somewhere that tells me the
previous
instruction executed.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200808/1