External References to Libraries

[David Lewis]

Here's some code I wrote to handle various aspects of linking to
external libraries. I found existing material in books and on the web
to be a little tough going to separate fact from fancy. But it was
ultimately useful with a bit of work on my part, so I thought I'd pay
it forward by making it easier for others.

One way to do it, of course, is manual -- in the VBA Editor, use tools

references to set references to libraries and/or browse for them.

If you want to be sure, however, that a given application always has
the non-default references you want linked in, do the following...

1. Set it manually, using tools > references.

2. Run the GetCurrentExternalReferences sub below, which will place
many of the parameters of existing libraries into the spreadsheet; this
will include the one you want, since you just added it manually.

3. Using the values in the worksheet, create new constants from the
Name and GUID of the library you want, like the two existing ones

4. Write specific calls to AddExternalReference with those constants;
don't forget the Major and Minor versions from the worksheet table,
unless they are 1 and 0 respectively. For example, for regular
expression handling, you want major.minor = 5.5, since it is a superset
of the older 1.0.

5. Embed AddExternalReference and your newly constructed call in your
code, ideally at
some initialization point for the application.

Note that AddExterenalReference won't try to re-add a reference that is
already there, which would cause a runtime error, so you can call it
with impunity.

Here's the code. Feedback is welcome -- though this seems to work, I
may not have all aspects of it completely figured out.

Const RegExpName As String = "VBScript_RegExp_55"
Const RegExpGUID As String = "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}"
Const ScriptingName As String = "Scripting"
Const ScriptingGUID As String =
"{420B2830-E718-11CF-893D-00A0C9054228}"

Sub AddRegExpReference55()
Call AddExternalReference(RegExpName, RegExpGUID, 5, 5)
End Sub

Sub AddScriptingReference()
Call AddExternalReference(ScriptingName, ScriptingGUID)
End Sub

Sub DeleteRegExpReference() '-- for illustration; little real use
Call DeleteExternalReference(RegExpName)
End Sub

Sub GetCurrentExternalReferences()
Dim I As Integer
Dim R As Range

'-- Workbooks.Add '-- uncomment to put results in new workbook
SetColumnTitles _
"Name", "GUID", "Major", "Minor", "Description", _
"Type", "VBE.Version", "FullPath"
SetColumnWidths 20, 40, 6, 6, 40, 6, 12, 60
For I = 1 To ThisWorkbook.VBProject.References.Count
Cells(I + 1, 1) = ThisWorkbook.VBProject.References(I).Name
Cells(I + 1, 2) = ThisWorkbook.VBProject.References(I).GUID
Cells(I + 1, 3) = ThisWorkbook.VBProject.References(I).Major
Cells(I + 1, 4) = ThisWorkbook.VBProject.References(I).Minor
Cells(I + 1, 5) =
ThisWorkbook.VBProject.References(I).Description
Cells(I + 1, 6) = ThisWorkbook.VBProject.References(I).Type
Cells(I + 1, 7) =
ThisWorkbook.VBProject.References(I).VBE.Version
Cells(I + 1, 8) = ThisWorkbook.VBProject.References(I).FullPath
Next I
End Sub

Sub AddExternalReference( Name As String, GUID As String, Optional
Major As Integer = 1, Optional Minor As Integer = 0)
Dim RI As Integer
Dim Found As Boolean
Dim RName As String
Found = False
With ThisWorkbook.VBProject
For RI = 1 To .References.Count
RName = .References(RI).Name
If Name = RName Then
Found = True
Exit For
End If
Next
If Not Found Then .References.AddFromGuid GUID, Major, Minor
End With
End Sub

Sub DeleteExternalReference(Name As String) '-- not sure why you'd ever
do this
Dim RI As Integer
With ThisWorkbook.VBProject
For RI = 1 To .References.Count
If Name = .References(RI).Name Then
.References.Remove .References(RI)
Exit For
End If
Next
End With
End Sub

'-- A couple of useful utilities for making worksheet headers

Sub SetColumnTitles(ParamArray VS() As Variant)
Dim R As Integer
For R = 0 To UBound(VS)
With Cells(1, R + 1)
.Value = VS(R)
.Interior.ColorIndex = 15
.Font.Bold = True
End With
Next R
End Sub

Sub SetColumnWidths(ParamArray VN() As Variant)
Dim R As Integer
For R = 0 To UBound(VN)
Columns(R + 1).ColumnWidth = VN(R)
Next R
End Sub