custom sort

[Dave]

Hi,
XL2003
I was hoping someone could shed some light on this for me.
In the following code, notice that I had to use "n+1" to make it work when
sorting, but just n to delete the custom list.
When I look at the custom list in Tools,Options, I see:

NEW LIST
Mon, Tue, Wed, etc
Monday, Tuesday, Wednesday, etc
Jan, Feb, Mar, etc
January, February, March, etc

For some reason, when actually using the custom list,
XL seems to be counting the "NEW LIST" as number 1, but not so when deleting
or when getting the custom list number.

Application.AddCustomList Array("T", "B", "E")
n = Application.GetCustomListNum(Array("T", "B", "E"))

Range(z).Sort _
Key1:=Range("C2"), Header:=xlNo, OrderCustom:=n + 1

Application.DeleteCustomList n

Is there a way to sort, according to a custom list, without actually
creating an official custom list?
I tried:
Range(z).Sort _
Key1:=Range("C2"), Header:=xlNo, OrderCustomArray("T", "B", "E")
but it gave an error.

Regards - Dave.

 

[JLGWhiz]

Arrays are zero based by default, so when you use OrderCustom:=n it sees a
zero.
You can declare it as a base 1 array, but it is just as easy to do what you
did and add 1 where necessary.

 

[Dave]

Hi,
Ok, but it still seems inconsistent to me.
When I use the n=GetCustomListNum then n=5
When I do the sort, OrderCustom:= has to be 6 (n+1)
When I DeleteCustomList, I have to use n (5)

And can you help with the 2nd question regarding declaring simple lists
within the sort method?

Regards - Dave.

 

[JLGWhiz]

I think that if you check the value of n immediately after the sort line is
executed, you will find that n = 1. That tells it the order to sort in. In
the VBA help file it states that CustomOrder value is 1-based. which means,
of course, that value must be at least 1. As for the n(5), that is the
expression for the maximum size of your array. Until you loop through your
array, the first use of n will have a value of n = 0 which is the LBound
(lower boundary) and if you fully load the array then the UBound (upper
boundary) of the array would be 5 for a total of six items. You can look
this all up in the VBA help files for more details.

 

[Dave Peterson]

It seems weird to me, too.

Without knowing the real reason, I think it's because two different programmers
(or two different teams of programmers or even one person at different times!)
didn't really talk to each other.

VBA's help (xl2003) for Sort shows this for ordercustom:

OrderCustom Optional Variant. This argument is a one-based integer offset to
the list of custom sort orders. If you omit OrderCustom, a normal sort is used.

But I think that this just documents how it was implemented. My guess (and it's
just a guess) is that the sort programmer expected the custom list programmer to
use 0 based stuff. And the sort programmer wanted to index starting at 0+1, but
then saw how his portion had to work.

It was too late in the development cycle (or too many versions of excel were
already in used) to go back and "fix" the problem. So the solution was to
document it in the Help. And that made it a documented feature, not a
problem/bug/error.

But that's just a guess!

And you could go to some trouble by building a two column table -- on column for
the value and one for the order. Then use =vlookup() to return that order and
sort by the helper column.

But that's way too much work!

And since it's something I never remember (and I bet you won't either <bg>),
document your code so that if you need to do it again and find this macro, you
won't have to scratch your head again (or test until you see the solution!).

 

[Jim Cone]

Dave,
The "Normal" sort has an index (value) of 1.
The custom sort indexes are added to that value.
(and yes, 0 for the normal sort would have made more sense)
--
Jim Cone
Portland, Oregon USA




"Dave Peterson" <[email protected]>
wrote in message
It seems weird to me, too.

Without knowing the real reason, I think it's because two different programmers
(or two different teams of programmers or even one person at different times!)
didn't really talk to each other.

VBA's help (xl2003) for Sort shows this for ordercustom:

OrderCustom Optional Variant. This argument is a one-based integer offset to
the list of custom sort orders. If you omit OrderCustom, a normal sort is used.

But I think that this just documents how it was implemented. My guess (and it's
just a guess) is that the sort programmer expected the custom list programmer to
use 0 based stuff. And the sort programmer wanted to index starting at 0+1, but
then saw how his portion had to work.

It was too late in the development cycle (or too many versions of excel were
already in used) to go back and "fix" the problem. So the solution was to
document it in the Help. And that made it a documented feature, not a
problem/bug/error.

But that's just a guess!

And you could go to some trouble by building a two column table -- on column for
the value and one for the order. Then use =vlookup() to return that order and
sort by the helper column.

But that's way too much work!

And since it's something I never remember (and I bet you won't either <bg>),
document your code so that if you need to do it again and find this macro, you
won't have to scratch your head again (or test until you see the solution!).

 

[Dave]

Hi Jim,
Ok, that makes better sense. Thanks for taking the time. And yes, I will
make a not in my code.
Regards - Dave.

 

[Dave]

Hi Dave,
Sorry, answered the wrong post.
Please see answer to Jim Cone's post, and take it as yours.
Dave.