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
– John Coleman, Commented Oct 11, 2016 at 4:19
Wow! What an oversight on my part. That works now. Thanks John. — S. Puri
– S. Puri, Commented Oct 12, 2016 at 4:32

Aditya Pansare · Accepted Answer · 2016-10-11 06:29:04Z

2

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

edited Oct 11, 2016 at 6:29

user6432984

answered Oct 11, 2016 at 6:16

Aditya Pansare

1,1329 silver badges14 bronze badges

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

1 Comment

S. Puri Over a year ago

Again, thanks I now see my error in setting i when it should be rows/cols. Very embarrassing

Vincent G · Accepted Answer · 2016-10-11 08:19:39Z

1

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.

answered Oct 11, 2016 at 8:19

Vincent G

3,1981 gold badge16 silver badges31 bronze badges

3 Comments

S. Puri Over a year ago

Thanks, this doesn't obviously help me catch my error but does save time in extending the function to 2-dim arrays.

S. Puri Over a year ago

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?

Vincent G Over a year ago

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.

user6432984 · Accepted Answer · 2016-10-11 06:33:04Z

0

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

answered Oct 11, 2016 at 6:33

user6432984

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related