How to Sort a String Array in VBA

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Sort a String Array in VBA
Learn how to write VBA code in Excel. (Jupiterimages/Photos.com/Getty Images)

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:
Moderate

Other People Are Reading

Things you need

  • Microsoft Excel

Show MoreHide

Instructions

  1. 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. 2

    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"
    
  3. 3

    Call the sub procedure that will sort the values in the array and end the procedure:

    Call sort Array(dataArray)
    

    End Sub

  4. 4

    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
    

    End Sub

  5. 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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.