What is Dictionary in Excel VBA @Exceltrainingvideos
What is Dictionary in Excel VBA  @Exceltrainingvideos
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 VBAAdvantages of Using Column Headers in VlookupAutomatically Change Print OrientationCopy Every Nth Row of Data From Sheet1 to Sheet2Worksheet Change Event to Enter Numerical ValuesDisplay All Files in Folder in WorksheetGoogle Sheets for Translation2021 | Print Multiple Sheets AutomaticallyCopy User Form Image to Worksheet AutomaticallySelect Random Data Automatically | 2021Create Unique File Name with Date Time StampOpen Any File Using Excel VBA || 2021
Dinesh Kumar Takyar |

What is Dictionary in Excel VBA

SHARE TO X SHARE TO REDDIT SHARE TO FACEBOOK WALLPAPER