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

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

"Robert Flanagan" wrote in message
...
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

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" wrote in message
...
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

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

Bob

"JLGWhiz" wrote in message
...
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" wrote in message
...
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

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.


"Robert Flanagan" wrote in message
...
JLG, I haven't seen a posting by Dave. What was the better way?

Bob

"JLGWhiz" wrote in message
...
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" wrote in message
...
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