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 = data1Array(i) - data2Array(i)

   If diff < 0 Then
     diff = diff * -1
   End If

   average = diff + average

 Next i

 Sample = average/rows

End Function

Declare Data1Array and Data2Array as Variants (assignment of ranges to them won't work otherwise). — chuff
– chuff, Commented Apr 11, 2013 at 3:12
Thanks for correction, second error is gone. But first one is still there. Now I can access data in Data1Array but through two dimensions like this Data1Array(i,1). Can it be like this Data1Array(i) i.e. one dimension? — MrA2Z
– MrA2Z, Commented Apr 11, 2013 at 3:24
First-rather don't use rows as variable name- that is key word in Excel VBA... — Kazimierz Jawor
– Kazimierz Jawor, Commented Apr 11, 2013 at 3:25
Declare you array variables this way: Dim data1Array as Variant. Dimension of your array will be set automatically when line data1Array = Data1 is called. — Kazimierz Jawor
– Kazimierz Jawor, Commented Apr 11, 2013 at 3:28
@MrA2Z - yes. Application.Transpose can force a single row or single column 2D variant into 1D. See below — brettdj
– brettdj, Commented Apr 11, 2013 at 3:33

brettdj · Accepted Answer · 2013-04-11 03:29:53Z

7

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

answered Apr 11, 2013 at 3:29

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bat_Programmer Over a year ago

How do I create function accepting an array of ranges?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related