Reverse order of an array with VBA

Introduction - Reverse Array

This macro will reverse the order of an array using Excel VBA. This is a useful macro if you have an array sorted smallest to largest and you want to sort it largest to smallest. Instead of rewriting your sorting algorithm, you can reverse the order of your existing array.

This tutorial was inspired by a comment I received on my super fast VBA Quicksort macro, which sorts your VBA arrays from smallest to largest.

Example - Reverse Array

Reverse the order of your array

Sub ReverseArray(vArray As Variant)
'Reverse the order of an array, so if it's already sorted
'from smallest to largest, it will now be sorted from
'largest to smallest.
Dim vTemp As Variant
Dim i As Long
Dim iUpper As Long
Dim iMidPt As Long
iUpper = UBound(vArray)
iMidPt = (UBound(vArray) - LBound(vArray)) \ 2 + LBound(vArray)
For i = LBound(vArray) To iMidPt
    vTemp = vArray(iUpper)
    vArray(iUpper) = vArray(i)
    vArray(i) = vTemp
    iUpper = iUpper - 1
Next i
End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

Tutorial - Reverse Array

How the macro reverses the order of your array

This macro loops through half of your array using a combination of the LBound and UBound functions to find the midpoint. It only needs to go through half your array because it’s essentially swapping the first half (counting up) with the last half (counting donw).

It takes the first element if your array and moves it to the last element, then it takes your last element and moves it to your first element. It keeps going until the entire array is in the opposite order.

I’ve come across a number of functions online that claim to reverse your VBA arrays, but the way they define the midpoint is incorrect. Many of them either add a + 1 or nothing to the end, where this macro properly adds the lower bound of your array.

If you were to hardwire the “adder” on the end if your midpoint, you’re inherently forcing your macro to only work with Base 0 or Base 1 arrays, depending on how you do it.

This macro is different. It will work with Base 0, Base 1 and any other base array you pass it. For example, even if your array was defined with elements from -1 to 5, it will still be properly reversed.

Other macros online will mess up the central elements in your array and you’d be left with a hodgepodge of orders in the middle. That’s not what you want!

Calling the ReverseArray Macro

Here’s a basic example to show you how to call the ReverseArray macro to reverse the order of the elements in your array. All you have to do is pass the macro 1 argument: the name of your array. The array is named v in this example.

Sub Reverse_Example()
Dim v() As Variant
v() = Array(1, 2, 3, 4, 5, 6)
Call ReverseArray(v)
'From here on, the array "v" is in reverse order (6,5,4,3,2,1)
End Sub

This macro will work whether you use Option Base 0 (default) or Option Base 1 to define your arrays.

It will also work if you have a custom lower bound, like in the following example:

Sub Reverse_Example2()
Dim v(-5 To 5) As Variant
For i = LBound(v) To UBound(v)
    v(i) = i
Next i
Call ReverseArray(v)
'From here on, the array "v" is in reverse order (5,4,3...-4,-5)
End Sub

Not very many people know how to reverse the order of an array, but there are even rarer array tricks out there for you to learn. Grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to show you even more tricks for handling arrays.

Application Ideas

Reversing the order of your array is most useful when you already have a sorted array, but you want it sorted the other way. Sometimes you just don’t want to use a For Loop with the Step option set to -1 to walk through your array backwards.

In these instances, instead of changing the logic of the sorting algorithms you already use, just switch the order of your array so it’s sorted the other way. The first element will now be your last element and your last element will now be your first element.

Doing it this way may take a little longer than sorting it the way you want it up front, but the added time is miniscule compared to the aggravation of rewriting your sorting algorithms!

Whether you use the VBA bubble sort or VBA Quicksort algorithms, this ReverseArray example is a nice compliment to your existing array manipulation tools. Just call the procedure immediately after sorting your array, and your array will be sorted the other way.

This was a neat little tutorial, but we’re just getting started. When you’re ready to take your VBA to the next level, subscribe using the form below.