3

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?

mits
  • 876
  • 3
  • 11
  • 20
  • I am assuming `if day.date_cell = LastMetrisi` also doesn't do what you desire? – chancea Feb 12 '15 at 16:34
  • 1
    The `Is` operator will only return true when comparing the same _instance_ of an object (https://msdn.microsoft.com/en-us/library/kb136x1y.aspx). You could compare `day.date_cell.address` instead to check for the same range. – SierraOscar Feb 12 '15 at 16:35
  • 2
    The `Range` property always returns a *new* object reference even if you are referring to the same cell. (well, it will re-use previous references if they are not currently pointed to by a variable) – Rory Feb 12 '15 at 17:01
  • @Rory - have a reference for that? In relation to [this question](https://stackoverflow.com/questions/76877807/is-operator-gives-me-false-can-i-know-the-reason-why). – BigBen Aug 10 '23 at 17:09
  • 1
    @BigBen No, I've never seen one. That's just based on experience (and also makes sense logically), but is easily demonstrated, as you showed. :) – Rory Aug 11 '23 at 07:10

1 Answers1

4

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...
BigBen
  • 46,229
  • 7
  • 24
  • 40
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • 1
    The link to MSDN article is about totally different technology (VB.NET is not VBA). Here is [link](https://msdn.microsoft.com/en-us/library/office/gg264676.aspx) to the ```Is``` operator in VBA. – Daniel Dušek Feb 12 '15 at 19:45