How to add a reference programmatically using VBA

Ommit

There are two ways to add references via VBA to your projects

1) Using GUID

2) Directly referencing the dll.

Let me cover both.

But first these are 3 things you need to take care of

a) Macros should be enabled

b) In Security settings, ensure that "Trust Access To Visual Basic Project" is checked

c) You have manually set a reference to `Microsoft Visual Basic for Applications Extensibility" object

Way 1 (Using GUID)

I usually avoid this way as I have to search for the GUID in the registry... which I hate LOL. More on GUID here.

Topic: Add a VBA Reference Library via code

Link: http://www.vbaexpress.com/kb/getarticle.php?kb_id=267

'Credits: Ken PulsSub AddReference()     'Macro purpose:  To add a reference to the project using the GUID for the     'reference library    Dim strGUID As String, theRef As Variant, i As Long     'Update the GUID you need below.    strGUID = "{00020905-0000-0000-C000-000000000046}"     'Set to continue in case of error    On Error Resume Next     'Remove any missing references    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1        Set theRef = ThisWorkbook.VBProject.References.Item(i)        If theRef.isbroken = True Then            ThisWorkbook.VBProject.References.Remove theRef        End If    Next i     'Clear any errors so that error trapping for GUID additions can be evaluated    Err.Clear     'Add the reference    ThisWorkbook.VBProject.References.AddFromGuid _    GUID:=strGUID, Major:=1, Minor:=0     'If an error was encountered, inform the user    Select Case Err.Number    Case Is = 32813         'Reference already in use.  No action necessary    Case Is = vbNullString         'Reference added without issue    Case Else         'An unknown error was encountered, so alert the user        MsgBox "A problem was encountered trying to" & vbNewLine _        & "add or remove a reference in this file" & vbNewLine & "Please check the " _        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"    End Select    On Error GoTo 0End Sub

Way 2 (Directly referencing the dll)

This code adds a reference to Microsoft VBScript Regular Expressions 5.5

Option ExplicitSub AddReference()    Dim VBAEditor As VBIDE.VBE    Dim vbProj As VBIDE.VBProject    Dim chkRef As VBIDE.Reference    Dim BoolExists As Boolean    Set VBAEditor = Application.VBE    Set vbProj = ActiveWorkbook.VBProject    '~~> Check if "Microsoft VBScript Regular Expressions 5.5" is already added    For Each chkRef In vbProj.References        If chkRef.Name = "VBScript_RegExp_55" Then            BoolExists = True            GoTo CleanUp        End If    Next    vbProj.References.AddFromFile "C:\WINDOWS\system32\vbscript.dll\3"CleanUp:    If BoolExists = True Then        MsgBox "Reference already exists"    Else        MsgBox "Reference Added Successfully"    End If    Set vbProj = Nothing    Set VBAEditor = NothingEnd Sub

Note: I have not added Error Handling. It is recommended that in your actual code, do use it :)

EDIT Beaten by mischab1 :)

There are two ways to add references using VBA. .AddFromGuid(Guid, Major, Minor) and .AddFromFile(Filename). Which one is best depends on what you are trying to add a reference to. I almost always use .AddFromFile because the things I am referencing are other Excel VBA Projects and they aren't in the Windows Registry.

The example code you are showing will add a reference to the workbook the code is in. I generally don't see any point in doing that because 90% of the time, before you can add the reference, the code has already failed to compile because the reference is missing. (And if it didn't fail-to-compile, you are probably using late binding and you don't need to add a reference.)

If you are having problems getting the code to run, there are two possible issues.

1. In order to easily use the VBE's object model, you need to add a reference to Microsoft Visual Basic for Application Extensibility. (VBIDE)
2. In order to run Excel VBA code that changes anything in a VBProject, you need to Trust access to the VBA Project Object Model. (In Excel 2010, it is located in the Trust Center - Macro Settings.)

Aside from that, if you can be a little more clear on what your question is or what you are trying to do that isn't working, I could give a more specific answer.

Browsing the registry for guids or using paths, which method is best. If browsing the registry is no longer necessary, won't it be the better way to use guids?Office is not always installed in the same directory. The installation path can be manually altered. Also the version number is a part of the path.I could have never predicted that Microsoft would ever add '(x86)' to 'Program Files' before the introduction of 64 bits processors.If possible I would try to avoid using a path.

The code below is derived from Siddharth Rout's answer, with an additional function to list all the references that are used in the active workbook.What if I open my workbook in a later version of Excel? Will the workbook still work without adapting the VBA code?I have already checked that the guids for office 2003 and 2010 are identical. Let's hope that Microsoft doesn't change guids in future versions.

The arguments 0,0 (from .AddFromGuid) should use the latest version of a reference (which I have not been able to test).

What are your thoughts? Of course we cannot predict the future but what can we do to make our code version proof?

Sub AddReferences(wbk As Workbook)    ' Run DebugPrintExistingRefs in the immediate pane, to show guids of existing references    AddRef wbk, "{00025E01-0000-0000-C000-000000000046}", "DAO"    AddRef wbk, "{00020905-0000-0000-C000-000000000046}", "Word"    AddRef wbk, "{91493440-5A91-11CF-8700-00AA0060263B}", "PowerPoint"End SubSub AddRef(wbk As Workbook, sGuid As String, sRefName As String)    Dim i As Integer    On Error GoTo EH    With wbk.VBProject.References        For i = 1 To .Count            If .Item(i).Name = sRefName Then               Exit For            End If        Next i        If i > .Count Then           .AddFromGuid sGuid, 0, 0 ' 0,0 should pick the latest version installed on the computer        End If    End WithEX: Exit SubEH: MsgBox "Error in 'AddRef'" & vbCrLf & vbCrLf & err.Description    Resume EX    Resume ' debug codeEnd SubPublic Sub DebugPrintExistingRefs()    Dim i As Integer    With Application.ThisWorkbook.VBProject.References        For i = 1 To .Count            Debug.Print "    AddRef wbk, """ & .Item(i).GUID & """, """ & .Item(i).Name & """"        Next i    End WithEnd Sub

The code above does not need the reference to the "Microsoft Visual Basic for Applications Extensibility" object anymore.