VBA Type Mismatch on CustomOrder
Glad you figured it out. The following also works (per this post):
Sub NewSortTest()
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A20"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
CustomOrder:=keyRange, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:B20")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Function keyRange() As String
keyRange = "alpha,bravo,charlie,delta,echo,foxtrot,golf,hotel,india,juliet"
End Function
Edit: Even easier
CustomOrder:=CVar(keyRange)
Edit: Why Does This Work?
I've been trying to figure that out myself. The help documentation on the CustomOrder
property lacks any meat, as you found out in your search. I've been experimenting with different things to see if I could get at an answer, and I haven't had much luck. I think CustomOrder
is doing some magic in the background. It does just fine with a String
literal or a Long
, as you found out. And it has no poblem with a String
properly cast as Variant
. But it doesn't like String
variables. It must have something to do with String
variables being reference types. I have no idea why it wouldn't be able to handle that, but I also don't know how it creates a custom list on the fly from a String
literal. If you find anything that explains it, I'd love to know.
I worked it out. You need to use a ListArray:
Sub NewSortTest()
Dim keyRange As Variant
Dim sortNum As Long
keyRange = Array("alpha", "bravo", "charlie", "delta", "echo", "foxtrot", "golf", "hotel", "india", "juliet")
Application.AddCustomList ListArray:=keyRange
sortNum = Application.CustomListCount
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add key:=Range("A1:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=sortNum, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:B20")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub