Passing a range to Excel User Defined Function and assigning it to an array

Question

I am trying to

1. pass two ranges - multiple rows single column - to a user defined function in Excel 2007,
2. then assign it to an array for processing.

Can anybody tell me how to assign such range to an array?

The range is not constant as I am using an UDF in different cells for different data so I cannot use e,g, Range("A1:A10")

The code is working when I just use Data1.Rows.Cells(i, 1) instead of arrays. But I think it is better to use one dimensional arrays for efficiency.

Here is my current 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 < 0 Then
     diff = diff * -1
   End If

   average = diff + average

 Next i

 Sample = average/rows

End Function

Answer 1

Something like this to work with 1D ranges which includes testing for

1. unequal ranges
2. single cell ranges (can't use variants)

sample sub

Sub Test()
MsgBox Sample([a1:a3], [a5:a7])
End Sub

function

 Function Sample(Data1 As Range, Data2 As Range)

 Dim X
 Dim Y
 Dim lngCnt As Long
 Dim dbDiff As Double
 Dim dbAvg As Double

 If Data1.rows.Count <> Data2.rows.Count Then
 Sample = "Different range sizes"
 Exit Function
 ElseIf Data1.rows.Count = 1 Then
 Sample = "Single cell range"
 Exit Function
 End If

 X = Application.Transpose(Data1)
 Y = Application.Transpose(Data2)

 For lngCnt = 1 To UBound(X)
   dbDiff = X(lngCnt) - Y(lngCnt)
   If dbDiff < 0 Then
     dbDiff = dbDiff * -1
   End If
   dbAvg = dbDiff + dbAvg
 Next

 Sample = dbAvg / lngCnt

End Function