In this Article
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 EasyStop 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!