Why does Target Is Range("A1") fail?

[Greg Truby]

Posting here 'cause more of a "I'm curious" question than an "I need help" question (though I'm sure Von Pookie will move me if I chose the wrong board)

My question: Why doesn't the IS operator return True when comparing Target to a range in VB when they are indeed the same? Why do we have to keep backing in via rng.Address = Target.Address or Not Intersect() Is Nothing?

Example:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    
    <SPAN style="color:#007F00">'_____Works_____</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$B$2" <SPAN style="color:#00007F">Then</SPAN>
        Application.StatusBar = "To Be"
    <SPAN style="color:#00007F">Else</SPAN>
        Application.StatusBar = "Not to be"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#007F00">'_____This fails_____</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target <SPAN style="color:#00007F">Is</SPAN> Range("A1") <SPAN style="color:#00007F">Then</SPAN>
        Application.StatusBar = "A1 sauce anyone?"
        
    <SPAN style="color:#007F00">'____But this works_____</SPAN>
    <SPAN style="color:#00007F">ElseIf</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        Application.StatusBar = "¿Alguien quiere salsa A1?"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Again - this is just me wondering why... Am I missing something terribly obvious here?

(Edit) I am guessing it has to do with the Target argument for SelectionChange() coming in ByVal instead of ByRef, but not sure... (End Edit)

Thanks for any insights.

Regards,

 

[Greg Truby]

Wow - hard to believe this was six years ago already!

I just thought that I'd update this to confirm that none of this was fixed under Excel 2007. The code examples all fail in the exact same manner as before.

Also, in case anyone should want to copy and paste them to try out, I'm pasting in new copies of the code because when we upgraded the board the transition software stuck a bunch of asterisks in the old code blocks.


For the Worksheet Module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '_____Works_____
    If Target.Address = "$B$2" Then
        Application.StatusBar = "To Be"
    Else
        Application.StatusBar = "Not to be"
    End If
    '_____This fails_____
    If Target Is Range("A1") Then
        Application.StatusBar = "A1 sauce anyone?"
    '____But this works_____
    ElseIf Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.StatusBar = "¿Alguien quiere salsa A1?"
    End If
End Sub

And for the Standard Module:

Sub TestAsObject()
    Dim y As Object, z As Object
 
    Set y = Range("a1:b2")
    Set z = Range("a1:b2")
    MsgBox y Is z, , "Ranges"  '____False
 
    Set z = y
    MsgBox y Is z, , "Ranges 2" '____True
 
    Set y = ActiveSheet.Columns(1)
    Set z = ActiveSheet.Columns(1)
    MsgBox y Is z, , "Columns"  '____False
 
    Set y = Sheets(2)
    Set z = Sheets(2)
    MsgBox y Is z, , "Sheets"   '____True
 
    Set y = Workbooks(2)
    Set z = Workbooks(2)
    MsgBox y Is z, , "Workbooks" '____True
End Sub
 
Sub TestPassing()
    Dim rng1 As Range, ws1 As Worksheet, wb1 As Workbook
    Set rng1 = [A1]
    Set ws1 = Sheets(1)
    Set wb1 = Workbooks(1)
    TestCatching rng1, ws1, wb1
End Sub
Sub TestCatching(ByVal rngA As Range, _
    ByVal wsA As Worksheet, _
    ByVal wbA As Workbook)
 
    Dim rng2 As Range, ws2 As Worksheet, wb2 As Workbook
    Set rng2 = [A1]
    Set ws2 = Sheets(1)
    Set wb2 = Workbooks(1)
    MsgBox rngA Is rng2, , "Ranges byVal"   '____Returns FALSE
    MsgBox wsA Is ws2, , "Worksheets byVal" '____Returns TRUE
    MsgBox wbA Is wb2, , "Workbooks byVal"  '____Returns TRUE
End Sub

 

[Jaafar Tribak]

Hi Greg how are you doing .

Intersting topic.

Although the Range Property and the Target Parameter are both Ranges and both share the same cell Address they do not point to the same range Object. In fact each points to a different object !

consider this code :

Private Sub Worksheet_Change(ByVal Target As Range)

    Debug.Print "Range(""A1"")Pointer"; vbTab & "Target Pointer"
    Debug.Print ObjPtr(Range("A1")) & vbTab & vbTab & vbTab & ObjPtr(Target)
    Debug.Print
 
End Sub

From the above code you can notice two really weird things :

1- The Target and Range("A1") point to different objects !

2- The objects pointed to by the Range Property and Target argument change every time you run the change event !

I guess that's why it's so difficult to use the Is Operator. This is not the case with other excel objects like the application, workbook ... objects which remain static during the whole current session.


The closest i came to compare the Target parameter and the target Range via the Is operator is this :

Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" _
(ByRef Destination As Any, ByRef Source As Any, ByVal Length As Long)

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address = Range("a1").Address Then
        CopyMemory Target, Range("a1"), 4
        If Target [COLOR=red][B]Is[/B][/COLOR] Range("a1") Then
            MsgBox "hello!"
        End If
    End If

End Sub

The reason i still needed to use the Address Property is because once you copy the Range A1 Object Pointer into the Target argument via the CopyMemory API, every worksheet cell passed to the Is operator returns the same object pointer ! In other words, if you edit the above code and remove the line If Target.Address = Range("a1").Address... then the line : If Target Is Range("a1") will always evaluate to TRUE no matter which range you have ie Range("a2"); "a3",...

the Range object memory Pointer seems to be constantly moving around and changing its current memory address. - Weird stuff.

Regards.