VBA Array Sort – Using Excel Sort
In this page, we have given a Excel vba code snippet that works well for sorting array of 1 dimension.
You can also customize & extend this code to work for 2 dimension array as well. We will publish the code for 2D array sort, once we are done with the testing.
How does this Excel Vba Sort Array works?
- Create a temporary worksheet.
- Copy array to that Excel worksheet.
- Sort the Range in Excel.
- Convert sorted range back to Array.
Yes. We are going to use Excel’s inbuilt sort option here. Lets see the code now.
Sub ExcelVbaSortArray() 'Declare Array as variant & Range Dim sheetSort As Worksheet Dim vSortArray As Variant Dim rSortRange As Range Dim vItemsInArray As Double 'Assign values to array vSortArray = VBA.Split("a,b,s,c,d,f,e,g,h,j", ",", , vbTextCompare) vItemsInArray = UBound(vSortArray) + 1 'Create Temporary Sheet to Sort Set sheetSort = Worksheets.Add(after:=Sheets(Sheets.Count)) Set rSortRange = sheetSort.Range("A1:A" & vItemsInArray) rSortRange = Application.Transpose(vSortArray) 'Sort values in Array rSortRange.Sort rSortRange 'Convert Range to Array vSortArray = Application.Transpose(rSortRange.Value) Debug.Print VBA.Join(vSortArray, ",") 'Delete Sheet Application.DisplayAlerts = False sheetSort.Delete Application.DisplayAlerts = True Set sheetSort = Nothing End Sub
The above actually sort the array in alphabetic order. It get completed within micro seconds.
In the above code, We have used function Transpose. To know more about what it does, You have to read about how a Array is converted to Range and vise versa. Click here to know more about this conversion steps..
There are multiple sorting techniques available like bubble sort, heap sort, merge sort, selection sort & so on. But it is better to use Excel in built sort option in here. Because it is the fastest when working with Excel.