Uploaded September 2019 | Updated November 2026, 12 hours ago
What is Dictionary in Excel VBA. An introduction to Dictionary object in VBA.
Here's the VBA code used in this video:
Module1
Option Explicit
Sub createDictionary()
Dim mydictionary As Scripting.Dictionary
Set mydictionary = New Scripting.Dictionary
'MsgBox mydictionary.Count
mydictionary.Add "Dave", 25000
mydictionary.Add "Aslam", 30000
mydictionary.Add "Krishna", 32000
'mydictionary.RemoveAll
'salary increased after 1 year
'Assign new salary
mydictionary("Dave") = 30000
mydictionary("Aslam") = 35000
mydictionary("Krishna") = 40000
Dim i As Long, nextBlankRow As Long
nextBlankRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For i = 0 To mydictionary.Count - 1
'Debug.Print mydictionary.Keys(i), mydictionary.Items(i)
Cells(nextBlankRow, 1) = mydictionary.Keys(i)
Cells(nextBlankRow, 2) = mydictionary.Items(i)
nextBlankRow = nextBlankRow + 1
Next i
End Sub
Module2:
Option Explicit
Sub WriteDictionary(mydictionary As Scripting.Dictionary, shtReport As Worksheet)
shtReport.Cells.Clear
Dim k As Variant, lRow As Long
lRow = 2
Range("A1") = "Customer ID"
Range("B1") = "Amount"
For Each k In mydictionary.Keys
shtReport.Cells(lRow, 1) = k
shtReport.Cells(lRow, 2) = mydictionary(k)
lRow = lRow + 1
Next
End Sub
Sub GetTotals()
' Create a dictionary
Dim mydictionary As New Scripting.Dictionary
' Get worksheet
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
' Get range
Dim myRange As Range
Set myRange = sht.Range("A1").CurrentRegion
Dim customerID As String
Dim Amount As Long
Dim i As Long
For i = 2 To myRange.Rows.Count
customerID = myRange.Cells(i, 1)
Amount = myRange.Cells(i, 2)
mydictionary(customerID) = mydictionary(customerID) + Amount
Next i
' Get the report worksheet
Dim shtReport As Worksheet
Set shtReport = ThisWorkbook.Worksheets("customerReport")
' Write customer totals
WriteDictionary mydictionary, shtReport
' Clean memory
Set mydictionary = Nothing
End Sub
More details available at exceltrainingvideos.com/what-is-dictionary-in-excel-vba
What is Dictionary in Excel VBA. An introduction to Dictionary object in VBA.
Here's the VBA code used in this video:
Module1
Option Explicit
Sub createDictionary()
Dim mydictionary As Scripting.Dictionary
Set mydictionary = New Scripting.Dictionary
'MsgBox mydictionary.Count
mydictionary.Add "Dave", 25000
mydictionary.Add "Aslam", 30000
mydictionary.Add "Krishna", 32000
'mydictionary.RemoveAll
'salary increased after 1 year
'Assign new salary
mydictionary("Dave") = 30000
mydictionary("Aslam") = 35000
mydictionary("Krishna") = 40000
Dim i As Long, nextBlankRow As Long
nextBlankRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For i = 0 To mydictionary.Count - 1
'Debug.Print mydictionary.Keys(i), mydictionary.Items(i)
Cells(nextBlankRow, 1) = mydictionary.Keys(i)
Cells(nextBlankRow, 2) = mydictionary.Items(i)
nextBlankRow = nextBlankRow + 1
Next i
End Sub
Module2:
Option Explicit
Sub WriteDictionary(mydictionary As Scripting.Dictionary, shtReport As Worksheet)
shtReport.Cells.Clear
Dim k As Variant, lRow As Long
lRow = 2
Range("A1") = "Customer ID"
Range("B1") = "Amount"
For Each k In mydictionary.Keys
shtReport.Cells(lRow, 1) = k
shtReport.Cells(lRow, 2) = mydictionary(k)
lRow = lRow + 1
Next
End Sub
Sub GetTotals()
' Create a dictionary
Dim mydictionary As New Scripting.Dictionary
' Get worksheet
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
' Get range
Dim myRange As Range
Set myRange = sht.Range("A1").CurrentRegion
Dim customerID As String
Dim Amount As Long
Dim i As Long
For i = 2 To myRange.Rows.Count
customerID = myRange.Cells(i, 1)
Amount = myRange.Cells(i, 2)
mydictionary(customerID) = mydictionary(customerID) + Amount
Next i
' Get the report worksheet
Dim shtReport As Worksheet
Set shtReport = ThisWorkbook.Worksheets("customerReport")
' Write customer totals
WriteDictionary mydictionary, shtReport
' Clean memory
Set mydictionary = Nothing
End Sub
More details available at exceltrainingvideos.com/what-is-dictionary-in-excel-vba










