This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Creating references programmatically in VBA
Did you know that you can create and manage references from within VBA code? This article gives you ideas on how to do just that. This blog is part of a complete online VBA tutorial. For the best way to learn to program in VBA, attend a Wise Owl VBA course.

Posted by Andy Brown on 03 January 2012

Creating and Using References in Code

It is a little-known fact (well, I keep forgetting it!) that you can create and manage your references within VBA.

You need to make sure that you are comfortable with the earlier parts of this blog series on referencing other VBA applications before reading any further.

Referencing the VBA Extensibility Library

To work with references from within VBA code, you first need to create a reference (it's all a bit circular!) to the Microsoft Visual Basic for Applications Extensibility library:

Create a reference to the Microsoft VBA Extensibility Library

To do this, select Tools -> References from the VBA code editor menu, and tick the box shown above.

Sample Code to List References

To give you an idea of what's possible, here is a simple program to list out all of the references in the current code project:

Sub ListReferences()

'create a variable to refer to each reference

Dim ref As Reference

'list out all of the current references

For Each ref In Application.VBE.ActiveVBProject.References

Debug.Print ref.Name, ref.Description

Next ref

End Sub

Here VBE refers to the Visual Basic editor within which you are writing code. The program above might show the following output:

The truncated output from the above program in the immediate window

If you have problems running the code above, you may need to tell your application to trust the VBA project object model (Andrew has blogged in full on macro security too).

Useful Reference Properties

A reference object has a fair few properties:

Some of the properties of a reference - the most useful are listed below.

Here are some of the most useful properties:

Property	What it gives	Example
Name	A short description of the reference	stdole
Description	A longer description	OLE Automation
Guid	A unique identifier for the reference	{00020430-0000-0000-C000-000000000046}
FullPath	Where the object library is	C:\Windows\system32\stdole2.tlb
IsBroken	Whether the reference is broken	False

Adding References Programmatically

You can use the AddFromGUID or AddFromFile methods to add a new reference. For example:

Sub ListReferences()

'try to add a reference to Word

On Error GoTo CanNotAddWord

Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Office 2010\Office14\MSWORD.OLB"

Exit Sub

CanNotAddWord:

MsgBox "Can not reference Word"

End Sub

The code above would create a reference to the Word 2010 object library, provided that the relevant object library is in the folder given.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR