VBA for Excel 2007 tutorial-VBA arrays to sort data in rows


VBA arrays to sort data in rows

In this example, We use VBA array to sort the full names in the current worksheet. Before sorting, the full names are not in order. After running the VBA example code, the full names are sorted in ascending order.

To compare the names, we need help from the StrComp(st1,st2) function.
This function return -1 if the string st1 is less that string st2. It returns 1 if string st1 is greater than st2 and it returns 0 if both string are equal.


To run this example VBA code, you will need a Form, and a CommandButton. You may type the full names text in to your current worksheet.



VBA for Excel: using VBA array to sort the names in Excel sheet
VBA code for this example:


Private Sub CommandButton1_Click()
    Dim myarr(5) As String
    Dim i, j As Integer
    i = 1
    'read names from Excel sheet and store them in the array
    While Cells(i, 1) <> ""
        myarr(i - 1) = Cells(i + 1, 1)
        i = i + 1
    Wend
   
    'Sorting the names
    For i = 0 To 4
      For j = i + 1 To 4
        If StrComp(myarr(j), myarr(i)) = -1 Then 'Compare names
            Dim temp As String
            temp = myarr(j) 'switching the names
            myarr(j) = myarr(i)
            myarr(i) = temp
        End If
        Next
    Next
   
    'Show the sorted array
    For i = 0 To 4
        Cells(i + 2, 1) = myarr(i)
    Next
   
End Sub


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.