Written by Allen Wyatt (last updated February 4, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
Mark is in a work environment that uses two versions of Excel?2002 and 2003. A problem arises when he opens a workbook with a macro in Excel 2003, which installs the VB library of Office 11, and then later someone tries to open the same workbook in Excel 2002, which gives an error when the macro tries to run because it cannot find the correct VBA library reference. Mark knows he can manually correct this by going to the VB editor and clearing the reference to the missing VBA library, but he wonders if there is any way to have the workbook check the Excel version automatically and update the VBA library reference accordingly.
One way to deal with this is to create and save the workbook using the earlier version of Excel. When it is opened in the later version of Excel, the reference to the VBA library will be automatically updated. Excel does the updates going to later versions, but it doesn?t do them going to earlier versions.
This means, however, that even if the workbook is created in the earlier version of Excel, once it is opened and subsequently saved in the later version of Excel, users of the earlier version will have problems opening it.
The solution, according to some sources, is to resort to what is known as ?late binding.? This simply means that the macro is written so that it looks up specific functions only during run-time, not when the macro is saved. This is referenced a bit in the following Microsoft Knowledge Base article:
http://support.microsoft.com/?kbid=244167
You can try late binding techniques by opening the VBA editor and removing any references previously established. Then add code similar to the following near the start of your macro:
If Application.Version = "10.0" Then 'Excel 2002 Dim oExcel As Object 'Apply late binding Set oExcel = CreateObject("Excel.Application") End If If Application.Version = "11.0" Then 'Excel 2003 ' ' Add whatever references you want for Excel 2003 ' End If
At the end of the macro make sure that you set any defined objects (such as oExcel) to Nothing. A good example of code that is more robust than what is presented here can be found in these articles:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=267 http://www.vbaexpress.com/kb/getarticle.php?kb_id=272
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3340) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Macros that run automatically when you open or close a workbook are quite helpful. You may not want them to run, however, ...
Discover MoreSometimes a macro command line can get very, very long. This can make it hard to understand when you look at it a month ...
Discover MoreOne way you can use macros in a workbook is to have them automatically triggered when certain events take place. Here's ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-05-12 12:22:29
John
Doesn't changing references programmatically require the user to manually enable "Trust access to the VBA project object model" in Macro Security? -thx
2016-06-27 14:18:26
Alberto
Many thanks for your suggestion. I resolved my issue.
2015-04-13 05:14:15
Rene
@ Steve
I ve the same problem and I tried to solve it using late binding (like above). It dosen´t work.
Did you fix the problem?
Beste regards Rene
2014-09-08 18:05:28
Steve
I have a similar problem. I have a workbook that utilizes a lot of code and forms that was created in Excel 2010. Some of my locations are using Excel 2013. One of my references is to the Microsoft Outlook Object Library, which changes to v15 in Excel 2013. This library causes problems when compiling if you attempt to open the workbook in Excel 2010. To compensate, I removed the reference to the v15 Outlook library and manually added the reference to the v14 library, which Excel 2010 uses. It works fine in Excel 2013, until you close and re-open, then it frustratingly changes the reference back to v15 again. Any ideas?
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments