References in VBA

[Harry Covair]

Hi the forum,

A macro written in VBA Excel requires sometimes to activate one or more references (VBE / Tools / references).

Question 1: When the Excel file is used on another PC, do the selected references accompany the file?

Question 2: If not, how to activate the required references in VBA?

Thanks in advance for your help

Regards

Harry

 

[Debraj]

Hi Harry..


Your all queries are interesting and discuss full..

Looks like this will cross 20+ post and some top voice..

or may be its a old topic to discuss..

You are starting famous Early & Late binding topic..


You can select the references before the code runs..

or you may select the references by coding .. so that in other system you dont have to select the Reference before running the code..


Please refer below URL's ..


http://www.mrexcel.com/forum/showthread.php?192500-Syntax-for-late-binding-to-MSFORMS-DataObject/page3

http://support.microsoft.com/kb/245115

http://msdn.microsoft.com/en-us/library/0tcf61s1.aspx

http://www.excelguru.ca/forums/showthread.php?792-Access-VBA-Writing-to-excel-with-Late-Binding

 

[Colin Legg]

If you've ticked a reference and saved the workbook, it will still be ticked when another user opens the workbook.


So, suppose you have a reference which is:

C:MyReference.xla


When the other user opens the workbook, Excel will try to find the file C:MyReference.xla on the other computer. If it is there then Excel will load it; if the reference isn't found then it will not be loaded and the reference will be given a prefix of 'MISSING:' and it is very likely that the VBA code will not work.


So, you should not need to activate any references at runtime, but you do need to ensure that all of the referenced files are available on the other computer. The referenced files themselves do not 'ship' with the workbook.


A typical situation when you might have a problem is when you're automating another Office program from Excel. If you're using early binding then you'll have a reference set to that Office program: for example, if you're automating Word then you'll have a reference to the Microsoft Word [x.x] Object library. [x.x] is representative of the version: 12.0 for Word 2007, 14.0 for Word 2010 etc. If you have users with different versions of Office installed, then the users with an older version than the one referenced will have a problem. A workaround to this is to automate Word using late binding - ie. with no reference required.