Assign variable for CustomOrder parameter in vba

Question

I have the following code

Sub Test()
    Dim x
    x = "ow, bv, xz"
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("D1"), CustomOrder:=x
        .SortFields.Add Key:=Range("C1"), Order:=xlDescending
        .SortFields.Add Key:=Range("B1"), Order:=xlAscending
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .Apply
    End With
End Sub

I tried to use CustomOrder and assign a variable x to the string. When using the string directly, it works well but not with using variable Any idea how to fix that?

[This answer](https://stackoverflow.com/a/6104297/4717755) suggests using an array to specify your sort order. — PeterT, Apr 13 '22 at 17:01

score 1 · Accepted Answer · answered Apr 13 '22 at 17:00

1

Seems like a bug. The following work for me:

CustomOrder:=Cstr(x)

CustomOrder:=Join(Split(x)) 'overkill, but for laughs

CustomOrder:=x & vbNullString

answered Apr 13 '22 at 17:00

BigBen

46,229
7
24
40

Assign variable for CustomOrder parameter in vba

1 Answers1