How to Sort a String Array in VBA
- 1). Launch Microsoft Excel, click the "Developer" tab, then click "Visual Basic" to open the VB Editor. Create a new sub procedure by adding the following code:
Private Sub SortVBAArray() - 2). Create your string array and add ten values to it:
Dim dataArray(10) As String
dataArray(0) = "John"
dataArray(1) = "Zackari"
dataArray(2) = "Sam"
dataArray(3) = "Adam"
dataArray(4) = "Bob"
dataArray(5) = "Kitzia"
dataArray(6) = "Daniel"
dataArray(7) = "Oscar"
dataArray(8) = "Alan"
dataArray(9) = "Yarexli" - 3). Call the sub procedure that will sort the values in the array and end the procedure:
Call sortArray(dataArray)
End Sub - 4). Create the sub procedure that will sort the string array in ascending order and display the results through the Immediate window:
Sub sortArray(tmpArray() As String)
Dim firstIdx As Integer
Dim lastIdx As Integer
Dim xCntr As Integer
Dim yCntr As Integer
Dim Temp As String
Dim List As String
firstIdx = LBound(tmpArray)
lastIdx = UBound(tmpArray)
For xCntr = firstIdx To lastIdx - 1
For yCntr = xCntr + 1 To lastIdx
If tmpArray(xCntr) > tmpArray(yCntr) Then
Temp = tmpArray(yCntr)
tmpArray(yCntr) = tmpArray(xCntr)
tmpArray(xCntr) = Temp
End If
Next yCntr
Next xCntr
For xCntr = 1 To UBound(tmpArray)
List = List & vbCrLf & tmpArray(xCntr)
Next
Debug.Print List
End Sub - 5). Click the first sub procedure and press "Ctrl" + "G" to display the Immediate window. Press "F5" to run the program and view the results.