Error Handling - On Error GoTo doesn't trap error successfully

D

David

When you use the "On Error GoTo" approach to trap an error, is it
necessary to "clear out" the error in some way before a later instance
of the same type of error can be trapped?

I have a program that loops through an excel table, and the error
handling seems to work the first time bad data is encountered (in this
case, when the cell has a string "N/A" rather than the expected
currency data type) but then when it hits a second instance of the same
error (Run TIme Error '13' Type Mismatch) it stops dead in its tracks.

Any suggestions?
 
J

Jim Thomlinson

From your description of the problem you should be handling the #N/A in the
normal flow of execution, not an errorhandler (IMO). If you can reasonably
expect a problem it is not an error. It seems to me that with

if not isnumeric('whatever you are looking at') then

Could avoid throwing the error in the first place.

That being said, you can not catch an error within an error handler, so if
an error is being thrown while you are in the errorhandler then you need to
rethink your error handler. To clear an error use...

err.clear

If you need more help you will have to post some code.
 
D

David

Jim - Thanks for the reply. I've put in the relevant section of code
below but let me try to describe the situation more clearly to spare
you the time to wade through the whole listing. I appreciate your time
and will be as concise as I can.

I have programmed a procedure to go to Yahoo's quote server and pull
down quotes. The quoteserver sends back a CSV file that I've parsed
into columns. This creates a table which has a row for each ticker and
a column for each data item about the ticker, e.g. Open, High, Low,
Close, Volume, CompanyName, and so on. This procedure works fine.

If Yahoo does not have a certain data item, then the quote server sends
down the string "N/A" This is simply a string, not to be confused with
the excel error code which can be detected with ISNA().

The next procedure I wrote (excerpted below) attempts to retrieve the
value from the page where Yahoo puts it and assign it to a variable.
The error happens in this line or ones like it:

cLow = RetrieveData(rRetrData, sTicker, "low")

where RetrieveData() is a function that I wrote which takes a range,
column name and row name and finds the data item sought. sTicker is a
string variable which is the row name and "Low" is the header for the
column.

The RetrieveData function goes to work and comes back with a string
"N/A" which of course does not fit into the variable cLow which is Dim
as Currency. This causes the Type Mismatch Error.

All I really need my procedure to do is to stop trying to process that
particular ticker, and skip to the next ticker.

Any suggestions as to better ways to make the program get past the
missing data would be highly appreciated. I know I could just Dim the
variables as variants instead of currency but that just makes the
problem resurface downstream in later processing when other procedures
try to do mathematical operations on the data.





Sub CalculateIntradayResults()

Dim cLast As Currency 'last price
Dim cHigh As Currency 'intraday high from Yahoo, from the current
refresh cycle
Dim cLow As Currency 'intraday Low from Yahoo from the current
refresh cycle
Dim cPriorHigh As Currency ' intraday high from the previous
refresh cycle
Dim cPriorlow As Currency 'intraday low from the previous refresh
cycle
Dim cPriorHighSinceLow 'highest price since intraday low in prev
refresh cycle
Dim cPriorLowSinceHigh ' Lowest price since intraday high in prev
refresh cycle

'step through the retrieved data one ticker at a time and extract
values to variables
For iRow = iFirstRow To iLastRow
sTicker = rIntraCalc.Cells(iRow, 1)
On Error GoTo MissingData
cLast = RetrieveData(rRetrData, sTicker, "Last")
On Error GoTo MissingData
cHigh = RetrieveData(rRetrData, sTicker, "High")
On Error GoTo MissingData
cLow = RetrieveData(rRetrData, sTicker, "low")
On Error GoTo MissingData
cPriorHigh = RetrieveData(rIntraCalc, sTicker, "IntraHigh")
On Error GoTo MissingData
cPriorlow = RetrieveData(rIntraCalc, sTicker, "IntraLow")
On Error GoTo MissingData
cPriorHighSinceLow = RetrieveData(rIntraCalc, sTicker,
"HiSinceLow")
On Error GoTo MissingData
cPriorLowSinceHigh = RetrieveData(rIntraCalc, sTicker,
"LowSinceHi")

'once all the data has been captured in variables, test some
conditions and record results
If cLast > cPriorHigh Then
[skipped code not relevant to this problem]
End If

[ skipped code not relevant to this problem]

GoTo ResumeWithoutError: ' this skips the error message below
if no problems.
MissingData:
'MsgBox "missing data problem with " & sTicker & " in calculate
intraday results"
' this will fire once at the end of run because the last cell
is blank.
ResumeWithoutError:
Next iRow

End Sub
 
D

David

Jim - Thanks for the reply. I've put in the relevant section of code
below but let me try to describe the situation more clearly to spare
you the time to wade through the whole listing. I appreciate your time
and will be as concise as I can.

I have programmed a procedure to go to Yahoo's quote server and pull
down quotes. The quoteserver sends back a CSV file that I've parsed
into columns. This creates a table which has a row for each ticker and
a column for each data item about the ticker, e.g. Open, High, Low,
Close, Volume, CompanyName, and so on. This procedure works fine.

If Yahoo does not have a certain data item, then the quote server sends
down the string "N/A" This is simply a string, not to be confused with
the excel error code which can be detected with ISNA().

The next procedure I wrote (excerpted below) attempts to retrieve the
value from the page where Yahoo puts it and assign it to a variable.
The error happens in this line or ones like it:

cLow = RetrieveData(rRetrData, sTicker, "low")

where RetrieveData() is a function that I wrote which takes a range,
column name and row name and finds the data item sought. sTicker is a
string variable which is the row name and "Low" is the header for the
column.

The RetrieveData function goes to work and comes back with a string
"N/A" which of course does not fit into the variable cLow which is Dim
as Currency. This causes the Type Mismatch Error.

All I really need my procedure to do is to stop trying to process that
particular ticker, and skip to the next ticker.

Any suggestions as to better ways to make the program get past the
missing data would be highly appreciated. I know I could just Dim the
variables as variants instead of currency but that just makes the
problem resurface downstream in later processing when other procedures
try to do mathematical operations on the data.





Sub CalculateIntradayResults()

Dim cLast As Currency 'last price
Dim cHigh As Currency 'intraday high from Yahoo, from the current
refresh cycle
Dim cLow As Currency 'intraday Low from Yahoo from the current
refresh cycle
Dim cPriorHigh As Currency ' intraday high from the previous
refresh cycle
Dim cPriorlow As Currency 'intraday low from the previous refresh
cycle
Dim cPriorHighSinceLow 'highest price since intraday low in prev
refresh cycle
Dim cPriorLowSinceHigh ' Lowest price since intraday high in prev
refresh cycle

'step through the retrieved data one ticker at a time and extract
values to variables
For iRow = iFirstRow To iLastRow
sTicker = rIntraCalc.Cells(iRow, 1)
On Error GoTo MissingData
cLast = RetrieveData(rRetrData, sTicker, "Last")
On Error GoTo MissingData
cHigh = RetrieveData(rRetrData, sTicker, "High")
On Error GoTo MissingData
cLow = RetrieveData(rRetrData, sTicker, "low")
On Error GoTo MissingData
cPriorHigh = RetrieveData(rIntraCalc, sTicker, "IntraHigh")
On Error GoTo MissingData
cPriorlow = RetrieveData(rIntraCalc, sTicker, "IntraLow")
On Error GoTo MissingData
cPriorHighSinceLow = RetrieveData(rIntraCalc, sTicker,
"HiSinceLow")
On Error GoTo MissingData
cPriorLowSinceHigh = RetrieveData(rIntraCalc, sTicker,
"LowSinceHi")

'once all the data has been captured in variables, test some
conditions and record results
If cLast > cPriorHigh Then
[skipped code not relevant to this problem]
End If

[ skipped code not relevant to this problem]

GoTo ResumeWithoutError: ' this skips the error message below
if no problems.
MissingData:
'MsgBox "missing data problem with " & sTicker & " in calculate
intraday results"
' this will fire once at the end of run because the last cell
is blank.
ResumeWithoutError:
Next iRow

End Sub
 
J

Jim Thomlinson

First off with your errorhandler, you only need to write it once at the top
of the code. Once set this is what the program will do in case of an error
unless you specify otherwise. Something like this

Sub Whatever()
On Error goto Errorhandler 'Ineffect until otherwise stated
'Do this that and the other thing
'Now we are going to do something that will trow and error somethimes
on error resume next 'Now this is in effect
set myWorkbook = workbooks("MyBook.xls") 'Error if this file is not open
on error got errorhandler 'Back to the original error handler
if myWorkbook is nothing then
workbooks.open("C:\MyBook.xls")
set myWorkbook = workbooks("MyBook.xls")
endif
ErrorHandler:
end sub

Your function RetrieveData returns a variant I suspect? If not it probably
should. Then perhaps something more like this will work.

dim varReturnValue as variant
varReturnValue = RetrieveData(rRetrData, sTicker, "low")
if is numeric(varReturnValue) then
cLow = cdbl(varReturnValue)
else
'Do something else...
end if
--
HTH...

Jim Thomlinson


David said:
Jim - Thanks for the reply. I've put in the relevant section of code
below but let me try to describe the situation more clearly to spare
you the time to wade through the whole listing. I appreciate your time
and will be as concise as I can.

I have programmed a procedure to go to Yahoo's quote server and pull
down quotes. The quoteserver sends back a CSV file that I've parsed
into columns. This creates a table which has a row for each ticker and
a column for each data item about the ticker, e.g. Open, High, Low,
Close, Volume, CompanyName, and so on. This procedure works fine.

If Yahoo does not have a certain data item, then the quote server sends
down the string "N/A" This is simply a string, not to be confused with
the excel error code which can be detected with ISNA().

The next procedure I wrote (excerpted below) attempts to retrieve the
value from the page where Yahoo puts it and assign it to a variable.
The error happens in this line or ones like it:

cLow = RetrieveData(rRetrData, sTicker, "low")

where RetrieveData() is a function that I wrote which takes a range,
column name and row name and finds the data item sought. sTicker is a
string variable which is the row name and "Low" is the header for the
column.

The RetrieveData function goes to work and comes back with a string
"N/A" which of course does not fit into the variable cLow which is Dim
as Currency. This causes the Type Mismatch Error.

All I really need my procedure to do is to stop trying to process that
particular ticker, and skip to the next ticker.

Any suggestions as to better ways to make the program get past the
missing data would be highly appreciated. I know I could just Dim the
variables as variants instead of currency but that just makes the
problem resurface downstream in later processing when other procedures
try to do mathematical operations on the data.





Sub CalculateIntradayResults()

Dim cLast As Currency 'last price
Dim cHigh As Currency 'intraday high from Yahoo, from the current
refresh cycle
Dim cLow As Currency 'intraday Low from Yahoo from the current
refresh cycle
Dim cPriorHigh As Currency ' intraday high from the previous
refresh cycle
Dim cPriorlow As Currency 'intraday low from the previous refresh
cycle
Dim cPriorHighSinceLow 'highest price since intraday low in prev
refresh cycle
Dim cPriorLowSinceHigh ' Lowest price since intraday high in prev
refresh cycle

'step through the retrieved data one ticker at a time and extract
values to variables
For iRow = iFirstRow To iLastRow
sTicker = rIntraCalc.Cells(iRow, 1)
On Error GoTo MissingData
cLast = RetrieveData(rRetrData, sTicker, "Last")
On Error GoTo MissingData
cHigh = RetrieveData(rRetrData, sTicker, "High")
On Error GoTo MissingData
cLow = RetrieveData(rRetrData, sTicker, "low")
On Error GoTo MissingData
cPriorHigh = RetrieveData(rIntraCalc, sTicker, "IntraHigh")
On Error GoTo MissingData
cPriorlow = RetrieveData(rIntraCalc, sTicker, "IntraLow")
On Error GoTo MissingData
cPriorHighSinceLow = RetrieveData(rIntraCalc, sTicker,
"HiSinceLow")
On Error GoTo MissingData
cPriorLowSinceHigh = RetrieveData(rIntraCalc, sTicker,
"LowSinceHi")

'once all the data has been captured in variables, test some
conditions and record results
If cLast > cPriorHigh Then
[skipped code not relevant to this problem]
End If

[ skipped code not relevant to this problem]

GoTo ResumeWithoutError: ' this skips the error message below
if no problems.
MissingData:
'MsgBox "missing data problem with " & sTicker & " in calculate
intraday results"
' this will fire once at the end of run because the last cell
is blank.
ResumeWithoutError:
Next iRow

End Sub
Click to expand...
 
B

Bill Schanks

One thing I would suggest ... in the error handling routine do you
have:

Goto xxx

OR

Resume xxx

If you have goto then the error routine will not catch a second error,
as it is technically still in the error handling step.
 
S

Steve D

I am having this problem and it is because of a GoTo in the error handler.
The problem is that I need to stop the code on the error and go back to a
specific point in the macro or I get stuck in a loop. Below is a brief piece
of my code. Any help would be greatly appreciated.

Sub abc()

Dim.........
Set Variable Values............

Res1:
Do While Range("PathList!A" & v_pathrow).Value <> Empty

v_path = Range("PathList!A" & v_pathrow).Value
On Error GoTo ErrorLg
Workbooks.Open Filename:=v_path '*****Point of
most errors
Sheets("Cover Page").Select
v_cc = Range("c16").Value
v_currow = 1
Do While Range("F" & v_currow).Value <> "Total Count"
v_currow = v_currow + 1
Loop '***Loop I get stuck in


ActiveWindow.Close (False)


v_pasterow = v_pasterow + 1
v_pathrow = v_pathrow + 1
Loop
Rows(v_pasterow & ":" & v_pasterow + 1).Select
Range("A18").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Exit Sub

ErrorLg:
Range("ErrorLog!A" & v_errorrow).Value = v_path
v_errorrow = v_errorrow + 1
v_pathrow = v_pathrow + 1
Rows(v_pasterow & ":" & v_pasterow).Select
Selection.Delete Shift:=xlUp
Err.Clear
GoTo Res1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Set Error handling INSIDE error-trap 6
Calling Error Handling in VBA 0
Calling Error Handling in VBA 0
Error Handling with Worksheetfunction.Match 3
why doesn't on error work? 7
On Error Goto 9
Error Handling then continue running 3
Filter, Delete and Error handling - need help 4

Top