title: Passing a range to Excel User Defined Function and assigning it to an array
body: <p>I am trying to </p>
  
  <ol>
  <li>pass two ranges - <em>multiple rows single column</em> - to a user defined function in Excel 2007,</li>
  <li>then assign it to an array for processing. </li>
  </ol>
  
  <p>Can anybody tell me how to assign such range to an array?</p>
  
  <p>The range is not constant as I am using an UDF in different cells for different data so I cannot use e,g, <code>Range("A1:A10")</code></p>
  
  <p>The code is working when I just use <code>Data1.Rows.Cells(i, 1)</code> instead of arrays. But I think it is better to use one dimensional arrays for efficiency.</p>
  
  <p>Here is my current code</p>
  
  <pre><code>Function Sample(Data1 As Range, Data2 As Range) As Double
  
   'Size of Data1 and Data2
   Dim rows As Integer
   rows = Data1.Rows.Count 
  
   'Declaring two one dimensional arrays
   Dim data1Array(rows) As Double --- Getting error here
   Dim data2Array(rows) As Double --- Getting error here
  
   Dim diff As Double
   Dim average As Double
   Dim i As Integer
  
   'Assigning Range to Array
   data1Array = Data1 --- Getting Error here
   data2Array = Data2 --- Getting Error here
  
   average = 0
   diff = 0
  
   For i = 1 To rows
  
     diff = data1Array(i) - data2Array(i)
  
     If diff &lt; 0 Then
       diff = diff * -1
     End If
  
     average = diff + average
  
   Next i
  
   Sample = average/rows
  
  End Function
  </code></pre>