Reading/Writing Dictionary Data

On This Page

This section introduces formatted output of dictionary data, as well as reading/writing data between Excel worksheets and dictionaries.

Formatted Output of Dictionary Data

[Excel VBA]

There is nothing special about outputting dictionary data in Excel VBA; where formatted output is needed, the Format function can be used.

First, create a dictionary object and add key-value pairs, then output the value corresponding to each key in the Immediate Window. Among them, student scores are formatted to two decimal places. The sample file path is: Samples\ch08\Excel VBA\Dictionary Data Read and Write.xlsm.

code.vba 
Sub Test()
    Dim dicT As Scripting.Dictionary
    Set dicT = New Scripting.Dictionary
    dicT.Add "name", "Zhang San"
    dicT.Add "sex", "Male"
    dicT.Add "score", 92
    Debug.Print "Name: " & dicT("name")
    Debug.Print "Gender: " & dicT("sex")
    Debug.Print "Score: " & Format(dicT("score"), "0.00")  ' Formatted output
End Sub

Running the procedure outputs dictionary data in the Immediate Window:

code.vba 
Name: Zhang San
Gender: Male
Score: 92.00

[Python]

When using the print function to output dictionary data, you can use the format function to specify the output format. First, create a dictionary:

code.python 
>>> student = {'name':'Zhang San','sex':'Male','score':92}

Use {} as placeholders; inside the parentheses you can add numbers starting from 0, or omit numbers. Dictionary data is passed as arguments to the format function.

code.python 
>>> print('Name: {0}, Gender: {1}, Score: {2}'.format(student['name'], student['sex'], student['score']))
Name: Zhang San, Gender: Male, Score: 92

Use {} placeholders, specify parameter names inside the parentheses, and pass corresponding named parameters to format:

code.python 
>>> print('Name: {name}, Gender: {sex}, Score: {score}'.format(name=student['name'], sex=student['sex'], score=student['score']))
Name: Zhang San, Gender: Male, Score: 92

Use {} placeholders, enter the key name inside, and pass the dictionary as argument to format; note to add ** before the dictionary name:

code.python 
>>> print('Name: {name}, Gender: {sex}, Score: {score}'.format(**student))
Name: Zhang San, Gender: Male, Score: 92

Use {} placeholders, input dictionary index form, dictionary name can be replaced by 0. Pass dictionary to format:

code.python 
>>> print('{0[name]}:{0[sex]},{0[score]}'.format(student))
Zhang San:Male,92

Reading/Writing Data Between Excel Worksheets and Dictionaries

Reading/writing between Excel worksheets and dictionaries includes two aspects: reading Excel worksheet data into a dictionary, and writing dictionary data into an Excel worksheet.

The following shows the Excel worksheet data in Figure 8-4. Read the data in cell ranges A1:B2 and A4:B5 and save them into a dictionary, using A and B as keys and 1 and 2 as values.

[Excel VBA]

The code below reads data from range A1:B2 in Figure 8-4 into a dictionary, using A and B as keys and 1 and 2 as values. Sample file path: Samples\ch08\Excel VBA\Dictionary Data Read and Write.xlsm.

code.vba 
Sub Test()
    Dim arr01()   ' Save data from range A1:A2, 2D array
    Dim arr02()   ' Save data from range B1:B2, 2D array
    Dim arr1()   ' Convert arr01() to 1D array
    Dim arr2()   ' Convert arr02() to 1D array
    Dim intI As Integer
    Dim intR As Integer   ' Size of 1D array
    Dim dicT As Scripting.Dictionary
    Set dicT = New Scripting.Dictionary   ' Create dictionary object
    arr01 = Range("A1:A2")   ' Get data
    arr02 = Range("B1:B2")
    arr1 = Application.WorksheetFunction.Transpose(arr01)  ' Convert to 1D array
    arr2 = Application.WorksheetFunction.Transpose(arr02)
    intR = UBound(arr1)
    For intI = 1 To intR   ' Add key-value pairs to dictionary
        dicT.Add arr1(intI), arr2(intI)
        Debug.Print arr1(intI) & vbTab & arr2(intI)   ' Output
    Next
End Sub

Running the procedure outputs in Immediate Window:

code.vba 
A   1
B   2

The next code reads range A4:B5 into a dictionary, using A and B as keys and 1 and 2 as values. Note: because both keys and values come from row data in the range, we need to transpose twice when converting 2D to 1D. First transpose row data to column data, second transpose 2D to 1D. Sample file path: Samples\ch08\Excel VBA\Dictionary Data Read and Write.xlsm.

code.vba 
Sub Test2()
    Dim arr01()   ' Save data from A4:B4, 2D array
    Dim arr02()   ' Save data from A5:B5, 2D array
    Dim arr1(), arr2()   ' First transpose result, 2D array
    Dim arr3(), arr4()   ' Result after converting 2D to 1D, 1D array
    Dim intI As Integer
    Dim intR As Integer
    Dim dicT As Scripting.Dictionary
    Set dicT = New Scripting.Dictionary
    arr01 = Range("A4:B4")   ' Get data
    arr02 = Range("A5:B5")
    arr1 = Application.WorksheetFunction.Transpose(arr01)   ' Row to column
    arr2 = Application.WorksheetFunction.Transpose(arr02)
    arr3 = Application.WorksheetFunction.Transpose(arr1)   ' Column to 1D
    arr4 = Application.WorksheetFunction.Transpose(arr2)
    intR = UBound(arr3)
    For intI = 1 To intR
        dicT.Add arr3(intI), arr4(intI)
        Debug.Print arr3(intI) & vbTab & arr4(intI)
    Next
End Sub

Running the procedure outputs:

code.vba 
A   1
B   2

[Python]

In Python, using the dictionary converter provided by the xlwings package, you can easily read Excel worksheet range data into a dictionary. Data in A4:B5 is row-oriented; use the transpose parameter set to True.

code.python 
>>> class="text-secondary">import xlwings class="text-secondary">as xw
>>> bk = xw.Book()
>>> sht = xw.sheets.active
>>> sht.range('A1:B2').options(dict).value
{'A': 1.0, 'B': 2.0}
>>> sht.range('A4:B5').options(dict, transpose=True).value
{'A': 1.0, 'B': 2.0}

Now implement the reverse: given a dictionary, write data into ranges A1:B2 and A4:B5 as shown in Figure 8-4. Assume dictionary keys are A and B, values are 1 and 2.

[Excel VBA]

Create dictionary object and add key-value pairs. Use Keys and Items methods to get all keys and values as 1D arrays. Note: to write 1D array into a single column range, first convert to 2D using Transpose; to write into a single row, transpose twice (first 1D to 2D column, then 2D column to 2D row).

Case 1: Write dictionary data into A1:B2, A and B as column data.

code.vba 
Sub Test3()
    Dim arr1(), arr2()
    Dim intI As Integer
    Dim intR As Integer
    Dim dicT As Scripting.Dictionary
    Set dicT = New Scripting.Dictionary
    dicT.Add "A", 1
    dicT.Add "B", 2
    intR = dicT.Count
    Range("A1").Resize(intR, 1) = Application.WorksheetFunction.Transpose(dicT.Keys)   ' Keys
    Range("B1").Resize(intR, 1) = Application.WorksheetFunction.Transpose(dicT.Items)  ' Values
End Sub

Case 2: Write into A4:B5, A and B as row data.

code.vba 
Sub Test4()
    Dim arr1(), arr2()
    Dim intI As Integer
    Dim intR As Integer
    Dim dicT As Scripting.Dictionary
    Set dicT = New Scripting.Dictionary
    dicT.Add "A", 1
    dicT.Add "B", 2
    intR = dicT.Count
    Range("A4").Resize(1, intR) = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(dicT.Keys))   ' Write keys
    Range("B4").Resize(1, intR) = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(dicT.Items))   ' Write values
End Sub

Running the program writes dictionary data into worksheet as in Figure 8-4.

[Python]

In Python, using xlwings dictionary converter:

code.python 
>>> class="text-secondary">import xlwings class="text-secondary">as xw
>>> bk = xw.Book()
>>> sht = xw.sheets.active
>>> dic = {'a': 1.0, 'b': 2.0}
>>> sht.range('A1:B2').options(dict).value = dic
>>> sht.range('A4:B5').options(dict, transpose=True).value = dic