Multiple Apps

Sorting Arrays

Ease of Use

Intermediate

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

A method for sorting an array alphabetically or numerically. 

Discussion:

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. 

Code:

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:

BubbleSort.zip 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