Solved

VBA References

Posted on 2004-10-07
11
785 Views
Last Modified: 2008-03-17
I have been developing in VBA for Access and Excel for some time now.  I have a question about references and older versions of Office.  I just installed Office XP on my machine and have started to do some development.  If I send the database to someone with Office 2000, the reference does not downgrade to say Excel 2000.  It says the reference to Excel 10.0 library is missing.

Now the other way works fine.  If I develop in Access 2000 and have a reference to Excel 9.0 library, and someone with Office XP opens it, the VBA will automatically upgrade the reference to 10.0.

Is there any way to make sure the correct references are used, or do I have to install both Office 2000 and Office XP on my machine and add just use the older references so that it works on all machines?  Thanks.
0
Comment
Question by:ingenito
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 125 total points
ID: 12252433
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12252439
I have a different version of this problem with ADO references differing in version between machines and have not found a sure-fire fix.  I'll be watching to see if anyone else has.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 12252481
Hello ingenito,

There is no way to force the non-Access references to downdate.

The solution to this sort of problem is to use 'late binding'.
This is a technique where you do not use any Excel reference library at all and declare the excel objects as datatype 'Object'.
Look at the CreateObject function in VBA Help.


Pete
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 12

Expert Comment

by:pique_tech
ID: 12252613
<vent>

Yuck.  "The only solution to this problem is to program in a way generally recognized as a bit shoddy."  LOL  What a brilliant Microsoft-ish solution!

</vent>
0
 

Author Comment

by:ingenito
ID: 12252933
I agree with pique_tech in that its a pretty inefficient way to go about things.  I don't want to have to write extra code and then worry about commenting everything when I take out the references.  Also, if I don't use references to begin with then the very helpful property dialog popups and such will not work.  I guess the best way would be to write a function that starts when either when the database or worksheet is opened and then check for and add references there.  Any other suggestions?
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12253117
You can attempt to programmatically manage the references, but that could get ugly once your app gets to a machine with several dozen custom references added.  Late binding, while a little inconvenient, is the easiest solution you have to this issue.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12253486
>> Also, if I don't use references to begin with then the very helpful property dialog popups and such will not work

I generally code using Early binding, then change the Dim and Set statements to use Late binding.
0
 
LVL 84
ID: 12253768
>> "The only solution to this problem is to program in a way generally recognized as a bit shoddy."  

I'd certainly not call Late Binding "shoddy" ... it's simply another programming tool.

Generally speaking you should ALWAYS program to the lowest common denominator - it your user base is using a mixture of Excel 97 and greater, then it is incumbent on you to insure that your code will work (and compile) on that platform, and you do that by either (a) doing all your development work on a machine with Excel 97 installed and referenced or (b) using late binding or (c) setting minimum requirements and enforcing them using your installation tool.

Regarding ADO references, generally speaking you should use your installer to determine file versions, and require your end user to have version xxx or greater before allowing installation to continue. Or, of course, developing on a machine with the lowest common denominator (as above) and therefore not worrying about other references. Of course, ADO (actually you're talking about MDAC here) references are notoriously difficult to deal with; mis-matched versions of any of the hundreds of files installed by MDAC can cause you no end of trouble. The solution for this is the same as above: Make sure your end user has the correct version OR make sure your dev machine has the lowest common version.
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12253846
Fair enough, I'm actually an empirical pragmatist with theoretical purist tendencies... (but not a poseur...lol)

I meant no offense.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 12263307
empirical - theoretical - in the same mind?  Probably is offensive but none taken. ;))
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Why this Update SQL not Updating! 15 57
Microsoft Access report help 4 38
Intermittent OleDbConnection Error 20 53
Outlook mail to Access 8 30
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question