Programmatically add VBA project references without VBE or VBIDE..?

Question

This is a different twist to the old question about programmatically adding VBA project references: is there a way to programmatically add references, but...without VBE or an existing reference to VBIDE..?

Many Microsoft apps have the built-in application class VBE, with its own references collection: Application.VBE.ActiveVBProject.References. This includes Access, Excel, Word, PowerPoint, Visio, and probably many others. But some Microsoft apps don't have VBE, such as Outlook and Publisher. Plus, there are a variety of 3rd-party software packages with VBA, such as AutoCad and Corel Draw, but whether these have the VBE class is not known.

For those apps that don't have VBE, is there any fully-programmatic way to add a reference to VBIDE..? (assuming it's on the system)

I'm willing to entertain just about any idea except SendKeys. I've had thoughts of editing the document files at the character level. For instance, I'm sure if I drill down into the XML of an Excel file, I'd find the tags where the VBA references are stored. And for Access it would be SaveToText and LoadFromText, then dig through the text file. But for other apps, I know nothing or next to nothing of their file structures.

Pretty sure that would be a security hole.. Programmatic access to the VBIDE Extensibility library needs to be explicitly enabled... at least in Office. — Mathieu Guindon
I suggest that you consider using late binding instead. This will solve the problems you might encounter with references for much less effort. — this
<< I'm sure if I drill down into the XML of an Excel file, I'd find the tags where the VBA references are stored>> Probably not. The VBA code is stored in binary file format within the Office Open XML zip packages. — Cindy Meister

S Meaden S Meaden · Accepted Answer · 2018-07-14T16:34:36

Mathieu Guindon is right, VBA needs access granted to manipulate workbook projects, typically one grants this once for an installation and it rarely gets revoked. Cindy Meister is right the references are stored in the binary blob so drilling into the xml will not help.

To grant access, (this is on Excel 2016)

Click on File then on menu which flies in from left select Options, this displays Excel Options dialog.
On Excel Options dialog, on the left hand menu select Trust Center, this displays Trust Center pane.
In Trust Center pane, click Trust Center Settings, this display a Trust Center dialog.
in the Trust Center dialog, on the left hand menu, select Macro Settings ...
In the Macro Settings pane, check the checkbox "Trust access to the VBA project object model"

So the following code works for me because access is granted. The code adds the Microsoft Scripting Runtime library (that is my favourite non-core).

Sub Test()

    Dim objProj As Object 'VBIDE.VBProject
    Set objProj = ThisWorkbook.VBProject

    Dim vRefLoop As Variant

    Dim bIsMyReferencePresent As Boolean
    bIsMyReferencePresent = False
    For Each vRefLoop In objProj.References

        Debug.Print vRefLoop.Name
        If vRefLoop.Name = "Scripting" Then

            bIsMyReferencePresent = True

        End If

    Next

    If bIsMyReferencePresent = False Then

        objProj.References.AddFromFile "c:\windows\syswow64\scrrun.dll"

    End If

End Sub

Programmatically add VBA project references without VBE or VBIDE..?

2 Answers