Buzz
Attributes and Methods of the Err Object in VBA Excel
Exploring Attributes and Methods of the Err Object
1. Err Object Attributes
The Err object comprises the following attributes:
Although in most cases, we don't need to use the Err object, sometimes it can be useful when handling errors in Excel.
For example, suppose we have a dataset below, and we want to find the square root in the adjacent cell for each number in the selected range.
By using the code snippet below to do that. However, because cell A5 contains a text string instead of a number, it will return an error:
Sub FindSqrRoot()
Dim rng As Range
Assign rng to Selection
For every cell in rng
cell.Offset(0, 1).Value equals the square root of cell.Value
Move to the next cell
End Sub
The issue is this type of notification doesn't provide us with any information about the error and where the error occurred.
In this case, we can utilize the Err object to retrieve detailed information about the error message.
For example, if we now use the following VBA code, it will halt the code execution immediately after the error occurs and display an error message dialog with the address of the cell where the error occurred:
Initiate FindSqrRoot subroutine
Declare rng as a Range variable
Assign rng to the current selection
For each cell in the range rng
Redirect to ErrHandler on Error
Assign the square root of cell value to the cell next to it
Move to the next cell
Error Handler:
DisplayErrorMsg 'Error Number:' & Err.Number & vbCrLf & _
'Error Description: ' & Err.Description & vbCrLf & _
'Error occurred at: ' & cell.Address
End ErrorHandling
The above code snippet will provide us with more detailed information, especially providing the address of the error occurrence.
If desired, we can refine this code further to ensure it runs to completion (instead of halting each time an error occurs), then provide a full list of addresses where errors occurred.
To achieve this, we use the following code snippet:
Sub CalculateSquareRoot()
Declare ErrorCells As String
Declare range As Range
In the event of an error, continue to the next line
Assign the current selection to the range variable
For Every single cell In range
Assign the square root of the cell's value to the cell one column to the right
If there is an error number other than 0
Concatenate the address of the current cell to the ErrorCells variable
Handle Errors Gracefully
Terminate If Condition
Move to Next Cell
Display Error Cells
Terminate Procedure
Conclude Procedure
The provided code snippet will execute until the end and return the square root of the selected cells. Subsequently, it will display an error message listing all cells containing errors as shown below:
2. Object Error Methods
Apart from the Err attributes used to display detailed error information, we can employ 2 Err methods for error handling.
Description Method
Clear: Resets all Err object properties.
Raise: Generates a run-time error.
2.1. Err Clear Method
For instance, suppose we have the following dataset and want to obtain the square root of all numbers in the adjacent column.
When utilizing the code snippet below to fetch the square root of all numbers in the adjacent column, it will return an error message as cells A5 and A9 contain text strings:
Sub FindSqrRoot2()
Declare ErrorCells As String
Declare rng As Range
Resume Next on Error
Assign Selection to rng
For Every cell In rng
cell.Advance(0, 1).Value = SquareRoot(cell.Value)
If Error.Number <> 0 Then
Accumulate ErrorCells with cell Location
Clear Error
End
Advance to Next cell
Display MessageBox with ErrorCells
Conclude Subroutine
Note: In this example, we utilize the Err.Clear method within the If Then statement.
When an error occurs and is trapped by the If condition, we employ the Err.Clear method to reset the error number to 0. This ensures that the If condition only traps error cells. If Err.Clear is not used, once an error occurs, it will always evaluate to true in the If condition, and the error code will not be reset.
Additionally, we can utilize the command On Error GoTo -1 to reset the error.
Note: Err.Clear and On Error Goto -1 are entirely different. Err.Clear only clears the error description and code but does not completely reset the error. This means if another error occurs within the same code, we cannot handle the error before resetting it. In such cases, we would have to use the On Error Goto -1 command.
2.2. Err Raise Method
The Err.Raise method allows us to generate a run-time error. Below is the syntax for using the Err.Raise method:
Err.Raise [number], [source], [description], [helpfile], [helpcontext]
Where all parameters are optional, and we can use them to create more meaningful error messages.
We can employ this method when an error occurs (meaning any type of error will occur), then use the method to inform the user about the error (instead of using simple, unhelpful messages that VBA displays by default).
For example, we still use the example above but want to set it up so that all cells only display numerical values.
Sub RaiseError()
Declare rng As Range
Assign Selection to rng
Go To ErrHandler when Error Occurs
For Each Cell in rng
If Cell.Value is Not Numeric Then
Raise an error with message 'Not a number' and source Cell.Address
End If
Advance to Next Cell
ErrorHandler:
Display MessageBox with Err Description and Help File
Conclude Subroutine
The above code will display an error message including a description and context file.
So there you have it, you've just explored the attributes and methods of the Err object with Mytour. If you have any doubts or questions needing clarification, feel free to leave your thoughts in the comments for us to discuss!
Furthermore, don't miss out on Mytour's insightful articles and shares:
- Check out the article All About VBA in Excel Part 1
- Check out the article All About VBA in Excel Part 2
- Check out the article All About VBA in Excel Part 3
Wishing you success!
