How to check that Excel cell contains REF! error in VBA

Question

I found many discussions on how to do it in Excel, but my goal is to capture REF! error in VBA, not in Excel itself. Is it possible ?

Answer 1

If IsError(cell.Value) Then
    If cell.Value = CVErr(xlErrName) Then
        ...
    End If
End If

---

The original code is wrong and will give a Type Mismatch error if the cell does not contain an error.

If cell.Value = CVErr(xlErrRef) Then
    ...
End If

Answer 2

Sub CheckRef()
Dim CheckRange As Range, CheckCell As Range

    Set CheckRange = [A1:D10]                ' as per app

    For Each CheckCell In CheckRange
    If IsError(CheckCell) And _
       CVErr(CheckCell) = CVErr(2023) Then ' 2023 --> xlErrRef
            MsgBox ("#REF! in  " & CheckCell.AddressLocal)
            Exit Sub                     ' exit after first #REF! found
        End If
    Next CheckCell
End Sub

example

• enter "=1/0" in B2 to create an error different to "#REF!"
• enter 1 in B4, B5
• enter "=B4+B5" in B7
• delete row 4
• run Sub CheckRef()