VBA Error Function

The VBA Error function is used to retrieve the error message that corresponds to a specific error number. It can be useful in error handling routines, allowing developers to display more detailed error messages to users.

VBA Error Function – Syntax and Arguments

Purpose:

The main purpose of the Error function is to improve the user experience by providing more informative error messages. It can also be used to troubleshoot and debug code by identifying the specific error that is occurring.

Syntax:

The syntax for the VBA Error function is:

Error(errornumber)

Arguments:

• errornumber: This is a required argument that specifies the error number for which the error message should be returned.

Example

Suppose we have a simple VBA code that divides a number by 0, which would result in a ‘Division by zero’ error. We can use the Error function to retrieve the error message and display it to the user as follows:

Sub testErrorFunction()
    On Error GoTo errorHandler
    Dim num1 As Integer
    Dim num2 As Integer
    
    num1 = 20
    num2 = 0
    num1 / num2
    
    Exit Sub
    
errorHandler:
    MsgBox "Error: " & Error(Err.Number)
End Sub

In this example, we use the ‘On Error GoTo’ statement to handle any errors that occur in the code. Once the error is encountered, the code jumps to the ‘errorHandler’ code block where we use the Error function to retrieve the error message and display it in a message box. In this case, the output would be: “Error: Division by zero”.

Remarks:

• The error number entered as an argument should correspond to a valid VBA error number. Otherwise, the Error function will return an empty string.
• The Error function can also be used without an argument, in which case it will return the error message of the most recent error that occurred (similar to the property ‘Err.Description’).
• The Error function is only available in VBA and cannot be used in regular Excel worksheets or formulas.

Important Notes:

• Sometimes, the VBA error number may not be the same as the error number displayed by Excel. In such cases, you can use the ‘Err.Number’ property to get the correct error number to use as an argument for the Error function.
• The Error function is not limited to displaying Excel-specific errors. It can also return operating system errors if they occur in VBA code.
• Certain errors, such as ‘Object required’ or ‘Invalid procedure call or argument’ do not have assigned error numbers in VBA. In such cases, using the Error function will return a blank string.

Understanding VBA Error Function with Examples

Example 1: Basic Syntax of VBA Error Function

Function Name: Error(Number)

The Error function in VBA is used to return an error message based on the error number given as an argument. It has a single parameter, the error number, which is a numeric value that corresponds to a specific error message. The function will return a string that describes the error associated with the specified error number.

• The first step is to open the VBA editor by clicking on the “Visual Basic” option under the “Developer” tab on the ribbon. Alternatively, you can use the shortcut key “Alt + F11”.
• In the VBA editor, create a new module by clicking on “Insert” and then selecting “Module”.
• In the module, write the following code:

Sub errorExample()
    Dim errorMessage As String
    errorMessage = Error(13) 'error number 13 corresponds to "Type mismatch" error
    MsgBox errorMessage
End Sub

When you run this code, the variable ‘errorMessage’ will be assigned the text “Type mismatch”. This is because the function takes in the error number as an argument and returns the corresponding error message.

Example 2: Using Error Function to Handle Errors in VBA Code

The Error function can also be used to handle errors in your VBA code. It can be used to display user-friendly error messages and to perform specific actions based on the type of error that occurs.

• Create a new module in the VBA editor and write the following code:

Sub divisionExample()
    On Error GoTo errorhandler 'specifies the location of the error handler
    Dim num1 As Integer, num2 As Integer
    num1 = InputBox("Enter the first number:")
    num2 = InputBox("Enter the second number:")
    result = num1 / num2 'this line will generate a "Division by zero" error if num2 is 0
    MsgBox "The result is: " & result
    Exit Sub
    
errorhandler:
    If Err.Number = 11 Then 'error number 11 corresponds to division by zero
        MsgBox "Cannot divide by zero"
    End If
End Sub

In this code, the error handler is specified using the “On Error” statement. The line following this statement specifies the location of the error handler, in this case, the “errorhandler” label. If an error occurs, the code will jump to the error handler and perform the specified actions. In this case, it will check the error number using the ‘Err’ function and display a user-friendly message.
If the user enters 0 as the second number, the code will go to the error handler and display the message “Cannot divide by zero”. This allows for better error handling in VBA code rather than displaying the default error messages to the user.

Example 3: Using Error Function with VBA Built-in Functions

The Error function in VBA can also be used with other built-in functions to handle errors. Below is an example of using the Error function with the ‘InputBox’ function to handle errors if the user enters invalid data.

• Open the VBA editor and insert a new module.
• Write the following code:

Sub userInput()
    On Error GoTo errorhandler
    Dim age As Integer
    age = InputBox("Enter your age:")
    
    If age < 18 Then
        Msgbox "Sorry, you must be 18 or above to use this function"
        Exit Sub
    End If
    MsgBox "Welcome! You can use this function"
    
errorhandler:
    If Err.Number = 13 Then
        MsgBox "Invalid age entered, please try again"
    End If
End Sub

In this code, the Error function is used in conjunction with the ‘InputBox’ function. If the user enters an invalid age (e.g. “ten” instead of “10”), the code will jump to the error handler and display a user-friendly message. If the user enters a valid age, the code will continue as normal.

Using the Error function in this way allows for better user experience as it prompts the user to enter valid data rather than causing the code to break or display cryptic error messages.
In conclusion, the Error function in VBA is a useful tool for handling errors and displaying user-friendly error messages. It can be used with other built-in functions to create more robust code and enhance the overall user experience. By understanding the basic syntax of the function and its various applications, you can effectively use it in your VBA projects.