VBA: Reverse an array?

Question

How could I reverse an array that is full of integers e.g.:

[1;5;8;45;54]

To:

[54;45;8;5;1]

Are there any built in functions I could use?

I tried using this method:

Array.Reverse(arr)

I added Mscorlib.dll from Tools > References, but it showed error: Syntax error. At the Array.Reverse(arr) location.

Answer 1

you could use ArrayList class and wrap its Reverse method:

Function ReverseArray(arr As Variant) As Variant
    Dim val As Variant

    With CreateObject("System.Collections.ArrayList") '<-- create a "temporary" array list with late binding 
        For Each val In arr '<--| fill arraylist
            .Add val
        Next val
        .Reverse '<--| reverse it
        ReverseArray = .Toarray '<--| write it into an array
    End With
End Function

to be used like:

Sub main()
    Dim arr As Variant

    arr = ReverseArray(Array(1, 2, 3, 4, 5)) '<-- it returns an array of Variant/Integer with values 5,4,3,2,1        
End Sub

Answer 2

Array.Reverse sounds like VB.Net, not VBA.

Chip Pearson has functions for just about anything you will want to do with arrays (and other structures).

http://www.cpearson.com/excel/vbaarrays.htm --> ReverseArrayInPlace

The relevant part is:

Ndx2 = UBound(InputArray)
' loop from the LBound of InputArray to the midpoint of InputArray
For Ndx = LBound(InputArray) To ((UBound(InputArray) - LBound(InputArray) + 1) \ 2)
    'swap the elements
    Temp = InputArray(Ndx)
    InputArray(Ndx) = InputArray(Ndx2)
    InputArray(Ndx2) = Temp
    ' decrement the upper index
    Ndx2 = Ndx2 - 1
Next Ndx