![]() ' Dependencies: Excel Tools > References > Microsoft Excel Object Libraryĭim excel_application As Excel.Application Private Function sort_array_2D_excel(array_2D, array_sortkeys, Optional array_sortorders, Optional tag_header As String = "Guess", Optional tag_matchcase As String = "False") Tested calling Excel 2010 from Visio 2010 You didn't want an Excel-based solution but since I had the same problem today and wanted to test using other Office Applications functions I wrote the function below. If (i < lngMax) Then Call QuickSortArray(SortArray, i, lngMax, lngColumn) If (lngMin < j) Then Call QuickSortArray(SortArray, lngMin, j, lngColumn) SortArray(j, lngColTemp) = arrRowTemp(lngColTemp) SortArray(i, lngColTemp) = SortArray(j, lngColTemp) ReDim arrRowTemp(LBound(SortArray, 2) To UBound(SortArray, 2))įor lngColTemp = LBound(SortArray, 2) To UBound(SortArray, 2)ĪrrRowTemp(lngColTemp) = SortArray(i, lngColTemp) If IsObject(varMid) Then ' note that we don't check isObject(SortArray(n)) - varMid might pick up a valid default member or property ' We send 'Empty' and invalid data items to the end of the list: VarMid = SortArray((lngMin + lngMax) \ 2, lngColumn) If InStr(TypeName(SortArray), "()") = lngMax Then ' no sorting required ' ' Escape failed comparison with empty variant 'Posted by Jim Rech 10/20/98 Excel.Programming ' Sample Usage: sort arrData by the contents of column 3 Public Sub QuickSortArray(ByRef SortArray As Variant, _ If your array has fewer than 1024 members, I'd use a rudimentary BubbleSort. Note that quicksort algorthms - and any recursive algorithm - can fill the stack and crash Excel. Wend comparison operators and trap your code in an infinite loop. On the other hand, I code for Excel, and there's a bit more in the way of defensive coding - be warned, you'll need it if your array contains the pernicious 'Empty()' variant, which will break your While. I posted some code in answer to a related question on StackOverflow:Īlain's optimised Quicksort is very shiny: I just did a basic split-and-recurse, but the code sample above has a 'gating' function that cuts down on redundant comparisons of duplicated values. If pos = 48 And iCode <= 57 Then isDigit = True IsDigit(Used in CompareNaturalNum) Function isDigit(ByVal str As String, pos As Integer) As Boolean If IsNull(string1) And Not IsNull(string2) ThenĮlseIf IsNull(string1) And IsNull(string2) ThenĮlseIf Not IsNull(string1) And IsNull(string2) Then ' Everything was the same so far, check if Len(string2) > Len(String1) N2 = Val(Mid(string2, iPosOrig2, nOffset2))ĮlseIf (nOffset1 Mid(string2, iPos2, 1)) Then N1 = Val(Mid(string1, iPosOrig1, nOffset1)) StrPivot = strArray((intBottom + intTop) \ 2) Natural Number Quick Sort Public Sub QuickSortNaturalNum(strArray() As String, intBottom As Integer, intTop As Integer)ĭim strPivot As String, strTemp As Stringĭim intBottomTemp As Integer, intTopTemp As Integer Difference from other Q-Sorts: I don't swap the values if the BottomTemp = TopTemp.I translated the CompareNaturalNum function which was originally written in C from the internet as well.I stole the Quick Sort from the internet a long time ago, not sure where now.Normally, if you sort strings with numbers you'll get something like this: Text1īut you really want it to recognize the numerical values and be sorted like Text1 If (a(l) > a(i)) Then swap a, l, i '// Tri-Median Methode!' Private Sub QuickSort(ByRef a() As Long, ByVal l As Long, ByVal r As Long)ĭim M As Long, i As Long, j As Long, v As Long I have it optimized to run on an array of Int/Longs but it should be simple to convert it to one that works on arbitrary comparable elements. I converted the 'fast quick sort' algorithm to VBA, if anyone else wants it. (There's a working multi-dimensional array QuickSort here.) Note that this only works with single-dimensional (aka "normal"?) arrays. If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi (Source: ) Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long) When it's done, myArray will be sorted and you can do what you want with it. UBound(myArray).)Įxample: Call QuickSort(myArray, 0, UBound(myArray)) Below is a function for it.Ĭall it simply by passing an array of values (string or numeric it doesn't matter) with the Lower Array Boundary (usually 0) and the Upper Array Boundary (i.e. ![]() The most versatile and usually the quickest is the Quicksort algorithm. There are many algorithms available on the web for sorting. Edit: The referenced source () has since closed, but here are the relevant author comments:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |