Excel VBA Dictionary – A Complete Guide

VBA Dictionary

“The greatest masterpiece in literature is only a dictionary out of order.” – Jean Cocteau

 
 

A Quick Guide to the VBA Dictionary

FunctionParams
Early binding reference“Microsoft Scripting Runtime”
(Add using Tools->References from the VB menu)
Declare (early binding)
Dim dict As Scripting.Dictionary
Create(early binding) Set dict = New Scripting.Dictionary
Declare (late binding) Dim dict As Object
Create(late binding) Set dict = CreateObject("Scripting.Dictionary")
Add item (key must not already exist) dict.Add Key, Value
e.g. dict.Add "Apples", 50
Change value at key. Automatically adds if the key does not exist. dict(Key) = Value
e.g. dict("Oranges") = 60
Get a value from the dictionary using the keyValue = dict(Key)
e.g. appleCount = dict("Apples")
Check if key existsdict.Exists(Key)
e.g. If dict.Exists("Apples") Then
Remove itemdict.Remove Key
e.g. dict.Remove "Apples"
Remove all itemsdict.RemoveAll
Go through all items (for each loop)Dim key As Variant
For Each key In dict.Keys
    Debug.Print key, dict(key)
Next key
Go through all items (for loop - early binding only)Dim i As Long
For i = 0 To dict.Count - 1
   Debug.Print dict.Keys(i),      dict.Items(i)
Next i
Get the number of itemsdict.Count
Make key case sensitive (the dictionary must be empty).dict.CompareMode = vbBinaryCompare
Make key non case sensitive (the dictionary must be empty).dict.CompareMode = vbTextCompare

 
 

CLICK HERE TO DOWNLOAD THE TEST DATA FOR THIS POST

 
 

What is the VBA Dictionary?

In VBA we use Arrays and Collections to store groups of values. For example, we could use them to store a list of customer names, student marks or a  list of values from a range of cells.

A Dictionary is similar to a Collection. Using both types, we can name an item when we add it. Imagine we are storing the count of different fruit types.

 
 
We could use both a Collection and a Dictionary like this

' Add to Dictionary
dict.Add Key:="Apple", Item:=5

' Add to Collection
coll.Add Item:=5, Key:="Apple"

 
 

VBA Dictionary Fruit

Example of Key, Value pairs

 
 
In both cases, we are storing the value 5 and giving it the name “Apple”. We can now get the value of Apple from both types like this

' Get value from Dictionary
Total = dict("Apple")

' Get value from Collection
Total = coll("Apple")

 
 
So far so good. The Collection however, has two major faults

  1. We cannot check if the key already exists.
  2. We cannot change the value of an existing item.

 
 
The first issue is pretty easy to get around: Check Collection Key exists. The second is more difficult.

The VBA Dictionary does not have these issues. You can check if a Key exists and you can change the Item and the Key.

 
 
For example, we can use the following code to check if we have an item called Apple.

If dict.Exists("Apple") Then 
    dict("Apple") = 78 

 
 

These may seem very simple differences. However, it means that the Dictionary is very useful for certain tasks. Particularly when we need to retrieve the value of an item.

 
 

A Dictionary in real world terms

If you are still not clear about a Dictionary then think of it this way. A real world dictionary has a list of keys and items. The Keys are the words and the Items are the definition.

When you want to find the definition of a word you go straight to that word. You don’t read through every item in the Dictionary.

A second real world example is a phone book(remember those?). The Key in a phone book is the name\address and the Item is the phone number. Again you use the name\address combination to quickly find a phone number.

In Excel the VLookup function works in a similar way to a Dictionary. You look up an item based on a unique value.

 
 

A Simple Example of using the VBA Dictionary

The code below give a simple but elegant example of using the Dictionary. It does the following

  1. Adds three fruit types and a value for each to a Dictionary.
  2. The user is asked to enter the name of a fruit.
  3. The code checks if this fruit is in the Dictionary.
  4. If yes then it displays the fruit name and the value.
  5. If no then it informs the user the fruit does not exist.

 
 

Sub CheckFruit()

    ' Select Tools->References from the Visual Basic menu.
    ' Check box beside "Microsoft Scripting Runtime" in the list.
    Dim dict As New Scripting.Dictionary
    
    ' Add to fruit to Dictionary
    dict.Add key:="Apple", Item:=51
    dict.Add key:="Peach", Item:=34
    dict.Add key:="Plum,", Item:=43

    Dim sFruit As String
    ' Ask user to enter fruit
    sFruit = InputBox("Please enter the name of a fruit")

    If dict.Exists(sFruit) Then
        MsgBox sFruit & " exists and has value " & dict(sFruit)
    Else
        MsgBox sFruit & " does not exist."
    End If
    
    Set dict = Nothing
    
End Sub

 
 
This is a simple example but it shows how useful a Dictionary is. We will see a real world example later in the post. Let’s look at the basics of using a Dictionary.

 
 

Creating a Dictionary

To use the Dictionary you need to first add the reference.

  1. Select Tools->References from the Visual Basic menu.
  2. Find Microsoft Scripting Runtime in the list and place a check in the box beside it.

 
 
We declare a dictionary as follows

Dim dict As New Scripting.Dictionary

or

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

 
 
Creating a Dictionary this way is called “Early Binding”. There is also “Late Binding”. Let’s have a look at what this means.

 
 

Early versus Late Binding

To create a Dictionary using Late binding we use the following code. We don’t need to add a reference.

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

 
 
In technical terms Early binding means we decide exactly what we are using up front. With Late binding this decision is made when the application is running. In simple terms the difference is

  1. Early binding requires a reference. Late binding doesn’t.
  2. Early binding allows access to *Intellisense. Late binding doesn’t.
  3. Early binding may require you to manually add the Reference to the “Microsoft Scripting Runtime” for some users.

 
 
(*Intellisense is the feature that shows you the available procedures and properties of an item as you are typing.)

Microsoft recommends that you use early binding in almost all cases.

 
 

Adding Items to the Dictionary

FunctionParamsExample
AddKey, Itemdict.Add "Apples", 50

 
 
We can add items to the dictionary using the Add function. Items can also be added by assigning a value which we will look at in the next section.

 
 
Let’s look at the Add function first. The Add function has two parameters: Key and Item. Both must be supplied

dict.Add Key:="Orange", Item:=45
dict.Add "Apple", 66
dict.Add "12/12/2015", "John"
dict.Add 1, 45.56

 
 
In the first add example above we use the parameter names. You don’t have to do this although it can be helpful when you are starting out.

The Key can be any data type. The Item can be any data type, an object, array, collection or even a dictionary. So you could have a Dictionary of Dictionaries, Array and Collections. But most of the time it will be a value(date, number or text).

If we add a Key that already exists in the Dictionary then we will get the error

 
 
Error 457

 
 
The following code will give this error

dict.Add Key:="Orange", Item:=45

' This line gives an error as key exists already
dict.Add Key:="Orange", Item:=75

 
 

Assigning a Value

OperationFormatExample
AssignDictionary(Key) = Item dict("Oranges") = 60

 
 
We can change the value of a key using the following code

dict("Orange") = 75

 
 
Assigning a value to Key this way has an extra feature. If the Key does not exist it automatically adds the Key and Item to the dictionary. This would be useful where you had a list of sorted items and only wanted the last entry for each one.

' Adds Orange to the dictionary 
dict("Orange") = 45 

' Changes the value of Orange to 100
dict("Orange") = 100

 
 

Checking if a Key Exists

FunctionParametersExample
ExistsKey If dict.Exists("Apples") Then

 
 
We can use the Exists function to check if a key exists in the dictionary

' Checks for the key 'Orange' in the dictionary
If dict.Exists("Orange") Then
    MsgBox "The number of oranges is " & dict("Orange") 
Else
    MsgBox "There is no entry for Orange in the dictionary."
End If

 
 

Other useful functions

FunctionParametersExample
CountN/Adict.Count
RemoveKeydict.Remove "Apples"
RemoveAllN/Adict.RemoveAll

 
 
The three functions in the above table do the following:

  1. Count – returns the number of items in the Dictionary.
  2. Remove – removes a given key from the Dictionary.
  3. RemoveAll – removes all items from the Dictionary

 
 
The following sub shows an example of how you would use these functions

Sub AddRemoveCount()

    Dim dict As New Scripting.Dictionary

    ' Add some items
    dict.Add "Orange", 55
    dict.Add "Peach", 55
    dict.Add "Plum", 55
    Debug.Print "The number of items is " & dict.Count
    
    ' Remove one item
    dict.Remove "Orange"
    Debug.Print "The number of items is " & dict.Count
    
    ' Remove all items
    dict.RemoveAll
    Debug.Print "The number of items is " & dict.Count

End Sub

 
 

The Key and Case Sensitivity

Some of the string functions in VBA have a vbCompareMethod. This is used for functions that compare strings. It is used to determine if the case of the letters matter.

 
 

VBA Dictionary Key

© BigStockPhoto.com

 
 
The Dictionary uses a similar method. The CompareMode property of the Dictionary is used to determine if the case of the key matters. The settings are

TextCompare: Upper and lower case are considered the same.

BinaryCompare: Upper and lower case are considered different. This is the default.

 
 
With the Dictionary we can use these settings to determine if the case of the key matters.

Sub CaseMatters()
    
    Dim dict As New Scripting.Dictionary
    dict.CompareMode = BinaryCompare
    dict.Add "Orange", 1
    
    ' Prints False because it considers Orange and ORANGE different 
    Debug.Print dict.Exists("ORANGE")    
    
    Set dict = Nothing

End Sub

 
 
This time we use TextCompare which means that the case does not matter

Sub CaseMattersNot()
    
    Dim dict As New Scripting.Dictionary
    dict.CompareMode = TextCompare
    dict.Add "Orange", 1
    
    ' Prints true because it considers Orange and ORANGE the same
    Debug.Print dict.Exists("ORANGE")    
    
    Set dict = Nothing

End Sub

 
 
Note: The Dictionary must be empty when you use the CompareMode property or you will get the error: “Invalid procedure call or argument”.

 
 

Things to Watch Out For

BinaryCompare (the case matters) is the default and this can lead to subtle errors. For example, imagine you have the following data in cells A1 to B2.

Orange, 5
orange, 12

 
 
The following code will create two keys – on for “Orange” and one for “orange”. This is subtle as the only difference is the case of the first letter.

Dim dict As New Scripting.Dictionary

dict.add Key:=(Range("A1")), Item:=Range("B1")
dict.add Key:=(Range("A2")), Item:=Range("B2")

 
 
If you do use TextCompare for the same data you will get an error when you try to add the second key as it considers “Orange” and “orange” the same.

Dim dict As New Scripting.Dictionary
dict.CompareMode = TextCompare

dict.add Key:=(Range("A1")), Item:=Range("B1")
' This line will give an error as your are trying to add the same key
dict.add Key:=(Range("A2")), Item:=Range("B2")

 
 
If you use the assign method then it does not take the CompareMode into account. So the following code will still add two keys even though the CompareMode is set to TextCompare.

Sub Assign()
    
    Dim dict As New Scripting.Dictionary
    dict.CompareMode = TextCompare
    
    ' Adds two keys
    dict(Range("A1")) = Range("B1")
    dict(Range("A2")) = Range("B2")
    
    ' Prints 2
    Debug.Print dict.Count
    
End Sub

 
 

Reading through the Dictionary

We can read through all the items in the Dictionary. We can go through the keys using a For Each loop. We then use the current key to access an item.

Dim k As Variant
For Each k In dict.Keys
    ' Print key and value
    Debug.Print k, dict(k)
Next

 
 
We can also loop through the keys although this only works with Early Binding.

Dim i As Long
For i = 0 To dict.Count - 1
    Debug.Print dict.Keys(i), dict.Items(i)
Next i

 
 

Troubleshooting the Dictionary

This section covers the common errors you may encounter using the Dictionary.

Missing Reference

 
 
Issue: You get the error message “User-defined type not defined”
This normally happens when you create the Dictionary but forget to add the reference.

Dim dict As New Scripting.Dictionary

 
Resolution: Select Tools->Reference from the Visual Basic menu. Place a check in the box beside “Microsoft Scripting Runtime”.
 
See Section: Creating a Dictionary

 
 

Exists is not Working

Issue: You have added a key to the Dictionary but when you use the Exists function it returns false
This is normally an issue with Case Sensitivity(see above).
The following code adds “Apple” as a key. When we check for “apple” it returns false. This is because it takes the case of the letters into account.

dict.Add "Apple", 4

If dict.Exists("apple") Then
    MsgBox "Exists"
Else
    MsgBox "Does not Exist"
End If

 
You can set the CompareMode property to TextCompare and this will ignore the case.

Dim dict As New Scripting.Dictionary
dict.CompareMode = TextCompare

 
Resolution: Set the CompareMode to TextCompare to ignore case or ensure your data has the correct case.
 
See Section: The Key and Case Sensitivity

 
 

Object Variable Error

Issue: You get the error message “Object variable or With block variable not set” when you try to use the Dictionary.

The normally happens when you forget to use New before you use the Dictionary. For example, the following code will cause this error

Dim dict As Scripting.Dictionary
' This line will give "Object variable..." error
dict.Add "Apple", 4

 
Resolution: Use the New keyword when creating the Dictionary

Dim dict As New Scripting.Dictionary

 
Or

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

 
See Section: Creating a Dictionary
 

 
 

Useful Tips for Troubleshooting the Dictionary

If you are investigating an issue with the Dictionary it can be useful to see the contents.

 
 
Use the following sub to Print each Key and Item to the Immediate Window(Ctrl + G).

Sub PrintContents(dict As Scripting.Dictionary)
    
    Dim k As Variant
    For Each k In dict.Keys
        ' Print key and value
        Debug.Print k, dict(k)
    Next

End Sub

 
 
You can use it like this

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

' Add items to Dictionary here

' Print the contents of the Dictionary to the Immediate Window
PrintContents dict

 
 
If you are stepping through the code you can also add dict.Count to the Watch Window to see how many items are currently in the Dictionary. Right-click anywhere in the code window and select Add Watch. Type dict.Count into the text box and click Ok.

You can also use the Dictionary itself as a Watch. Add Dict to the Watch window. If you click on the plus sign you will see the contents of the Dictionary. This can be useful but it only shows the key and not the item.

Note: You can only view Watches when the code is running.

 
 

A Real World Dictionary Example

Let’s have a look at a real world example of using a dictionary. Our data for this example is the World Cup Final matches from 2014.

 
 
VBA World Cup

 
 
CLICK HERE TO DOWNLOAD THE TEST DATA FOR THIS POST

 
 
Our task here is to get the number of goals scored by each team.

The first thing we need to do is to read all the data. The following code reads through all the matches and prints the names of the two teams involved.

Sub GetTotals()
    
    ' Get worksheet
    Dim wk As Worksheet
    Set wk = ThisWorkbook.Worksheets("2014")
    
    ' Get range for all the matches
    Dim rgMatches As Range
    Set rgMatches = wk.Range("A1").CurrentRegion
    
    Dim sTeam1 As String, sTeam2 As String
    Dim lGoals1 As Long, lGoals2 As Long

    Dim row As Range
    For Each row In rgMatches.Rows
        ' read the data from each match
        sTeam1 = row.Cells(1, 5)
        sTeam2 = row.Cells(1, 9)
        lGoals1 = row.Cells(1, 6)
        lGoals2 = row.Cells(1, 7)
        ' Print each teams/goals to Immediate Window(Ctrl + G)
        Debug.Print sTeam1, sTeam2, lGoals1, lGoals2
    Next row
    
End Sub

 
 
What we want to do now is to store each team and the goals they scored. When we meet a team for the first time we add the name as a Key and the number of goals as the Item.

 
 

VBA Dictionary World Cup

Celebrating a Goal | © BigStockPhoto.com

 
 
If the team has already been added then we add the goals they scored in the current match to their total.

 
 
Using a Dictionary, we can easily check if the team already exists. We can also update the value for this team.

If dict.Exists(sTeam1) Then
    ' If exists add to total
    dict(sTeam1) = dict(sTeam1) + lGoals1
Else
    ' if doesn't exist then add
    dict(sTeam1) = lGoals1
End If

 
 
We write out the values from the Dictionary to the worksheet as follows

Public Sub WriteDictionary(dict As Scripting.Dictionary _
    , shReport As Worksheet)
    
    shReport.Cells.Clear
    Dim k As Variant, lRow As Long
    lRow = 1
    For Each k In dict.Keys
        shReport.Cells(lRow, 1) = k
        shReport.Cells(lRow, 2) = dict(k)
        lRow = lRow + 1
    Next
    
End Sub

 
 
We obviously want the scores to be sorted. It is much easier to read this way. There is no easy way to sort a Dictionary. The way to do it is to copy all the items to an array. Sort the array and copy the items back to a Dictionary.

 
 
What we can do is sort the data once it has been written to the worksheet. We can use the following code to do this

Public Sub SortByScore(shReport As Worksheet)
    
    Dim rg As Range
    Set rg = shReport.Range("A1").CurrentRegion
    rg.sort rg.Columns("B"), xlDescending
    
End Sub

 
 
Our final GetTotals Sub looks like this

Sub GetTotals()
    
    ' Create dictionary
    Dim dict As New Scripting.Dictionary
    
    ' Get worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("2014")
    
    ' Get range
    Dim rgMatches As Range
    Set rgMatches = sh.Range("A1").CurrentRegion
    
    Dim sTeam1 As String, sTeam2 As String
    Dim lGoals1 As Long, lGoals2 As Long
    Dim i As Long
    For i = 2 To rgMatches.Rows.Count
        sTeam1 = rgMatches.Cells(i, 5)
        sTeam2 = rgMatches.Cells(i, 9)
        lGoals1 = rgMatches.Cells(i, 6)
        lGoals2 = rgMatches.Cells(i, 7)
        
        If dict.Exists(sTeam1) Then
            ' If exists add to total
            dict(sTeam1) = dict(sTeam1) + lGoals1
        Else
            ' if doesn't exist then add
            dict(sTeam1) = lGoals1
        End If
        
        If dict.Exists(sTeam2) Then
            ' If exists add to total
            dict(sTeam2) = dict(sTeam2) + lGoals2
        Else
            ' if doesn't exist then add
            dict(sTeam2) = lGoals2
        End If
        
    Next i
    
    ' Get the report worksheet
    Dim shReport As Worksheet
    Set shReport = ThisWorkbook.Worksheets("Report")
    
    ' Write the teams and scores to the worksheet
    WriteDictionary dict, shReport
    
    ' Sort the range
    SortByScore shReport

    ' Clean up
    Set dict = Nothing
        
End Sub

 
 
When you run this code you will get the following results

VBA Results

Teams ordered by number of goals scored

 
 

When To Use The Dictionary

So when should you use the VBA Dictionary? When you have a task where

  1. You have a list of unique items e.g. countries, invoice numbers, customer name and addresses, project ids, product names etc.
  2. You need to retrieve the value of a unique item.

 
 

VBA Dictionary 1

Key/Values of Countries and Land area in Km2

 
 

What’s Next?

If you want to read about more VBA topics you can view a complete list of my posts here. I also have a free eBook(see below) which you will find useful if you are new to VBA.

 
 
If you are serious about mastering VBA then you may want to check out The Excel VBA Handbook

 
 

Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook

 
 

34 comments

  1. Outstanding blog post Paul!!

    Especially for someone learning from what I can find online.

    I know it must be impossible to include everything about a topic,
    but you do great on the basics, especially with the samples.

    With the Scripting Dictionary, I have difficulty as I’m on a Mac(not by choice, it’s my employer)
    I have found this by Patrick O’Beirne
    https://sysmod.wordpress.com/2011/11/02/dictionary-class-in-vba-instead-of-scripting-dictionary/

    This has a link to both his classes for both Dictionary Class and KeyValuePair Class
    https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class-update/
    In some cases, I have read this is faster than the scripting dictionary.

    Love your work

    1. Thanks very much Dane. Glad you like the post.
      They take considerable effort and after I finish one I vow never again:-)

      That looks like a good alternative by Patrick and you can use it the same way as the Scripting Dictionary.

      Regards
      Paul

  2. Collection is better:
    1) exists method can be written using error handling
    2) keys can be kept ordered using a binary search method
    (easy and short to write) for inserting items at the correct position
    3) collection is probably a combination of a hash map and a fast array holding an integer column and an object address column

    1. Hi Henry,

      1 and 2 are non-trivial tasks for someone who is new to the concept of a Dictionary. This is who the post is aimed at.
      I was going to mention the exists/error handling method in the post. In the end, I thought it would cause too much confusion.

      The other difference with Collections and Dictionaries is that Collections are read-only. This is not easy to get around.

      Regards.
      Paul

  3. Hi Paul,
    Thanks for the great overview of Dictionaries. I have not used them before and am not an expert VBA coder by any stretch of the imagination.
    I am attempting to automate a monthly process for the users that requires a Lookup of an Emp Id from another worksheet (which contains over 100,000 rows) in the same file to the 2nd sheet which contains employee info which must be copied to fields in the 1st worksheet.
    Based on your clear instructions, I was able to build a sample dictionary in the Immediate window. Next, I need to copy specific fields for each record to the 1st worksheet. Keeping in mind, this file will be updated (the # of rows will change) and the process needs to be reproduced each month, what is the best method to copy the data to the first sheet?
    Thanks in advance for your help.

  4. do you know of any good online resources to develop more knowledge specifically on using Dictionaries, further to this post? many thanks!

  5. First of all Paul, GREAT POST !!!
    wondering….is it possible to load into Directory all cells that are BOTH Empty AND with Interior.Color = 16777215
    and then code should enter to all match cells (with both criteria) the number 1.
    I found this code it some other site but could not figure out why it is not working:

    Dim d As Object, e
    Set d = CreateObject(“scripting.dictionary”)
    For Each e In Intersect(Columns(ActiveCell.Column), ActiveSheet.UsedRange)
    If e.Value = “” And e.Interior.Color = 16777215 Then
    d(e.Value) = 1
    End If
    Next

    appreciate your help.
    Thanks !

      1. Thanks a lot Paul !
        How i pass all the e.address = 1 (i.e: results) back to the spreadsheet/to relevant cells ?

        appreciate your help.

        1. If you want to do this you need to store the cell address in the dictionary.

          Why do you need a Dictionary for this?
          You can just update the cells in the loop as your read them.

          1. thanks for your reply Paul.

            i need it as the range is very big.
            it’s about 261 columns (i.e.: LastColumn) and about 20,000 rows (i.e: LastRow) so….
            it takes very long time to loop every cell. I need find if the interior color of the cell is XlNone and if the cell is empty (Null String) . if both criteria are TRUE to add number 1 to (each) cell.
            I thought to use this method of CreateObject(“scripting.dictionary”) in order to make things much faster, but I’m hard to figure out why it does not work…?

            any help will be highly appreciated !

            P.S:
            I found another way to do it but the code still run 2.5 minutes.

            this is the way i found:
            Sub FillEmptyNonColorCellsWith_1()

            LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
            LC = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

            Application.FindFormat.Clear
            Application.FindFormat.Interior.ColorIndex = -4142
            Range(Cells(2, 3), Cells(LR, LC)).Replace “”, “1”, SearchFormat:=True
            Application.FindFormat.Clear
            End Sub

            Thanks.

  6. I’ve try this one as well but….
    don’t seems to work:

    ‘ Fiil empty cells AND cells with Interior.Color = 16777215 with 1’ns
    Dim d As Object, e
    Set d = CreateObject(“scripting.dictionary”)
    For Each e In Intersect(Columns(ActiveCell.Column), ActiveSheet.UsedRange)
    If d(e.Value) = “” And d(e.Interior.Color) = 16777215 Then
    d(e.Value) = 1
    Else: d(e.Value) = “”
    End If
    Next

  7. Complile error: Variable not defined

    “TextCompare” (see below) is highlighted

    When using a dictionary, all of the reference materials I’ve seen mention using either BinaryCompare or TextCompare as the value for the CompareMode property. However, none seem to indicate whether these are values or keywords. If they are values, do I need to define them as constants and if so, what magical values do I need to assign to them?

    Here’s a code snippet…

    Option Explicit

    Public OAppDict As Object

    Public Sub Demo_1()
    Dim i As Long
    Dim objOApp As COracleApp

    Set OAppDict = CreateObject(“Scripting.Dictionary”)
    Set objOApp = New COracleApp

    OAppDict.CompareMode = TextCompare ‘TextCompare is being flagged as a variable that is not defined

    1. They are already defined as VBA constants. In your code you’re missing the vb in front of TextCompare.

      OAppDict.CompareMode = vbBinaryCompare
      OAppDict.CompareMode = vbTextCompare

      1. Thanks. Is there a comprehensive place to find all of the “vb” constants that are applicable to VBA? That way, I’ll know when I can use one of those versus having to define a Constant?

        1. You can press F1 on the function and this will open the Microsoft help. If you click on a constant and press Shift + F2 it will list all the related constants.

  8. I’ve created a class so that I can store an object (multiple values, potentially different data types) as the data (Item) component in a Dictionary but I’m having difficulties in updating a data member within that object and getting it (or the object) stored back into the Dictionary. I’m getting “Run-time error 438 (object doesn’t support this property or method”

    Background: I’m attempting to use a Dictionary to hold info (sheet name, next row to write to) about multiple sheets that might I create/update in my Excel workbook.

    The following code is a prototype to make sure that the Dictionary can be loaded, updated and referenced as needed. I’m having problems updating the data (Item) component which is based on a class rather than a simple or single data type such as String or Long

    ‘******************************************
    ‘* FROM A Class Module named as COracleApp
    ‘******************************************

    Private pSheet As String
    Private pRowInx As Long

    Public Property Get Sheet() As String
    Sheet = pSheet
    End Property

    Public Property Let Sheet(Value As String)
    pSheet = Value
    End Property

    Public Property Get RowInx() As Long
    RowInx = pRowInx
    End Property

    Public Property Let RowInx(Value As Long)
    pRowInx = Value
    End Property

    ‘************************
    ‘* From a regular module
    ‘************************

    Option Explicit

    Public OAppDict As Object

    Public Sub Demo_1()
    Dim i As Long
    Dim objOApp As COracleApp

    Set OAppDict = CreateObject(“Scripting.Dictionary”)
    Set objOApp = New COracleApp

    LoadOAppDict

    ‘For brevity, I’ve removed the code that traverses the Dictionary
    ‘and seems to indicate that the Dictionary is propertly loaded

    ‘Example update
    If OAppDict.Exists(“GL”) Then
    Set objOApp = OAppDict(“GL”)
    objOApp.RowInx = objOApp.RowInx + 1
    OAppDict.Item(“GL”) = objOApp ‘this line is being flagged with Run-time error 438
    End If

    Set OAppDict = Nothing
    Set objOApp = Nothing
    End Sub

    Public Sub LoadOAppDict()
    Dim objOApp As COracleApp

    Set objOApp = New COracleApp
    objOApp.Sheet = “1_FA”
    objOApp.RowInx = 1
    OAppDict.Add Key:=”AP”, Item:=objOApp

    Set objOApp = New COracleApp
    objOApp.Sheet = “3_GL”
    objOApp.RowInx = 1
    OAppDict.Add Key:=”GL”, Item:=objOApp

    End Sub

    1. You need to use Set when assigning any non-basic type. A basic type is long, string, double etc. You assign these without using set. Any non-basic type requires set. I have written a post about it called VBA Objects – The Ultimate Guide.

      Your line should read

      Set OAppDict.Item(“GL”) = objOApp

  9. I stumbled onto your website as I was “googling” how to use class modules. I’m fairly good with VBA (self-taught) and am trying to improve my skills. First off, thank you for a very informative, and easy-to-understand blog on the subject (your class module blog said I should come here first). Second; I was VERY surprised when I got about 1/2 way through and didn’t come to the part where I need to pay for the remainder, or enroll in classes. You have certainly went to the top of my “Go To” list for help. Thanks Very Much.

  10. Hi Paul,

    I was wondering if you’re willing to extend the article with a (few) paragraph(s) about nested dictionaries and / or collections. I have great difficulty with using the right syntax to access keys or items and can’t find a good thorough explanation like the way you did it in this article.
    Kind regards,
    Ron

  11. Paul: I, too, appreciated your article on the Dictionary object, and would like to see as well what you told Ron about nested dictionaries. Further, since there is no native method of copying one dictionary to another (that is, set dict2 = dict1 just makes dict2 a reference to dict1, so changes to one change the other as well), and it is pretty straightforward to iterate the key/item pairs in dict1 to add them to dict2, this is much more problematic for a dictionary that has nested dictionaries, it would be great if you could comment on true copying of a nested dictionary. Thanks. Nick

    1. Thanks Nick,

      The solution to Ron’s question didn’t require nested dictionaries in the end.

      You can copy between nested like this example

      Sub NestedDictionary()
          
          Dim dict1 As Object
          
          ' Main dictionary
          Set dict1 = CreateObject("Scripting.Dictionary")
          
          ' Nested dictionaries
          Dim a As Object, b As Object
          Set a = CreateObject("Scripting.Dictionary")
          Set b = CreateObject("Scripting.Dictionary")
          a.Add "apple", 44
          b.Add "Oranges", 656
          
          ' Add to main dictionary
          dict1.Add 1, a
          dict1.Add 2, b
          
          ' Copy the nested dictionary
          Dim dict2 As Object
          Set dict2 = CopyNestedDict(dict1)
      
      End Sub
      
      Function CopyNestedDict(dict1 As Object)
      
          Dim key1 As Variant, key2 As Variant
          Dim newDict As Object
          
          Set newDict = CreateObject("Scripting.Dictionary")
          For Each key1 In dict1
               
               newDict.Add key1, dict1(key1)
          
          Next key1
          
          Set CopyNestedDict = newDict
          
      End Function
      

      Paul

  12. Hi there,

    please find below a simple sub to print nested dictionaries.

    Sub PrintDictionary(Dict As scripting.Dictionary, Optional intNumIndent As Integer = 0)
    Dim key As Variant

    If intNumIndent = 0 Then
    debug.print “Tools::PrintDictionary – INFO. Printing ” & Dict.Count & ” elements…”
    End If

    For Each key In Dict.Keys
    If TypeName(Dict.item(key)) = “Dictionary” Then
    debug.print key & ” => [ ”
    Call PrintDictionary(Dict.item(key), intNumIndent + 2)
    debug.print “], ”
    Else
    Select Case TypeName(Dict.item(key))
    Case “Integer”, “String”, “Long”, “Single”, “Double”, “Decimal”, “Boolean”:
    debug.print String(intNumIndent, ” “) & key & ” => ” & Dict.item(key) & “,”
    Case “Date”:
    debug.print String(intNumIndent, ” “) & key & ” => ” & Format(Dict.item(key), “DD/MM/YYYY hh\hmm”) & “,”
    Case Else:
    debug.print String(intNumIndent, ” “) & key & ” => ” & “[” & TypeName(Dict.item(key)) & “]” & “,”
    End Select
    End If
    Next
    End Sub

  13. Hey Paul, very good guide, but this line…
    >> (Note: you cannot change the Key itself)
    … is misleading, because you can!

    Take a look at this:

    Public Function ChangeDictKey(ByRef OldKey As Variant, ByRef NewKey As Variant, ByRef Dict As Dictionary)
    If Dict.Exists(OldKey) Then _
    Dict.Key(OldKey) = NewKey
    End Function

  14. Hi Paul, many thanks for your post from Germany. As beginner in VBA it is very hard for me to find good explanations in German language. I have never found any substantuial explanation about dictionaries in German language. Also your posts about collections and objects are much more helpful for me than the German stuff about this issues. Please go ahead with your site ! Thanks, Bernd

Leave a Reply

Your email address will not be published. Required fields are marked *