Sort function causes a crash and does not perform secondary sort

%3CLINGO-SUB%20id%3D%22lingo-sub-1477123%22%20slang%3D%22en-US%22%3ESort%20function%20causes%20a%20crash%20and%20does%20not%20perform%20secondary%20sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1477123%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPublic%20Function%20sorttable(ws%2C%20Table%2C%20Column1%2C%20Column2)%0A%0ADim%20oWorksheet%20As%20Worksheet%0ADim%20fRangeSort%20As%20Range%0ADim%20oRangeKey%20As%20Range%0ADim%20oRangeKey%20As%20Range%0A%0ASet%20oWorksheet%20%3D%20ws%0A%0A'%20one%20range%20that%20includes%20all%20colums%20do%20sort%0ASet%20oRangeSort%20%3D%20oWorksheet.Range(Table)%0A'%20start%20of%20column%20with%20keys%20to%20sort%0ASet%20fRangeKey%20%3D%20oWorksheet.Range(Column1)%0ASet%20oRangeKey%20%3D%20oWorksheet.Range(Column2)%0A%0A'%20custom%20sort%20order%0ADim%20sCustomList(1%20To%2057)%20As%20String%0AsCustomList(1)%20%3D%20%22Total%22%0AsCustomList(2)%20%3D%20%22Unallocated%22%0AsCustomList(3)%20%3D%20%22EE%20-%20Unallocated%22%0AsCustomList(4)%20%3D%20%22ACP%22%0AsCustomList(5)%20%3D%20%22AT%22%0AsCustomList(6)%20%3D%20%22AT%20CABINETS%22%0AsCustomList(7)%20%3D%20%22BC%22%0AsCustomList(8)%20%3D%20%22BD%22%0AsCustomList(9)%20%3D%20%22BW%22%0AsCustomList(10)%20%3D%20%22General%22%0AsCustomList(11)%20%3D%20%22Integrator%22%0AsCustomList(12)%20%3D%20%22LVS%22%0AsCustomList(13)%20%3D%20%22MEDIUM%22%0AsCustomList(14)%20%3D%20%22MES%22%0AsCustomList(15)%20%3D%20%22MSW%22%0AsCustomList(16)%20%3D%20%22PER%22%0AsCustomList(17)%20%3D%20%22SF%22%0AsCustomList(18)%20%3D%20%22TB%22%0AsCustomList(19)%20%3D%20%22TF%22%0AsCustomList(20)%20%3D%20%22US%22%0AsCustomList(21)%20%3D%20%22PC%22%0AsCustomList(22)%20%3D%20%22BOOM%22%0AsCustomList(23)%20%3D%20%22ME%20-%20Unallocated%22%0AsCustomList(24)%20%3D%20%22AU%22%0AsCustomList(25)%20%3D%20%22CS%22%0AsCustomList(26)%20%3D%20%22CH%22%0AsCustomList(27)%20%3D%20%22DH%22%0AsCustomList(28)%20%3D%20%22DR%22%0AsCustomList(29)%20%3D%20%22EMU%22%0AsCustomList(30)%20%3D%20%22EF%22%0AsCustomList(31)%20%3D%20%22AF%22%0AsCustomList(32)%20%3D%20%22OH%22%0AsCustomList(33)%20%3D%20%22PH%22%0AsCustomList(34)%20%3D%20%22WT%22%0AsCustomList(35)%20%3D%20%22CC%20-%20Unallocated%22%0AsCustomList(36)%20%3D%20%22GR%22%0AsCustomList(37)%20%3D%20%22CA%22%0AsCustomList(38)%20%3D%20%22MI%22%0AsCustomList(39)%20%3D%20%22EI%22%0AsCustomList(40)%20%3D%20%22CCD%22%0AsCustomList(41)%20%3D%20%22BMC%22%0AsCustomList(42)%20%3D%20%22CT_%22%0AsCustomList(43)%20%3D%20%22CM_%22%0AsCustomList(44)%20%3D%20%22CBL_%22%0AsCustomList(45)%20%3D%20%22DGN_%22%0AsCustomList(46)%20%3D%20%22COC_%22%0AsCustomList(47)%20%3D%20%22S%20-%20Unallocated%22%0AsCustomList(48)%20%3D%20%22S%20-%20Internal%22%0AsCustomList(49)%20%3D%20%22S%20-%20External%22%0AsCustomList(50)%20%3D%20%22Oth_%22%0AsCustomList(51)%20%3D%20%22NM%22%0AsCustomList(52)%20%3D%20%22BCP%20-%20C%22%0AsCustomList(53)%20%3D%20%22TB_C%22%0AsCustomList(54)%20%3D%20%22IS%20-%20C%22%0AsCustomList(55)%20%3D%20%22IS%20-%20Com%22%0AsCustomList(56)%20%3D%20%22IS%20-%20CS%22%0AsCustomList(57)%20%3D%20%22Placeholder%22%0A%0AApplication.AddCustomList%20ListArray%3A%3DsCustomList%0A'%20use%20this%20if%20you%20want%20a%20list%20on%20the%20spreadsheet%20to%20sort%20by%0A'%20Application.AddCustomList%20ListArray%3A%3DRange(%22D1%3AD3%22)%0A%0AoWorksheet.Sort.SortFields.Clear%0AoRangeSort.Sort%20Key1%3A%3DfRangeKey%2C%20Order1%3A%3DxlAscending%2C%20_%0AKey2%3A%3DoRangeKey%2C%20OrderCustom%3A%3DApplication.CustomListCount%20%2B%201%2C%20_%20%0AMatchCase%3A%3DTrue%2C%20_%0AOrientation%3A%3DxlTopToBottom%2C%20DataOption1%3A%3DxlSortNormal%0A%0A'%20clean%20up%0AActiveSheet.Sort.SortFields.Clear%0AErase%20sCustomList%0ASet%20oWorksheet%20%3D%20Nothing%0A%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20and%20largest%20problem%20I%20am%20facing%20is%20that%20this%20function%20once%20executed%20results%20in%20the%20following%20excel%20crash%20after%20~7-10%20minutes%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFaulting%20application%20path%3A%20C%3A%5CProgram%20Files%5CMicrosoft%20Office%5Croot%5COffice16%5CEXCEL.EXE%20%0AFaulting%20module%20path%3A%20C%3A%5CProgram%20Files%5CCommon%20Files%5CMicrosoft%20Shared%5COffice16%5Cmso20win32client.dll%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20problem%20is%20that%20the%20key2%20sort%20is%20not%20occurring.%20I%20thought%20I%20set%20it%20up%20correctly%20based%20on%20the%20examples%20I%20could%20find.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ECan%20anyone%20help%20identify%20why%20this%20could%20cause%20excel%20to%20crash%20and%20any%20ideas%20about%20how%20to%20get%20the%20double%20column%20sort%20to%20work%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1477123%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

 

 

Public Function sorttable(ws, Table, Column1, Column2)

Dim oWorksheet As Worksheet
Dim fRangeSort As Range
Dim oRangeKey As Range
Dim oRangeKey As Range

Set oWorksheet = ws

' one range that includes all colums do sort
Set oRangeSort = oWorksheet.Range(Table)
' start of column with keys to sort
Set fRangeKey = oWorksheet.Range(Column1)
Set oRangeKey = oWorksheet.Range(Column2)

' custom sort order
Dim sCustomList(1 To 57) As String
sCustomList(1) = "Total"
sCustomList(2) = "Unallocated"
sCustomList(3) = "EE - Unallocated"
sCustomList(4) = "ACP"
sCustomList(5) = "AT"
sCustomList(6) = "AT CABINETS"
sCustomList(7) = "BC"
sCustomList(8) = "BD"
sCustomList(9) = "BW"
sCustomList(10) = "General"
sCustomList(11) = "Integrator"
sCustomList(12) = "LVS"
sCustomList(13) = "MEDIUM"
sCustomList(14) = "MES"
sCustomList(15) = "MSW"
sCustomList(16) = "PER"
sCustomList(17) = "SF"
sCustomList(18) = "TB"
sCustomList(19) = "TF"
sCustomList(20) = "US"
sCustomList(21) = "PC"
sCustomList(22) = "BOOM"
sCustomList(23) = "ME - Unallocated"
sCustomList(24) = "AU"
sCustomList(25) = "CS"
sCustomList(26) = "CH"
sCustomList(27) = "DH"
sCustomList(28) = "DR"
sCustomList(29) = "EMU"
sCustomList(30) = "EF"
sCustomList(31) = "AF"
sCustomList(32) = "OH"
sCustomList(33) = "PH"
sCustomList(34) = "WT"
sCustomList(35) = "CC - Unallocated"
sCustomList(36) = "GR"
sCustomList(37) = "CA"
sCustomList(38) = "MI"
sCustomList(39) = "EI"
sCustomList(40) = "CCD"
sCustomList(41) = "BMC"
sCustomList(42) = "CT_"
sCustomList(43) = "CM_"
sCustomList(44) = "CBL_"
sCustomList(45) = "DGN_"
sCustomList(46) = "COC_"
sCustomList(47) = "S - Unallocated"
sCustomList(48) = "S - Internal"
sCustomList(49) = "S - External"
sCustomList(50) = "Oth_"
sCustomList(51) = "NM"
sCustomList(52) = "BCP - C"
sCustomList(53) = "TB_C"
sCustomList(54) = "IS - C"
sCustomList(55) = "IS - Com"
sCustomList(56) = "IS - CS"
sCustomList(57) = "Placeholder"

Application.AddCustomList ListArray:=sCustomList
' use this if you want a list on the spreadsheet to sort by
' Application.AddCustomList ListArray:=Range("D1:D3")

oWorksheet.Sort.SortFields.Clear
oRangeSort.Sort Key1:=fRangeKey, Order1:=xlAscending, _
Key2:=oRangeKey, OrderCustom:=Application.CustomListCount + 1, _ 
MatchCase:=True, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

' clean up
ActiveSheet.Sort.SortFields.Clear
Erase sCustomList
Set oWorksheet = Nothing

End Function

 

 

The first and largest problem I am facing is that this function once executed results in the following excel crash after ~7-10 minutes:

 

 

Faulting application path: C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE 
Faulting module path: C:\Program Files\Common Files\Microsoft Shared\Office16\mso20win32client.dll

 

 

The second problem is that the key2 sort is not occurring. I thought I set it up correctly based on the examples I could find.


Can anyone help identify why this could cause excel to crash and any ideas about how to get the double column sort to work?

0 Replies