vba 数组到 Excel 范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24685159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Array to Excel Range
提问by hollige1
I'm new to VBA and trying to write an array to an excel range through an array UDF.
我是 VBA 新手，并试图通过数组 UDF 将数组写入 excel 范围。
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.
我正在尝试将数组输出到放置公式的最大行数。如果这很重要，我正在为字典使用 Microsoft 脚本库。使用 Excel 中的数组公式 (CTRL+Shift+Enter)，如何将数组大小调整为放置公式的范围，然后将数组放入单元格中？我希望单元格上的公式为 =test("G1:J20") 并且公式将放置在单元格 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
回答by ja72
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
回答by Alter
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
