VBA: What happens to Range objects if user deletes cells?

30
votes

Suppose I have some module in with some variable r of type Range. Suppose that, at some point, I store a Range object there (e.g. the active cell). Now my question: What happens to the value of r if the user deletes the cell (the cell, not only its value)?

I tried to figure this out in VBA, but without success. The result is strange. r is not Nothing, the value of r is reported to be of type Range, but if I try to look at its properties in the debugger window, each property's value is reported as "object required".

How can I, programmatically, determine whether variable r is in this state or not?

Can I do this without generating an error and catching it?

4
vbaexcel
I extended the question by the very last sentence. – JohnB
I don't think you can check the state without generating an error, unfortunately. I haven't seen a way where you didn't have to re-"Set" a Range object again after deleting it. You can always use the Cells(,) collection instead where you won't run into this issue, even if you delete a Cell on the worksheet. – Joseph
Why do you not want to use the error-catching approach? There are quite a few things in VBA for which error catching is the only way to proceed. This seems to be one of them. Even determining the number of dimensions of an array requires error handling. – Jean-François Corbett

4 Answers

22
votes

Nice question! I've never thought about this before, but this function will, I think, identify a range that was initialzed - is not Nothing - but is now in the "Object Required" state because its cells were deleted:

Function RangeWasDeclaredAndEntirelyDeleted(r As Range) As Boolean
Dim TestAddress As String

If r Is Nothing Then
    Exit Function
End If
On Error Resume Next
TestAddress = r.Address
If Err.Number = 424 Then    'object required
    RangeWasDeclaredAndEntirelyDeleted = True
End If
End Function

You can test is like this:

Sub test()
Dim r As Range

Debug.Print RangeWasDeclaredAndEntirelyDeleted(r)
Set r = ActiveSheet.Range("A1")
Debug.Print RangeWasDeclaredAndEntirelyDeleted(r)
r.EntireRow.Delete
Debug.Print RangeWasDeclaredAndEntirelyDeleted(r)
End Sub
6
votes

I believe that when you use the Set keyword in VBA, it creates a pointer in the background to the worksheet's Range object in the worksheet you specified (each cell being an object in the collection of Cells of the Worksheet for a given Range). When the range is deleted while you are still referencing it in memory, the memory for the object that the Range variable was pointing to has been deallocated.

However, your Range variable most-likely still contains the pointer to the recently removed Range object, which is why it isn't nothing, but whatever it's pointing to doesn't exist anymore, which causes problems when you try to use the variable again.

Check out this code to see what I mean:

Public Sub test2()
    Dim r As Excel.Range
    Debug.Print ObjPtr(r)           ' 0

    Set r = ActiveSheet.Range("A1")
    Debug.Print ObjPtr(r)           ' some address

    r.Value = "Hello"

    r.Delete
    Debug.Print ObjPtr(r)           ' same address as before
End Sub

Check out this article for more info about ObjPtr(): http://support.microsoft.com/kb/199824

So while you have a valid address to an object, unfortunately the object doesn't exist anymore since it has been deleted. And it appears that "Is Nothing" just checks for an address in the pointer (which I think VBA believes that the variable is "Set").

As to how to get around this problem, unfortunately I don't see a clean way of doing it at the moment (if anyone does find an elegant way to handle this, please post it!). You can use On Error Resume Next like so:

Public Sub test3()
    Dim r As Excel.Range
    Debug.Print ObjPtr(r)           ' 0

    Set r = ActiveSheet.Range("A1")
    Debug.Print ObjPtr(r)           ' some address

    r.Value = "Hello"

    r.Delete
    Debug.Print ObjPtr(r)           ' same address as before

    On Error Resume Next
    Debug.Print r.Value
    If (Err.Number <> 0) Then
        Debug.Print "We have a problem here..."; Err.Number; Err.Description
    End If
    On Error GoTo 0
End Sub
4
votes

How can I, programmatically, determine whether variable r is in this state or not?

Can I do this without generating an error and catching it?

No.

To the best of my knowledge, you can't test for this condition reliably: not without raising and catching an error.

Your question has been noticed and discussed elsewhere: Two of the big names in Excel/VBA blogging (Dick Kusleika and Rob Bovey) have looked into it, and you may find something informative in there. But the answer's No.

All in all, a good question with rather worrying answer.

2
votes

To test if a range object is currently invalid, I use this function:

Public Function InvalidRangeReference(r As Range) As Boolean    
    On Error Resume Next
    If r.Count = 0 Then
        InvalidRangeReference = Err
    End If    
End Function