VBA Type Mismatch on CustomOrder

Question

I have this code which works great:

Sub NewSortTest()
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add key:=Range("A1:A20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "alpha,bravo,charlie,delta,echo,foxtrot,golf,hotel,india,juliet", DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I'm trying to modify the above code by using a variable called keyRange for CustomOrder:=:

Sub NewSortTest()
    Dim keyRange As String

    keyRange = "alpha,bravo,charlie,delta,echo,foxtrot,golf,hotel,india,juliet"

    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

However I get a Type Mismatch error. I can see in MSDN that CustomOrder:= is of type Variant. I have tried Variant instead of String, but I get the same error.

Edit: CustomOrder is actually of type SortField. How can I convert the String keyRange to a SortField object?

Answer 1

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

Answer 2

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.

Answer 3

I know this is an old thread but figured my approach seems worth sharing.

I stored the SortField as a variable then set the CustomOrder property as per below, it seems the complier will take the delimited string and convert it to whatever it requires. FYI loNewReport is a ListObject

Dim sField As SortField
Set sField = loNewReport.Sort.SortFields.Add(loNewReport.ListColumns("Branch").DataBodyRange, xlSortOnValues, xlAscending)
sField.CustomOrder = "QLD, NSW, VIC"
loNewReport.Sort.Apply
Set sField = Nothing