21

What is the operator or function to test whether two variables of the same custom object type refer to the same object? I've tried

If myObject = yourObject Then

But get a runtime error 438 object doesn't support this property or method. I'm guessing that's telling me to override the '=' operator to test if all the fields of the two objects have the same value. But what I want is to test whether they are the same object.

Swiftslide
  • 1,307
  • 7
  • 23
  • 34

2 Answers2

42

I'm guessing that's telling me to override the '=' operator to test if all the fields of the two objects have the same value.

No, it tells you the objects don't have a default property which would have been called otherwise, and the returned results compared.

You test reference equality with Is

If myObject Is yourObject Then
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • 1
    @GSerg: That doesn't seem to work for two range objects. For example, Set r1 = Range("A1") and Set r2 = Range("A1"), then Debug.Print r1 Is r2 returns False. – Excel Developers Jan 09 '15 at 14:55
  • 16
    @ExcelDevelopers `Is` tests referential equality, not equality according to a higher-level logic. The two `Range` objects are different instances, thus `Is` correctly returns false. The fact they refer to the same range is not relevant - `Is` cannot and does not know about this additional logical connection, only specific to `Range`s. If you want to check if two `Range`s refer to the same sheet range, you need to either compare their `Address`es or see if `Application.Intersect(r1, r2)` gives the range of the same size as both `r1` and `r2`. – GSerg Jan 09 '15 at 15:07
-1

You need to serialize the objects somehow and then compare attribute by attribute values. The "is" operator is as dumb as it gets, it only matches if another object is the same instance assigned to the compared variable. I suggest using a jsonStringify library. I adapted one for my DexTools.xlam open source project https://github.com/dexterial/Dextools/tree/master/Main starting from the Parsing JSON in Excel VBA post. It has much more added features since I added quite a few other excel objects serialization/hashing options and it is made using the vba test driven development that DexTools incorporates. It is still work in progress so dont expect miracles

Dexterial
  • 19
  • 3