Excel Guides

Automatically Changing References to VBA Libraries in Excel

If you're working with Microsoft Excel, you may need to reference different Visual Basic for Applications (VBA) libraries in your macros. For example, the Visual Basic Editor (VBE) references the Microsoft Visual Basic for Applications Extensibility 5.3 library by default. If you want to use objects, methods, or properties from a different library, you need to set a reference to that library in your VBA code.

You can set references to libraries in the VBE by going to Tools > References. This will open the References dialog box, which lists all the available libraries that you can reference. If the library you need is not listed, you can browse for it on your computer. Once you've added a reference to a library, any objects, methods, or properties from that library will be available for use in your VBA code.

You can also change references automatically using the Application.ReferenceLibrary method. This method takes two arguments: the first is the name of the library that you want to reference, and the second is a Boolean value that specifies whether or not to make that reference permanent. The syntax for this method is as follows:

Application.ReferenceLibrary("libraryname", permanent)

For example, if you wanted to add a reference to the Microsoft ActiveX Data Objects 2.8 Library, you would use the following code:

Application.ReferenceLibrary "MSDATASRC", True 

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.