Difficulty with trying to use ComplexSorting subroutine

DonEB

Board Regular
Joined
Apr 26, 2016
Messages
133
Office Version
  1. 2019
Platform
  1. Windows
I found in the forum a subroutine called ComplexSorting. I needed to be able to sort two columns of a 2d array and, if it works, it will be just I've been looking for. It seems like this routine was referenced on many occasions, so I figured I would give it a try.

I attempted to utilize this program within a Subroutine I created called PreComboSort. I'll share with you the part of the code that leads up to the call of ComplexSorting.
VBA Code:
ReDim inarr1(1 To UBound(inarray), 1 To UBound(inarray, 2)) As Variant
Dim j As Long

'NOTES:  I use LastRowb because the length and width of the table being placed into the array may vary from one operation to the next so this is my attempt to accommodate that.
LastRowb = Sheets(wsName2).Range("B5:B24").Find(What:="*", After:=Sheets(wsName2).Range("B5:B24").Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

ReDim inarr1(1 To LastRowb - 4, 1 To LastRowb) As Variant

'####################################################################################
'# Sort Selected Rows
‘####################################################################################

For i = 4 To (LastRowb - 1)
     For j = 1 To LastRowb
          inarr1(i - 3, j) = inarray(i, j) 'copies contents of one array into another
     Next j
Next i

'SampleUsage: ComplexSorting vTable, Array(7, 8, 2, 1)               'NOTE:  Sample Usage was take directly out of the ComplexSorting subroutine
ComplexSorting inarr1, Array(1, 2)                                                'NOTE:  my desire is to sort on the 1st then 2nd column

When I try to run the above subroutine, it stops on the following piece of code where the following message appears: "Compile error: Argument not optional"
VBA Code:
QuickSortArray SortArray, , , CLng(sColumns(LBound(sColumns)))
Below, I've included the comlete ComlexSorting subroutine in case there is more that needs to be looked at or maybe I have an incomplete version. But since people claim this code works, I'm at a loss as to what I may be doing wrong since the instructions seem to be simple regarding what needs to be passed via the ByRef.

VBA Code:
Sub ComplexSorting(ByRef SortArray As Variant, sColumns As Variant)
'Posted by Lucas Almeida 06/04/21:
'This ComplexSorting was created thanks to the help of QuickSortArray for 2D dimensinal Arrays created by:
'Posted by Jim Rech 10/20/98 Excel.Programming
'Modifications, Nigel Heffernan:

'DESCRIPTION:
'ComplexSorting works similar to the Advanced Sort for Excel Tables
'So it can apply multiple sorts to the same 2D Array at the same time

'ARGUMENT VARIABLES:
'SortArray - is the 2D array you want to sort
'sColumns - are the columns you want to sort as numbers - Example: sColumns = Array(7, 2, 3)

'Keep in mind that in this example (7, 2, 3) it will sort the column 7 FIRST, then the 2nd column, then the 3rd
'So it sorts from left to right

'SampleUsage: ComplexSorting vTable, Array(7, 8, 2, 1)

'I haven't made a lot of error handlers, but if you pass the arguments in the right way, everything should work just fine ;)

Dim i As Integer, i1 As Long, Min As Long, Max As Long, MinSort As Long, MaxSort As Long
Dim Str(1 To 1) As Variant
For i = LBound(sColumns) To UBound(sColumns)
     If Not IsNumeric(sColumns(i)) Or IsEmpty(sColumns(i)) Then
          Err.Raise vbObjectError + 513, , "Only integers must be in sColumns array"
     End If
Next

'Do the first Sort
QuickSortArray SortArray, , , CLng(sColumns(LBound(sColumns)))

'If there is just one number inside sColumns, just exit sub
If LBound(sColumns) = UBound(sColumns) Then
     Exit Sub
End If

MinSort = LBound(SortArray)
MaxSort = UBound(SortArray)

'For each column you want to Sort (after the first)
For i = LBound(sColumns) + 1 To UBound(sColumns)
     Min = MinSort
     'For each line inside the 2D array
     For i1 = MinSort To MaxSort
          'It will search for the first(Min) and last(Max) line of occurrence for each value inside the last already sorted column
          'It will run the QuickSortArray based on the Min and Max

          If Min = i1 Then                                                                                                   'If it is the first occurrence of the value
               If SortArray(i1, sColumns(i - 1)) = SortArray(i1 + 1, sColumns(i - 1)) Then    'if the next value is equal to this first value
                    Str(1) = SortArray(i1, sColumns(i - 1))
              Else
                   Min = Min + 1                                                                                            'No need for sorting - unique value in the column
              End If
          Else
               If MaxSort = i1 Then                                                                                        'Last Line - Needed to evade the error in the ElseIf because of SortArray(i1 + 1)
                    Max = i1
                   QuickSortArray SortArray, Min, Max, CLng(sColumns(i)) 'Sort
               ElseIf SortArray(i1, sColumns(i - 1)) <> SortArray(i1 + 1, sColumns(i - 1)) Then         'If the next value is a new value
                    Max = i1
                    QuickSortArray SortArray, Min, Max, CLng(sColumns(i))                                       'Sort
                    Min = i1 + 1
               End If
          End If
     Next
Next
End Sub


Thanks for any suggestions or guidance you may be able to provide.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top