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

I am trying to

- pass two ranges –
*multiple rows single column*– to a user defined function in Excel 2007, - 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
```

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

- unequal ranges
- 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
```