Excel VBA nested error handlers

Question

Hi All,

I am facing a strange behavior with nested error handlers.

I have a userform with his own error handler and somewhere in the code I call a function to filter an ADODB recordset. (Excel has sometimes issues with mixed data fields.)

The function has its own error handler.

Basically the code is as:

Sub UF_cmdButton

on error goto ...

variant=function

End Sub

Function Filter

on error goto ...

test

End Function

I know for sure that the test on the current record will fail because I am using:

If CDbl(RecordSet.Fields(intWherePos)) < CDbl(varWhereVal) Then blnAddRecord = True

and that field contains only "yes" or "no".

The strange thing is that it jumps to the function error handler for the first record and to the error handler of the UF for the second record. I tried already a lot of things without success.

I have a work-around, that is not the issue, I was just wondering why the first record is handled correctly and the second bumps me out.

Thanks for reading.

Answer 1

Since your UF does not appear to have a Resume statement in the error handler, the first error will bring VBA in error handling state, but it never returns out of the error handling state. And error handler MUST either be terminated with a Resume or Resume Next statement, or an Exit Sub or Exit Function. If not, the error handling state is not reset and the next error will be handled differently (cause a runtime error).

Answer 2

Have someone else look at your code mostly helps :-)

Answer 3

Hi JK,

Thank you. Adding a resume statement did indeed solve the issue. I've spent some hours yesterday trying and trying but didn't see that the resume statement was missing. Just feeling now like a beginner.

Answer 4

Hi JK,

Sure,

In the UF:

On Error GoTo Error_Sub

''' calling the UDF:

varRecordSet = RecordFilter(RecordSet, strSQl, strWhere, strWhereOperator, varWhereVal, _

         strWhere1, strWhereOperator1, varWhereVal1, _

         Headers, Me.chkIgnoreCase, Me.chkImportText, Me.opbAnd)

Exit_Sub:

   Application.ScreenUpdating = True

   Exit Sub

Error_Sub:

   MsgBox Prompt:=Err.Number & " " & Err.Description, Title:=JPE_APP_TITLE

   Resume Exit_Sub

End Sub

In the UDF:

Do Until RecordSet.EOF

      ReDim Preserve arrData(dicFieldPos.Count - 1, lngRecords)

      If intWherePos >= 0 Then   ''' condition to evaluate

         blnAddRecord = False

         On Error GoTo AddRecord

         Select Case strWhereOperator

            Case "="

               ''' 3 conditions to evaluate

               ''' field should not be null

               ''' arithmetic compare if varWhereVal is numeric

               ''' string compare else

               If Not IsNull(RecordSet.Fields(intWherePos)) Then

                  If IsNumeric(varWhereVal) Then

                     'If IsNumeric(RecordSet.Fields(intWherePos)) Then

                        If CDbl(RecordSet.Fields(intWherePos)) = CDbl(varWhereVal) Then blnAddRecord = True

                     'End If

                  Else

AddRecord:

      On Error Resume Next

      If blnAddRecord Then

         For intFields = 0 To dicFieldPos.Count - 1

            arrData(CLng(dicFieldPos.Keys(intFields)), lngRecords) = IIf(IsNull(RecordSet.Fields(dicFieldPos.Items(intFields))), "", CStr(Trim(RecordSet.Fields(dicFieldPos.Items(intFields)))))

            If blnImportText And IsNumeric(arrData(CLng(dicFieldPos.Keys(intFields)), lngRecords)) Then arrData(CLng(dicFieldPos.Keys(intFields)), lngRecords) = Chr(39) & arrData(CLng(dicFieldPos.Keys(intFields)), lngRecords)

         Next intFields

         lngRecords = lngRecords + 1

      End If

      RecordSet.MoveNext

   Loop

On Error GoTo 0

   RecordFilter = arrData

   Set dicFieldPos = Nothing

   ActiveSheet.Columns.AutoFit

It is the bold line that bumps me out for the second record, passes for the first one.

The first record brings me to the AddRecord label, the second to the Error_Sub label. As said I found already a work-around with If IsNumeric(RecordSet.Fields(intWherePos)) Then but it looks to me so strange that I don't see.

I could understand that my approach is not working for each record and bumps me out when evaluating the first record, in that case I would say I have a structural fault in the code but this is more looking as you are allowed to make one mistake.

Answer 5

Hi JP,

Can you show a tiny bit more of your code, specifically the error handling bit appears to be missing?