Hello!

In VBA I can set an option compare to binary in order to compare strings
case sensitive. With that option set, "a" and "A" are treated as different
values ("A" < "a")
But if I use the sort method with MatchCase:=True the sort order is "a" <
"A" !

What I need is compare mode and sort mode to behave according to the same
order of characters/strings.

Is there a way around this? Can I sort binary? Or am I missing something else?

Any help is appreciated

Herbert Becker,
Austria

When you set Option Compare... you are telling VBA to do something.
When you use the Sort method of the Range object, you are asking XL to
do something. While one might argue that your VBA setting should apply
to XL the reality is that they don't and it is up to you to ensure that
the two behave the way you want them to.

Also, you might want to check the documentation of the Sort method's
MatchCase argument. To me it seems counter-intuitive: "MatchCase
Optional Variant. True to do a case-sensitive sort; False to do a sort
that=3Fs not case sensitive."

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello!

In VBA I can set an option compare to binary in order to compare strings
case sensitive. With that option set, "a" and "A" are treated as different
values ("A" < "a")
But if I use the sort method with MatchCase:=True the sort order is "a" <
"A" !

What I need is compare mode and sort mode to behave according to the same
order of characters/strings.

Is there a way around this? Can I sort binary? Or am I missing something else?

Any help is appreciated

Herbert Becker,
Austria

Hello Tushar,

first of all, thank you for your reply.

You are right, the compare option concerns the VBA interpreter and the sort
method is used on a Excel object. But both are based on two different "ideas
of how to compare" strings. That might be a fact, but it's still annoying
that there is no way I can make these two behave identically.

According to Excel Help the option MatchCase is a variant. And yet it can
only be True or False.

Unless someone else has has an idea I will have to come up with a costly
workaround ...

Thanks again,
Herbert

"Tushar Mehta" wrote:

When you set Option Compare... you are telling VBA to do something.
When you use the Sort method of the Range object, you are asking XL to
do something. While one might argue that your VBA setting should apply
to XL the reality is that they don't and it is up to you to ensure that
the two behave the way you want them to.

Also, you might want to check the documentation of the Sort method's
MatchCase argument. To me it seems counter-intuitive: "MatchCase
Optional Variant. True to do a case-sensitive sort; False to do a sort
that=3Fs not case sensitive."

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello!

In VBA I can set an option compare to binary in order to compare strings
case sensitive. With that option set, "a" and "A" are treated as different
values ("A" < "a")
But if I use the sort method with MatchCase:=True the sort order is "a" <
"A" !

What I need is compare mode and sort mode to behave according to the same
order of characters/strings.

Is there a way around this? Can I sort binary? Or am I missing something else?

Any help is appreciated

Herbert Becker,
Austria