Descending Quicksort Issue

[dawds]

Hi all,

I have a web scraper that pulls various sports teams' scores and puts each separate teams' scores into a different sheet. I've started scraping upwards of 200 teams a time, so I implemented the QuickSort algorithm as a means for sorting my sheets. The sort works perfectly when ascending, but I tried modifying the QuickSort code to sort descending and it stays as ascending. I've read multiple posts on the subject and they all modify the QuickSort code in the same fashion that I did, so I'm unsure where I'm going wrong. Any help would be greatly appreciated. Here's my code for creating my array to be sorted and then the QuickSort code I found online:

Sub call_sort()
    Dim myArray() As Variant
    
    x = Worksheets.Count
    ReDim myArray(5 To x)


    For i = 5 To x
        myArray(i) = Worksheets(i).Name
    Next i


    Application.Run "Personal.xlsb!quick_sort_asc", myArray, LBound(myArray), UBound(myArray)


    For i = 5 To x
        Worksheets(myArray(i)).Move Before:=Worksheets(i)
    Next i


End Sub

Public Sub quick_sort_asc(ByRef vArray As Variant, inLow As Long, inHi As Long)
  Dim tmpLow As Long, tmpHi As Long
  Dim pivot As Variant, tmpSwap As Variant


  tmpLow = inLow
  tmpHi = inHi
  pivot = vArray((inLow + inHi) \ 2)


  Do While (tmpLow <= tmpHi)
     Do While (vArray(tmpLow) < pivot And tmpLow < inHi)
        tmpLow = tmpLow + 1
     Loop


     Do While (pivot < vArray(tmpHi) And tmpHi > inLow)
        tmpHi = tmpHi - 1
     Loop


     If (tmpLow <= tmpHi) Then
        tmpSwap = vArray(tmpLow)
        vArray(tmpLow) = vArray(tmpHi)
        vArray(tmpHi) = tmpSwap
        tmpLow = tmpLow + 1
        tmpHi = tmpHi - 1
     End If
  Loop


  If (inLow < tmpHi) Then quick_sort_asc vArray, inLow, tmpHi
  If (tmpLow < inHi) Then quick_sort_asc vArray, tmpLow, inHi


End Sub

*** Lines modified for Descending ***
Do While (vArray(tmpLow) > pivot And tmpLow < inHi)
     tmpLow = tmpLow + 1
Loop
Do While (pivot > vArray(tmpHi) And tmpHi > inLow)
     tmpHi = tmpHi - 1
Loop

 

[Eric W]

I tried the quicksort code as written on an array with elements 0-4 and it worked. I changed the 2 lines that you marked to get it to sort descending, and it worked. I then used the same array with element numbers 5-9, and it did not work, ascending or descending. Possibly there's an issue with how it chooses the pivot points.

Try changing your array so that it starts at 0, or 1, and see what happens. Change your loops to:

For i = 0 to x - 5
    myArray(i) = Worksheets(i + 5).Name
Next i

 

[GTO]

Quickly tested, but appears to work:

Public Sub quick_sort_desc(ByRef vArray As Variant, ByVal inLow As Long, ByVal inHi As Long)
' From shg's suggestion at: https://www.mrexcel.com/forum/excel-questions/550009-do-quicksort-descending-order.html#post2717838


Dim tmpLow As Long
Dim tmpHi As Long
Dim pivot As Variant
Dim tmpSwap As Variant
  
  tmpLow = inLow
  tmpHi = inHi
  pivot = vArray((inLow + inHi) / 2)
  
  Do
    Do While vArray(tmpLow) > pivot
      tmpLow = tmpLow + 1
    Loop
    
    Do While vArray(tmpHi) < pivot
      tmpHi = tmpHi - 1
    Loop
    
    If tmpLow <= tmpHi Then
      tmpSwap = vArray(tmpLow)
      vArray(tmpLow) = vArray(tmpHi)
      vArray(tmpHi) = tmpSwap
      tmpLow = tmpLow + 1
      tmpHi = tmpHi - 1
    End If
  Loop While tmpLow <= tmpHi
  
  If inLow < tmpHi Then quick_sort_desc vArray, inLow, tmpHi
  If tmpLow < inHi Then quick_sort_desc vArray, tmpLow, inHi
  
End Sub

Hope that helps,

Mark

 

[dawds]

Thanks for both responses. I realized a little while after I posted that I had accidentally put my scraper values in ascending order while testing the QuickSort, so no sorting ever occurred. I changed the starting value of the array as you suggested (both 1 and 0), but after myArray() values stayed the same even though it seems to run through the code fine. I shouldn't have to create a new array to store the sorted values correct? Thanks again