title: Array to Excel Range
body: <p>I'm new to VBA and trying to write an array to an excel range through an array UDF.</p>
  
  <p>I'm trying to output the array to a maximum number of rows that the formula was placed in. I am using the Microsoft Scripting Library for the dictionary, if that matters.
    With an array formula in excel (CTRL+Shift+Enter), how do I resize my array to the range that the formula was placed in and then place the array in the cells?
    I would like the formula on the cells to be =test("G1:J20") and the formula will be placed in the cells A1:B20.</p>
  
  <pre><code>Code:
      Function test(ByVal inputRange As Range) As Variant
      Dim Cell As Variant
      Dim D As Dictionary
      Dim Arr() As Variant
      Dim i As Long
      Set D = New Dictionary
  
  ' Remove duplicates
  For Each Cell In inputRange
      If D.Exists(CStr(Cell.Value)) = False Then
          D.Add CStr(Cell.Value), 1
      Else
          D.Exists (Cell.Value)
          D.Item(Cell.Value) = D.Item(Cell.Value) + 1
     End If
  Next
  D.Remove vbNullString
  
  Redim Arr(0 To Application.Max(D.Count, Application.Caller.Cells.Count))
  
  'Fill the array with the keys from the Dictionary
  For i = 0 To D.Count - 1
      Arr(i) = D.Keys(i)
  Next i
  
  test = Application.Transpose(Arr)
  End Function
  </code></pre>