Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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 tutorial on referencing other VBA applications before reading any further.
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.
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:
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 |
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.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.