Custom sort order in VBA(XL2000) ? (VBA Excel2000)

Topic

New Reply

Does the Sort method in VBA(XL2000) allow for a custom sort order? The following excerpt from the VBA Help seems to imply that it does, but I can’t figure out how to define and reference the custom sort order (presumably a user-defined array).

expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod)
…
OrderCustom Optional Variant. This argument is a 1-based integer offset nto the list of custom sort orders. If you omit OrderCustom, 1 (Normal) is used.

Thanks.

Reply | Quote

Replies

Custom sort orders are based on the custom lists you can create in Tools/Options…, Custom Lists tab. There are four built-in lists (2 for days of the week and 2 for months of the year), but you can add your own custom lists.

To sort a range according to the n-th custom list, use n+1 for the OrderCustom argument. You must add 1 because 1 is reserved for Normal. For instance, the 3rd custom list in my Excel 97 is “Jan, Feb, Mar, …”. So if I have a range of cells containing abbreviated month names, I can sort them in chronological order (as opposed to alphabetical order) by specifying OrderCustom:=4.

If you want your own custom sort order, add the sorting list in Tools/Options…, Custom Lists.

Reply | Quote

Oh, of course! Custom Lists, not array variables. Now it makes sense. I’ll give it a try.
Thanks, Hans!

Reply | Quote

Hans,

I fooled around with sorting by Custom Lists until I got them working. There were two bugs/inaccuracies in the Help, though:

1. AddCustomList method does fail if the list already exists; and
2. These constants are backwards: xlSortOrientation.xlSortColumns =1, xlSortOrientation.xlSortRows =2; but
   the macro recorder constants work: Constants.xlLeftToRight=2 Constants.xlTopToBottom=1

Thanks again for your tip.

Reply | Quote

Hi Mike,

(1) In Excel 97 AddCustomList does *not* fail if the list already exists. If this changed in Excel 2000, Microsoft may have forgotten to update the help text.

(2) The constants are correct. Sort by rows = sort from left to right, and sort by columns = sort from top to bottom.

Regards,
Hans

Reply | Quote

Hans,

It’s interesting that AddCustomList doesn’t fail in XL97. I had to make an ugly work-around in XL2000 to account for the case in which the list had already been created.

And regarding the constants, I guess it’s a matter of semantics on the names. I suppose one could consider “sorting a row” to mean sorting the elements in the rows, i.e. sorting from left to right. On the other hand, “sorting BY rows” seems to me to mean something different. Oh, well, now I know what Microsoft means by it. Thanks.

Reply | Quote

Although some people are opposed to it, I often use a construction like

On Error Resume Next
Application.AddCustomList …
On Error Goto 0 (or On Error Goto Error_Handler_Label)

That way, your code will continue if adding the custom list fails. If necessary, you might check the error number to see why it failed.

Reply | Quote

Hmm, that looks a lot more elegant than the function I wrote. Why would anyone object? Because of the GoTo?

My code looks to see whether there are any user-defined custom lists. If any are present, it checks those lists to see if the first and last entries are those of my list. If not, it creates my list from a worksheet. I’d still have to do some of that in my error handling routine, but that still might have been a better approach. However, since it’s working, I’ll leave it alone. Thanks.

Function lMakeCustomList(rgListData As Range) As Long
'
'Checks to see if custom list containing "Walnut" and "Production Alternate" already exists.
'Creates it from "hard-wired" range in StationInfo if it does not.  Returns List Number.
'VBA NOTE: Contrary to Help, AddCustomList method fails if list already exists.
'26-Aug-2002 M.L.May -- working procedure with argument and variant list array

Dim lLists As Long                          'number of Custom Lists
Dim l As Long                               'list counter
Dim i As Integer                            'generic loop counter
Dim bFound As Boolean                       'true if station list was found
Dim ListArray() As Variant                  'variant array to hold Custom List

'if lList > 4 then custom lists exist, so check 'em all for first and last entries
    Application.StatusBar = "Looking for Custom List

Reply | Quote