Sort Array

Basic Exercise

Statement

Create a Sub that takes a vector as a parameter and sorts it.

Suggested Concepts:

Dim - Declares variables.
For Next - Repeats a group of statements a specified number of times.
Array - In the vector declaration you can define its index (start and end).
LBound e UBound - To generalize the subroutine, working with vectors of any size.
If Else - Conditionally executes a group of statements.

Remember that in most cases there is more than one way to solve the exercise.

Hint: Remember how > and < operators work for texts.

Solved Exercise

Exercise Solution

Create two Subs: one for sorting and one for testing the solution.

Sub Solution()
End Sub
Sub SortArray()
End

In the test Sub, let's create a String vector with values.

Sub Solution()
    Dim Names(2 To 11) As String
    Names(2) = "James"
    Names(3) = "Denise"
    Names(4) = "John"
    Names(5) = "Alexa"
    Names(6) = "Sandra"
    Names(7) = "Mark"
    Names(8) = "Daniel"
    Names(9) = "Laura"
    Names(10) = "Steven"
    Names(11) = "Lucy"
End Sub

Now we adjust the Sub of the solution to receive a vector as an input to order it.

Sub SortArray(vector)
    Dim TempValue As String
    Dim i As Long
    Dim j As Long
    Dim FirstIndex As Long
    Dim LastIndex As Long

    'Identify vector range
    FirstIndex = LBound(vector)
    LastIndex = UBound(vector)

    For i = FirstIndex To LastIndex - 1
        For j = i + 1 To LastIndex
            If vector(i) > vector(j) Then
            'Change order when vector(i)> vector(j)
                TempValue = vector(i)
                vector(i) = vector(j)
                vector(j) = TempValue
            End If
        Next j
    Next i
End Sub

For each loop made with counter j we will have defined another value from the beginning of the vector, being the first iteration defining the first element of the vector, the second referring to the second and so on.

This algorithm is known as BubbleSort

We apply the solution in our test Sub showing the results in a MsgBox sequence

Sub Solution()
    Dim i as Integer
    Dim Names(2 To 11) As String
    Names(2) = "James"
    Names(3) = "Denise"
    Names(4) = "John"
    Names(5) = "Alexa"
    Names(6) = "Sandra"
    Names(7) = "Mark"
    Names(8) = "Daniel"
    Names(9) = "Laura"
    Names(10) = "Steven"
    Names(11) = "Lucy"
    SortArray Names

    For i = 2 To 11
        MsgBox (Names(i))
    Next i

End Sub

Consolidated Answer

Sub Solution()
    Dim i as Integer
    Dim Names(2 To 11) As String
    Names(2) = "James"
    Names(3) = "Denise"
    Names(4) = "John"
    Names(5) = "Alexa"
    Names(6) = "Sandra"
    Names(7) = "Mark"
    Names(8) = "Daniel"
    Names(9) = "Laura"
    Names(10) = "Steven"
    Names(11) = "Lucy"
    SortArray Names

    For i = 2 To 11
        MsgBox (Names(i))
    Next i

End Sub

Sub SortArray(vector)
    Dim TempValue As String
    Dim i As Long
    Dim j As Long
    Dim FirstIndex As Long
    Dim LastIndex As Long
    FirstIndex = LBound(vector)
    LastIndex = UBound(vector)
    For i = FirstIndex To LastIndex - 1
        For j = i + 1 To LastIndex
            If vector(i) > vector(j) Then
                TempValue = vector(i)
                vector(i) = vector(j)
                vector(j) = TempValue
            End If
        Next j
    Next i
End Sub

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED