Excel Constants for Sorting

Question

I'm using the sort procedure in Excel VBA and want to have a variable set that changes the order to ascending or descending, depending on a condition. I tried this but am getting a "type mismatch" error.

If SBF = 0 Then
    S = "xlAscending"
Else: S = "xlDescending"
End If  
ActiveWorkbook.Worksheets(SN(x)).sort.SortFields.Add Key:=Range( _
        "B3:B" & last_cell), SortOn:=xlSortOnValues, Order:=S, DataOption:= _
        xlSortNormal

Answer 1

xlAscending and xlDescending are Excel Constants. To see what values they have simply print it in Immediate window. See the snapshot below.

So you can actually write your code as

If SBF = 0 Then S = 1 Else S = 2

ActiveWorkbook.Worksheets(SN(x)).Sort.SortFields.Add _
Key:=Range("B3:B" & last_cell), _
SortOn:=xlSortOnValues, _
Order:=S, _
DataOption:=xlSortNormal

Similarly the values of xlSortOnValues and xlSortNormal is 0. If you want you can also write the above code as

If SBF = 0 Then S = 1 Else S = 2

ActiveWorkbook.Worksheets(SN(x)).Sort.SortFields.Add _
Key:=Range("B3:B" & last_cell), _
SortOn:=0, _
Order:=S, _
DataOption:=0

EDIT

I am assuming that S has been declared as an Integer or a Long and not as a String.

Answer 2

This works:

Public Enumeration, XlSortOrder, instance

Dim instance As XlSortOrder
If SBF = 0 Then
    instance = xlAscending
Else: instance = xlDescending
End If
ActiveWorkbook.Worksheets(SN(x)).sort.SortFields.Add Key:=Range( _
    "B3:B" & last_cell), SortOn:=xlSortOnValues, Order:=instance, DataOption:= _
    xlSortNormal