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.

You can increase the size of the video:

Full screen mode for YouTube

You can view the video in full screen mode as shown on the left, using the icon at the bottom right of the frame.

You can also increase the quality of the video:

Changing resolution

You can improve the resolution of the video using another icon at the bottom right of the frame. This will slow down the connection speed, but increase the display and sound quality. This icon only becomes visible when you start playing the video.

Finally, if nothing happens when you play the video, check that you're not using IE in compatibility view.

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!