How to add a reference programmatically using VBA

Question

I've written a program that runs and messages Skype with information when if finishes. I need to add a reference for Skype4COM.dll in order to send a message through Skype. We have a dozen or so computers on a network and a shared file server (among other things). All of the other computers need to be able to run this program. I was hoping to avoid setting up the reference by hand. I had planned on putting the reference in a shared location, and adding it programmatically when the program ran.

I can't seem to figure out how to add a reference programmatically to Excel 2007 using VBA. I know how to do it manually: Open VBE --> Tools --> References --> browse --_> File Location and Name. But that's not very useful for my purposes. I know there are ways to do it in Access Vb.net and code similar to this kept popping up, but I'm not sure I understand it, or if it's relevant:

ThisWorkbook.VBProject.References.AddFromGuid _     GUID:="{0002E157-0000-0000-C000-000000000046}", _     Major:=5, Minor:=3 

So far, in the solutions presented, in order to add the reference programmatically I will need to add a reference by hand and change the Trust Center - which is more than just adding the reference. Though I guess if I follow through with the solutions proposed I will be able to add future references programmatically. Which probably makes it worth the effort.

Any further thoughts would be great.

Answer 1

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

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

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 :)

Answer 2

There are two ways to add references using VBA. .AddFromGuid(Guid, Major, Minor) and .AddFromFile(Filename). Which one is best depends on what you are trying to add a reference to. I almost always use .AddFromFile because the things I am referencing are other Excel VBA Projects and they aren't in the Windows Registry.

The example code you are showing will add a reference to the workbook the code is in. I generally don't see any point in doing that because 90% of the time, before you can add the reference, the code has already failed to compile because the reference is missing. (And if it didn't fail-to-compile, you are probably using late binding and you don't need to add a reference.)

If you are having problems getting the code to run, there are two possible issues.

1. In order to easily use the VBE's object model, you need to add a reference to Microsoft Visual Basic for Application Extensibility. (VBIDE)
2. In order to run Excel VBA code that changes anything in a VBProject, you need to Trust access to the VBA Project Object Model. (In Excel 2010, it is located in the Trust Center - Macro Settings.)

Aside from that, if you can be a little more clear on what your question is or what you are trying to do that isn't working, I could give a more specific answer.