VBA Formatting Text
VBA Color Selected
VBA Metric Converter
VBA Do Loop Sum
VBA Sum of Powers
VBA Sort Array
VBA Simple Loop
VBA Harmonic Mean
VBA Alternate Color
VBA Even or Odd
VBA First Capital Letter
VBA Intersection
VBA Ascending Order
VBA Concatenate
VBA Select Case
VBA Columns to Lines
VBA Making an Addition
VBA Offset Resize
VBA Search and Color
VBA Current Region
VBA Simple Email

VBA Message Box
VBA Final Score
VBA Factorial Function
VBA Infinite Series
VBA Count dollar bills
VBA Prime Number
VBA If Insert Cell
VBA Text to Columns
VBA Even and Odd
VBA Ballot Box

Basic Exercise

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

**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

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

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

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

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

```
Sub Solution()
End Sub
Sub SortArray()
End
```

```
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
```

```
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**

```
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 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

VBA Formatting Text
VBA Color Selected
VBA Metric Converter
VBA Do Loop Sum
VBA Sum of Powers
VBA Sort Array
VBA Simple Loop
VBA Harmonic Mean
VBA Alternate Color
VBA Even or Odd
VBA First Capital Letter
VBA Intersection
VBA Ascending Order
VBA Concatenate
VBA Select Case
VBA Columns to Lines
VBA Making an Addition
VBA Offset Resize
VBA Search and Color
VBA Current Region
VBA Simple Email

VBA Message Box
VBA Final Score
VBA Factorial Function
VBA Infinite Series
VBA Count dollar bills
VBA Prime Number
VBA If Insert Cell
VBA Text to Columns
VBA Even and Odd
VBA Ballot Box

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.

© 2019 SuperExcelVBA | ABOUT

Thank you for contributing. A message was sent reporting your comment.