Sorting a string array in Visual Basic for Applications (VBA) is not as simple as in other, more recent programming languages. In VBA, you can sort an array in ascending order using a couple of "For...Loops" and looping through each element in the array. This method is useful when you need to display values back to the user in ascending order, to give your data a more professional look.
- Skill level:
Other People Are Reading
Things you need
- Microsoft Excel
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()
Create your string array and add ten values to it:
Dim data Array(10) As String data Array(0) = "John" data Array(1) = "Zackari" data Array(2) = "Sam" data Array(3) = "Adam" data Array(4) = "Bob" data Array(5) = "Kitzia" data Array(6) = "Daniel" data Array(7) = "Oscar" data Array(8) = "Alan" data Array(9) = "Yarexli"
Call the sub procedure that will sort the values in the array and end the procedure:
Call sort Array(dataArray)
Create the sub procedure that will sort the string array in ascending order and display the results through the Immediate window:
Sub sort Array(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
Click the first sub procedure and press "Ctrl" + "G" to display the Immediate window. Press "F5" to run the program and view the results.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for