Object Is Object evaluation problem
 

Having a problem getting to "Range" objects to test as equivalent. In the
simplified code below after calling Sub One and then Sub Two with the same
"Range" argument the MsgBox displayed the addresses of X and C as the same,
but failed to evaluate the Object X and C as being equivalent.

What's wrong with the code?
(Of course this assumes my simplified code below accurately represents my
actual code :-)

Simplified code:

Sub One (ByVal A As Range)
Call Test(A, True)
End Sub

Sub Two(ByVal B As Range)
Call Test(B, False)
End Sub

Sub Test(ByVal X as Range, Flag As Boolean)
Static C As Range

MsgBox X.Address & " " & C.Address
' After Second Call to Test both addresses show the same value.

Select Case Flag
Case Is = True
Set C = X
Case Is = False
If X Is C Then
MsgBox "X is C"
Else
MsgBox "X is not C"
End If
End Select
End Sub

Call One(R) ' Where R is a cell reference (Range).
Call Two(R) ' Where R is same cell reference.

Object Is Object evaluation problem
 

Test whether R.Address = C.Address.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
Having a problem getting to "Range" objects to test as equivalent. In the
simplified code below after calling Sub One and then Sub Two with the same
"Range" argument the MsgBox displayed the addresses of X and C as the
same,
but failed to evaluate the Object X and C as being equivalent.

What's wrong with the code?
(Of course this assumes my simplified code below accurately represents my
actual code :-)

Simplified code:

Sub One (ByVal A As Range)
Call Test(A, True)
End Sub

Sub Two(ByVal B As Range)
Call Test(B, False)
End Sub

Sub Test(ByVal X as Range, Flag As Boolean)
Static C As Range

MsgBox X.Address & " " & C.Address
' After Second Call to Test both addresses show the same value.

Select Case Flag
Case Is = True
Set C = X
Case Is = False
If X Is C Then
MsgBox "X is C"
Else
MsgBox "X is not C"
End If
End Select
End Sub

Call One(R) ' Where R is a cell reference (Range).
Call Two(R) ' Where R is same cell reference.

Object Is Object evaluation problem
 

Actually, that's what I ended up doing, but I was just wondering if anyone
knew why the Object comparison apparently failed.

Thanks for your response.


"Jon Peltier" wrote:

Test whether R.Address = C.Address.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
Having a problem getting to "Range" objects to test as equivalent. In the
simplified code below after calling Sub One and then Sub Two with the same
"Range" argument the MsgBox displayed the addresses of X and C as the
same,
but failed to evaluate the Object X and C as being equivalent.

What's wrong with the code?
(Of course this assumes my simplified code below accurately represents my
actual code :-)

Simplified code:

Sub One (ByVal A As Range)
Call Test(A, True)
End Sub

Sub Two(ByVal B As Range)
Call Test(B, False)
End Sub

Sub Test(ByVal X as Range, Flag As Boolean)
Static C As Range

MsgBox X.Address & " " & C.Address
' After Second Call to Test both addresses show the same value.

Select Case Flag
Case Is = True
Set C = X
Case Is = False
If X Is C Then
MsgBox "X is C"
Else
MsgBox "X is not C"
End If
End Select
End Sub

Call One(R) ' Where R is a cell reference (Range).
Call Two(R) ' Where R is same cell reference.

Object Is Object evaluation problem
 

They've never worked this way as far as I can remember, so without bothering
to wonder why (it's MS & XL after all), I just learned what worked.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
Actually, that's what I ended up doing, but I was just wondering if anyone
knew why the Object comparison apparently failed.

Thanks for your response.


"Jon Peltier" wrote:

Test whether R.Address = C.Address.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
Having a problem getting to "Range" objects to test as equivalent. In
the
simplified code below after calling Sub One and then Sub Two with the
same
"Range" argument the MsgBox displayed the addresses of X and C as the
same,
but failed to evaluate the Object X and C as being equivalent.

What's wrong with the code?
(Of course this assumes my simplified code below accurately represents
my
actual code :-)

Simplified code:

Sub One (ByVal A As Range)
Call Test(A, True)
End Sub

Sub Two(ByVal B As Range)
Call Test(B, False)
End Sub

Sub Test(ByVal X as Range, Flag As Boolean)
Static C As Range

MsgBox X.Address & " " & C.Address
' After Second Call to Test both addresses show the same value.

Select Case Flag
Case Is = True
Set C = X
Case Is = False
If X Is C Then
MsgBox "X is C"
Else
MsgBox "X is not C"
End If
End Select
End Sub

Call One(R) ' Where R is a cell reference (Range).
Call Two(R) ' Where R is same cell reference.

Object Is Object evaluation problem
 

In fact, a lot of these object comparisons don't work. You have to resort to
approaches like this:

Worksheets:
If wksA.Name = wksB.Name Then

Workbooks
If WbkA.Name = wbkB.Name Then

etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
Actually, that's what I ended up doing, but I was just wondering if anyone
knew why the Object comparison apparently failed.

Thanks for your response.


"Jon Peltier" wrote:

Test whether R.Address = C.Address.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
Having a problem getting to "Range" objects to test as equivalent. In
the
simplified code below after calling Sub One and then Sub Two with the
same
"Range" argument the MsgBox displayed the addresses of X and C as the
same,
but failed to evaluate the Object X and C as being equivalent.

What's wrong with the code?
(Of course this assumes my simplified code below accurately represents
my
actual code :-)

Simplified code:

Sub One (ByVal A As Range)
Call Test(A, True)
End Sub

Sub Two(ByVal B As Range)
Call Test(B, False)
End Sub

Sub Test(ByVal X as Range, Flag As Boolean)
Static C As Range

MsgBox X.Address & " " & C.Address
' After Second Call to Test both addresses show the same value.

Select Case Flag
Case Is = True
Set C = X
Case Is = False
If X Is C Then
MsgBox "X is C"
Else
MsgBox "X is not C"
End If
End Select
End Sub

Call One(R) ' Where R is a cell reference (Range).
Call Two(R) ' Where R is same cell reference.