Excel VBA: Dealing with Excel Errors

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video.

In this blog, we will learn how to deal with Excel errors using Excel VBA.

An incorrect formula in Excel can display an error value in Excel. These can be #N/A, #VALUE!, #DIV/0, #NAME?, #REF!, #NULL!, #NUM!.

Trying to loop over data with errors and attempting to read the cell value, will throw an error in VBA. To avoid this, we would need to check whether there exists an error in a cell, and then, either deal with the error or skip it and move on with our code. We can check for errors using IS functions (ISNA, ISERROR etc.). 

We don’t need to loop over the entire dataset to check for errors. Using SpecialCells, we can directly check whether there exists any errors across our dataset.

And we can go a step further, and identify what type of error exists in a cell using CVErr() function. 

Let’s check how to do all of this.

Excel Errors

The full list of possible Excel errors is as below.

In this video, we will focus on #N/A errors, which are very common in corporate reporting files. We will also, see examples of #VALUE! and #DIV/0. We won’t go through the others, as the treatment for them will be the same as that for #VALUE! and #DIV/0.

Dealing with Errors in Excel

Consider the below dataset, where we have #N/A errors in col C because Smooth Peanut Butter 500G x 10 can’t be found in the reference table.

If we try to reference Col C in another formula in Col D, we will get the same error. This is the same issue in VBA. While in Excel the error value will get displayed in the cell, in VBA the error will be break the macro! 

=IF(C3="Relish","Group A","Group B")

The way around this is to use an IS function. ISNA will allow us to check whether the error is #N/A or not. ISERROR will check for any error value. Unless you specifically want to check for a #N/A error, I will suggest using ISERROR at all times.

=IF(ISNA(C3),"Unknown",IF(C3="Relish","Group A","Group B"))
=IF(ISERROR(C3),"Unknown",IF(C3="Relish","Group A","Group B"))

Dealing with Errors in VBA

Our problem statement for this VBA section, is that we want to loop through our data columns and check whether there are any Excel errors.

If there is an error, we can choose to do any one of three things:

  1. Change Each Underlying Cell Value with Error
  2. Change the Background Colour of each cell with error
  3. Or, alert the user that there is an error in the worksheet, which they can manually fix and re-run the macro.

Let’s try to highlight errors in Col C.

For the first approach, we will use ISNA() which is a method of the WorksheetFunction object. This object allows us to use Excel functions within VBA. I have commented out the options to blank out the error value or alert the user.

Sub FindError()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Dataset01")

Dim i As Long
'Using ISNA
For i = 2 To 6
    If Application.WorksheetFunction.IsNA(ws.Range("C" & i).Value) = True Then
        'ws.Range("C" & i).Value = ""
        'ws.Range("C" & i).value = "Unknown"
        ws.Range("c" & i).Interior.Color = vbYellow
        'MsgBox "Error Value in Cell " & ws.Range("C" & i).Address
        'Exit Sub
    End If
Next i

End Sub

In the next approach, we can use ISERROR() which is actually a VBA function. So, we can access it directly.

Sub FindError()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Dataset01")

Dim i As Long
'Using ISERROR
For i = 2 To 6
    If IsError(ws.Range("C" & i).Value) = True Then
        'ws.Range("C" & i).Value = ""
        'ws.Range("C" & i).value = "Unknown"
        ws.Range("c" & i).Interior.Color = vbYellow
        'MsgBox "Error Value in Cell " & ws.Range("C" & i).Address
        'Exit Sub
    End If
Next i

End Sub

Highlight all errors using SpecialCells

If we are just interested in addressing all errors in one go, we can use the SpecialCells method of the Worksheet.Cells object. We can basically, identify each cell with an error in it and do something with the cell accordingly.

However, this is where we need to consider whether our dataset has formulas or just error values. Because our approach will be different for both.

Consider a dataset with formulas.

We can use the SpecialCells method of the Cells object. This method can take 2 parameters. Cell type and Value. You can refer to the Microsoft docs page for full list of parameters.

Since we are looking in formulas, we will choose xlCellTypeFormulas as Cell Type and xlErrors as Value.

This will return a range object which will contains the cells with error values. And we can change the background color of these cells to yellow.

However, if there are no error values, then VBA will give an error. To avoid this, we will put in some error handling. A Resume Next statement prior and Goto 0 statement after the SpecialCells method to skip that line if no results are found.

Sub CleanData()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Dataset01")

On Error Resume Next
With ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    .Interior.Color = vbYellow
End With
On Error GoTo 0

End Sub

If are dataset doesn’t have formulas, then we need to choose xlCellTypeConstants as Cell Type and xlErrors as Value. This will only return errors within cells with constants in them. Constants are values that don’t change. In our case, its everything except for formulas.

Sub CleanData()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Dataset01")

On Error Resume Next
With ws.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
    .Interior.Color = vbYellow
End With
On Error GoTo 0

End Sub

But, what if we have both values and formulas in our dataset. We can then, use both With statements together.

Sub CleanData()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Dataset01")

On Error Resume Next
With ws.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
    .Interior.Color = vbYellow
End With
With ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    .Interior.Color = vbYellow
End With
On Error GoTo 0

End Sub

Now, suppose we want to just check whether there exists atleast one error in the worksheet, and alert the user if there is.

We can extract the results of the SpecialCells method into Range variables and check whether the returned Range is empty or not. If it isn’t, we can alert the user.

Sub CleanData()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Good Data")

Dim rngConst As Range, rngForm As Range

On Error Resume Next
Set rngForm = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
Set rngConst = ws.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If Not rngForm Is Nothing Or Not rngConst Is Nothing Then
    MsgBox "Atleast one error value found. Pls check the data."
    Exit Sub
End If


End Sub

Identify Each Error: CVErr()

So, far we have seen how to loop through a range of data and identify if there is an error. And we have also, seen how to determine that errors exist within an entire dataset. Now, we will go through how to determine what type of error actually exists and deal with each error type individually.

We will do two things now. First, we will loop through each cell in our data range. And in the loop, we will use a Function to determine what type of error exists in each cell. The function we are going to use is CVErr. This function can take a parameter. The choices are below. One for each error type.

The result of the function with the specific parameter will be a real error value which will equate to the error value we see in our cell.

What we will do is use a Select Case statement to check whether the error value in our cell equals to the output of each parameter. If we find a match, we will know what error type exists in the cell and we can do something accordingly. For our example, we will color code each cell with a different color based on the error value.

Sub IndividualErrors()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Dataset03")
Dim rngcell As Range
For Each rngcell In ws.Range("A1").CurrentRegion
    If IsError(rngcell.Value) = True Then
        Select Case rngcell.Value
            Case CVErr(xlErrDiv0)
                rngcell.Value = 0
            Case CVErr(xlErrNA)
                rngcell.Value = ""
            Case CVErr(xlErrName)
                rngcell.Interior.ColorIndex = 5
            Case CVErr(xlErrNull)
                rngcell.Interior.ColorIndex = 6
            Case CVErr(xlErrNum)
                rngcell.Interior.ColorIndex = 7
            Case CVErr(xlErrRef)
                rngcell.Interior.ColorIndex = 8
            Case CVErr(xlErrValue)
                rngcell.Interior.ColorIndex = 9
            Case Else
                rngcell.Interior.ColorIndex = 10
        End Select
    End If
Next rngcell
End Sub

Related Posts