数组到Excel范围 [英] Array to Excel Range

问题描述

我是VBA的新手，试图通过数组UDF将数组写入excel范围。

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

我试图将数组输出到最大数量该公式被放置的行。我正在使用Microsoft脚本库的字典，如果这是重要的。
使用Excel中的数组公式（CTRL + Shift + Enter），如何将我的数组调整到公式所在的范围，然后将数组放在单元格中？
我希望单元格上的公式为= test（G1：J20），公式将放在单元格A1：B20中。

I am 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.

代码：
函数测试（ByVal inputRange As Range）As Variant
Dim Cell As Variant
Dim D As Dictionary
Dim Arr（）As Variant
Dim i As Long
设置D =新词典

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

解决方案

要将数组读取和写入单元格，您需要一个2D数组。例如：

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

或只是读取值

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

这篇关于数组到Excel范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！