Introduction | Example | Tutorial | Applications | Comments

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

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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

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.

Closing Thoughts

Before commenting, please take a moment to share this article with your friends on Facebook, Twitter, and Google+. Social media is a great way to grow the site and reach more users, so I genuinely appreciate it when you share my articles with friends on your favorite platform! :)

About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.