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

Greg Truby · Nov 12, 2003

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:

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


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,

Mark O'Brien · Nov 12, 2003

Every object has a default property. The default property of the Range object is "Value". This is an inherent thingamabob and I can't remember if or how you can change this.

So the line

Code:

  If Target Is Range("A1")

is actually the equivalent to saying:

Code:

  If Target Is Range("A1").Value

...which is not comparing the objects, but rather comparing the Target object to the Value of Range A1.

I would like to illustrate this with an example, but I can't right now, so this is just my theory until I can disprove it. My main stumbling block is that the Target object is also a range.

NateO · Nov 12, 2003

Hello,

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

No, as far as I can discern, the is operator should be functional and accurate here, but for some opaque reason, it is not with range objects, I do not know why.

See the following:

Sub test1()
Dim y As Range, z As Range
Set y = Range("a1")
Set z = Range("a1")
MsgBox y Is z
End Sub

Sub test2()
Dim y As Worksheet, z As Worksheet
Set y = Sheets(2)
Set z = Sheets(2)
MsgBox y Is z
End Sub

Fine with worksheets, not so good with ranges.

Incidentally, while I don't have a great answer, I do believe this is a legitimate Excel VBA question, I shall move it to the Q&A forum.

Edit: Looks like it's a bug:
http://support.microsoft.com/default.aspx?scid=kb;en-us;114345&Product=xlw

Greg Truby · Nov 12, 2003

Mark,

If the default property was getting in the way, then wouldn't the following also fail?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim rng1 As Range
 Set rng1 = Target
 '_____Returns TRUE_____
 If rng1 Is Target Then MsgBox "True" Else MsgBox "False"
End Sub

Nate,

Thanks for the reference. Interestly, the code that they provide as a test to "replicate" the problem does produce a true (it beeps). I tried using the generic Object type, didn't work:

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


And my original theory of because it's passed byVal doesn't appear to hold water either...

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


So, mark me as "plumb stumped". But glad to know that it's a stumper of a question!

parry · Nov 12, 2003

Greg Truby said:
Interestly, the code that they provide as a test to "replicate" the problem does produce a true (it beeps). I tried using the generic Object type, didn't work:

This is an interesting bug. I see the article is quite old - refers to VBA ver 1 and Excel 5 so MS has obviously fixed something since then, but what exactly?

dk · Nov 12, 2003

I just spent half an hour searching Google and MSDN and couldn't find anything useful, just this acknowledgement of the problem.

Anyone else?

NateO · Nov 12, 2003

There's more elaboration on the cause here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;111493&Product=xlw

This may be older than the first one I cited... I see nothing newer.

It says it was reviewed 10/22/2003, what this or their cause means, I don't know....

Greg Truby · Nov 12, 2003

Thanks for the "heads up" with that link, Nate. Through experience I'd learned that the IS operator wouldn't work well on range objects (but thought it was just me - hence my post on this). But I had no idea it could fail on other objects too. That's a nice pitfall to know about. It hasn't happened, but I could easily see myself spenting a good long time doin' this number

in a debugging quagmire at some future date if not for your input!

Thanks again!

Greg

NateO · Nov 12, 2003

You're welcome Greg.

The stated cause in the second reference may indeed be true, but it doesn't go far enough. In my first example, I wasn't calling properties separately, I had two fixed variables which should be static at that point, I haven't tested it, but I expect no better results with Constants... It simply leads me to believe:

1) The underlying issue with Excel is deeper than the stated cause with respect to ranges.
2) You're rolling the dice when you use the Is Operator{/i] on Excel objects. Although, I've never had a problem with:

test = object is nothing

Interesting question. Bon chance.

Mark O'Brien · Nov 13, 2003

Greg Truby said:
Mark,

If the default property was getting in the way, then wouldn't the following also fail?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim rng1 As Range
 Set rng1 = Target
 '_____Returns TRUE_____
 If rng1 Is Target Then MsgBox "True" Else MsgBox "False"
End Sub

Yes, hence the part of my original post:

Mark O'Brien said:
I would like to illustrate this with an example, but I can't right now, so this is just my theory until I can disprove it. My main stumbling block is that the Target object is also a range.

Thanks for the links Nate.

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

Greg Truby

MrExcel MVP

Mark O'Brien

MrExcel MVP

NateO

Legend

Greg Truby

MrExcel MVP

parry

MrExcel MVP

dk

MrExcel MVP

NateO

Legend

Greg Truby

MrExcel MVP

NateO

Legend

Mark O'Brien

MrExcel MVP

Similar threads

Share this page

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

MrExcel MVP

MrExcel MVP

Legend

MrExcel MVP

MrExcel MVP

MrExcel MVP

Legend

MrExcel MVP

Legend

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock