Excel VBA - assign Sort order to variable

Question

I have this code that works perfectly:

    ActiveWorkbook.Worksheets("compleanni").Sort.SortFields.Add2 Key:=Range( _
                                                                       "I6:I23"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers

I'd like to change it like this:

myorder="xlAscending"
....
....
Order:=myorder

but it doesn't work: I get run-time error 13. Any suggestions?

Get rid of the quotation marks in the assignment and it should work. — Excel Hero
– Excel Hero, Commented Dec 16, 2020 at 17:14

FunThomas · Accepted Answer · 2020-12-16 17:13:00Z

1

xlAscending is a predefined constant in VBA (Excel), its type is long (value is 1, but that's not important).

Dim your variable myorder as Long and assing the constant:

Dim myorder as Long
myorder = xlAscending ' (or xlDescending)

answered Dec 16, 2020 at 17:13

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA - assign Sort order to variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related