Does 2 error handlers mess up excel?

[Nicole Seibert]

I have this bit of code:
RETRY1:
Windows(NameWorksheet & ".xls").Activate
On Error GoTo ERRORTRAP1:
Sheets("Estimated - BA Approved").Select
Columns("A:A").Select
Dim T1
T1 = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:R" & T1).Sort Key1:=Range("A3"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Sheets("NOT Estimated - BA NOT Approved").Select
Columns("A:A").Select
Dim T2
T2 = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:R" & T2).Sort Key1:=Range("A3"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

RETRY2:
Windows(OldWorksheet & ".xls").Activate
On Error GoTo ErrHandler:



And then later on I have this bit of code:
Exit Sub
Else
End If
Next j
Next i
STOP2:
MsgBox ("This program has run and found no matching project numbers. You
may now combine Demand data.")
End Sub
ERRORTRAP1:
NameWorksheet = InputBox("You have entered an incorrect name of the new
Demand worksheet. Please try again.")
Resume RETRY1
ERRHANDLER:
OldWorksheet = InputBox("You have entered an incorrect old Demand worksheet
name. Please try again.")
Resume RETRY2:


Can Excel not handle two error handling statements? Does the "seek until
you find: ERRHANDLER" not work if you have already searched for a different
goto line? What's the deal?

Thanks,
Nicole

 

[Jim Thomlinson]

Nope. VBA is able to handle multiple error handlers. It is very difficult
from your code to determine just what is causing the errors, but from what I
can tell you might be overusing the errorhandler (see my little rant at the
bottom of this post). You can not use an error handler within another error
handler, which I think is what is happening to you. Your code throws an error
which send you to the error handler. Within that code you are trying to react
to an error generated within the error handler itself which is not allowed.

IMO errorhandling is only to be used as a last resort to allow your code to
react to rare or unforseeable events. In short if you can reasonably forsee a
problem your code should handle that in the regular flow and not through an
error handler. A legitimate error to catch would be one like trying to access
a file on the network, but the network is down, or trying to read from a file
but somehow the file was moved. These are thing you can not reasonably
forsee, so an error handler is a great way to deal with them. Part of my
reasoning for this is once in the error handler, if an error is thrown, there
is not much you can do.

 

[Edward Ulle]

I believe before label ERRORTRAP1: it should be Exit Sub not End Sub.

As it is it should not compile since the label is outside of sub.

And End Sub should be after Resume RETRY2.



*** Sent via Developersdex http://www.developersdex.com ***

 

[Nicole Seibert]

Thanks for the help.
In this case I am asking the user to input workbook names (this is near the
beginning of my code) and if they type these in incorrectly I want some way
to catch this error and give them a chance to retype the workbook name. This
has not worked so far.

 

[Jim Thomlinson]

Give something similar to this a try...

dim wbk as workbook

on error resume next
set wbk = Windows(NameWorksheet & ".xls")
do while wbk is nothing
'reprompt for workbook
NameWorksheet = inputbox("Enter the name")
set wbk = Windows(NameWorksheet & ".xls")
loop
on error goto 0
wbk.activate