Error handling in VBA is crucial for managing runtime errors and ensuring your code runs smoothly. Here’s how it works in a couple of different common scenarios:

1. Standard Error in a Single Procedure:
   • When an error occurs in a single procedure, you can use the On Error statement to define how VBA should handle it. The common forms are:
     • On Error GoTo [label]: Directs VBA to a specific label in your code where you handle the error.
     • On Error Resume Next: Tells VBA to continue with the next line of code after the error.
     • On Error GoTo 0: Disables any enabled error handler in the current procedure[1][2].
   Example:

   Sub ExampleProcedure()
       On Error GoTo ErrorHandler
       ' Code that might cause an error
       Exit Sub
   ErrorHandler:
       MsgBox "An error occurred: " & Err.Description
   End Sub

2. Error in a Nested Function with Error Handling:
   • If an error occurs in a nested function that also has error handling, the error is handled by the error handler in the nested function first. If the error handler in the nested function does not resolve the error, control returns to the calling procedure’s error handler.If the nested function’s error handler resolves the error, execution resumes in the nested function. If not, it propagates back to the calling procedure. (This means if you don’t use a Resume statement, the error will bubble up to the parent’s error handling specification)
   
   Example:

   Sub MainProcedure()
       On Error GoTo MainErrorHandler
       Call NestedProcedure
       Exit Sub
   MainErrorHandler:
       MsgBox "Error in MainProcedure: " & Err.Description
   End Sub

   Sub NestedProcedure()
       On Error GoTo NestedErrorHandler
       ' Code that might cause an error
       Exit Sub
   NestedErrorHandler:
       MsgBox "Error in NestedProcedure: " & Err.Description
       Resume Next
   End Sub

3. Additional Error in the Error Handler:
   • If an error occurs within an error handler, VBA will look for an error handler in the calling procedure. If none exists, the error becomes unhandled and VBA will display an error message and halt execution.
   • To manage this, you can use nested error handlers or ensure that your error handlers are robust enough to handle potential errors.
   Example:

   Sub ExampleProcedure()
       On Error GoTo ErrorHandler
       ' Code that might cause an error
       Exit Sub
   ErrorHandler:
       On Error GoTo NestedErrorHandler
       ' Code that might cause another error
       Exit Sub
   NestedErrorHandler:
       MsgBox "An additional error occurred: " & Err.Description
       Resume Next
   End Sub

By using these techniques, you can effectively manage errors in your VBA code and ensure that your procedures handle unexpected issues gracefully[1][2][3]. If you have any specific scenarios or further questions, feel free to ask!

References

[1] VBA On Error – Error Handling Best Practices – Automate Excel

[2] On Error statement (VBA) | Microsoft Learn

[3] VBA Error Handling – VBA Planet