How to add a reference programmatically

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 -- gt  Tools -- gt  References -- gt  browse --  gt  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

User · Answer

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.

In order to easily use the VBE's object model, you need to add a reference to Microsoft Visual Basic for Application Extensibility. (VBIDE)
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.

User · Answer

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   amp  vbNewLine            amp   add or remove a reference in this file   amp  vbNewLine  amp   Please check the              amp   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          gt  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

User · Answer

Browsing the registry for guids or using paths  which method is best  If browsing the registry is no longer necessary  won t it be the better way to use guids  Office is not always installed in the same directory  The installation path can be manually altered  Also the version number is a part of the path  I could have never predicted that Microsoft would ever add   x86   to  Program Files  before the introduction of 64 bits processors  If possible I would try to avoid using a path   The code below is derived from Siddharth Rout s answer  with an additional function to list all the references that are used in the active workbook  What if I open my workbook in a later version of Excel  Will the workbook still work without adapting the VBA code  I have already checked that the guids for office 2003 and 2010 are identical  Let s hope that Microsoft doesn t change guids in future versions    The arguments 0 0  from  AddFromGuid  should use the latest version of a reference  which I have not been able to test    What are your thoughts  Of course we cannot predict the future but what can we do to make our code version proof    Sub AddReferences wbk As Workbook        Run DebugPrintExistingRefs in the immediate pane  to show guids of existing references     AddRef wbk    00025E01-0000-0000-C000-000000000046     DAO      AddRef wbk    00020905-0000-0000-C000-000000000046     Word      AddRef wbk    91493440-5A91-11CF-8700-00AA0060263B     PowerPoint  End Sub  Sub AddRef wbk As Workbook  sGuid As String  sRefName As String      Dim i As Integer     On Error GoTo EH     With wbk VBProject References         For i   1 To  Count             If  Item i  Name   sRefName Then                Exit For             End If         Next i         If i  gt   Count Then             AddFromGuid sGuid  0  0   0 0 should pick the latest version installed on the computer         End If     End With EX  Exit Sub EH  MsgBox  Error in  AddRef    amp  vbCrLf  amp  vbCrLf  amp  err Description     Resume EX     Resume   debug code End Sub  Public Sub DebugPrintExistingRefs       Dim i As Integer     With Application ThisWorkbook VBProject References         For i   1 To  Count             Debug Print      AddRef wbk       amp   Item i  GUID  amp            amp   Item i  Name  amp               Next i     End With End Sub   The code above does not need the reference to the  Microsoft Visual Basic for Applications Extensibility  object anymore

User · Answer

Here is how to get the Guid s programmatically   You can then use these guids filepaths with an above answer to add the reference   Reference  http   www vbaexpress com kb getarticle php kb id 278  Sub ListReferencePaths    Lists path and GUID  Globally Unique Identifier  for each referenced library   Select a reference in Tools  gt  References  then run this code to get GUID etc      Dim rw As Long  ref     With ThisWorkbook Sheets 1           Cells Clear         rw   1          Range  A   amp  rw  amp    D   amp  rw    Array  Reference   Version   GUID   Path           For Each ref In ThisWorkbook VBProject References             rw   rw   1              Range  A   amp  rw  amp    D   amp  rw    Array ref Description                        v    amp  ref Major  amp       amp  ref Minor  ref GUID  ref FullPath          Next ref          Range  A D   Columns AutoFit     End With End Sub   Here is the same code but printing to the terminal if you don t want to dedicate a worksheet to the output   Sub ListReferencePaths      Macro purpose   To determine full path and Globally Unique Identifier  GUID    to each referenced library   Select the reference in the Tools References   window  then run this code to get the information on the reference s library  On Error Resume Next  Dim i As Long   Debug Print  Reference name   amp         amp   Full path to reference   amp         amp   Reference GUID    For i   1 To ThisWorkbook VBProject References Count    With ThisWorkbook VBProject References i       Debug Print  Name  amp         amp   FullPath   amp         amp   GUID    End With  Next i  On Error GoTo 0  End Sub

[excel] How to add a reference programmatically

Examples related to excel

Examples related to vba

Examples related to reference