Multiple Apps

Sorting Arrays

Ease of Use


Version tested with


Submitted by:

Jacob Hilderbrand


A method for sorting an array alphabetically or numerically. 


VBA cannot just sort an array, but we can use a method called a Bubble Sort to get the results we want. Note that the array is set in the "test" macro and sorted in the "BubbleSort(MyArray() As Variant)" macro. 


instructions for use


Option Base 1 Option Explicit Sub test() Dim arrayContacts() As Variant arrayContacts = Array("James", "Mary", "Tom", "Beth", "Bob", "Chris", _ "Daniel", "Lari", "Al", "Teresa") Call BubbleSort(arrayContacts) End Sub Sub BubbleSort(MyArray() As Variant) Dim First As Integer Dim Last As Integer Dim i As Integer Dim j As Integer Dim Temp As String Dim List As String First = LBound(MyArray) Last = UBound(MyArray) For i = First To Last - 1 For j = i + 1 To Last If MyArray(i) > MyArray(j) Then Temp = MyArray(j) MyArray(j) = MyArray(i) MyArray(i) = Temp End If Next j Next i For i = 1 To UBound(MyArray) List = List & vbCrLf & MyArray(i) Next MsgBox List End Sub

How to use:

  1. Open Excel or Word.
  2. Open VBA (Press Alt + F11).
  3. Insert -Module.
  4. Paste the code into the code window at right.
  5. Close the VBE (Alt + Q or press the X in the top-right corner.
  6. Save the file if desired.

Test the code:

  1. Tools-Macro-Macros. and double-click test. You will get a message box with the sorted array.

Sample File: 13.28KB 

Approved by mdmackillop

This entry has been viewed 248 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express