CODEWITHSUNDEEP
vbadictionary
lakshmen
lakshmen

Reputation: 29064

How to check whether a item (not the key) exists in the dictionary VBA

I would like to check whether a specific value exists in a dictionary. I have created a dictionary as such:

Set Curr = CreateObject("Scripting.Dictionary")
For Index = 1 To NoOfCurr
   Curr.Add Index, Cells(15 + Index, 5).Value
Next

I need to check whether a string exists in the dictionary. I have written a code as such:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = arr.Exists(stringToBeFound)
End Function

And it gets called as such: IsInArray("USD", Curr) and it returns a false.

I am using Exists function, but it is returning me a False. How do I change IsInArray to see whether the USD is in the Currency dictionary?

Upvotes: 1

Views: 7002

Answers (1)

Fumu 7
Fumu 7

Reputation: 1091

You may misunderstand about 'Dictionary' of VBA.

You can not get 'key' by 'value', but can get 'value' by 'key'.

In your code, Curr has indices(integer) as 'key's and currency as 'value's. This is why you can't determine that "USD" is included in the dictionary 'Curr'.

If you want to check "USD" exist in cells in E column or not, you should use currency as 'key's.

Following code may be what you want and you can get right answer by using 'IsInArray("USD", Curr)' .

Set Curr = CreateObject("Scripting.Dictionary")
For Index = 1 To NoOfCurr
    Curr.Add Cells(15 + Index, 5).Value, Index 'currency is used as keys.
Next

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = arr.Exists(stringToBeFound)
End Function

Upvotes: 4

Related Questions