First Error works but Subsequent Error Handling Does Not

E

Edwin Kelly

I have a module that has fairly detailed error handling (many error codes).
When I get multiple errors with the exact same error code, it catches the
first one but not the subsequent ones. It does not do this for all error
codes, just one in particular that I can see so far. The error is generated
by another object.
I've seen posts on this but when I enter a GoTo 0 it says it is not a
defined label. Not exactly sure where this needs to be because I need the
error handler to put something in a cell indicating the error. Not sure how
else to do this. Any help is much appreciated.
I have an Err.Clear at the end but it does not help. Any ideas? Here is
the snippet in the error handler.
--- code snippet ---
ErrorHandler:

If Err.Number = -2147220985 Then
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "Ivalid Tagname"
Err.Clear
GoTo NextNoTag
....more error codes handled below this
 
P

Patrick Molloy

VBA error handling is a little primitive

ON ERROR RESUME NEXT
is popular but one should avoid it in complex procedures (IMHO)

ON ERROR GOTO errorline
sets the trap - and an error condition switches the code to the line
referenced

ON ERROR GOTO 0
this switches off the previous ON ERROR statement.
 
E

Edwin Kelly

Ok thanks so much. I doctored up my code to add that just after my error
handler performs its designated function. I wanted to avoid putting my
entire code here because of length but I don't know what else to do because
that did not work. Perhaps I put the "On Error GoTo 0" in the wrong place.
here is my entire Loop code. I've not included all of it, just the loop
where I am seeing the problem. The PHDObject refers to a Honeywell DLL that
is returning the error code.

--- code section ---
'Setup object parameters
Set PhdObject = CreateObject("VisualPHD.Data")

For Loop1 = 1 To lLastRow - 3
On Error GoTo ErrorHandler <--- This is also at the begining of the procedure
If Loop1 < 20089 Then GoTo NextNoTag <---This is my kluge way to stop/restart
sTagname = shtTagList.Range("A" & Loop1 + 3).Value
Application.StatusBar = "Checking Tag: " & sTagname & " - " & Loop1 & "
of " & lLastRow - 3
shtData.Range(Cells(Loop1 + 1, 1), Cells(Loop1 + 1, 1)).Select
ActiveCell.Value = sTagname
With PhdObject
.Hostname = sHost
.tags.Add sTagname
.SampleMethod = "Raw"
.StartTime = Range("StartTime")
.EndTime = Range("EndTime")
End With

lStart = Timer
PhdObject.Fetch
lEnd = Timer
Set tag = PhdObject.tags(sTagname)
PhdObject.MoveLast
Debug.Print tag.RowCount
For i = 1 To tag.RowCount
If (tag.Confidence >= 0) Then
ActiveCell.Offset(0, 1) = tag.Units
ActiveCell.Offset(0, 2) = tag.TagDefinition.Description
ActiveCell.Offset(0, 3) = tag.Timestamp
ActiveCell.Offset(0, 4) = tag.Value
ActiveCell.Offset(0, 5) = tag.Confidence
ActiveCell.Offset(0, 6) = lEnd - lStart
Debug.Print lEnd - lStart
ActiveCell.Offset(0, 7) = ""
Exit For
ElseIf (tag.Confidence) = -1 Then
ActiveCell.Offset(0, 1) = tag.Units
ActiveCell.Offset(0, 2) = tag.TagDefinition.Description
ActiveCell.Offset(0, 3) = tag.Timestamp
ActiveCell.Offset(0, 4) = tag.Value
ActiveCell.Offset(0, 5) = tag.Confidence
ActiveCell.Offset(0, 5) = "-1"
ActiveCell.Offset(0, 7) = "-|- EOD"
PhdObject.MovePrevious
Else
ActiveCell.Offset(0, 6) = lEnd - lStart
If IsNull(Err.Number) Or Err.Number = 0 Then
ActiveCell.Offset(0, 7) = "NoData"
Else
ActiveCell.Offset(0, 7) = Err.Number & ":" & Err.Description
End If
tag.MovePrevious
End If
Next i
NextTag:
PhdObject.tags.Remove (sTagname)
NextNoTag:
On Error GoTo 0
Next Loop1

Set PhdObject = Nothing
Range("A2").Select
shtTagList.Activate
shtTagList.Range("E2").Value = Now

Application.ScreenUpdating = True
Application.StatusBar = False
Exit Sub

NoData:
MsgBox "There is no PHD data to capture, ending process.", vbOKOnly, "No Data"
Application.StatusBar = False
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:

If Err.Number = -2147220985 Then
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "Fetch Failed - Data Unpack Error"
Err.Clear
GoTo NextTag
ElseIf Err.Number = -2147220986 Then
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "EOD"
GoTo NextTag
ElseIf Err.Number = -2147220980 Then
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "Invalid Tagname"
GoTo NextTag
ElseIf Err.Number = -2147220932 Then
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "No Virtual Tag Calculation"
GoTo NextTag
ElseIf Err.Number = -2147220956 Then
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "Invalid Data"
Resume Next
ElseIf Err.Number = -214722091 Then
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "Error for " & sTagname & vbCrLf &
Err.Number & vbCrLf & Err.Description
Resume Next
ElseIf Err.Number = -2147024809 Then
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "Error for " & sTagname & vbCrLf &
Err.Number & vbCrLf & Err.Description
Resume Next
ElseIf Err.Number = -2147220923 Then
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "Invalid Login"
Exit Sub
ElseIf Err.Number = 438 Then
GoTo NextTag
Else
Debug.Print Loop1 & " of " & (lLastRow - 3) & " Tag:" & sTagname & "
Error:" & Err.Number & " Desc:" & Err.Description
ActiveCell.Offset(0, 6).Value = "Error for " & sTagname & vbCrLf &
Err.Number & vbCrLf & Err.Description
Application.StatusBar = False
Resume Next
End If

--
Edwin Kelly
Houston, TX


Patrick Molloy said:
VBA error handling is a little primitive

ON ERROR RESUME NEXT
is popular but one should avoid it in complex procedures (IMHO)

ON ERROR GOTO errorline
sets the trap - and an error condition switches the code to the line
referenced

ON ERROR GOTO 0
this switches off the previous ON ERROR statement.
 

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

Top