title: Easy generic print Dictionary to Excel Worksheet using VBA
body: <p>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.</p>
  
  <pre><code>'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 &lt; 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 &amp; key &amp; " ";
              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 &amp; dicttoprint &amp; " "
      End If
  End Sub
  </code></pre>
  
  <p>Edit1:
  Ok, been thinking about, I have an idea, but can't solve some corner cases...</p>
  
  <p>Example expected output below:</p>
  
  <pre><code>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_|__________|_________|
  </code></pre>
  
  <p>Ok, I think now this output solves some corner cases. Any ideas on how to implement it?</p>
  
  <p>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.</p>
  
  <p>Edit 2:</p>
  
  <p>Ok, this is pseudo code idea - is almost VBA, but I don't know how to make this work... </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Edit2:</p>
  
  <p><a href="https://gist.github.com/ericoporto/e18bc0ae8432afcd673d634e70c940ae" rel="nofollow noreferrer">Added it in a gist here</a> </p>