How to add a reference programmatically using VBA

by

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

enter image description here

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

enter image description here

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 Puls
Sub 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 0
End Sub

Way 2 (Directly referencing the dll)

This code adds a reference to Microsoft VBScript Regular Expressions 5.5

Option Explicit

Sub 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 = Nothing
End Sub

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

EDIT Beaten by mischab1 🙂

More Related Contents:

  1. “Can’t find Project or Library” for standard VBA functions
  2. VBA comma separated cells, needt last entry only
  3. Test or check if sheet exists
  4. Microsoft Excel ActiveX Controls Disabled?
  5. What is the default scope of worksheets and cells and range?
  6. Does the VBA “And” operator evaluate the second argument when the first is false?
  7. Are there disadvantages in putting code into Userforms instead of modules?
  8. Save each sheet in a workbook to separate CSV files
  9. Is VBA an OOP language, and does it support polymorphism?
  10. Export each sheet to a separate csv file
  11. Detect if range is empty
  12. Checking if a worksheet-based checkbox is checked
  13. Assign keyboard shortcut to run procedure
  14. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  15. Uncheck all checkboxes across entire workbook via CommandButton
  16. return csv file as recordset
  17. VBA workbooks.Close without being prompted to if the user wants to save?
  18. VBA equivalent to Excel’s mod function
  19. Copy a selected range to another worksheet
  20. Excel macro – paste only non empty cells from one sheet to another
  21. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  22. Pointers needed for speeding up nested loop macro in VBA
  23. ‘1004’: “The sort reference is not valid.”
  24. Create hyperlink to another sheet
  25. Reducing WithEvent declarations and subs with VBA and ActiveX
  26. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  27. Excel VBA – select multiple columns not in sequential order
  28. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  29. Excel – Using COUNTIF/COUNTIFS across multiple sheets/same column
  30. Delete all data rows from an Excel table (apart from the first)

Leave a Comment