title: Converting variant into array in VBA
body: <p>I am trying to use the Excel built-in function SumProduct in VBA but keep getting errors. The code snippet looks as follows</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>However, this code returns <em>#Value</em>, and I guess it's because X is Variant and Y is of type Double (so type-mismatch).</p>
  
  <p>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.</p>
  
  <p>Any suggestions?</p>
  
  <p>Thanks</p>