Strange range behavior when used as a key in a dictionary

Question

I have the following code:

Dim dicMyHash As Dictionary
Dim rngMyRange As Range

' A1 is empty - although the outcome is the same in any case
Set rngMyRange = Range("A1")
Set dicMyHash = New Dictionary

dicMyHash.Add Key:=rngMyRange(1), Item:=0

Debug.Print dicMyHash.Exists(rngMyRange(1).Value)   ' returns False
Debug.Print rngMyRange(1) = rngMyRange(1).Value     ' returns True

This behavior is somewhat unexpected. Is there some kind of casting in the background? The property rngMyRange(1).Valuereturns a variant, whereas rngMyRange(1)- rngMyRange.item(1)what is range. However, casting rngMyRange(1)to variantgives the same results.

In addition, adding keys by value (therefore, a copy is passed as a key rngMyRange(1)). But still I can’t understand why I ca .Existsn’t find the key.

Thank you in advance!

Answer 1

So here we pass three different values:

• Original range.
• Range.Valuewhich is an option.
• (1), .

, . Dictionary.Exists, .

? , . Range Range.Value, r = r.Value, r = r.Offset(0, 0).

. : Dictionary.Exists , . . , Dictionary.Exists :

• -? .
• -? a = b.
• ? a Is b.

, r r.Value, , - . r, , r.Offset(0, 0), , , .

, , , r , d.Keys(0):

Dim d As Scripting.Dictionary
Dim r As Range
Set r = [a1]
Set d = New Dictionary
d.Add r, 0
Set r = d.Keys(0)
Debug.Print d.Exists(r)

Answer 2

, , rngMyRange , .

, :

dicMyHash.Add Key:=rngMyRange(1).value, Item:=0

, - true.

Locals Window .

Answer 3

I'm not sure how you use it, but this will return True:

Sub test()
Dim dicMyHash As Dictionary
Dim rngMyRange As Range

Set rngMyRange = Range("A1")
Set dicMyHash = New Dictionary

dicMyHash.Add Key:=rngMyRange(1).Value, Item:=0 ' assign it with Value
Debug.Print dicMyHash.Exists(rngMyRange(1).Value)
End Sub

So, you will have an element with a key in any of A1.

I believe the reason that it does not work without Valueis because you are assigning Range Key. It would be more appropriate for me if you assigned a range to dictionaries.