Sorting data is a task all programmers run into. Most of the time, we are connecting to a database like SQL, MySQL, and Access and those database providers give us nice and neat sort methods for Ascending and Descending order. We just ask for the data, tell it how we want it sorted, and BAM! Formatted results. Brilliant!
But you say, "gee Corey, that's great and all, but I want to sort data that is NOT in a database like an array". That's where the adventure begins!
First, there are two well established methods of sorting arrays. There is the Bubble Sort and the Disconnected Recordset methods. Let's start with bubble sort.
First, here's the code:
arrData = Array(324,234,33,7,467,34)
For i = LBound(arrData) to UBound(arrData)
For j = LBound(arrData) to UBound(arrData)
If j <> UBound(arrData) Then
If arrData(j) > arrData(j + 1) Then
TempValue = arrData(j + 1)
arrData(j + 1) = arrData(j)
arrData(j) = TempValue
End If
End If
Next
Next
This is the bubble sort method. Think of it like this. An array is just a long line of numbers or letters. The bubble sort looks at the first element of the array and compares it to the second. If the first is greater than the second, it saves the value to a temporary variable, copies the second to the first, and the sets the second element back to the first. Confusing? Just think of it as swapping places.
Now a bubble sort works by going through the entire array and swapping two elements at a time until the final product is a nice sorted array. Nifty. But what if we wanted to do descending? Simple, change the greater than to a less than:
If arrData(j) < arrData(j + 1) Then
See, told you it was simple. Now, let's take this new bubble sorting knowledge and wrap it up into a nice function we can use, reuse, and abuse.
Function BubbleSort(arrData,strSort)
'Input: arrData = Array of data. Text or numbers.
'Input: strSort = Sort direction (ASC or ascending or DESC for descending)
'Output: Array
'Notes: Text comparison is CASE SENSITIVE
' strSort is checked for a match to ASC or DESC or else it defaults to Asc
strSort = Trim(UCase(strSort))
If Not strSort = "ASC" And Not strSort = "DESC" Then
strSort = "ASC"
End If
For i = LBound(arrData) to UBound(arrData)
For j = LBound(arrData) to UBound(arrData)
If j <> UBound(arrData) Then
If strSort = "ASC" Then
If arrData(j) > arrData(j + 1) Then
TempValue = arrData(j + 1)
arrData(j + 1) = arrData(j)
arrData(j) = TempValue
End If
End If
If strSort = "DESC" Then
If arrData(j) < arrData(j + 1) Then
TempValue = arrData(j + 1)
arrData(j + 1) = arrData(j)
arrData(j) = TempValue
End If
End If
End If
Next
Next
BubbleSort = arrData
End Function
Now we're coding! This function will take in an array and a sort direction. ASC for ascending (lowest to highest) and DESC for descending (highest to lowest). It even handles text comparison but is case sensitive. If you wanted to disregard case sensitivity, you can check to see if the element is a string and if so, convert it to upper (or lower) case before you do the sort.
That's it for now. Stay tuned for my next installment: Sorting arrays by disconnected recordsets!
Have a burning VBScript question to be answered? Send it in and you may just be featured on the blog. :0)
Happy Scripting!
Corey
5 comments:
Nice Bubblesort :) Useful... Thanks
Very useful, thanks
Thank you for your script.
Or use an ArrayList:
Dim AL : Set AL = CreateObject("System.Collections.ArrayList")
AL.Add 123
AL.Add 454
AL.Add 1
AL.Sort
msgbox join(AL.ToArray(), vbNewLine)
and descending?
AL.Reverse
msgbox join(AL.ToArray(), vbNewLine)
If you are using ArrayList from System.Collections, you need to note that it cannot handle sorting when using multiple variable types.
For example:
Dim AL : Set AL = CreateObject("System.Collections.ArrayList")
AL.Add 123
AL.Add 454
AL.Add "My Name"
AL.Sort
msgbox join(AL.ToArray(), vbNewLine)
That will give you this error: "mscorlib: Failed to compare two elements in the array"
If you used bubble sorting, you could sort stings and numbers together. .Net is a lot less forgiving than vbscript.
Post a Comment