VBA Throw / Raise Error – Err.Raise – Custom Error Msg

This tutorial will demonstrate how to raise a custom error in VBA.

Custom errors can be created in VBA code when the programmer wants to return a specific message to the user, rather than relying on the default error message box that will pop up, or when the user actually wants to show a custom error if a certain value is inputted into a variable or variables in the code.

Creating a Simple Custom Error Message

The Err. Raise method allows us to customize the error number and the error description in our code.

Sub TestRaiseError()
  On Error GoTo eh
  If Range("A1") <> "Fred" Then
    Err.Raise vbObjectError + 1000, , "The text in the cell A1 should say Fred."
  End If
  Exit Sub
 eh:
    MsgBox "User Error: " & Err.Description
End Sub

We need to raise a custom error number that we have documented ourself.  We use the constant vbObjectError in addition to our custom number to ensure that we do not end up using any of the error numbers that are reserved by VBA for internal use.

Creating a Custom Error Message Depending on User Input

We can raise an error that will return a specific message – depending on what information is entered into the code.

First, we can create this function:

Function TestCustomError(x As Integer, y As Integer)
  If y - x > 50 Then
    Err.Raise vbObjectError + 50, "in My workbook", "The difference is too small"
  ElseIf y - x < 50 Then
    Err.Raise vbObjectError - 55, "in My workbook", "The difference is too large"
  End If
End Function

Then we can create this code to test the function:

Sub TestErrRaise()
  On Error GoTo eh
  TestCustomError 49, 100
  Exit Sub
eh:
  MsgBox ("User Error: " & vbCrLf & Err.Description & vbCrLf & Err.Source)
End Sub

As the difference between the numbers 49 and 100 is greater than 50, the custom error description returned will be “The difference is too large“.

If we were to amend this line in the code:

TestCustomError 55, 100

Then the custom error description returned would be “The difference is too small”.

If we were then to amend the line of code to read:

TestCustomError 50, 100

Then no error would be returned by the function TestCustomError.

Replacing the Custom Excel Error Message with a Custom Message

You can use existing Excel error to create your own custom message to return to the user.

Take the example of the code below:

Sub CustomMessage()
 Dim x As Integer, y As Integer
 x = 100
 y = 0
 MsgBox x / y
End Sub

This will result in the following error being returned:

However, we can customize the message “Division by zero” by altering the code as per the example below:

Sub CustomMessage()
  On Error GoTo eh
  Dim x As Integer, y As Integer
  x = 100
  y = 0
  MsgBox x / y
  Exit Sub
  eh:
  Err.Raise Err.Number, , "You cannot divide by zero - please amend your numbers!"
End Sub

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!