Quick Excel 2000 VBA question

[dabookshah@gmail.com]

Hi there. I am having trouble getting a VBA program that I have written
to work on Excel 2000. Basically, I need to know the signature of the
Range.Sort method in Excel 2000. I have looked around a lot and it
seems there is no documentation on Excel 2000, only 2003. The best I
could find was a very helpful document that mentioned that it had
"changed" between 2000 and 2003. Can anyone help, or possibly provide a
link to any documentation that i might have missed?

Thanks in advance
Rohan Shah

[Mat P:son]

Sure, no problem:

This is what the Object Browser says about Excel.Range.Sort() in Excel 2000:

Function Sort(
[Key1],
[Order1 As XlSortOrder = xlAscending],
[Key2],
[Type],
[Order2 As XlSortOrder = xlAscending],
[Key3],
[Order3 As XlSortOrder = xlAscending],
[Header As XlYesNoGuess = xlNo],
[OrderCustom],
[MatchCase],
[Orientation As XlSortOrientation = xlSortRows],
[SortMethod As XlSortMethod = xlPinYin])

For your information: here's also the Excel 2003 version:

Function Sort(
[Key1],
[Order1 As XlSortOrder = xlAscending],
[Key2],
[Type],
[Order2 As XlSortOrder = xlAscending],
[Key3],
[Order3 As XlSortOrder = xlAscending],
[Header As XlYesNoGuess = xlNo],
[OrderCustom],
[MatchCase],
[Orientation As XlSortOrientation = xlSortRows],
[SortMethod As XlSortMethod = xlPinYin],
[DataOption1 As XlSortDataOption = xlSortNormal],
[DataOption2 As XlSortDataOption = xlSortNormal],
[DataOption3 As XlSortDataOption = xlSortNormal])

So it seems as if the only bits that have changed between the two versions
are the three additional parameters DataOption<N>

If you want me to provide you with the comprehensive signature (i.e., the
one you find in the office type library (OLB) file rather than the rather
limited declaration from the VBE Object Browser) then I'll be happy to
provide you with that as well -- just let me know...

Cheers,
/MP
"dabookshah@gmail.com" wrote:

> Hi there. I am having trouble getting a VBA program that I have written
> to work on Excel 2000. Basically, I need to know the signature of the
> Range.Sort method in Excel 2000. I have looked around a lot and it
> seems there is no documentation on Excel 2000, only 2003. The best I
> could find was a very helpful document that mentioned that it had
> "changed" between 2000 and 2003. Can anyone help, or possibly provide a
> link to any documentation that i might have missed?
>
> Thanks in advance
> Rohan Shah
>
>

[Mat P:son]

Actually, I could just as well provide you with the IDL for IRange::Sort() at
once -- I don't really see why I shouldn't...

Cheers,
/MP

====================================

From Excel 2000's OLB (Excel9.olb):

HRESULT _stdcall Sort(
[in, optional] VARIANT Key1,
[in, optional, defaultvalue(1)] XlSortOrder Order1,
[in, optional] VARIANT Key2,
[in, optional] VARIANT Type,
[in, optional, defaultvalue(1)] XlSortOrder Order2,
[in, optional] VARIANT Key3,
[in, optional, defaultvalue(1)] XlSortOrder Order3,
[in, optional, defaultvalue(2)] XlYesNoGuess Header,
[in, optional] VARIANT OrderCustom,
[in, optional] VARIANT MatchCase,
[in, optional, defaultvalue(2)] XlSortOrientation
Orientation,
[in, optional, defaultvalue(1)] XlSortMethod SortMethod,
[out, retval] VARIANT* RHS);

====================================

....And for your information, here's Excel 2003:

HRESULT _stdcall Sort(
[in, optional] VARIANT Key1,
[in, optional, defaultvalue(1)] XlSortOrder Order1,
[in, optional] VARIANT Key2,
[in, optional] VARIANT Type,
[in, optional, defaultvalue(1)] XlSortOrder Order2,
[in, optional] VARIANT Key3,
[in, optional, defaultvalue(1)] XlSortOrder Order3,
[in, optional, defaultvalue(2)] XlYesNoGuess Header,
[in, optional] VARIANT OrderCustom,
[in, optional] VARIANT MatchCase,
[in, optional, defaultvalue(2)] XlSortOrientation
Orientation,
[in, optional, defaultvalue(1)] XlSortMethod SortMethod,
[in, optional, defaultvalue(0)] XlSortDataOption DataOption1,
[in, optional, defaultvalue(0)] XlSortDataOption DataOption2,
[in, optional, defaultvalue(0)] XlSortDataOption DataOption3,
[out, retval] VARIANT* RHS);

====================================

"dabookshah@gmail.com" wrote:

> Hi there. I am having trouble getting a VBA program that I have written
> to work on Excel 2000. Basically, I need to know the signature of the
> Range.Sort method in Excel 2000. I have looked around a lot and it
> seems there is no documentation on Excel 2000, only 2003. The best I
> could find was a very helpful document that mentioned that it had
> "changed" between 2000 and 2003. Can anyone help, or possibly provide a
> link to any documentation that i might have missed?
>
> Thanks in advance
> Rohan Shah
>
>

[Bob Phillips]

Rohan,

XP introduced three new arguments in Sort, DataOption1, DataOption2,
DataOption3 which were not available in 2000. Remove these.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<dabookshah@gmail.com> wrote in message
news:1149572026.504476.262650@h76g2000cwa.googlegroups.com...
> Hi there. I am having trouble getting a VBA program that I have written
> to work on Excel 2000. Basically, I need to know the signature of the
> Range.Sort method in Excel 2000. I have looked around a lot and it
> seems there is no documentation on Excel 2000, only 2003. The best I
> could find was a very helpful document that mentioned that it had
> "changed" between 2000 and 2003. Can anyone help, or possibly provide a
> link to any documentation that i might have missed?
>
> Thanks in advance
> Rohan Shah
>

[DaBookshah]

Hmm. Ok I still haven't sorted out my problems, maybe it will help if i
explain things better. I have written a program using Visual Basic for
Applications and Excel, but all the development machines have Excel
2003, and it's meant to work with Excel 2000. I'm not really sure how
to build a version that works with Excel 2000.
This could all be completely wrong, but here's what I was thinking.

I recall from somewhere that Excel9.olb just contains information about
the methods exposed by the excel COM object(s). So I thought copying
Excel9.olb from a different machine and importing it into my visual
stuido project would help.....no such luck. I assume that I import
Excel9.olb my adding it to the references list, but for some reason it
just adds a reference to Excel 2003(loaded on this machine).

Basically I don't know how to build an Excel2000 compatible version of
my program from a machine that has Excel2003(or even if its possible).

Thanks,
Rohan Shah

[Mat P:son]

"DaBookshah" wrote:

> Hmm. Ok I still haven't sorted out my problems, maybe it will help if i
> explain things better. I have written a program using Visual Basic for
> Applications and Excel, but all the development machines have Excel
> 2003, and it's meant to work with Excel 2000. I'm not really sure how
> to build a version that works with Excel 2000.

Set up a test machine running Excel 2000 and thoroughly verify your app.

Nowadays, it's far less painful thanks to VMware and other "virtual machine"
applications, on which you can configure a whole range of systems, running
different operating systems, languages, and for example office installations.

Theoretically, all new version of Excel (and other apps) should only add
features, never change what's already available, so as long as you avoid
using the latest, coolest features you should be okay. By compiling your XLA
periodically on your test machine you'll get an error as soon as you've used
features unavailable on Excel
2000.

The other option is to downgrade your development machine, but that's not
very fun, of course...

> This could all be completely wrong, but here's what I was thinking.
>
> I recall from somewhere that Excel9.olb just contains information about
> the methods exposed by the excel COM object(s).

Yeah, all the type library information (enumerations, constants, methods,
aliases, etc)

> So I thought copying
> Excel9.olb from a different machine and importing it into my visual
> stuido project would help.....no such luck. I assume that I import
> Excel9.olb my adding it to the references list, but for some reason it
> just adds a reference to Excel 2003(loaded on this machine).

Yes, for obvious reasons you're not allowed to kick out your Excel.exe
reference (the one pointing to v11) and when you're trying to import
Excel9.olb, you're just importing an older version of the same thing. Hence,
Excel ignores you attempts.

Having said that, running your XLA on an older version of Excel will work
just fine, even though you have compiled it on your Excel 2003-box. Excel
2000 will refer to the Excel9 typelib info (TLB v1.3) and then recompile the
XLA in the background. If it finds all the imports in its own TLB it will be
happy, and if not, well, then you'll end up with a compilation error in Excel
(this is something you don't want to run into after shipping, obviosly, so
compile frequently on older platforms).

All this recompilation-on-demand hocus pocus normally works just fine, and
if every call you do is early-bound then you can rely on the Excel 2000
compiler to tell you whether your app will work on Excel 2000 or not. The
story is slightly different if you're using late binding, of course, since
the compiler can't tell whether a particular call will fail or not --
instead, you will end up with run-time failures.

By the way: It may be useful to take a look in the old OLB now and again
though, just for a laugh :o) To iinspect the interfaces and stuff you can use
OLEView, Type Library Editor, or some other typelib browser.

> Basically I don't know how to build an Excel2000 compatible version of
> my program from a machine that has Excel2003(or even if its possible).

It is indeed possible. You just have to be a bit careful. And say goodbye to
the fancy stuff in late versions of Excel, unfortunately... :o)

> Thanks,
> Rohan Shah

Cheers,
/MP