By fessGUID


2009-05-27 12:00:03 8 Comments

Does VBA have dictionary structure? Like key<>value array?

9 comments

@Mitch Wheat 2009-05-27 12:04:53

Yes.

Set a reference to MS Scripting runtime ('Microsoft Scripting Runtime'). As per @regjo's comment, go to Tools->References and tick the box for 'Microsoft Scripting Runtime'.

References Window

Create a dictionary instance using the code below:

Set dict = CreateObject("Scripting.Dictionary")

or

Dim dict As New Scripting.Dictionary 

Example of use:

If Not dict.Exists(key) Then 
    dict.Add key, value
End If 

Don't forget to set the dictionary to Nothing when you have finished using it.

Set dict = Nothing 

@David-W-Fenton 2009-05-29 04:07:00

This data structure type is provided by the scripting runtime, not by VBA. Basically, VBA can use practically any data structure type that is accessible to it via a COM interface.

@regjo 2009-12-10 12:32:00

Just for the sake of completeness: you need to reference the "Microsoft Scripting Runtime" for this to work (go to Tools->References) and check its box.

@David-W-Fenton 2011-04-29 03:38:19

Could someone explain to me how the Scripting Runtime's Dictionary object is different from a VBA collection?

@Mitch Wheat 2011-04-29 03:41:43

@ David-W-Fenton: a collection is not keyed

@David-W-Fenton 2011-04-30 18:40:19

Uh, VBA collections ARE keyed. But maybe we have a different definition of keyed.

@joweiser 2012-01-12 23:55:02

In Excel 2010 the reference to "Microsoft Scripting Runtime" is not necessary when using the CreateObject() method.

@ihightower 2012-01-21 12:26:02

I am using Excel 2010... but without the reference to "Microsoft Scripting Runtime" Tools - Ref.. Just doing CreateObject does NOT work. So, @masterjo I think your comment above is wrong. Unless I am missing something.. So, guys Tools -> references is required.

@joweiser 2012-01-22 02:44:39

ihightower: would it be possible for you to post a short pastebin of the code that's not working without the reference? I'm really curious! Tested this on a couple different environments and it runs without the reference.

@David Zemens 2013-09-25 13:50:27

As an FYI, you can't use the Dim dict As New Scripting.Dictionary without the reference. Without the reference, you have to use the late binding CreateObject method of instantiating this object.

@Zev Spitz 2015-09-16 21:42:26

@David-W-Fenton (1) I don't think there is any way to iterate over a Collection's keys. (2) Setting an item (via the default property) with an existing key to a Dictionary will silently overwrite; this is sometimes very useful. (3) It is possible to change the CompareMode of the Dictionary, to be case-sensitive or other CompareMode options.

@Zev Spitz 2015-09-16 21:44:21

@joweiser This is true for previous versions of Office as well.

@Zev Spitz 2015-09-16 21:45:22

@MitchWheat You can also write dict(key) = value without explicitly checking if the key exists or not.

@Patrick Lepelletier 2016-06-12 23:30:56

to create the reference to "Microsoft Scripting Runtime" you can also do it by adding this code : ThisWorkbook.VBProject.References.AddFromFile "C:\windows\system32\scrrun.dll"

@Mitch Wheat 2018-12-17 10:05:51

@Andreas Dietrich: the question doesn't mention Excel, so not sure why you are bringing it up.

@Andreas Dietrich 2018-12-17 10:08:10

@MitchWheat you are right, but I found it out in the common Excel VBA context and think it may be also interesting to others

@Mitch Wheat 2018-12-17 10:15:49

It's totally irrelevant to the question asked,

@Caleb Hattingh 2011-11-29 04:10:44

VBA has the collection object:

    Dim c As Collection
    Set c = New Collection
    c.Add "Data1", "Key1"
    c.Add "Data2", "Key2"
    c.Add "Data3", "Key3"
    'Insert data via key into cell A1
    Range("A1").Value = c.Item("Key2")

The Collection object performs key-based lookups using a hash so it's quick.


You can use a Contains() function to check whether a particular collection contains a key:

Public Function Contains(col As Collection, key As Variant) As Boolean
    On Error Resume Next
    col(key) ' Just try it. If it fails, Err.Number will be nonzero.
    Contains = (Err.Number = 0)
    Err.Clear
End Function

Edit 24 June 2015: Shorter Contains() thanks to @TWiStErRob.

Edit 25 September 2015: Added Err.Clear() thanks to @scipilot.

@Simon Tewsi 2013-05-05 12:37:38

Well done for pointing out the built in Collection object can be used as a dictionary, since the Add method has an optional "key" argument.

@MiVoth 2013-06-18 08:20:54

The bad thing about the collection object is, that you cannot check if a key is already in the collection. It'll just throw an error. That's the big thing, i don't like about collections. (i know, that there are workarounds, but most of them are "ugly")

@Ben McIntyre 2013-12-20 01:32:38

Note that the lookup of string keys (eg. c.Item("Key2") ) in the VBA Dictionary IS hashed, but lookup by integer index (eg. c.Item(20) )is not - it's a linear for/next style search and should be avoided. Best to use collections for only string key lookups or for each iteration.

@RubberDuck 2014-07-15 04:02:35

That Contains function is a beautiful hack on the error handler.

@TWiStErRob 2015-06-20 12:14:39

I found a shorter Contains: On Error Resume Next _ col(key) _ Contains = (Err.Number = 0)

@scipilot 2015-09-24 22:08:47

Shouldn't you clear Err at the end of Contains()? Else it would get caught by calling functions' error handling. (Note you cannot therefore use it during error handling without caching the current error being handled.)

@Caleb Hattingh 2015-09-24 23:08:51

@scipilot: That's a good idea. Errors are automatically cleared when hitting On Error or Try/Catch/Finally, but a user might still have code somewhere that does error detection and sees the leftover stuff from here.

@PaulG 2017-10-18 19:07:37

I believe in this case yes. On Error Resume will not raise the error up the stack. This will have the error still set at the call site, but will not 'raise' the error up the call stack. If you use On Error Goto in the function and do the test that way, this will trap the error and the error is local to the function and won't need reset.

@jpmc26 2018-02-22 11:57:32

Perhaps the function should be named ContainsKey; someone reading only the invocation may confuse it for checking that it contains a particular value.

@Tarik 2018-09-06 15:18:37

Maybe you want to add On Error GoTo 0 after Err.Clear

@Si8 2019-03-13 14:02:20

I was to have a comma separated key to one value. Can that be done with the a Collection?

@Michiel van der Blonk 2015-11-23 01:27:04

All the others have already mentioned the use of the scripting.runtime version of the Dictionary class. If you are unable to use this DLL you can also use this version, simply add it to your code.

https://github.com/VBA-tools/VBA-Dictionary/blob/master/Dictionary.cls

It is identical to Microsoft's version.

@Evan Kennedy 2015-06-04 19:28:40

Building off cjrh's answer, we can build a Contains function requiring no labels (I don't like using labels).

Public Function Contains(Col As Collection, Key As String) As Boolean
    Contains = True
    On Error Resume Next
        err.Clear
        Col (Key)
        If err.Number <> 0 Then
            Contains = False
            err.Clear
        End If
    On Error GoTo 0
End Function

For a project of mine, I wrote a set of helper functions to make a Collection behave more like a Dictionary. It still allows recursive collections. You'll notice Key always comes first because it was mandatory and made more sense in my implementation. I also used only String keys. You can change it back if you like.

Set

I renamed this to set because it will overwrite old values.

Private Sub cSet(ByRef Col As Collection, Key As String, Item As Variant)
    If (cHas(Col, Key)) Then Col.Remove Key
    Col.Add Array(Key, Item), Key
End Sub

Get

The err stuff is for objects since you would pass objects using set and variables without. I think you can just check if it's an object, but I was pressed for time.

Private Function cGet(ByRef Col As Collection, Key As String) As Variant
    If Not cHas(Col, Key) Then Exit Function
    On Error Resume Next
        err.Clear
        Set cGet = Col(Key)(1)
        If err.Number = 13 Then
            err.Clear
            cGet = Col(Key)(1)
        End If
    On Error GoTo 0
    If err.Number <> 0 Then Call err.raise(err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext)
End Function

Has

The reason for this post...

Public Function cHas(Col As Collection, Key As String) As Boolean
    cHas = True
    On Error Resume Next
        err.Clear
        Col (Key)
        If err.Number <> 0 Then
            cHas = False
            err.Clear
        End If
    On Error GoTo 0
End Function

Remove

Doesn't throw if it doesn't exist. Just makes sure it's removed.

Private Sub cRemove(ByRef Col As Collection, Key As String)
    If cHas(Col, Key) Then Col.Remove Key
End Sub

Keys

Get an array of keys.

Private Function cKeys(ByRef Col As Collection) As String()
    Dim Initialized As Boolean
    Dim Keys() As String

    For Each Item In Col
        If Not Initialized Then
            ReDim Preserve Keys(0)
            Keys(UBound(Keys)) = Item(0)
            Initialized = True
        Else
            ReDim Preserve Keys(UBound(Keys) + 1)
            Keys(UBound(Keys)) = Item(0)
        End If
    Next Item

    cKeys = Keys
End Function

@user2604899 2013-08-22 12:29:55

If by any reason, you can't install additional features to your Excel or don't want to, you can use arrays as well, at least for simple problems. As WhatIsCapital you put name of the country and the function returns you its capital.

Sub arrays()
Dim WhatIsCapital As String, Country As Array, Capital As Array, Answer As String

WhatIsCapital = "Sweden"

Country = Array("UK", "Sweden", "Germany", "France")
Capital = Array("London", "Stockholm", "Berlin", "Paris")

For i = 0 To 10
    If WhatIsCapital = Country(i) Then Answer = Capital(i)
Next i

Debug.Print Answer

End Sub

@jcb 2018-02-27 01:11:33

The concept of this answer is sound, but the sample code won't run as written. Each variable needs its own Dim keyword, Country and Capital need to be declared as Variants due to the use of Array(), i ought to be declared (and must be if Option Explicit is set), and the loop counter is going to throw an out of bound error -- safer to use UBound(Country) for the To value. Also maybe worth noting that while the Array() function is a useful shortcut, it's not the standard way to declare arrays in VBA.

@John M 2012-01-25 21:36:09

An additional dictionary example that is useful for containing frequency of occurence.

Outside of loop:

Dim dict As New Scripting.dictionary
Dim MyVar as String

Within a loop:

'dictionary
If dict.Exists(MyVar) Then
    dict.Item(MyVar) = dict.Item(MyVar) + 1 'increment
Else
    dict.Item(MyVar) = 1 'set as 1st occurence
End If

To check on frequency:

Dim i As Integer
For i = 0 To dict.Count - 1 ' lower index 0 (instead of 1)
    Debug.Print dict.Items(i) & " " & dict.Keys(i)
Next i

@John M 2012-02-15 15:50:50

An additional tutorial link is: kamath.com/tutorials/tut009_dictionary.asp

@raddevus 2017-10-30 19:55:33

This was a very good answer and I used it. However, I found that I couldn't reference the dict.Items(i) or dict.Keys(i) in the loop as you do. I had to store those (item list and keys list) in separate vars before entering the loop and then use those vars to get to the values I needed. Like - allItems = companyList.Items allKeys = companyList.Keys allItems(i) If not, I would get the error: "Property let procedure not defined and property get procedure did not return an object" when attempting to access Keys(i) or Items(i) in the loop.

@Matthew Flaschen 2009-05-27 12:02:13

Yes. For VB6, VBA (Excel), and VB.NET

@Mitch Wheat 2009-05-27 12:06:34

not sure what VB.NET has to do with VBA?

@fessGUID 2009-05-27 12:15:33

You can read question ones more: I've asked about VBA: Visual Basic for Application, not for VB, not for VB.Net, not for any other language.

@Konrad Rudolph 2009-05-27 12:22:36

fessGUID: then again, you should read answers more! This answer can also be used for VBA (in particular, the first link).

@Mitch Wheat 2009-05-27 12:27:17

which is why I asked the original question about relevance of VB.NET...

@Matthew Flaschen 2009-05-28 03:42:06

I admit. I read the question too fast. But I did tell him what he needed to know.

@Oorang 2009-05-29 20:30:00

The relevance of .Net is that sooner or later VBA (which is currently a subset of VB6) will end up moving to being a subset of .Net. Besides you should never complain if people give you more than you ask for. It's just ungrateful.

@Richard Gadsden 2010-08-27 14:26:12

@Oorang, there's absolutely no evidence of VBA becoming a subset of VB.NET, backcompat rules in Office - imagine trying to convert every Excel macro ever written.

@David-W-Fenton 2011-05-06 01:47:36

VBA is actually a SUPERSET of VB6. It uses the same core DLL as VB6, but then adds on all sorts of functionality for the specific applications in Office.

@Simon Tewsi 2013-05-05 12:33:09

@David-W-Fenton: If I recall correctly, VBA is actually the language engine that VB6 and its predecessors use. There is a core VBA language and then additional libraries for Office applications like Word, Excel, Access.

@Kalidas 2011-11-02 18:36:53

The scripting runtime dictionary seems to have a bug that can ruin your design at advanced stages.

If the dictionary value is an array, you cannot update values of elements contained in the array through a reference to the dictionary.

@Qbik 2014-12-13 22:43:05

is it really true ?

@Jarmo 2009-05-27 12:19:03

VBA does not have an internal implementation of a dictionary, but from VBA you can still use the dictionary object from MS Scripting Runtime Library.

Dim d
Set d = CreateObject("Scripting.Dictionary")
d.Add "a", "aaa"
d.Add "b", "bbb"
d.Add "c", "ccc"

If d.Exists("c") Then
    MsgBox d("c")
End If

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How do I sort a dictionary by value?

43 Answered Questions

[SOLVED] How to merge two dictionaries in a single expression?

11 Answered Questions

13 Answered Questions

[SOLVED] Iterating over dictionaries using 'for' loops

19 Answered Questions

15 Answered Questions

[SOLVED] Convert two lists into a dictionary in Python

21 Answered Questions

[SOLVED] Check if a given key already exists in a dictionary

  • 2009-10-21 19:05:09
  • Mohan Gulati
  • 2719965 View
  • 2582 Score
  • 21 Answer
  • Tags:   python dictionary

14 Answered Questions

[SOLVED] Add new keys to a dictionary?

  • 2009-06-21 22:07:39
  • lfaraone
  • 2811290 View
  • 2170 Score
  • 14 Answer
  • Tags:   python dictionary

49 Answered Questions

[SOLVED] Sort a Map<Key, Value> by values

26 Answered Questions

[SOLVED] What is the best way to iterate over a dictionary?

  • 2008-09-26 18:20:06
  • Jake Stewart
  • 1311704 View
  • 2197 Score
  • 26 Answer
  • Tags:   c# dictionary loops

Sponsored Content