Converting variant into array in VBA

Question

I am trying to use the Excel built-in function SumProduct in VBA but keep getting errors. The code snippet looks as follows

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).

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.

Any suggestions?

Thanks

Bathsheba Bathsheba · Accepted Answer · 2013-05-17T12:48:31

Y will need to be a 2D variant array. (I suggest you construct Y in the required form directly.) Try this:

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

Converting variant into array in VBA

1 Answers