VBA Reference

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.

 

Click OK.

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.

OR

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.