How to constract an array UDF?

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

Faraz A. Qureshi

Sure would oblige if one would kindly provide the CODE for developing a UDF
for simple formula of reversing the column values like the following:

=OFFSET($A$1,ROWS($A$1:$A$20)-ROWS($B$1:B1),0)

Thus, if a UDF like the following, is entered in an array form:
{=REVCOL(A1:A20)}
on the range B1:B20, the B1:B20 would represent the data of A1:A20 but in
the reverse order.

Thanx in advance,
Best Regards,

Faraz
 
I'm not sure why you would want a UDF to do what you already have a relatively compact formula doing, but here it is...

Function REVCOL(R As Range) As Variant
Dim CellValues As Variant, CallerRow As Long
CellValues = WorksheetFunction.Transpose(R)
CallerRow = Application.Caller.Row
REVCOL = CellValues(R.Count + R(1).Row - CallerRow)
End Function

Note that this is **not** an array formula... just commit it with the Enter key; and don't forget to make the range argument for it with absolute cell references so that when you copy the function down, the cell address doesn't "drift".
 
That's the main reason bro!
What I need is to have some knowledge of how to create an array formula?
Simply a construction model not necessarily for Reversing prpose.
It was only an example.
Please c if u can help me in giving some other example of your own as to how
to construct an array formula?

Thanx again!
Looking 4ward 4 your guidance.

Best Regards,

Faraz
 
That's the main reason bro!
What I need is to have some knowledge of how to create an array formula?
Simply a construction model not necessarily for Reversing prpose.
It was only an example.
Please c if u can help me in giving some other example of your own as to how
to construct an array formula?

Thanx again!
Looking 4ward 4 your guidance.

Best Regards,

Faraz








- Show quoted text -
Click to expand...

Here's a UDF using an array formula:

Function RevCol(ReverseA As Variant) As Variant
Dim NumRows As Long, TempA() As Double, i As Long

If TypeName(ReverseA) = "Range" Then ReverseA = ReverseA.Value2
NumRows = UBound(ReverseA)
ReDim TempA(1 To NumRows, 1 To 1)

For i = 1 To NumRows
TempA(i, 1) = ReverseA(NumRows - i + 1, 1)
Next i
RevCol = TempA

End Function

So the main things to remember are:

Declare the range you are operating on and the function as variants
Convert the variant/range into an array with arrayname =
arrayname.value2 (the "If typename = "Range"" bit allows the function
to be used from another VBA routine, passing an array, rather than a
range).
Do what you want to the array
Assign the array at the functions return value.
Enter with Ctrl-Shift-Enter

This sort of array UDF will usually be much faster than a UDF working
on range objects.

These blog articles might help:
http://newtonexcelbach.wordpress.com/2008/03/04/ranges-and-arrays/
http://newtonexcelbach.wordpress.com/2008/03/05/ranges-and-arrays-2/

By the way, for the non-VBA solution I'd use:
=INDEX($A$2:$A$21,ROW($A$21)-ROW(A1))
(for data in A2:A21)

Doug
 
Back
Top