How to constract an array UDF?

[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

 

[Rick Rothstein]

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".

 

[Faraz A. Qureshi]

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

 

[Dougaj4]

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 -

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