Array to Excel Range

0
votes

I'm new to VBA and trying to write an array to an excel range through an array UDF.

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.

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
2
vbaexcel
If you ReDim an array without using Preserve then you lose the contents, but in any case you can only ReDim the last dimension of a 2-D array... Try calling your function from a test Sub to more easily debug it. – Tim Williams

2 Answers

1
votes

To read and write arrays to cells you need a 2D array. For example:

Dim data() as Variant, N as Integer, M as Integer
' Say you want a 100×50 array
N = 100 : M = 50
ReDim data(1 to N, 1 to M)
' Fill data()
Range("A1").Resize(N,M).Value = data

Or to just read values

Dim data() as Variant, N as Integer, M as Integer, i as Integer, j as Integer
data = Range("A1:AX100").Value
N = UBOUND(data,1) : M = UBOUND(data,2)
For i = 1 to N
    For j = 1 to M
        Debug.Print(data(i,j))
    Next j
Next i
0
votes

Here is a method to put an array into a worksheet range, is this what you meant?

Sub test()
    Dim v(0 To 2, 0 To 2) As Variant
    Dim r As Range
    'fill the array with values
    populate v
    'range must be same dimensions as array, in this case 3x3
    Set r = ActiveSheet.Range("A1:C3")
    'this simply puts array into range values
    r.Value2 = v
End Sub

Function populate(v As Variant)
    For i = 0 To 2
        For j = 0 To 2
            v(j, i) = i * j * j - i + 2
        Next j
    Next i
End Function

However, since you're already looping through the dictionary for the values, why not just write the values directly to the worksheet? You can mimic the transpose by swapping your row and column indices

Sub test()
    Dim dict As Dictionary
    Set dict = New Dictionary
    'fill dictionary with values
    populate dict
    'loop through dictionary, and add items to worksheet
    For i = 0 To dict.Count - 1
        ActiveSheet.Cells(1, i + 1).Value = dict.Keys(i)
        ActiveSheet.Cells(2, i + 1).Value = dict.Items(i)
    Next i
End Sub

Function populate(dict As Dictionary)
    dict.Add "help", "me"
    dict.Add "I'm", "lost"
    dict.Add "everything", "1"
    dict.Add "or", "0"
End Function