How to use VBA Dictionary to boost data management

Efficiency is a  key requirement when dealing with data.  This post will explain the flexibility of VBA Dictionary in instantly verifying values and seamlessly integrating missing ones  

This post will guide you through the use of VBA Dictionary to convert an initial inventory list, containing duplicates of items and their respective quantities, into a final report with no duplicates and the total quantity for each item

1 What is a VBA Dictionary and how it will be used  

VBA Dictionaries are structured in the same way as a normal word dictionary. As in any word dictionary there is the word (called Key in VBA) and the explanation associated with the word (called Iteam in VBA)

The Item in the VBA Dictionary can be any variable associated to the Key

One of the main benefits of VBA Dictionary is that you can immediately verify if a Key exists in the VBA Dictionary without having to loop through it as you would normally do with an array. Additionally a new Key can be quickly added to the Dictionary

The steps that will be followed to build the code are the following

1. A VBA Dictionary will be created 

2. The code will loop through the inventory list (reported in a sheet called  Stock) and verify if the item exists in the VBA Dictionary

   • If the item does not exists it will be added to the VBA Dictionary. The stock item (column A) is the VBA Dictionary Key and the quantity (Column B) is the VBA Dictionary Item 

   • If the item does exist it will add the new quantity to the VBA Dictionary Item of the existing Key

3. At the end of the loop it will copy the VBA Dictionary to the sheet called Output 

3 Code Walkthrough

The IF statement uses the Exists method of the VBA Dictionary. 

The line dict.Exists (custID) verifies if the dictionary has a Key which is equal to the custID variable taken from the inventory list in the Stock sheet. The dict.Exists method returns a boolean variable ( TRUE or FALSE) 

1. • If it does exists. The code line, dict (custID), refers to the Item belonging to the Key equal to custId, and adds the amount to the one already contained in the Item of the dictionary

   • If it does not exist the code line, dict.Add sustID, amount,  adds to the dictionary a new Key that will have the value equal to custID and the Item equal to amount 

Below the full code is reported 

Dim dict As New Scripting.Dictionary

Dim sh As Worksheet

Dim rg As Range

Dim i As Long

Dim outputSheet As Worksheet

Dim row As Long

    ' Set the worksheet for input data

    Set sh = ThisWorkbook.Worksheets("Stock")

    Set rg = sh.Range("A1").CurrentRegion

     ' Set the output worksheet

    Set outputSheet = ThisWorkbook.Worksheets("Output")

    row = 1 ' Initialize the row for output

    For i = 2 To rg.Rows.Count

        Dim custID As String

        Dim amount As Long

         ' Retrieve values from the range

        custID = rg.Cells(i, 1).Value

        amount = rg.Cells(i, 2).Value

        ' Check if the customer ID exists in the dictionary

        If dict.Exists(custID) Then

        ' Update items if necessary

        ' Update the value associated with the custID in the dictionary

                dict(custID) = amount + dict(custID)

           Else

            dict.Add custID, amount

        End If

    Next i

    ' Output data to the "Output" sheet

    Dim key As Variant

    For Each key In dict.Keys

        outputSheet.Cells(row, 1).Value = key ' Use key, not dict.key

        outputSheet.Cells(row, 2).Value = dict(key) ' Use dict(key), not dict.Item

        row = row + 1 ' Move to the next row

    Next key

End Sub