Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
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. -- HTH... Jim Thomlinson "David" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
Thanks, Jim and Bill. That helps enormously.
-David |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
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 -- Thank You, Steve "Bill Schanks" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
Use Resume, not Goto, in your error handling block.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Steve D" wrote in message ... 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 -- Thank You, Steve "Bill Schanks" wrote: 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling - On Error GoTo doesn't trap error successfully
Perfect!!! Thanks.
-- Thank You, Steve "Chip Pearson" wrote: Use Resume, not Goto, in your error handling block. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Steve D" wrote in message ... 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 -- Thank You, Steve "Bill Schanks" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how error-trap "no cells were found error" | Excel Programming | |||
ON Error goto fails on 2nd error | Excel Programming | |||
On Error doesn't trap Application.Run error | Excel Programming | |||
How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
Error Handler not handling error... | Excel Programming |