Using References in VBA

There are many situations where you need to create a reference to an external module in VBA in order to use the capabilities of that module. A reference provides access to the module. If you want to create forms with special features, then you need a reference to the module that contains those special features. Many examples in VBA for Dummies rely on references to perform their work.

I’ve had a number of readers write that they can’t find the modules I reference in the book. The most common request is about the example that begins on page 156. You can see the problem screenshot on page 157 in Figure 7-1.

To begin, you choose Tools | References to display the References – VBAProject dialog box. That dialog box will contain the references specific to your version of Office and the versions of other products installed on your machine. Because there is so much potential for differences between systems, your dialog box will never precisely match mine. In fact, my dialog box no longer matches those in the book because I’ve upgraded my version of Office since then (partly so I can discuss Office 2010 changes in my posts). Here are the default references for Excel 2010 (remember, your dialog box may vary from the one shown hereI’m using Office 2010 Professional Plus).

References01

As you can see, this version of Excel uses the Microsoft Excel 14.0 Object Library, not the 12.0 version shown in Figure 7-1. The example still works fine, despite the differences. When I opened the ExcelForms.xls file, Excel automatically updated the references used by the example applications for me. Even if it hadn’t, all I’d need to do is update them manually using the References – VBAProject dialog box.

A good rule of thumb is that if the book references a particular version of a module, you can use that version or higher safely. Generally, Microsoft makes modules used for VBA backward compatible. It isn’t always the case though. For example, the Ribbon interface negates the use of older menu-style interface changes in many respects. Yes, the changes appear on the Add-ins tab, but they don’t quite work the same as they did in the past, so clever changes might not work at all. From a module perspective, the updates do generally work.

Some readers have asked about using older modules. For example, if I have the 12.0 version of a module in the book, they wonder about using the 10.0 version. In this case, there aren’t any guarantees. Most of the examples should work just fine, but there isn’t any guarantee that they will. If someone has tried using an older version and found that something doesn’t work, let me know about it. When I have enough of these situations, I’ll create a post so that you know what won’t work with an older version. A safer bet is to obtain a copy of the 4th edition of my book which does use some older modules (unfortunately, I wasn’t able to find a link for this postAmazon shows it as unavailable).

What happens if you can’t find a module at all? It could mean that you’re using an educational version of Office. These educational versions have caused a good deal of problems in getting the book examples to work. In general, I don’t recommend using the educational versions for working with VBA unless you want to write simple examples. Microsoft doesn’t make the educational versions feature complete for a good reasonthey want you to buy the full version of their product so they can make large sums of money. If you do run across other problems, please let me at John@JohnMuellerBooks.com.