Easy generic print Dictionary to Excel Worksheet using VBA

Question

I'm working with really intricate data. Because of this I wrote this really nice function to print data to the debug area - the imediate window you can reach with Ctrl + G on the VBA, inside Excel. I need a similar function to print this generic data (that has numbers, strings, dictionarys and arrays) to a worksheet.

'call using: Call PrintDict(data)
' Where data can be a number, a string, a dictionary or an Array, 
' with any of these inside.

Sub PrintDict(ByVal dicttoprint As Variant, Optional indent As Integer = 0, Optional wasdict As Boolean = False)
    Dim i As Long
    Dim j As Long
    Dim indentStr As String
    indentStr = ""
    i = 0

    Do While i < indent
        indentStr = indentStr + " "
        i = i + 1
    Loop

    Dim key

    If (TypeName(dicttoprint) = "Dictionary") Then
        If (wasdict = True) Then
            Debug.Print vbNewLine;
        End If
        For Each key In dicttoprint.Keys:
            Debug.Print indentStr & key & " ";
            Call PrintDict(dicttoprint.Item(key), indent + 2, True)
        Next
    ElseIf (TypeName(dicttoprint) = "Variant()") Then
        If (wasdict = True) Then
            Debug.Print vbNewLine;
        End If
        For j = LBound(dicttoprint) To UBound(dicttoprint)
            Call PrintDict(dicttoprint(j), indent + 2)
        Next j
    Else
        Debug.Print indentStr & dicttoprint & " "
    End If
End Sub

Edit1: Ok, been thinking about, I have an idea, but can't solve some corner cases...

Example expected output below:

key1:____|__________|__________|__________|_________|
_________|key1.1:___|_numvalue_|__________|_________|
_________|__________|_numvalue_|__________|_________|
_________|__________|_arr1Indx1|_numvalue_|_________|
_________|__________|_arr1Indx2|_numvalue_|_________|
_________|__________|_arr1Indx3|_numvalue_|_________|
_________|key1.2:___|_numvalue_|__________|_________|
_________|__________|_numvalue_|__________|_________|
key2:____|_numvalue_|__________|__________|_________|
key3:____|__________|__________|__________|_________|
_________|_arr2Indx1|keyA.1:___|_numvalue_|_________|
_________|__________|keyA.2:___|_strvalue_|_________|
_________|_arr2Indx2|_numvalue_|__________|_________|

Ok, I think now this output solves some corner cases. Any ideas on how to implement it?

I'm thinking on having the function be able to pass X,Y parameters, that are optional and to return last Y. When working with text, the cursor naturally goes down, I don't know how to do this through recursion in a worksheet.

Edit 2:

Ok, this is pseudo code idea - is almost VBA, but I don't know how to make this work...

Function PrintToWS(ByVal data As Variant, _
    Optional rowi As Integer = 0, _
    Optional coli As Integer = 0) As Integer

    If (TypeName(data) = "Dictionary") Then
        For Each key In data.Keys:
            Cells(rowi, coli).Value = key
            coli = coli + PrintToWS(data.Item(key), rowi+1, coli)
        Next
    ElseIf (TypeName(data) = "Variant()") Then
        For j = LBound(data) To UBound(data)
            coli = coli + PrintToWS(data(j), rowi+1, coli)
        Next j           
    Else
        Cells(rowi, coli).Value = data
        coli = coli + 1
    End If

    PrintToWS = coli
End Function

Edit2:

Added it in a gist here

That's part of the problem, I'm not used to work with worksheets, I have no clue which output would be best. — eri0o, Jan 21 '15 at 18:45
I downvoted because it was unclear the output you were looking for. Since you have now shared, I removed the DV. — Chrismas007, Jan 21 '15 at 19:38
@Chrismas007 , how does one write a single value to a cell in excel? I've read Cells(row,column).Value = ValueToWrite on the internet, but this doesn't work! — eri0o, Jan 21 '15 at 20:41
what doesn't work about that? If the `ValueToWrite` is a valid string or number containing value then it should work. — Chrismas007, Jan 21 '15 at 20:42
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/69344/discussion-between-chrismas007-and-elric). — Chrismas007, Jan 21 '15 at 20:44
I've tryied it to do the function after, but got runtime error 1004. Sub test() Cells(0, 0).Value = "test" End Sub — eri0o, Jan 21 '15 at 20:46
You're closer than you think. Instead of printing out text line-by-line, build up an array row-by-row. Then output that to the worksheet by assigning the array to a range. See http://stackoverflow.com/questions/3992717/how-can-i-redim-preserve-a-2d-array-in-excel-2007-vba-so-that-i-can-add-rows/3993119#3993119 and http://stackoverflow.com/questions/8911497/reference-region-in-multidimensional-array/8964033#8964033 for more. You can probably reuse most of your working code. Then answer your own question with the result! — jtolle, Jan 22 '15 at 00:02

score 2 · Accepted Answer · edited Apr 26 '18 at 10:00

Solved. Code is below:

'usage: PrintToWS(yourdata)
' Optional parameters are to be used internally by the function, 
'leave optional parameters blank.

Function PrintToWS(ByVal data As Variant, _
    Optional rowi As Integer = 1, _
    Optional coli As Integer = 1, _
    Optional wasdict As Integer = 0) As Integer

    Dim key
    Dim j As Integer

    If (TypeName(data) = "Dictionary") Then
        For Each key In data.Keys:
            Cells(rowi + wasdict, coli).Value = key
            rowi = PrintToWS(data.Item(key), rowi + wasdict, coli + 1, 1)
            wasdict = 0
        Next
    ElseIf (TypeName(data) = "Variant()") Then
        For j = LBound(data) To UBound(data)
            rowi = PrintToWS(data(j), rowi, coli + 1)
        Next j
    Else
        Cells(rowi, coli).Value = data
        rowi = rowi + 1
    End If

    PrintToWS = rowi
End Function

I know this is old, but do you also have a code snipped for reading in the dict? Seems a little difficult because you don't know offhand how many levels there are — dv3, Oct 04 '17 at 09:54
@dv3 I don't understand what you mean with reading in the dict. I do have a code to print as text in the Excel console - yes, Excel has a console. — eri0o, Oct 05 '17 at 19:12

Easy generic print Dictionary to Excel Worksheet using VBA

1 Answers1