VBA References - when is Office Object Library Reference set? Best practice re. Early/Late binding

[AndyB]

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, thank

Andy

 

[Chip Pearson]

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

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

 

[Bob Phillips]

Don't forget that you can develop with early binding and then convert to
late binding for release. Here is a previous post of mine on this
technique, working through an example http://tinyurl.com/2qern

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

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

 

[AndyB]

Chip

Thanks for the speedy response, which sounds like sensible advice. I can relatively straightforwardly convert to late binding, so will probably do this

I'm still interested in a couple of points though
1. Assuming I could lay my hands on a properly licensed copy of Office 2000 (not straightforward, since it is no longer available retail and has been pulled from MSDN) and continued to use early binding, would I not still have the same issue for users of later versions of Office

2. At what point *does* Excel fix up the reference to point to the appropriate Object Library? It seems that if the reference is already listed in the References list when my workbook is opened, the conversion will be done, but if it is not already present (and it isn't if you have done the install-on-demand thing), no conversion is done. So something must have been loaded at some point (part of excel or some other workbook) that had an explicit dependency on the correct version. Any thoughts

Thanks again

And


----- Chip Pearson wrote: ----

Andy

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


--
Cordially
Chip Pearso
Microsoft MVP - Exce
Pearson Software Consulting, LL
www.cpearson.co

Problem : When is the reference to the correct version of th

MS Office Library set up in Excel? I can get MISSING reference
in certain circumstances and not others..will run in other (earlier or later) versions of Office


....snip ...

 

[AA2e72E]

Using early binding during development and converting to late binding for release is quite productive: it makes intellisense available. Howevr, take care with named constants-they are meaningful in early binding but may be problematic with late binding (may require the substitution of the actual values)

Using the earliest version is ok but for two reasons: someone is bound to be using an even earlier version and earlier versions end not to have programmer friendly features e.g. the Excel 2000 application object does not have a hwnd property whereas later versions do, Excel 97 does not have CopyFromRecordSet whereas later versions do. Workarounds for the lack of such features, where use, create a lot of clutter

 

[AndyB]

This sounds like a good compromise, thanks Bob

Andy

----- Bob Phillips wrote: ----

Don't forget that you can develop with early binding and then convert t
late binding for release. Here is a previous post of mine on thi
technique, working through an example http://tinyurl.com/2qer

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

Problem : When is the reference to the correct version of the MS Offic

Library set up in Excel? I can get MISSING references in certai
circumstances and not others..other (earlier or later) versions of Office


....snip...