Using Macro Errors in VBA

Error Handling

To handle errors in excel, VBA can either keep executing the program when it encounters errors using "On Error Resume Next" statement, or they could either throw relevant messages using which one could identify the error.

We start by declaring two range objects and initialising the range object with a selected range.

Code

Dim rng As Range, cell As Range
Set rng = Selection

Now since we want to calculate the square root of each cell from a randomly selected range, we use a “For Each Next loop” for this:

 Code

For Each cell In rng
Next cell

Now add the following line to the loop.

Code

On Error Resume Next

Next, we calculate the square root of a value. In Excel VBA, we can use the “Sqr” function for the same. Add the following line to the loop:

Code

cell.Value = Sqr(cell.Value)

Now exit the VBA editor and test the program out.

Result

Explanation

Here, Excel VBA has ignored all cells containing invalid values such as negative numbers and text, and without using the 'On Error Resume Next' statement in the same program should throw two errors. Be careful to only use the 'On Error Resume Next' statement when you are sure ignoring errors is OK.

Err Object

In Excel VBA, when errors are thrown, an error object has all the details related to that error which is filled in its properties.

Here we will try to use the same program we previously used to generate square roots of numbers.

Code

Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In rng
    On Error GoTo InvalidValue:
    cell.Value = Sqr(cell.Value)
Next cell
Exit Sub
InvalidValue:
MsgBox Err.Number & " " & Err.Description & " at cell " & cell.Address
Resume Next

One should get the following result when one selects a Range("B2:B6") and clicks the command button on the sheet: 

Result

Explanation

when an error occurs, the number attribute/property of the 'Err' object is filled with a unique error number of the current error. The Description attribute/property is filled with the error description of that error.

Subscript Out of Range

This is a frequently occurring error in excel VBA, which happens when one refers to a non-existent value in the sheet or a non-existent array element. 

Ex. The initial step here would be to declare two range objects. We call the Range objects rng and cell.

Code

Workbooks("sales.xlsm").Close

Result when there's no open workbook with this name:

Ex. A Worksheets collection in Excel VBA contains all the Worksheet objects present in the workbook. Since we only have 3 worksheets, the line below that tries to place the word Hello into cell A1 on the 4th worksheet causes error.

Code

Worksheets(4).Range("A1").Value = "Hello"

Result when there's no 4th workbook:

Explanation

Here, a 'subscript out of range' error pops up as there is no 4th worksheet, so to fix this error, one can change the 4 to a 1, 2 or 3 (or insert a new worksheet by clicking the plus sign).

FAQs

1. How can a macro be interrupted in Excel VBA?

     One can interrupt a macro in Excel by pressing Esc or Ctrl + Break.

2. How can macro comments be used in Excel VBA?

    To let Excel VBA know that you want to insert a comment, precede the text with an apostrophe.

Key Takeaways

Congratulations on finishing the blog!! After reading this blog, you will grasp the concept of handling and debugging errors using different strategies, techniques and utility functions in Excel VBA.

If you are preparing yourself for the top tech companies, don't worry. Coding Ninjas has your back. Visit this link for a well-defined and structured material that will help you provide access to knowledge in every domain.