Clone wiki

VB and VBA Code Library / Example_for_Exception_Handling

Example for Exception Handling in Visual Basic


The module "ErrorFunctions.bas" enables an exception handling in Visual Basic 6.0 or VBA, which is known from modern languages like C++ or Java. The main problem in VB/VBA is the generic "err" object, which cannot be changed or instantiated. So only one error can be handled at a time.

Second point is: There is no support for a "call stack" in VB/VBA, so you'll never know which way the code came along to the point of the error unless you stumble in using the debugger.

Both problems may be solved by the code I provided. Best of all is, there is no need to install any other tool or OCX/ActiveX :-)


After copying or importing the code of the module into your project, you have to customize it a bit. This may be quite inconvenient, but don't bother, it is not that difficult. See the Public Enum at the beginning:

Public Enum eProjectErrorNumbers
    ' This entry must always be the last one in this enum!
End Enum

Delete any "ERR_*" name you don't need, and add what you need. I recommend to start any error message element with "ERR_", but there is of course no other reason for it but clarity. That's all for now, you may add further elements later.


Any function, sub or property that should be "protected" with this modern style exception handling should look like this:

Public Sub DoAnything()

Const FUNCTION_NAME = "DoAnything"

' ... other declarations here ...
Dim es As tSavedError


On Error GoTo Catch

' ... your code here ...

On Error Resume Next
Set xxx = Nothing ' ... destroy instanced variables here ...

On Error GoTo 0
RaiseSavedError es

Exit Sub

es = SaveError(TypeName(Me), FUNCTION_NAME, Erl)
Select Case es.number
    Case Else
        Debug.Print es.source, es.number, es.Description
End Select
Resume Final

End Sub

There are some elements we should take a closer look to:

  • The variable "es" (error save) is used to store any error information until it's used later
  • The subroutine "es = SaveError()" takes class name, function name and error line, puts that information together with information from the the generic error object into the structure (see "typedef" in the module code) and returns it. So it can be stored locally into "es".
  • The subroutine "RaiseSavedError es" again raises the error. Along with the "On Error GoTo 0" in the previous line this error now bubbles up in the air, until it will be catched from an "On Error Goto", that hopefully exists anywhere in the current call stack.

RaiseSavedError() will never raise anything if the saved error number is zero. So there is no need to check here if there is any error saved, because this is done by the sub itself.

RaiseSavedError() has a li'l brother called ShowSavedError(), who will display the collected error information with a MsgBox(). Feel free to customize this sub as well.

If you use this programming structure consequently in your code, you'll get an exception handling of nearly the same feeling as in modern languages.

One last point: Take a look at MZ-Tools, which provides any easy way to insert the above code structure into your project by a single mouse click.

Have fun :-)