Why isn't my error handling working?!

[tercerojista]

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".

 

[Bob Phillips]

You probably still got VBA handling all errors.

Goto to Tools>Options 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]

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

 

[Peter T]

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

 

[NickHK]

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]

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...

 

[tercerojista]

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...







- 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 are:
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.

 

[NickHK]

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

 

[Peter T]

I'm still in the dark as to why the error handler wasn't handling the
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

 

[tercerojista]

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