DataWright Information Services

Consulting and Resources for Excel and Access

Setting a reference in the VBA environment

Often you will find yourself needing to work with other applications, or use different object libraries, when creating VBA code. One or two examples might be:

  • Automating Excel or Word from Access
  • Using ADO in Excel to transfer data to and from an Access (or other) database

If you use code that says you need to set a reference to xx library, and you try to run the code without setting that reference, you will get a run-time error and te code will not run correctly, or at all.

Setting a reference is straightforward; as an example this page shows how to set a reference to the Microsoft ActiveX Data Objects 2.8 Library.

Go into the code environment if you are not already there (Alt+F11 is the keyboard shortcut), and select Tools > References. You will see a dialog like this: the references dialog
Scroll down the list until you find the library that you want to reference, and select the checkbox, then press OK making the selection
To check that it worked, re-open Tools > References and you should see the new reference added to the list of checked items. the reference has now been selected

Your code should now recognise the objects correctly, and all being well you won't have the run-time errors any more.