CODEWITHSUNDEEP
vbaexcelreferenceequality
mits
mits

Reputation: 926

`Is` operator does not return true for two range variables that point to the same cell

I have a custom class named imera in which I include a range property named date_cell.

When creating a collection of imera's, every imera's date_cell is set to reference to a specific cell in excel.

While attempting to search within the collection by date_cell:

Option Explicit
Public imeraCol as Collection
Sub searchByDateCell()
    Dim day As imera
    Dim LastMetrisi As Range
    Set LastMetrisi = Range("C27")
    For Each day In imeraCol
        If day.date_cell Is LastMetrisi Then
            'Do something
        End If
    Next day
    Set day = Nothing
End Sub

the "Is" operator doesn't seem to work as expected and return true, although I have tested by debug.print that in my colection exists an imera with date_cell set to range("C27").

And as a result the 'Do something section above, never executes.

Is there any explanation why might this happening?

Upvotes: 3

Views: 172

Answers (2)

mikebinz
mikebinz

Reputation: 406

I use this;

        Function _
SameRa(iRa1 As Range, iRa2 As Range, Optional iSucBothNothing As Boolean) As Boolean

    'Succeeds if iRa1 and iRa2 refer to the same Range, ie have the Same External Address

    'By Default, Fails if iRa1 and iRa2 are Both Nothing, but if
    '   iSucBothNothing is set True, it also Suceeds in this case

    On Error Resume Next
    SameRa = iRa1.Address(, , , True) = iRa2.Address(, , , True)
    On Error GoTo 0
    If iSucBothNothing Then _
        If Not SameRa Then _
            SameRa = iRa1 Is Nothing And iRa2 Is Nothing
    End Function

BTW, I agree with gembird; because the question refers to Excel (VBA rather than .Net) it's a bit misleading to be quoting .Net definitions

Upvotes: 0

SierraOscar
SierraOscar

Reputation: 17637

The Is operator will only return true when comparing the same instance of an object. From this MDSN article:

The Is operator determines if two object references refer to the same object. However, it does not perform value comparisons. If object1 and object2 both refer to the exact same object instance, result is True; if they do not, result is False.

You could compare day.date_cell.address instead to check for the same range.

If day.date_cell.Address = LastMetrisi.Address Then
   'Do Something...

Upvotes: 4

Related Questions