In this post I will demonstrate how to use Dictionaries in Excel VBA in order to calculate, store and use data summarized on different criteria.
In the real life I used the algorithm from this post to automate the issue of inter-company invoices. This automation saved dozens of hours considering that before they had it, users where issuing each invoice manually.
In this post I will present a simplified version of the file, just to demonstrate the usage of a dictionary that has arrays with multiple fields as items.
Let’s first explain the problem:
- We have raw data as in the below image. For each row that has the same Vendor and Customer we must summarize the amounts and store somewhere all the data related to the invoice: total invoiced amount, customer, vendor, invoice number, description etc.
- After we have stored in a temporary location from the memory the summarized data for all invoices, we must automatically create separate files with each invoice, like in the sheet “Invoice”, in the same folder with the “Main file” (the file with the raw data). The names of the created files should be: CustomerVendorInvoiceNumber:
- Example with the file for invoice 3003 with amount: 29,597.25 – 14,918.97 + 3,904.53= 18,582.81
- The file contains 3 sheets: one with the parameters, one with the raw data and one with the “invoice template” (you can download sample file at the end of this post)
*I like to use a worksheet with all parameters because in this way it is easier to make modifications if some column names or field names are changed. I don’t have to modify in the VBA code and the users can change themselves the values in the Parameters worksheet if they know what they are doing 🙂
- The VBA code that uses Dictionary of arrays to calculate the amounts and then creates files with each invoices is the following:
Sub generate_invoices() Dim compdict As Object Set compdict = CreateObject("scripting.dictionary") Dim aitems(1 To 6) As Variant Dim tempArray As Variant ReDim tempArray(1 To 5) Dim sheetsArray() As String Dim wkb As Workbook filepath = Application.ThisWorkbook.Path Set ws = ThisWorkbook.Sheets("DATA") lr = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row Set prm = ThisWorkbook.Sheets("Parameters") 'parameters custcol = prm.Range("B2").Value amountcol = prm.Range("B3").Value vendorcol = prm.Range("B4").Value invcol = prm.Range("B5").Value taxcol = prm.Range("B6").Value textcol = prm.Range("B7").Value invShtName = prm.Range("B8").Value invCust = prm.Range("B9").Value invAmount = prm.Range("B10").Value invVendor = prm.Range("B11").Value invInv = prm.Range("B12").Value invTax = prm.Range("B13").Value invText = prm.Range("B14").Value iSheets = prm.Range("B15").Text Set invSht = ThisWorkbook.Sheets(invShtName) sheetsArray = Split(iSheets, ",") vrow = 0 'insert unique values to dictionary For i = 2 To lr 'the key of dict is Customer&Vendor&InvNo combination dkey = ws.Range(custcol & i).Value & ws.Range(vendorcol & i).Value & ws.Range(invcol & i).Value cust = ws.Range(custcol & i).Value amount = ws.Range(amountcol & i).Value vendor = ws.Range(vendorcol & i).Value invNo = ws.Range(invcol & i).Value taxCode = ws.Range(taxcol & i).Value desc = ws.Range(textcol & i).Value If compdict.exists(dkey) Then 'if key exists, modify only the amount tempArray = compdict.Item(dkey) tempArray(1) = tempArray(1) + amount compdict.Item(dkey) = tempArray Erase tempArray Else 'if key does not exist, create it and add all rest of data as an array to dictionary item aitems(1) = amount 'amount aitems(2) = vendor 'vendor aitems(3) = invNo 'invoice no aitems(4) = taxCode 'tax code aitems(5) = desc 'text aitems(6) = cust 'customer compdict.Add dkey, aitems() Erase aitems() End If Next i 'loop in dict and for each key input values in sheeet Invoice and save 'the invoice as new file in the same path with the master file: Application.StatusBar = "Please wait, processing invoices.." Application.ScreenUpdating = False For Each Key In compdict.keys() invSht.Range(invCust) = compdict(Key)(6) 'customer invSht.Range(invAmount) = compdict(Key)(1) 'amount invSht.Range(invVendor) = compdict(Key)(2) 'vendor invSht.Range(invInv) = compdict(Key)(3) 'invoice no invSht.Range(invTax) = compdict(Key)(4) 'tax code invSht.Range(invText) = compdict(Key)(5) 'text 'add new workbook Set wkb = Workbooks.Add 'copy sheets with each invoice data in the new workbook For Each sht In sheetsArray ThisWorkbook.Sheets(sht).Copy Before:=wkb.Sheets(1) wkb.ActiveSheet.UsedRange.Copy wkb.ActiveSheet.UsedRange.PasteSpecial xlPasteValues, _ Operation:=xlNone, SkipBlanks:=True, Transpose:=False Next sht 'create file name and save workbook 'the file name is combination of Customer&Vendor&InvoiceNumber file_name = CStr(Key) & ".xlsx" wkb.SaveAs filepath & "\" & file_name wkb.Close Next Key Application.ScreenUpdating = True Application.StatusBar = False MsgBox "Completed !", vbInformation Set dict = Nothing Erase sheetsArray() End Sub
- You can download the sample file and play with it from THIS LINK Please note that in order to generate the files, you need to save the downloaded file in a folder on your PC as the invoices are generated in the same folder.