VBA - classes and structures videos | Excel VBA Part 39 - Dictionaries

Posted by Andrew Gould on 27 October 2014

The Dictionary class is similar to the Collection class in that it allows you to store a custom collection of objects. This video teaches you how to use dictionaries, including how to reference the Scripting Runtime Object Library, how to create a new Dictionary and add, remove, count and reference the items in it. You'll also learn about the Items and Keys arrays contained within a Dictionary and how to loop over these arrays in order to process all of the items that the Dictionary contains.

See our full range of VBA training resources, or test your knowledge of VBA with one of our VBA skills assessment tests.

There are no files which go with this video.

There are no exercises for this video.

Making a  video bigger

You can increase the size of your video to make it fill the screen like this:

View full screen

Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown at its bottom right-hand corner.


When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.

Improving the quality of a video

To improve the quality of a video, first click on the Settings icon:

Settings icon

Make sure you're playing your video so that the icons shown appear, then click on this gear icon at the bottom right-hand corner.


Choose to change the video quality:

Video quality

Click on Quality as shown to bring up the submenu.


The higher the number you choose, the better will be your video quality (but the slower the connection speed):

Connection speed

Don't choose the HD option unless you have a fast enough connection speed to support it!


Is your Wise Owl speaking too slowly (or too quickly)?  You can also use the Settings menu above to change your playback speed.

This page has 2 threads Add post
07 Nov 18 at 15:07

I am getting an object error with this code.

Sub Schedule15(theDictionary As Dictionary)

Dim singleKey As Variant

For Each singleKey In theDictionary.Keys()
Debug.Print theDictionary.Item(singleKey).Country
Next singleKey

Do you know what can be causing it?

08 Nov 18 at 16:50

I figured it out.  When I populated the dictionary, I switched the key and the item.  Basically, placed the key in the item spot and the item in the key spot.

22 Sep 18 at 07:19

This code is early bound:

    Dim DIC As Scripting.Dictionary
    'Dim DIC As Object
    Set DIC = New Scripting.Dictionary
    'Set DIC = CreateObject("Scripting.Dictionary")
    Dim MyArray() As Variant
    MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
    Dim n As Long
    For n = 1 To UBound(MyArray(), 1)
        DIC.Item(MyArray(n, 1)) = 0
    Next n
    Dim KeysArray() As Variant
    KeysArray() = DIC.Keys
    Dim NumKeys As Long
    NumKeys = DIC.Count
    Dim ElementsArray() As Variant
    ReDim ElementsArray(1 To DIC.Count, 1 To 1) As Variant
    Dim DataRng As Range
    Set DataRng = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(Module1.LRow(wks:=Sheet1), 1))
    Dim Counter As Long
    For Counter = 1 To DIC.Count - 1
        ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys(Counter))
    Next Counter

To convert it to late bound, I have to chnage this line:

ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys(Counter))

to this:

ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys()(Counter))

What's the significance of the extra ()?

24 Sep 18 at 09:08

The Dictionary.Keys() method is a function with no parameters which returns an array.  A very basic definition for this function might look like this:

Public Function Keys() As Variant()

    Keys = Array(1, 2, 3)
End Function

In order to return a single value from the array returned by the function you might assume that you can write this:

Sub TestKeys()
    Debug.Print Keys(0)
End Sub

This doesn't work as the compiler thinks you're attempting to pass an argument to a parameter of the function.
The correct syntax to return a value from the array returned by the function is:

Sub TestKeys()
    Debug.Print Keys()(0)
End Sub

The error message you see when you try to use the first syntax with a late-bound Dictionary is:

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

My guess is that the compiler assumes that you're attempting to pass a value to a parameter of a Property Let or Property Get procedure.  With early-binding it knows that there is a Keys function which has no parameters and calls it accordingly, treating the value in parentheses as the index of the array element you're requesting.

My advice would be to always use the full syntax Keys()(element_index) regardless of whether you're using early or late binding.

24 Sep 18 at 18:31

Thanks for your detailed explanation.

Andrew G  
25 Sep 18 at 07:29

No problem, hope it was useful!