Dictionary: Item not being returned

[pvr928]

Hi

I've created a dictionary, and then converted that dictionary into an array.

I am trying to retrieve the Item for the dictionary using a known Key.

I have used the code:

avKeys = dict.Keys

where avKeys is a Variant and dict is a Dictionary.

I am attempting to use a string (sSearchTerm) to return the Item, and:

avKeys(1) = aSearchTerm (say "1456.txt" (avKeys(1)) = "1456.txt" (aSearchTerm))

However,

Debug.Print dict(avKeys(1)) = 2

but

Debug.Print dict(aSearchTerm) = [Empty]

What am I missing if avKeys(1) = aSearchTerm (I've even tested it via the following:

   If avKeys(1) = aSearchTerm Then
    
        MsgBox "True"
        
   Else
    
        MsgBox "False"
        
    End If

Any help greatly appreciated.

Cheers

pvr928

 

[MickG]

Try Stepping through the code below, based on data shown, to see results:-

	A
1	Data1
2	Data2
3	Data3
4	Data4
5	Data5

<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

[COLOR="Navy"]Sub[/COLOR] MG28Jan52
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Dict [COLOR="Navy"]As[/COLOR] Object, Avkeys [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dict = CreateObject("scripting.dictionary")
Dict.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    '[COLOR="Green"][B]Dn.address added as dict.item[/B][/COLOR]
    Dict(Dn.Value) = Dn.Address
[COLOR="Navy"]Next[/COLOR]
'[COLOR="Green"][B]One dimension Array[/B][/COLOR]
Avkeys = Dict.Keys
MsgBox Avkeys(3)
MsgBox Dict(Avkeys(3))
MsgBox Dict("Data4")
'[COLOR="Green"][B]Two dimension array[/B][/COLOR]
Ray = Application.Transpose(Array(Dict.Keys, Dict.items))
MsgBox Ray(2, 1)
MsgBox Ray(2, 2)
MsgBox Dict(Ray(2, 1))
MsgBox Dict("Data2")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[pvr928]

Hi MickG

Thank you so much for the succinct code. It does help a lot.

I'm intrigued by the line:

Dict.CompareMode = vbTextCompare

Does this work in a similar manner to, perhaps, the Worksheet Function =ABS() in that it 'strips' out any extraneous information to get just the 'value' of the underlying item?

Cheers

pvr928

 

[MickG]

This is basics if it!!!!
vbTextCompare doesn't take Case into account. IE: "HELLO WORLD!" is the same as "hello world!", 1 uppercase, 1 lowercase. vbBinaryCompare is case sensitive, meaning "HELLO WORLD!" and "hello world!" are different.

 

[pvr928]

Thanks very much for explaining this to me MickG. Each time I use a Dictionary I think I've got it sorted and then when next I use one it seems to take a long time to get it right.

Thanks to your example above, I've got it working now.

Cheers

pvr928

 

[MickG]

You're welcome