Friday, 4 May 2018

VBA - Writing Dictionaries to Worksheet Cells

I love Dictionaries but I ought to post more samples as to how wonderfully versatile they are. Here is a frequently asked question about how to write contents of dictionary to a block of cells.

Option Explicit

Sub FAQDictionariesAndWorksheetCells()

    '* how to populate a dictionary and then write keys of dictionary to sheet

    Dim dic As Scripting.Dictionary     '* Tools->References: Microsoft Scripting Runtime
    Set dic = New Scripting.Dictionary
    
    dic.Add "Red", 128
    dic.Add "Blue", 224
    dic.Add "Green", 255
    
    '* as columns
    Sheet1.Cells(1, 1).Resize(dic.Count, 1).Value2 = Application.Transpose(dic.Keys)
    Sheet1.Cells(1, 2).Resize(dic.Count, 1).Value2 = Application.Transpose(dic.Items)
        
    '* as rows
    Sheet1.Cells(1, 4).Resize(1, dic.Count).Value2 = dic.Keys
    Sheet1.Cells(2, 4).Resize(1, dic.Count).Value2 = dic.Items

End Sub

No comments:

Post a Comment