On Error Not Working...

[egun]

I have some macros that go down a list of stock symbols, grab historical data
from Yahoo!, and then graph the data. The macros work well, but there's one
glitch. To test the code, I put a bogus stock symbol in (FMA - doesn't
exist). I have an "On Error Goto" statement just before the web query, and
when I try to query this symbol, the error trips and sends the macro to the
recovery code.

However, when I put two bad symbols in the list, the first one still trips
the "On Error", but the second one doesn't - instead the VBA debugger gives
me a run time error '1004' message: "The Internet site reports that the item
you requested could not be found. (HTTP/1.0 404)"

I can't figure out why the error trapping doesn't work the second time
around. The bogus stock symbols can be next to each other, or separated by
other "good" symbols, and the second one still doesn't work. Any ideas?

Thanks,

Eric

 

[Chip Pearson]

The "Goto" in an On Error statement works differently than a normal
Goto. If there is a On Error Goto <label> statement in effect and an
error occurs, VBA is running in "error mode" and On Error will not
handle any other errors. You have to get VBA out of "error mode" and
back into "normal mode". You do this with a Resume or Resume Next
statement (or by exiting the procedure). Thus your code would look
something like

Sub AAA()

On Error GoTo ErrHandler
'
' code that raises an error
'
ResumePoint:
'
' more code
'
Exit Sub
ErrHandler:
' handle the error
Resume Next
' or
Resume ResumePoint

End Sub

In the error handler block (the code between ErrHandler and End Sub),
you cannot have any error handling and you can't use Goto. Use Resume
instead.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)