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.