Andy,

You should develop in the earliest version that your users will
be using, in this case Office 2000. If you don't have Office
2000, use late binding.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"AndyB" wrote in message
...
Problem : When is the reference to the correct version of the
MS Office Library set up in Excel? I can get MISSING references
in certain circumstances and not others...

What is the accepted best practice for producing VBA code that
will run in other (earlier or later) versions of Office?

Details:

I have a set of workbooks and an AdddIn that I have developed
in Excel 2003. A lot of my end users use Office XP/ 2000. My
code is set up with a reference to "MS Office 11.0 Object
Library", and works fine (in order to do custom menus). When a
user with an earlier version of office runs the code, it works
fine and these references are automagically converted to "MS
Office 10.0/9.0 Object Library" as appropriate, which I
understand is the expected behaviour.

I use Early Binding, so need the correct reference in order for
the code to compile. I understand that I could make my code
immune to Office version by converting to Late Binding and I
understand how to do this, but I'm reluctant to do it, as I like
to be able to use the nice features such as Intellisense,
pre-defined constant values etc., and there are no dependencies
on any particular version in the code.

If everything is OK, why am I worried? ... Well, none of my end
users have yet experienced a problem, but I have simulated a
problem in testing (investigating a different installation
issue), and I would like the group's advice on whether I should
be concerned or not.

If I set up my test system with Win2000/OfficeXP, and Office XP
is set up to install everything on first use, then install my
stuff (basically a set of workbooks plus addin), I find that the
VBA will not compile, because the projects are still referencing
the Office 11.0 object library and no reference has been set up
to the Office 10.0 library.

I can manually fix this by unchecking the Office 11.0 reference
and adding the Office 10.0 reference, and then everything will
work fine from then on.

Also, on Office XP installations that use the default
installation options (rather than install on first use), the
Office 10.0 reference is already there before I install my stuff
and the correct reference is automagically used.

Some of my users will have had their Office installed from an
administrative install where the install-on-demand feature has
been set and so could get the same problem.

So my ultimate question is - Can I expect that the correct
reference will have usually been set by normal use of Excel
before I install my stuff, or do I need to take precautions to
avoid this potential problem? The precaution/workaround could be
as simple as asking the user to invoke a certain feature of Excel
to provoke the setting of the correct reference if they encounter
the problem ...

Any and all advice would be gratefully received, thanks

AndyB