There must be smart way to do Dictionary search

[arishy]

A1/B1

John 74

Jane 68

..

..

Sub Sample()

Dim Dict As Dictionary

Set Dict = New Dictionary


With Dict

.CompareMode = vbBinaryCompare

For i = 1 To 4

.Add Cells(0 + i, 1), Cells(0 + i, 2)

Next i

End With


Debug.Print GetKey(Dict, "68")

Debug.Print GetValue(Dict, "Jane")

End Sub


Function GetKey(Dic As Dictionary, strItem As String) As String

Dim key As Variant

For Each key In Dic.Keys

If Dic.Item(key) = strItem Then

GetKey = CStr(key)

Exit Function

End If

Next

End Function

Function GetValue(Dic As Dictionary, strkey As String) As String

Dim key As Variant

For Each key In Dic.Keys

If key = strkey Then

GetValue = CStr(Dic.Item(key))

Exit Function

End If

Next

End Function

 

[arishy]

By smart I mean if I do this:

Debug.print dict.item("Jane") I get nothing

And if I do:

Debug.print dict.Exists("Jane") I get True


And of course when I do:

Debug.Print GetValue("Jane") I get 68 using the function!!!!!

 

[shrivallabha]

The problem as I see is not with your dictionary interpretation but it is rather with range interpretation.


Dictionary object can handle keys as any kind of object( which you know as you have declared key as variant). So when you are adding keys in the first code, you are adding Keys / Items as Ranges ( not as string values as you might be thinking!)

[pre]

Sub Sample1()

Dim Dict As Dictionary
Set Dict = New Dictionary

With Dict
.CompareMode = vbBinaryCompare
For i = 1 To 4
'Note the change made from Cells(..) to Cells(..).Value
.Add Cells(0 + i, 1).Value, Cells(0 + i, 2).Value
Next i
End With

Debug.Print Dict.Item("Jane") 'Now this works as it is
Debug.Print Application.Index(Dict.Keys, Application.Match(68, Dict.Items, 0)) 'This requires some efforts

End Sub

[/pre]

Hth,

 

[arishy]

Thank you for your "clear" explanation. Very positive support.

I will study your second point(The index), in the mean time can you please explain to me why (while looping Dict):


For i = 0 To Dict.Count - 1

debug.print Dict.keys()(i), Dict.items()(i) works but

debug.print Dict.key(i), Dict.Item(I) did not

 

[shrivallabha]

Dict.Keys and Dict.Items are "arrays" that hold key and item collection of the dictionary object so you can manipulate them like normal arrays. Since they are zero based arrays, we need to do

Dict.Count - 1

as count is 1 based.


Key is Read / Write property which can be used to modify a Key so it needs to have specific syntax. Please check VBA Help documentation by searching "Dictionary.Key".


Read the documentation as much as you can and come back if you have any doubts.


Hth,

 

[arishy]

The doubt is right there:

debug.print Dict.keys()(i), Dict.items()(i) This one IS working


If I do this


debug.print Dict.keys(i), Dict.items(i)


It does NOT work ....Why I have to use closed brackets to make it work??????


If Dict.Keys is an array I should say Dict.Keys(1) But it gives me an error.

(Run-time Error '451')

Property let procedure not defined and property get procedure did not return an object.


For me this is incomprehensible to say the least.


What is more incomprehensible; putting the closed empty brackets solved the problem.


No document will be able to help me there. It needs someone like you who understand in depth the object in question, to shed some light on the subject.


And now let me hit the head on the nail. If I do Dict("jane") I get the value !!!!!

So, Dict AND Dict.keys BOTH are arrays !!! Mind boggling!!!


I believe, you wisely referred me to the Documentation because it has to do with object programming and these things cannot be explained in simple language UNLESS you KNOW the language.


I think we will have to leave at that last note