add a reference to Microsoft Visual Basic for Applications Extensibility" at run time

Hi
I've been on the WEB most of the day trying to figure this out. Is there code to "add a reference to Microsoft Visual Basic for Applications Extensibility" at run time.
I have this code that seems to require such a reference

' Modified from http://www.cpearson.com/excel/vbe.aspx
Public Sub DoModule8() 
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CreateEventProc("Activate", "Worksheet")
LineNum = LineNum + 1
.InsertLines LineNum, "Selection.OnAction = " & DQUOTE & "DoModule5"
.InsertLines LineNum, "ActiveSheet.Shapes(" & DQUOTE & "Button " & 1 & DQUOTE & ").Select"
.InsertLines LineNum, "Selection.OnAction = " & DQUOTE & "DoModule1"
.InsertLines LineNum, "ActiveSheet.Shapes(" & DQUOTE & "Button " & 2 & DQUOTE & ").Select"
End With
End Sub

And it results in this (after I set the reference manually)

Option Explicit
Private Sub Worksheet_Activate()
    ActiveSheet.Shapes("Button 2").Select
    Selection.OnAction = "DoModule1"
    ActiveSheet.Shapes("Button 1").Select
    Selection.OnAction = "DoModule5"
End Sub

Can I set the Reference pragmatically?
As always, your help is deeply appreciated.
John

If you add the reference manually (Tools -> References) when the file is distributed then assuming the same library is available on the other clients the library will be available.

You can add references via the Workbook Open event but unless there are version specific issues with the library itself it's often not necessary.

You could also just late bind and not worry about the reference.

Hi DonkeyOte

This is REALLY what I am asking

You can add references via the Workbook Open event

I was all over the Internet for hours looking for code to do just that. Alas, to know avail. Any idea what the code is to do that?

Hi romperstomper

I solved the issue by

just late bind and not worry about the reference

Thanks to both of you. I'll leave this as unsolved for a few days to see if I get feedback on the code question.

John

You can develop early-bound, test both early- and late-bound, and then leave it whichever way you like. For example,

#Const EarlyBound = True

Function Unique(r As Range, _
                Optional iMode As Long = 0, _
                Optional bCaseSensitive As Boolean = False, _
                Optional sSep As String = " ") As Variant

    ' blah, blah, blah ...   

    #If EarlyBound Then
        Dim dic     As Scripting.Dictionary
        Set dic = New Scripting.Dictionary
    #Else
        Dim dic     As Object
        Set dic = CreateObject("Scripting.Dictionary")
    #End If

    With dic
        ' ...

That way you get IntelliSense during development.

Programmatically it would be:

    ThisWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3

for example.

Hi romperstomper

After your last post, I searched on

VBProject.References.AddFromGuid

and found a wealth of information. Just didn't know what to search for. Thanks for that!

John