Why isn't range variable nothing in this case

[Robert Flanagan]

Put the following code in a workbook:

Sub VariableTest()
Dim anyR As Range
Set anyR = Selection
anyR.Parent.Parent.Close False
If anyR Is Nothing Then
MsgBox "is nothing"
Else
MsgBox "is Not nothing"
End If
End Sub

Now go to another workbook and run it. Since the workbook which anyR
pointed to is now closed, I expected anyR to be nothing. But it is not.

What I want to do is to test if the workbook has been closed by seeing if
anyR is nothing. The only way I see is something like this:

dim wS as worksheet
On Error Resume next
set wS = nothing 'in case it has previously been set
set wS = anyR.Parent
On Error goto 0

I can then test if wS is nothing.

I would much prefer a test directly on the range variable without using an
error trap, as it is easy to forget to remove an error trap. And the above
is several lines of code (I can always put in a function) Is there a way to
do a direct test?

Bob

 

[Bob Phillips]

Why test the range, why not test if the workbook is in the workbooks
collection?

 

[JLGWhiz]

To answer the original question, the object variable was not destroyed by
closing the workbook. It continues to hold the value it was set to until
the procedure ends. If you then run the procedure without reopening the
workbook, you will get a new value for the object variable, depending on
what is selected when the procedure runs. Dave has suggested a better way
to do what you want.

 

[Robert Flanagan]

JLG, I haven't seen a posting by Dave. What was the better way?

Bob

 

[JLGWhiz]

Sorry, I meant Bob Phillips. I was trying to do two things at once and got
screwed around on names. Bob suggested that you test for the closed parent
workbook and if it is nothing, which it should be if closed, then your code
would work. Or you could test for anyR.Value, which would probably give you
a subscript out of bounds error, but you could use that as a means of
texting.

The point is, that the way you are doing it, will not yield the results you
want.

 

[Peter T]

Got to agree with Bob here, or maybe test for r.parent.name under an error
handler.
"myVar Is Nothing" does not test if an "actual" object exists, merely that
the variable has at some stage been assigned the pointer to an object that
once existed.

If it's intended to delete the object, maintaining a reference to it that is
neither explicitly destroyed nor allowed to go out of scope can lead to
other problems, eg circular references, can't close the app that hosts the
object, etc

Try this

Sub abc()
Dim r As Range, ws As Worksheet, wb As Workbook, r2 As Range
Set wb = Workbooks.Add
Set ws = Worksheets(1)
Set r = ws.Range("A1")
wb.Close False

Debug.Print Not r Is Nothing, Not ws Is Nothing, Not wb Is Nothing
Set r2 = r ' hmm !

Debug.Print ObjPtr(r), ObjPtr(r2), ObjPtr(ws), ObjPtr(wb)

Set r = Nothing: Set r2 = Nothing: Set ws = Nothing: Set wb = Nothing

Debug.Print ObjPtr(r), ObjPtr(r2), ObjPtr(ws), ObjPtr(wb)
End Sub


Regards,
Peter T