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

What is N? Presumably the number of rows in X?

Bathsheba
– Bathsheba

2013-05-17 12:45:35 +00:00
Commented May 17, 2013 at 12:45 — Bathsheba
– Bathsheba, Commented May 17, 2013 at 12:45

Bathsheba · Accepted Answer · 2013-05-17 12:48:31Z

2

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

answered May 17, 2013 at 12:48

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting variant into array in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related