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

Question

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 =…

Accepted Answer

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

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

Solution

Problem