Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
I've written some code and added error handling, but the error
handling isn't kicking in when it should, and I get the usual runtime error message. Can anybody point out what I'm doing wrong here? The code is supposed to modify dates that are in the worksheet as text into proper Excel dates (in dd/mm/yyyy format). The error handling has been added to handle any cases where there is not a valid date to convert. The error handling works in some cases, but not in others. Here is the full code: - - - - - Option Explicit Sub ConvertBlackDates() '*** Converts the dates in the "Black" worksheet that are stored as text '*** into proper Excel dates. Dim lngFinalRow As Long, i As Integer Dim vaColArray(3) As Variant, j As Integer Dim strThisText As String, dtmThisDate As Date On Error GoTo ErrorHandler lngFinalRow = Cells(Rows.Count, 1).End(xlUp).Row '*** Set the columns that need to be modified vaColArray(0) = 9 vaColArray(1) = 33 vaColArray(2) = 34 '*** Loop through the data and make the necessary modifications For j = LBound(vaColArray) To UBound(vaColArray) - 1 For i = 2 To lngFinalRow strThisText = Cells(i, vaColArray(j)).Value '*** Switch on error handling here in case the value cannot be converted '*** to a date (e.g. the cell may contain a number that is not a date) On Error GoTo NextRow '*** Skip any blanks or non-numeric values If IsNumeric(Left(strThisText, 1)) Then dtmThisDate = CDate(Mid(strThisText, 4, 2) & "/" & Left(strThisText, 2) & "/" & Mid(strThisText, 7, 4)) Cells(i, vaColArray(j)).Value = dtmThisDate End If On Error GoTo ErrorHandler NextRow: Next i Next j Exit Sub ErrorHandler: MsgBox ("An error arose while trying to modify" & vbCr & _ "the date formats. Please contact your" & vbCr & _ "Excel support." & vbCr & vbCr & _ "Error description: " & Err.Description & vbCr & _ "Error source: Row " & i & " Column " & vaColArray(j)) End Sub - - - - - As an example, the error handling works fine when "strThisText" has a value of "999999", but not when it is "560307". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
You probably still got VBA handling all errors.
Goto to ToolsOptions in the VBIDE and on the General tab, in the Error trapping frame, see if Break on all Errors is set. If so, change to Break on unhandled errors, or Break in Class Module. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "tercerojista" wrote in message oups.com... I've written some code and added error handling, but the error handling isn't kicking in when it should, and I get the usual runtime error message. Can anybody point out what I'm doing wrong here? The code is supposed to modify dates that are in the worksheet as text into proper Excel dates (in dd/mm/yyyy format). The error handling has been added to handle any cases where there is not a valid date to convert. The error handling works in some cases, but not in others. Here is the full code: - - - - - Option Explicit Sub ConvertBlackDates() '*** Converts the dates in the "Black" worksheet that are stored as text '*** into proper Excel dates. Dim lngFinalRow As Long, i As Integer Dim vaColArray(3) As Variant, j As Integer Dim strThisText As String, dtmThisDate As Date On Error GoTo ErrorHandler lngFinalRow = Cells(Rows.Count, 1).End(xlUp).Row '*** Set the columns that need to be modified vaColArray(0) = 9 vaColArray(1) = 33 vaColArray(2) = 34 '*** Loop through the data and make the necessary modifications For j = LBound(vaColArray) To UBound(vaColArray) - 1 For i = 2 To lngFinalRow strThisText = Cells(i, vaColArray(j)).Value '*** Switch on error handling here in case the value cannot be converted '*** to a date (e.g. the cell may contain a number that is not a date) On Error GoTo NextRow '*** Skip any blanks or non-numeric values If IsNumeric(Left(strThisText, 1)) Then dtmThisDate = CDate(Mid(strThisText, 4, 2) & "/" & Left(strThisText, 2) & "/" & Mid(strThisText, 7, 4)) Cells(i, vaColArray(j)).Value = dtmThisDate End If On Error GoTo ErrorHandler NextRow: Next i Next j Exit Sub ErrorHandler: MsgBox ("An error arose while trying to modify" & vbCr & _ "the date formats. Please contact your" & vbCr & _ "Excel support." & vbCr & vbCr & _ "Error description: " & Err.Description & vbCr & _ "Error source: Row " & i & " Column " & vaColArray(j)) End Sub - - - - - As an example, the error handling works fine when "strThisText" has a value of "999999", but not when it is "560307". |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
Nope, Error trapping is set to "Break on Unhandled Errors"... Like I
say, the error handling is working for some errors, but not for others. As far as I can see, all errors in this code are handled. But I'm clearly missing something :-S On Mar 29, 10:16 am, "Bob Phillips" wrote: You probably still got VBA handling all errors. Goto to ToolsOptions in the VBIDE and on the General tab, in the Error trapping frame, see if Break on all Errors is set. If so, change to Break on unhandled errors, or Break in Class Module. -- HTH Bob - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
Try adapting your code with something like this
Sub ErrorTest() Dim i As Long, n As Long Dim bResNext As Boolean On Error GoTo errH For i = 1 To 3 bResNext = False If i = 3 Then i = i / 0 bResNext = True If i = 2 Then i = i / 0 'not for your needs but might also need to reset bResNext again here resNext: Next Exit Sub errH: If bResNext Then MsgBox "ignore error and resume next", , i Resume resNext End If MsgBox Err.Description, , i End Sub Regards, Peter T "tercerojista" wrote in message oups.com... I've written some code and added error handling, but the error handling isn't kicking in when it should, and I get the usual runtime error message. Can anybody point out what I'm doing wrong here? The code is supposed to modify dates that are in the worksheet as text into proper Excel dates (in dd/mm/yyyy format). The error handling has been added to handle any cases where there is not a valid date to convert. The error handling works in some cases, but not in others. Here is the full code: - - - - - Option Explicit Sub ConvertBlackDates() '*** Converts the dates in the "Black" worksheet that are stored as text '*** into proper Excel dates. Dim lngFinalRow As Long, i As Integer Dim vaColArray(3) As Variant, j As Integer Dim strThisText As String, dtmThisDate As Date On Error GoTo ErrorHandler lngFinalRow = Cells(Rows.Count, 1).End(xlUp).Row '*** Set the columns that need to be modified vaColArray(0) = 9 vaColArray(1) = 33 vaColArray(2) = 34 '*** Loop through the data and make the necessary modifications For j = LBound(vaColArray) To UBound(vaColArray) - 1 For i = 2 To lngFinalRow strThisText = Cells(i, vaColArray(j)).Value '*** Switch on error handling here in case the value cannot be converted '*** to a date (e.g. the cell may contain a number that is not a date) On Error GoTo NextRow '*** Skip any blanks or non-numeric values If IsNumeric(Left(strThisText, 1)) Then dtmThisDate = CDate(Mid(strThisText, 4, 2) & "/" & Left(strThisText, 2) & "/" & Mid(strThisText, 7, 4)) Cells(i, vaColArray(j)).Value = dtmThisDate End If On Error GoTo ErrorHandler NextRow: Next i Next j Exit Sub ErrorHandler: MsgBox ("An error arose while trying to modify" & vbCr & _ "the date formats. Please contact your" & vbCr & _ "Excel support." & vbCr & vbCr & _ "Error description: " & Err.Description & vbCr & _ "Error source: Row " & i & " Column " & vaColArray(j)) End Sub - - - - - As an example, the error handling works fine when "strThisText" has a value of "999999", but not when it is "560307". |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
Why not simplify the logic and test with IsDate ?
Dim DateStr as string DateStr=Mid(strThisText, 4, 2) & "/" & Left(strThisText, 2) & "/" & Mid(strThisText, 7, 4)) if IsDate(DateStr) then ..... Then you do not need to jump around. NickHK "tercerojista" wrote in message oups.com... I've written some code and added error handling, but the error handling isn't kicking in when it should, and I get the usual runtime error message. Can anybody point out what I'm doing wrong here? The code is supposed to modify dates that are in the worksheet as text into proper Excel dates (in dd/mm/yyyy format). The error handling has been added to handle any cases where there is not a valid date to convert. The error handling works in some cases, but not in others. Here is the full code: - - - - - Option Explicit Sub ConvertBlackDates() '*** Converts the dates in the "Black" worksheet that are stored as text '*** into proper Excel dates. Dim lngFinalRow As Long, i As Integer Dim vaColArray(3) As Variant, j As Integer Dim strThisText As String, dtmThisDate As Date On Error GoTo ErrorHandler lngFinalRow = Cells(Rows.Count, 1).End(xlUp).Row '*** Set the columns that need to be modified vaColArray(0) = 9 vaColArray(1) = 33 vaColArray(2) = 34 '*** Loop through the data and make the necessary modifications For j = LBound(vaColArray) To UBound(vaColArray) - 1 For i = 2 To lngFinalRow strThisText = Cells(i, vaColArray(j)).Value '*** Switch on error handling here in case the value cannot be converted '*** to a date (e.g. the cell may contain a number that is not a date) On Error GoTo NextRow '*** Skip any blanks or non-numeric values If IsNumeric(Left(strThisText, 1)) Then dtmThisDate = CDate(Mid(strThisText, 4, 2) & "/" & Left(strThisText, 2) & "/" & Mid(strThisText, 7, 4)) Cells(i, vaColArray(j)).Value = dtmThisDate End If On Error GoTo ErrorHandler NextRow: Next i Next j Exit Sub ErrorHandler: MsgBox ("An error arose while trying to modify" & vbCr & _ "the date formats. Please contact your" & vbCr & _ "Excel support." & vbCr & vbCr & _ "Error description: " & Err.Description & vbCr & _ "Error source: Row " & i & " Column " & vaColArray(j)) End Sub - - - - - As an example, the error handling works fine when "strThisText" has a value of "999999", but not when it is "560307". |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
NickHK, thank you very much: that's the sort of elegant solution I
like - I didn't know about the IsDate function. Everything works fine now. I'm still in the dark as to why the error handler wasn't handling the error that was arising, though... On Mar 29, 11:43 am, "NickHK" wrote: Why not simplify the logic and test with IsDate ? Dim DateStr as string DateStr=Mid(strThisText, 4, 2) & "/" & Left(strThisText, 2) & "/" & Mid(strThisText, 7, 4)) if IsDate(DateStr) then ..... Then you do not need to jump around. NickHK - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
On Mar 29, 1:41 pm, "tercerojista"
wrote: NickHK, thank you very much: that's the sort of elegant solution I like - I didn't know about the IsDate function. Everything works fine now. I'm still in the dark as to why the error handler wasn't handling the error that was arising, though... On Mar 29, 11:43 am, "NickHK" wrote: Why not simplify the logic and test with IsDate ? Dim DateStr as string DateStr=Mid(strThisText, 4, 2) & "/" & Left(strThisText, 2) & "/" & Mid(strThisText, 7, 4)) if IsDate(DateStr) then ..... Then you do not need to jump around. NickHK - Show quoted text -- Hide quoted text - - Show quoted text - I am actually now really curious about error handling - I've put together another bit of code that doesn't seem to take heed of the error handling every time. I've searched around and can't find any documentation regarding error handling not working, yet I can definitely put together code for which error handling will simply not kick in... Does anyone out there have any information regarding why error handling may choose not to work sometimes?! The two cases I have now a 1. The case explained in this thread, when a value that is not a date value is passed to Excel as if it were a date value (ie the date value is invalid) 2. An attempt is made to insert an invalid formula into a cell In both cases, an error is raised, and I'm baffled as to why these particular errors aren't picked up by the error handler. Other errors are picked up by the code. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
Error handling works. It may not appear to trigger if either
- What you are doing does not result in an error - Your code is faulty Post you code and what you consider the error condition(s). NickHK "tercerojista" wrote in message oups.com... On Mar 29, 1:41 pm, "tercerojista" wrote: NickHK, thank you very much: that's the sort of elegant solution I like - I didn't know about the IsDate function. Everything works fine now. I'm still in the dark as to why the error handler wasn't handling the error that was arising, though... On Mar 29, 11:43 am, "NickHK" wrote: Why not simplify the logic and test with IsDate ? Dim DateStr as string DateStr=Mid(strThisText, 4, 2) & "/" & Left(strThisText, 2) & "/" & Mid(strThisText, 7, 4)) if IsDate(DateStr) then ..... Then you do not need to jump around. NickHK - Show quoted text -- Hide quoted text - - Show quoted text - I am actually now really curious about error handling - I've put together another bit of code that doesn't seem to take heed of the error handling every time. I've searched around and can't find any documentation regarding error handling not working, yet I can definitely put together code for which error handling will simply not kick in... Does anyone out there have any information regarding why error handling may choose not to work sometimes?! The two cases I have now a 1. The case explained in this thread, when a value that is not a date value is passed to Excel as if it were a date value (ie the date value is invalid) 2. An attempt is made to insert an invalid formula into a cell In both cases, an error is raised, and I'm baffled as to why these particular errors aren't picked up by the error handler. Other errors are picked up by the code. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
I'm still in the dark as to why the error handler wasn't handling the
error that was arising, though... If(?) you are referring to the error handlers in the code in your first post, there is no Resume statement in this one - On Error GoTo NextRow ' code NextRow: I did suggest an approach you could have adapted into your routine purely for the error handling issue, perhaps you didn't see it. (Nick's alternative approach for your routine as a whole was of course a better solution.) Regards, Peter T |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why isn't my error handling working?!
On May 2, 11:20 am, "Peter T" <peter_t@discussions wrote:
I'm still in the dark as to why the error handler wasn't handling the error that was arising, though... If(?) you are referring to the error handlers in the code in your first post, there is no Resume statement in this one - On Error GoTo NextRow ' code NextRow: I did suggest an approach you could have adapted into your routine purely for the error handling issue, perhaps you didn't see it. (Nick's alternative approach for your routine as a whole was of course a better solution.) Regards, Peter T Thanks for the suggestion. The lack of a Resume statement does indeed seem to be what's making things fail here, and in my other code. Now I know not to put "GoTo Linelabel" at the end of the error handling procedure, but "Resume Linelabel" :-D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error handling | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
error handling not working | Excel Programming | |||
Error Handling | Excel Programming |