You can also return a Variant()
which represents an array of values. Here is an example for a function that reverses values from a range into a new range:
Public Function ReverseValues(ByRef r_values As Range) As Variant()
Dim i As Integer, j As Integer, N As Integer, M As Integer
Dim y() As Variant
N = r_values.Rows.Count
M = r_values.Columns.Count
y = r_values.value 'copy values from sheet into an array
'y now is a Variant(1 to N, 1 to M)
For i = 1 To N / 2
For j = 1 To M
Swap y(i, j), y(N - i + 1, j)
Next j
Next i
ReverseValues = y
End Function
In the worksheet you have to apply this function as an array formula (with Ctrl
-Shift
-Enter
) with an appropriate number of cells selected. The details of the Swap() function are not important here.
Note that for many rows, this is very efficient. Doing the x = Range.Value
and Range.Value = x
operations when x
is an array and the range contains multiple rows columns is many times faster than doing the operations one by one directly on the cells.