In Excel VBA, how can I test if an Excel.Range object variable loses its reference without raising runtime error 424..?

Question

In Excel VBA, if a variable is Excel.Range, and the range it refers to is deleted, it loses its reference. Any attempt to access the variable results in Runtime Error 424: object required.

Dim rng As Range
Set rng = Sheet1Range("A1")
Sheet1.Rows(1).Delete       'Range has been deleted.
Debug.Print rng.Address()   'Any access attempt now raises runtime error 424.

Is there a way to test for this state of "lost reference" without an error handler..?

Testing Nothing, Vartype(), and Typename() were all not useful because the variable is still a Range. I literally read through all of Excel.Application in the Object browser, but found nothing. Perhaps there's something I'm overlooking..? Such as one of those strange vestigial functions from prehistoric versions of Excel, like ExecuteExcel4Macro()..?

I've searched Google for the answer to this question, but didn't find anything helpful.

EDIT:

Some have asked why I'm trying to avoid an error handler. This is my normal programming philosophy for a couple reasons:

• I do recognize that sometimes an error handler is the quickest way, or the only way. But it's not the most elegant way. It just seems, well...crude to me. It's like the difference between white-washing a picket fence and painting a portrait of my cat. =-)
• The other reason I avoid error handlers is education. Many times when searching for an alternative, I'll discover properties, procedures, objects, or even entire libraries that I never knew before. And in doing so I find more armor with which to bulletproof my code.

Answer 1

Here's an approach that should be able to workaround the issue, although it isn't a great solution for checking if it was removed by itself. I think error handling is probably your best approach.

Sub Example()
    Dim foo1 As Range
    Dim foo2 As Range
    Dim foo3 As Range
    Dim numberOfCells As Long

    Set foo1 = Sheet1.Range("A1")
    Set foo2 = foo1.Offset(1, 0) 'Get the next row, ensure this cell exists after row deletion!
    Set foo3 = Union(foo1, foo2)
    numberOfCells = foo3.Cells.Count

    Debug.Print "There are " & numberOfCells & " cells before deletion"
    Sheet1.Rows(1).Delete

    Debug.Print "There are now " & foo3.Cells.Count & " cells"

    If foo3.Cells.Count <> numberOfCells Then
        Debug.Print "One of the cells was deleted!"
    Else
        Debug.Print "All cells still exist"
    End If
End Sub

Also, here is a more function oriented approach which may be a slightly better approach to add to your codebase. Again, not ideal, but it should not require an error handler.

Private getRange As Range

Sub Example()
    Dim foo         As Range
    Dim cellCount   As Long

    Set foo = Sheet1.Range("A1")
    cellCount = GetCellCountInUnion(foo)
    Sheet1.Rows(1).Delete

    If Not cellCount = getRange.Cells.Count Then
        Debug.Print "The cell was removed!"
    Else
        Debug.Print "The cell still exists!"
    End If

End Sub

Private Function GetCellCountInUnion(MyRange As Range) As Long
    Set getRange = Union(MyRange, MyRange.Parent.Range("A50000")) ‘second cell in union is just a cell that should exist
    GetCellCountInUnion = getRange.Cells.Count
End Function