Excel VBA function: How to pass range, convert to array, reverse, and return array

Question

I am trying to do some array math in Excel which requires me to reverse a number of 1-dimensional ranges a good amount of times, so I want to write a function for it, rather than create reverses in the spreadsheet.

I have written a reverse() function in VBA but it returns #VALUE! errors in the spreadsheet. This happens no matter array size, nor whether inputting a same size array function or enclosing with a summary function like SUM(). I verified that the reversing logic works as a Sub. This leads me to believe the issue is with passing/returning the range/array but I don't understand what is wrong.

Function reverse(x As Range) As Variant()
' Array formula that reverses a one-dimensional array (1 row, x columns)
    Dim oldArray() As Variant, newArray() As Variant
    Dim rows As Long: i = x.rows.Count
    Dim cols  As Long: i = x.Columns.Count
    ReDim oldArray(1 To rows, 1 To cols), newArray(1 To rows, 1 To cols)

    oldArray = x.Value
    newArray = oldArray

    For i = 1 To cols / 2 Step 1
        newArray(1, i) = oldArray(1, cols - i + 1)
        newArray(1, cols - i + 1) = oldArray(1, i)
    Next

    reverse = newArray
End Function

Keep in mind, I may extend it to reverse 2 dimensional arrays, but that's the trivial part. My question is just trying to ensure the function works on a (1, N) range.

Thanks!

`i = x.rows.Count` and `i = x.Columns.Count` both seem pointless. Those values of `i` are never used. What is that supposed to do? Perhaps it is the variables `rows` and `cols` (rather than `i`) that you meant to initialize. As it is, neither `rows` nor `cols` are ever moved from their default value of `0`. — John Coleman, Oct 11 '16 at 04:19
Wow! What an oversight on my part. That works now. Thanks John. — S. Puri, Oct 12 '16 at 04:32

score 2 · Answer 1 · edited Oct 11 '16 at 06:29

Find below code....

Function reverse(x As Range) As Variant()
' Array formula that reverses a one-dimensional array (1 row, x columns)
    Dim oldArray() As Variant, newArray() As Variant
    Dim rows As Long
    rows = x.rows.Count
    Dim cols  As Long
    cols = x.Columns.Count
    ReDim oldArray(1 To rows, 1 To cols), newArray(1 To rows, 1 To cols)

    oldArray = x.Value
    newArray = oldArray

    For i = 1 To cols / 2 Step 1
        newArray(1, i) = oldArray(1, cols - i + 1)
        newArray(1, cols - i + 1) = oldArray(1, i)
    Next
reverse = newArray
End Function

Again, thanks I now see my error in setting i when it should be rows/cols. Very embarrassing — S. Puri, Oct 12 '16 at 04:36

score 1 · Answer 2 · answered Oct 11 '16 at 08:19

The following code is more versatile, it use optional arguments to determine if rows, columns or both (or none) should be reversed. By default it will reverse columns.

Function ReverseRange(Source As Range, Optional ReverseRows As Boolean = False, Optional ReverseColumns As Boolean = True) As Variant()
    Dim SourceArray() As Variant
    Dim DestArray() As Variant
    SourceArray = Source.value

    Dim nRows As Long, nColumns As Long
    nRows = UBound(SourceArray, 1)
    nColumns = UBound(SourceArray, 2)
    ReDim DestArray(1 To nRows, 1 To nColumns)

    Dim r As Long, r2 As Long, c As Long, c2 As Long
    For r = 1 To nRows
        r2 = IIf(ReverseRows, nRows - r + 1, r)
        For c = 1 To nColumns
            c2 = IIf(ReverseColumns, nColumns - c + 1, c)
            DestArray(r2, c2) = SourceArray(r, c)
        Next c
    Next r
    ReverseRange = DestArray
End Function

Note that there is no verification on the range validity.

Thanks, this doesn't obviously help me catch my error but does save time in extending the function to 2-dim arrays. — S. Puri, Oct 12 '16 at 04:38
I'm not sure why, but neither yours or mine work if the range passed is of length = 1. This is a trivial case but shouldn't it return the same value back? — S. Puri, Oct 19 '16 at 03:00
As mentioned, my code does not make verifications on the range validity. If the range is only one cell, `source.Value` does not return an array, and you will get Error 13 "Type mismatch". The code may also fail if the range is not contiguous, and maybe in other cases. — Vincent G, Oct 19 '16 at 06:55

score 0 · Answer 3 · answered Oct 11 '16 at 06:33

This will reverse the columns in the range no matter the row count.

Function reverse(Source As Range) As Variant()
    Dim Data, RevData
    Dim x As Long, y As Long, y1 As Long

    Data = Source.Value

    ReDim RevData(1 to UBound(Data, 1),1 to UBound(Data, 2))

    For x = 1 To UBound(Data, 1)
        y1 = UBound(Data, 2)
        For y = 1 To UBound(Data, 2)
            RevData(x, y1) = Data(x, y)
            y1 = y1 - 1
        Next
    Next
    reverse = RevData
End Function

Excel VBA function: How to pass range, convert to array, reverse, and return array

3 Answers3

Linked