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.a...mming/200808/1

You can use line numbers (as in old-style Basic) and the
Erl (Error Line) variable for this:

Sub Errorhandling()
On Error GoTo ErrorHandler
10 Dim x As Integer
20 x = 0
30 x = 1 / x
Exit Sub
ErrorHandler:
MsgBox "Error occurred on line " & Erl
End Sub


There are some add-ins available to add line numbers
automatically - Google should find them.



On 10 Aug, 08:39, "Robeyx via OfficeKB.com" <u43777@uwe 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.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200808/1

You have 3 pieces of information that you can use. The first two are
straight-forward, Err.Number and Err.Description. The third needs to have
line numbers added to utilise

Sub TestError()
Dim Somevar As Double

10 On Error GoTo TestError_Error

20 Somevar = 7
30 Somevar = Somevar + 12
40 Somevar = Somevar / 0

TestError_Exit:
50 Exit Sub

TestError_Error:
60 MsgBox "Error: " & vbNewLine & _
" number: " & Err.Number & vbNewLine & _
" description: " & Err.Description & vbNewLine & _
" line number: " & Erl
70 Resume TestError_Exit

End Sub

Personally, I have never found Erl necessary, the error description usually
is enough. You could also add a procedure name constant and display that
value as well.

If you get MZTools, a free VBE utility from
http://www.mztools.com/v3/download.aspx , this has a function to
automatically add and remove line numbers.

--
__________________________________
HTH

Bob

"Robeyx via OfficeKB.com" <u43777@uwe wrote in message
news:887367d1bc4fe@uwe...
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.a...mming/200808/1

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.a...mming/200808/1

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

I should have added that it is not necessary to assign each line a line
number. When an error occurs, Erl will return the most recent line number
encountered before the error arises. For example,

Sub AAA()
Dim X As Long
Dim Y As Long
Dim Z As Long

On Error GoTo ExitSub:
1 ' Init Block
X = 1
Y = 0
Z = 3
2 ' Math Block
X = X + 1
Z = Z + 1
3 ' Calc Block
X = X + 1
Z = X / Y
Exit Sub
ExitSub:
Debug.Print "Error (" & Err.Number & " - " & _
Err.Description & " at block: " & Erl
End Sub

Here, the most recent line number prior to the error condition is displayed
as
Error (11 - Division by zero at block: 3

This is useful if you need just to determine which sectcion of the code
caused the error, not necessarily the exact line number.


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





"Chip Pearson" wrote in message
...
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

I'll have to remember this. Thanks

Barb Reinhardt



"Chip Pearson" wrote:

I should have added that it is not necessary to assign each line a line
number. When an error occurs, Erl will return the most recent line number
encountered before the error arises. For example,

Sub AAA()
Dim X As Long
Dim Y As Long
Dim Z As Long

On Error GoTo ExitSub:
1 ' Init Block
X = 1
Y = 0
Z = 3
2 ' Math Block
X = X + 1
Z = Z + 1
3 ' Calc Block
X = X + 1
Z = X / Y
Exit Sub
ExitSub:
Debug.Print "Error (" & Err.Number & " - " & _
Err.Description & " at block: " & Erl
End Sub

Here, the most recent line number prior to the error condition is displayed
as
Error (11 - Division by zero at block: 3

This is useful if you need just to determine which sectcion of the code
caused the error, not necessarily the exact line number.


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





"Chip Pearson" wrote in message
...
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