The Problem (Q-score 10, ranked #62nd of 95 in the VBA Core archive)
The scenario as originally posted in 2013
Sometimes a certain bit of code will raise an error in an expected way, and it’s most convenient to handle it locally rather than throw it to an error handling routine where it will get mixed up with other errors of the same type. Yet you don’t want unexpected errors to be swallowed; you want them to be raised as usual.
In the (slightly contrived) example below, the FindInArray function can raise different types of error. One of them, ERR__ELEMENT_NOT_FOUND_IN_ARRAY, is more or less expected and so I want to handle it locally. But other error numbers may also occur, and if so I want them to be dealt with by the error handling routine.
I find that if I deal with some expected error numbers locally, I can’t easily “rethrow” unexpected error numbers to be dealt with elsewhere.
How do I segregate the expected errors I want to deal with locally, from unexpected errors to be dealt with in error handling routine (or elsewhere)?
On Error GoTo ErrorHandler
'Some code...
'Here I want to trap a likely/expected error locally, because the same
'error may occur elsewhere in the procedure but require different handling.
On Error Resume Next
personIndex = FindInArray(personName, personArray)
If Err.Number = ERR__ELEMENT_NOT_FOUND_IN_ARRAY Then
MsgBox "Name not found in person array. Using default person."
Else
'What if it's a different kind of error?
' .e.g. ERR__ARRAY_CONTAINS_TWO_PERSONS_WITH_SAME_NAME
'I want to rethrow it, but can't because On Error Resume Next swallows it.
End If
On Error GoTo ErrorHandler 'back to normal
'I can't rethrow it here either, because On Error Goto cleared the Err object.
'-----------------------
ErrorHandler:
Select Case Err.Number
Case ERR__ELEMENT_NOT_FOUND_IN_ARRAY
'The error number doesn't give me enough info
'to know what to do with it here!
Case ERR__ARRAY_CONTAINS_TWO_PERSONS_WITH_SAME_NAME
'Existing code to deal with this error
Case ...
I guess I could “save” the error Number, Source, Description, etc. in some other variable / object, and use those to raise an error after On Error GoTo ErrorHandler 'back to normal, (and in fact I have implemented this just to see) but that seems terribly inconvenient and clumsy.
Why community consensus is tight on this one
Across 95 VBA Core entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — niche answer (below median) (+4)
7-line VBA Core pattern (copy-ready)
This answer is my opinion on the problem at hand, perhaps viewed from a slightly different angle.
When considering this block of code:
On Error Resume Next
personIndex = FindInArray(personName, personArray)
If Err.Number = ERR__ELEMENT_NOT_FOUND_IN_ARRAY Then
MsgBox "Name not found in person array. Using default person."
Else
End If
You mention: “expected errors” in the title.
But the thing is that no error should be thrown if you know in advance that it may occur.
They are a form of validation that should in my opinion be built in into the functions in the form of conditional statements.
The before mentioned code block would be something like this on a basic level:
If Not (in_array(vArray, "Jean-Francois")) Then
MsgBox "Name not found in person array. Using default person."
End If
Which in my opinion is a lot cleaner and readable.
With a custom function that is not part of the base code, but that does your check behind the scenes. Reusable functions can be wrapped in a module that you use in a way that is very similar to a static class.
Public Function in_array(vArray As Variant, sItem As String) As Boolean
Dim lCnt As Long
in_array = False
Do Until lCnt = UBound(vArray) + 1
If StrComp(vArray(lCnt), sItem, CompareMethod.Text) = 0 Then
in_array = True
Exit Function
End If
lCnt = lCnt + 1
Loop
End Function
Even better would be to use the in_array() function from within the findInArray() function and have only 1 line of code in the basesub, which would be:
personIndex = FindInArray(personName, personArray)
Let the functions in the back handle the rest and intercept exceptions that you can foresee.
This is only an example, obviously you write the functions and return values that are useful for you and you could probably add more extensive validation.
My point is that these return values are return messages that are a part of the application / validation logic, I don’t see them as technical errors – hence, I don’t see any benefit in using an error handler for them as a custom created function exactly fits your needs in a (my opinion) much cleaner structure.
I consider it a technical error when you pass for example three arguments into the function call while it only accepts two. The error handler notifies you, after which the developer may decide to make the current function more dynamic by allowing eg. optional parameters and fixing the bug.
Error-handling details to lift with the snippet
This answer wires error flow through MsgBox / Err.Description. Keep that intact: stripping it to “make it cleaner” removes the signal you’ll need when the macro fails silently on a user machine.
Loop-performance notes specific to this pattern
The loop in the answer iterates in process. On a 2026 Office build, setting Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual around a loop of this size typically cuts runtime by 40–70%. Re-enable both in the Exit handler.
When to Use It — classic (2013–2016)
Ranked #62nd in its category — specialized fit
This pattern sits in the 97% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.
What changed between 2013 and 2026
The answer is 13 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.