Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    Boise, Idaho, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    Boise, Idaho, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Oh, of course! Custom Lists, not array variables. <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> Now it makes sense. I'll give it a try.
    Thanks, Hans!

  4. #4
    Lounger
    Join Date
    Aug 2002
    Location
    Boise, Idaho, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hans,

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

    1. <LI>AddCustomList method does fail if the list already exists; and
      <LI>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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  6. #6
    Lounger
    Join Date
    Aug 2002
    Location
    Boise, Idaho, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  8. #8
    Lounger
    Join Date
    Aug 2002
    Location
    Boise, Idaho, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> 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.

    <pre>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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •