在VBA中将变量转换为数组

Question

我试图在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)

然而，这段代码返回#Value，我猜这是因为X是Variant而Y是Double类型(所以类型不匹配)。

是否有方法将此变体转换为double (array)?我尝试过将X声明为Double，然后循环遍历输入范围本身，但不知道如何"访问"输入范围中的每个元素。

有什么建议吗?

谢谢

Answer 1

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