Free Microsoft Excel 2013 Quick Reference

ThisWorkbook.VBProject.References

Hello all,
I have a spreadsheet that I wish to ensure that users have the TRUST
ACCESS TO VISUAL BASIC PROJECT checked. I am running a CALL
CHECKTRUST statement from the workbook_open statement. I have
computers running OfficeXP and Office2003.

When I run the code to test whether the option is checked on a machine
with Office 03 my Ref object returns a value. On a machine with
OfficeXP the Ref object returns "Nothing" even though the TRUST option
is checked. The call to AddReference in the code below removes
missing links and adds libraries based on GUIDS. Any thoughts about
making Checktrust to work on the OfficeXP machines appreaciated.
Thanks in Advance!

Sub Checktrust()
Dim Ref As Object
On Error Resume Next
Set Ref = ThisWorkbook.VBProject.References("Excel")
If Ref Is Nothing Then
MsgBox "Your Excel settings will need to be updated to run
this version of the eForm. " & vbNewLine & _
"1. From the menu, select TOOLS | MACRO | Security " &
vbNewLine & _
"2. Then go to the Trusted Sources tab, " & vbNewLine & _
"3. Check the box { TRUST ACCESS TO VISUAL BASIC PROJECT }
setting" & vbNewLine & _
"4. Exit Excel then open this file again. This is needed only
once", vbOKOnly
Else
Call AddReference
End If
endsub


2003

I am getting an runtime-error with
ThisWorkbook.VBProject.References.Item(i).FullPath
the error message claims not registered. That said, my routine obtains
the following fine:

..Item(i).Name (OK)
..Item(i).Description (OK)
..Item(i).FullPath (fails)

First, I am failing in my attempt to trap the error via: (Please help
on this also)

[ThisWorkbook.VBProject.References]

If IsError(.Item(i).FullPath) Then
Cells(i + 1, 3).Value = "Not in Registery Properly!!"
Else
Cells(i + 1, 3).Value = .Item(i).FullPath
End If

Second, using Tools>References, I have "Browsed" to
C:WINDOWSSystem32scrrun.dll clicked OK to no avail.

How can I force a clean recognition in the Registry so that I do not
get the error in the first place?

TIA EagleOne

Hi and Thanks in advance!

when I use this macro:

HTML Code:
Sub Thanks()
Dim Ref As Object  'Reference
  For Each Ref In ThisWorkbook.VBProject.References
    Debug.Print Ref.Name, Ref.Description, Ref.fullpath
  Next
End Sub
I get the References I have selected.

If one of the Ref I get is:

stdole
OLE Automation
C:WINDOWSsystem32stdole2.tlbIf unselect this reference manually.

How can I select-it again, but by vba?

Thanks!

Could be a .dll reference problem. when I ran CodeModule in the VBA help
search box it gave me no results. However, when I ran it in the Object
Browser, it shows up under VBComponents with Parent VBIDE. I am running
xl2003.

"PCLIVE" wrote:

> I have the following code that removes some lines from "ThisWorkbook". This
> seems to work fine from Excel 2000 and Excel 2002. However, it does not
> seem to work from Excel 2003. Is there any reason why this wouldn't work on
> Excell 2003 or later?
>
> Set CodeMod =
> ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
> With CodeMod
> StartLine = .ProcStartLine("Workbook_Open", vbext_pk_Proc)
> ProcLen = .ProcCountLines("Workbook_Open", vbext_pk_Proc)
> .DeleteLines StartLine, ProcLen
> End With
>
>
> Thanks in advance,
> Paul
>
>
>
>
>

Hi,

I'm currently trying to add the "Solver" reference using VBA code but for some reason my code keeps coming with errors. I have tried using the codes provided in the following thread but I still get the same error even though the code seems to work for others.

Autoload reference library by name

The error I get is:

Run-time error '1004':
Application-defined of object-defined error
The current code I have at the moment that I wish to use is:


	VB:
	
 
Dim k As String 
 
j = Application.Version 
k = "C:Program FilesMicrosoft OfficeOffice" & j & "LibrarySOLVERSOLVER32.Dll" 
Application.ThisWorkbook.VBProject.References.AddFromFile "C:Program FilesMicrosoft OfficeOffice14LibrarySOLVERSOLVER32.DLL" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am using Microsoft Excel and the VBA compiler to build this, so my theory is that it doesn't like being referred to as "VBProject".

The reason, I am doing this is because when I transfer my file to a computer with an older version of Solver, my program throws a fit and doesn't wish to work hence I require this code to make sure that the solver reference is always applied.

Many Thanks,

Vincent

Hello everybody!

I'm having a problem with a missing reference when moving an xlsm file into another computer.
After a little research I've found that the problem is the minor number.
For some reason, in my PC the reference is added with minor = 2 and the target PC needs it with minor=0 (I've checked the minor number in the target PC by fixing the reference by hand and listing the references again).

First of all, why's that difference with minor numbers? The workbook use 8 different references and only one fails to load because of that (I didn't check the other minor and major numbers because didn't fail, but I guess they're the same). The path to the reference (REFEDIT.DLL) is okay, the only difference is the minor number.

Anyway, I've tried to fix this with some code I found on the net and I can't remove the reference!


	VB:
	
 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 = "{00024517-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 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've run this in debug mode and stopped it after the Remove line was executed, went to see if the reference was actually removed but was still there so I guess that's why it isn't added again later (because it's already there).

What am I missing?

Hi folks. I've just realized that I had originally posted in the wrong subforum so maybe that's why my question wasn't being answered

further to my thread over here, I would like to know how to resolve the "can't enter break mode at this time" error.

Basically, I'm trying to add/remove an Outlook Reference in my vba code but everytime I get to the part of my code that adds or removes the Reference, I get the "can't enter break mode at this time" error.

I'm using this code

	VB:
	
 
Sub TestOutlookReference() 
    Dim obj As Object 
    Dim sFind As Boolean 
    Application.DisplayAlerts = False 
    For Each obj In ThisWorkbook.VBProject.References 
        If obj.Name = "Outlook" Then 
            [COLOR=red]ThisWorkbook.VBProject.References.Remove obj[/COLOR] 
             ' .References.Remove obj
            ThisWorkbook.VBProject.References.AddFromFile Application.Path & "msoutl.olb" 
            sFind = True 
            Exit For 
        End If 
    Next obj 
    [COLOR=red] If sFind = False Then ThisWorkbook.VBProject.References.AddFromFile "C:Program FilesMicrosoft
OfficeOFFICE14msoutl.olb"[/COLOR] 
    Application.DisplayAlerts = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've hightlighted the lines where I get the error.

As a note... the code does add/remove the Outlook Reference but the macro stops running after this error comes up.

Have looked all over the web but can't seem to find any resolution to this issue I have also tried adding the reference using the GUID but I still get the same error.

Would appreciate any/all help resoving this.

Thanks in advance

I am writing VBA Code to import data from an Access Database and using in Excel. I need to share this code with others in my office, so I need to Dynamically add the library "Microsoft ActiveX Data Objects 2.8 Library". I am using the following code, and it does not give me an error, but it also does not add the library.

	VB:
	
 AddReference() 
     'Include Reference Library
    On Error Resume Next 
    ThisWorkbook.VBProject.References.AddFromFile "C:Program FilesCommon FilesSystemADOmsado15.dll" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code is located in the same module that calls it, and is called at the beginning of the program. However, as soon as it runs, and the code calls for an ADODB object, it is not recognized, since the library did not add correctly.

I would greatly appreciate any help.
Thanks,
Chris Johnson

Hi, is there anyway to add a reference to solver with VBA code.

Not the main add in feature, but as a reference so that solver will work with code without causing an error.

I came accross this (thank you Jack):

	VB:
	
 JR_LoadSloverReference() 
     ' written by Jack in the UK - for www.OzGrid.com
     ' our web site - www.excel-it.com
     ' Excel Xp+  21st Aug 2005
     ' http://www.ozgrid.com/forum/showthread.php?t=38392
     
    If JR_TestSolverReference = False Then _ 
    ThisWorkbook.VBProject.References.AddFromFile Application.LibraryPath & "SOLVERSOLVER.XLA" 
    Exit Sub 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I put this inside 'workbook_open' will it load the reference.

i am trying to test for and eliminate "missing references"...

i have tried the code: (but i get runtime error 1004 application or object defined error--
at the line--"For i = 1 To ThisWorkbook.VBProject.References.Count")


	VB:
	
 CheckReferences() 
    Dim i As Integer 
     
    For i = 1 To ThisWorkbook.VBProject.References.Count 
        If ThisWorkbook.VBProject.References(i).IsBroken Then 
            ThisWorkbook.VBProject.References.Remove  _ 
            ThisWorkbook.VBProject.References(i) 
            Exit For 
        End If 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thank you...

Hi,

Ive got a spreadsheet that is essentially calculation based, reporting on a live view of a database. I have a small bit of code in it that allows an offline snapshot copy of itself to be saved so that it can be distributed.

The problem I have is that I want to remove the code from this snapshot to prevent it triggering Excels virus protection. In itself that's not a problem, the difficulty I have is that unless the reference to the VBIDE is removed it still triggers the virus protection.

In itself, the code to remove the reference works fine, but when it is combined witht the code that removes itself I get a compile error.

Any ideas?.


	VB:
	
) 
     'Sub triggered by the Before Close event that removes the command button and
     'removes the code if saving as output file
    Call RemoveCommandBtn 
    If RemoveMyCode = True Then 
        Call RemoveCode(ThisWorkbook.VBProject.Name) 
        Call DeleteRefLibraryVBIDE 
    End If 
End Sub 
 
Private Sub RemoveCode(ProjectName As String) 
     'Sub that parses through all the loaded code modules and removes them if they
     'are in the Report V.xls workbook
    Dim Project As VBProject 
    Dim Component As VBComponent 
    Dim CodeWindow As Variant 
    Dim line As Integer 
    Dim linecount As Integer 
    For Each Project In Application.VBE.VBProjects 
        If Project.Name = ProjectName Then 
            For Each Component In Project.VBComponents 
                If Not Component.CodeModule.CountOfLines = 0 Then 
                    Let linecount = Component.CodeModule.CountOfLines 
                    For line = 1 To linecount 
                        Component.CodeModule.DeleteLines (1) 
                    Next 
                    If Not Component.Type = vbext_ct_Document Then 'this is superfluious now as all the code is in the
'ThisWorkbook' Module
                        Project.VBComponents.Remove Project.VBComponents(Component.Name) 
                    End If 
                End If 
            Next 
        End If 
    Next 
End Sub 
 
Sub DeleteRefLibraryVBIDE() 
     'Deletes the reference to the VBIDE
    On Error Resume Next 
    With ThisWorkbook.VBProject.References 
        .Remove .Item("VBIDE") 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


hi all, i have some problem with the references in vba.

I have an excel add-in software which I want in incorporate into my spreadsheet. In order to call up and use the function of it, I need to add reference which points to that add-in. However when I open the spreadsheet in other computer which do not have that add-in software, the spreadsheet just messed up. Excel displays there is error opeing this worksheet, and repair the spreadsheet by removing all VBA code. Could anyone please kindly help on this?

I have also tried to add the reference by vba code


	VB:
	
ThisWorkbook.VBProject.References.addfromname 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but it seems not working, anyone has clue on it? thanks a lot.

Hi all,

I need to do this:
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

With ExcelSheet.Sheets(1).Columns("A:A")
.ColumnWidth = 15
' .HorizontalAlignment = xlRight
End With

I commented out the xlright line, becaue Access can't find it.

How do I add the excel library to access?

Its like this, but for Access I think...

Sub MakeLibrary_Word()

On Error Resume Next

ThisWorkbook.VBProject.References.AddFromGuid _
"{00020905-0000-0000-C000-000000000046}", 8, 0

End Sub

Thank all,

I have the following code that removes some lines from "ThisWorkbook". This
seems to work fine from Excel 2000 and Excel 2002. However, it does not
seem to work from Excel 2003. Is there any reason why this wouldn't work on
Excell 2003 or later?

Set CodeMod =
ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
With CodeMod
StartLine = .ProcStartLine("Workbook_Open", vbext_pk_Proc)
ProcLen = .ProcCountLines("Workbook_Open", vbext_pk_Proc)
.DeleteLines StartLine, ProcLen
End With

Thanks in advance,
Paul

Thought I'd chuck this on as it's something I use regularly and something I see a lot of other people mentioning in posts....

If you use code reliant on non-standard vb libraries then you can use the GUID for that library to reference it on the workbook being opened - then use the name of the library to remove it again when closing the file. Saves going in and ticking the references everytime!!

The below is what I use when working with ADO - I can not take credit for this as I found it on another forum but thought i'd post it up for others to use.

You can add these to your "ThisWorkbook" Sheet ... will install ADO (2.5 or above) and remove it on open and close of the workbook.


	VB:
	
 Workbook_Open() 
     
    On Error Resume Next 
     'Reference ADO Object Library using Major / Minor GUID
    Set ID = ThisWorkbook.VBProject.References 
    ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
) 
     
     'remove ADO reference
    Dim x As Object 
    n = Application.VBE.ActiveVBProject.References.Count 
     
    Do While Application.VBE.ActiveVBProject.References.Count > 0 And n > 0 
        On Error Resume Next 
        Set x = Application.VBE.ActiveVBProject.References.Item(n) 
        y = x.Name 
        If y = "ADODB" Then 
            Application.VBE.ActiveVBProject.References.Remove x 
        End If 
        n = n - 1 
    Loop 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If you don't know the GUID then reference it (by ticking it) and then run this to list all the active libraries and their GUIDs - adapted from NateO post on Mr E


	VB:
	
 Grab_References() 
     
    Dim n As Integer 
     
    Sheets.Add 
    ActiveSheet.Name = "GUIDS" 
     
    On Error Resume Next 
    For n = 1 To ActiveWorkbook.VBProject.References.Count 
        Cells(n,1) = ActiveWorkbook.VBProject.References.Item(n).Name 
        Cells(n,2) = ActiveWorkbook.VBProject.References.Item(n).Description 
        Cells(n,3) = ActiveWorkbook.VBProject.References.Item(n).GUID 
        Cells(n,4) = ActiveWorkbook.VBProject.References.Item(n).Major 
        Cells(n,5) = ActiveWorkbook.VBProject.References.Item(n).Minor 
        Cells(n,6) = ActiveWorkbook.VBProject.References.Item(n).fullpath 
    Next n 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope that proves as useful for others as it has for me.

Hi all
once i run this code , it gives me a wrong msg.....
why i donot know
any help is appreciated?

PHP Code:
 Sub test()

    On Error Resume Next
     'Reference Object Library using Major / Minor GUID
    Set ID = ThisWorkbook.VBProject.References
    ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5
    ID.AddFromGuid "{000204EF-0000-0000-C000-000000000046}"
    ID.AddFromGuid "{00020813-0000-0000-C000-000000000046}"
    ID.AddFromGuid "{00020430-0000-0000-C000-000000000046}"
    ID.AddFromGuid "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
    ID.AddFromGuid "{0002E157-0000-0000-C000-000000000046}"
    ID.AddFromGuid "{ktMsgBoxAddin}"
End Sub 
yours
h

I'm using the following code to detect if a referenced DLL is missing following an installation of the DLL.

This is used in a spreadsheet that is distributed to others and some (not many) are getting Error Number 1004.

I can't find anything in common with the computers that get this error. They are running at least Windows XP SP2 and at least Excel XP.

Anyone got a clue?
Dim x, y, z, Error1, Error2

On Error Resume Next
x = ThisWorkbook.VBProject.References.Item(ThisWorkbook.VBProject.References.Count - 0).Name
y = ThisWorkbook.VBProject.References.Item(ThisWorkbook.VBProject.References.Count - 1).Name
z = ThisWorkbook.VBProject.References.Item(ThisWorkbook.VBProject.References.Count - 2).Name
If Err.Number <> 0 Then
Error1 = True
Err.Clear
Else
Error1 = False
End If
Thanks,
Ernie

Does anyone know of a way of removing a MISSING reference through code?

The code below me moves are references a reference, but will not work if the
reference is MISSING

EA

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub CorrectOutlookReferenceLibrary()

're-references to Outlook 9.0

Dim xObject As Object

On Error Resume Next

Set xObject = ThisWorkbook.VBProject.References.Item("Outlook")

ThisWorkbook.VBProject.References.Remove xObject

ThisWorkbook.VBProject.References.AddFromFile "c:Program FilesMicrosoft
OfficeOfficemsoutl9.olb"

End Sub

I would like a macro un unselect a library reference. I have the following
code to set a reference, but I am curious as to how I would use a macro to
un-set a library. Thanks!

> This will set a reference to ADO:
>
> Sub AddADO()
>
> Dim r
>
> For Each r In ThisWorkbook.VBProject.References
> If r.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And r.Major = 2
> Then
> Exit Sub
> End If
> Next
>
> On Error GoTo NOTFOUND
>
> 'although usually the ADO version will be higher, doing Minor:=0 will
> install
> 'the higher version if available. On the other hand when you specify
> Minor:=5
> 'and only a lower version is available, this can't be installed
> '----------------------------------------------------------------------------
> ThisWorkbook.VBProject.References.AddFromGuid _
> GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _
> Major:=2, Minor:=0
> Exit Sub
>
> NOTFOUND:
> On Error GoTo 0
>
> End Sub
>

I have a worksheet that when opened, sets the reference MS Outlook 12.0 or 14.0 Object Library, whichever is needed according to MS Excel Version 2007 or 2010. What I need is a simple IF..THEN..ELSE statement that IF neither MS Outlook Object Library (12.0 or 14.0) is referenced THEN...... (the code I put here is shown below)....ELSE do nothing. Here's the code I use to load the proper Object per version.

  Private Sub Workbook_Open()

  Dim Reference As Object
  Dim OLB As String
  Dim Vmajor, Vminor
    
'  Load Microsoft Outlook 12.0 or 14.0 Object Library
     OLB14 = "{00062FFF-0000-0000-C000-000000000046}"
     Vmajor14 = 9
     Vminor14 = 4
   
     OLB12 = "{00062FFF-0000-0000-C000-000000000046}"
     Vmajor12 = 9
     Vminor12 = 3
     
       If Application.Version = 12 Then
         ThisWorkbook.VBProject.References.AddFromGuid OLB12, Vmajor12, Vminor12
       Else
       ThisWorkbook.VBProject.References.AddFromGuid OLB14, Vmajor14, Vminor14
     End If
    End Sub


Afternoon,

I have a workbook with two macros that don't get along. If the reference needed for the first is active, then the second crashes. So, I'm trying to manually set the reference within the first macro, then deactivate it at the end using a GUID.

Dim refMSO12 As References
Set refMSO12 = New References

'Adds in the Microsoft Office 12.0 Object Library
strGUID = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
ThisWorkbook.VBProject.References.AddFromGuid(strGUID, 2, 4) = refMSO12

.... Macro Content ....

With References
.Remove refMSO12
End With
It crashes on the Set refMSO12 = New References with a Run-Time Error 429: ActiveX component cannot create object. If I take that line out, it does actually add in the library, but also gives another errror of 438: Object doesn't support this property or method (doesn't highlight whats wrong either).

Thanks much for your help!

Carl

Hi,

I have an excel database which links into Outlook and Word via macros to automate sending of e-mails and creating documents, etc. Obviously, I have created the correct VBA references and things have been working fine for a while.

However, this is a shared workbook over a small number of machines and due to a recent upgrade, one of the machines is running Vista and Office 2007, whereas the rest run Office 2000 and NT.

All works well until the workbook is opened and saved on the Office 2007 machine as this then changes all the references to Word 12, Outlook 12, etc, instead of Word 9 as seen in Office 2000. Then, when an office 2000 machine opens the workbook, it has a compile error as it cannot find the office 12 references!!

I have created some code to fix this, which uses the AddFromGuid method, which works ok, e.g.:
ThisWorkbook.VBProject.References.AddFromGuid _
    "{00062FFF-0000-0000-C000-000000000046}", 9, 3
What I want to do is as excel opens, check for missing references and fix them automatically, which is pretty simple on the face of it. However, as the macro fails due to a compile error all macros stop running, therefore whatever I put in the Open_Workbook event doesn't run.

Does anyone know how to either stop the compiler running on start up, or a way of error handling the compile error??

Hi,

I have been trying to add a few reference libraries in my vba project. It works fine on my computer but I get this error message when I run it on someone else's computer:

Runtime error '1004':
Programmatic access to Visual Basic Project is not trusted.

The code that I used is:
ThisWorkBook.VBProject.References.AddFromFile ("C:Program FilesCommon FilesSystemADOmsado15.dll")

I know that I can avoid this error by going into Tools-> Macro -> Security and check the box that says trust VBE project (or something like that). However, I'd like to add the reference library without having the user to do that.

I also tried to use late binding but since I was intending to use late binding rather than adding a SAS reference library, some of the functions seems to not work...

Anyone have any thoughts on how I can solve this problem?

Thanks a lot for your time.

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

Hi all,

I want my program to automatically install all the applicable references and
I found a bit of code that looked like it would work however it didn't it
came up with 'error 1004 Programmatic access to Visual Basic Project is not
trusted'.How can I get arround this, below is my code.

Private Sub Workbook_Open()
'Visual Basic for Applications
ThisWorkbook.VBProject.References.AddFromFile ("C:Program FilesCommon
FilesMicrosoft SharedVBAVBA6VBE6.DLL")
'Microsoft Excel 11 Object Library
ThisWorkbook.VBProject.References.AddFromFile ("C:Program FilesMicrosoft
OfficeOFFICE11EXCEL.EXE")
'OLE Automation
ThisWorkbook.VBProject.References.AddFromFile
("C:WINDOWSsystem32STDOLE2.TLB")
'Microsoft Office 11 Object Library
ThisWorkbook.VBProject.References.AddFromFile ("C:Program FilesCommon
FilesMicrosoft SharedOFFICE11MSO.DLL")
'Microsoft Forms 2.0 Object Library
ThisWorkbook.VBProject.References.AddFromFile ("C:WINDOWSsystem32FM20.DLL")
'Microsoft Office Web Components 11.0
ThisWorkbook.VBProject.References.AddFromFile ("C:Program FilesCommon
FilesMicrosoft SharedWeb Components11OWC11.DLL")
'Microsoft Office Web Components 9.0
ThisWorkbook.VBProject.References.AddFromFile ("C:Program FilesMicrosoft
OfficeOfficeMSOWC.DLL")
End Sub

Thanks in adavnce

Jason