In this Article
This tutorial will explain what a reference is and how to use them in the VBA Editor
If you are working in Excel VBA, and you wish to work with Objects available in a different application – like Microsoft Word for example, you need to add a reference to the object library of that application.
Adding a reference to your VBA Project
In the VBE Window, click on the Tools menu and then click References…
Scroll down through the list of references to find the one you want to use. In this case, the Microsoft Word 16.0 Object Library.
Viewing the Object Library
Once you have added a reference to the database, you can use the objects that are associated with the application to which you have referenced. The OBJECT LIBRARY for that particular application is now available for you to use within Excel.
Click on the Object Browser button on the Standard toolbar.
Click on the View menu, and select Object Browser.
Select Word from the drop down list
You will now be able to view all the objects as well as their methods and properties that you can now use within your the Excel VBA code.
Using the Reference in an Excel Procedure
Once you have created a reference, you can use the objects from the object library in your VBA code in Excel. The procedure below will create a new Word Document and make Word visible on your screen.
Sub CreateWordDocument() Dim wdApp As Word.Application Dim wdDoc As Word.Document 'open word Set wdApp = New Word.Application 'create a document Set wdDoc = wdApp.Documents.Add 'type some stuff wdApp.Selection.TypeText "Good morning Melanie!" 'show word on the screen wdApp.Visible = True End Sub
Early vs Late Binding
Adding a reference to an Object Library in your project is known as Early Binding. This makes all the objects from that Object Library available to be used programmatically in your VBA Project. As you have already added a reference to the Microsoft Word Object Library in the procedure above, we are able to ref directly to the Word Application and the Word Document when we declare the wdApp and wdDoc variables by using the New method.
However, you can still use the Object Library of a different application without adding a reference to it in your code. You would need to declare the wdApp and wdDoc variables as Objects and then you would need to use the CreateObject method rather than the New method to create a new instance of Microsoft Word.
Sub CreateWordDocument() Dim wdApp As Object Dim wdDoc As Object 'open word Set wdApp = CreateObject("Word.Application") 'create a document Set wdDoc = wdApp.Documents.Add 'type some text wdApp.Selection.TypeText Text:="Good morning Melanie!" 'show word on the screen wdApp.Visible = True End Sub
Using Late Binding is useful if the program that you are creating is going to be used on multiple machines. If a user has a different version of Microsoft Office than you do they may not have the correct file on their PC for the Object Library. For example if you are using Excel version 16, but they are using Excel version 15, they may end up with a missing reference.
You can easily fix this by removing the tick next to the missing reference, and then scrolling down to find the correct reference and ticking that instead.
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!