vba 在VBA中将变量转换为数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16609353/
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
Converting variant into array in VBA
提问by gussilago
I am trying to use the Excel built-in function SumProduct in VBA but keep getting errors. The code snippet looks as follows
我正在尝试在 VBA 中使用 Excel 内置函数 SumProduct 但不断出现错误。代码片段如下所示
Dim X As Variant
'x is input value, given as Range
X = x.Value
Dim Y() As Double
ReDim Y(1 To N)
'filling Y with whatever values
Dim i As Long
For i = 1 To UBound(Y)
Y(i) = 2
next i
Result = WorksheetFunction.SumProduct(X,Y)
However, this code returns #Value, and I guess it's because X is Variant and Y is of type Double (so type-mismatch).
但是，此代码返回#Value，我想这是因为 X 是 Variant 而 Y 是 Double 类型（因此类型不匹配）。
Is there a way to convert this variant into double (array)? I have tried declaring X as Double instead, and then looping through the input-range itself, but don't know how to "access" each element in the input-range.
有没有办法将此变体转换为双精度（数组）？我尝试将 X 声明为 Double ，然后循环遍历输入范围本身，但不知道如何“访问”输入范围中的每个元素。
Any suggestions?
有什么建议？
Thanks
谢谢
采纳答案by Bathsheba
Y will need to be a 2D variant array. (I suggest you construct Y in the required form directly.) Try this:
Y 将需要是一个 2D 变体数组。（我建议你直接以所需的形式构造 Y。）试试这个：
Function Result()
Dim X As Variant
'rng is input value, given as Range. You can't have x and X in VBA due to case-insensitivity
X = rng.Value
N = UBound(X, 1) - LBound(X, 1) + 1 'Should really declare N before using it
Dim Y As Variant 'Changed type here
ReDim Y(1 To N, 1 To 1) 'We need a 2D variant
'filling Y with whatever values
Dim i As Long
For i = 1 To UBound(Y)
Y(i, 1) = 2
Next i
Result = WorksheetFunction.SumProduct(X, Y)
End Function
