2

I've found how to add a reference programmatically with VBA,
This explains how to add object references programmatically using the name of the Library,
with the example "VBScript_RefExp_55".

My question is how do I find this reference name to use in this code for different object libraries?
Such as the PowerPoint Library for example?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Will Mitchell
  • 21
  • 1
  • 4
  • 2
    Why do you _need_ to add references programmatically? If you set a reference in a workbook then it stays with the workbook even if someone else uses it. – SierraOscar Mar 31 '17 at 09:53
  • I do. The macro will be saved as a .bas and will be on a cloud for people to access and use when required. I am trying to make this process as simple as possible. This however is not the question. How to do it has been answered in my link. I need to know how to find the correct references. – Will Mitchell Mar 31 '17 at 09:58
  • 2
    Add it manually and then loop through the references and check the names (like the sample code in the link). Late binding would be far easier and more reliable though. – Rory Mar 31 '17 at 10:11
  • Okay I'll do that. Thanks, seems a bit off there is no library of references somewhere on the interwebs. – Will Mitchell Mar 31 '17 at 10:20
  • @Rory : Just a poke, to ask if I missed some steps in converting the code into Late Binding! ;) – R3uK Mar 31 '17 at 12:19
  • @R3uK no, I think you have it covered! – Rory Mar 31 '17 at 12:21
  • @Rory : Thx for taking time to take a look! ;) – R3uK Mar 31 '17 at 12:24

1 Answers1

3

I use this to get the info on my references :

Private Sub ListProjectReferencesList()
    Dim i                   As Long
    Dim VBProj              As Object  'VBIDE.VBProject
    Dim VBComp              As Object 'VBIDE.VBComponent
    Set VBProj = Application.VBE.ActiveVBProject
    Dim strTmp              As String
    On Error Resume Next
    For i = 1 To VBProj.References.Count
        With VBProj.References.Item(i)
            Debug.Print "Description: " & .Description & vbNewLine & _
                        "FullPath: " & .FullPath & vbNewLine & _
                        "Major.Minor: " & .Major & "." & .Minor & vbNewLine & _
                        "Name: " & .Name & vbNewLine & _
                        "GUID: " & .GUID & vbNewLine & _
                        "Type: " & .Type
            Debug.Print "-------------------"
        End With 'VBProj.References.Item(i)
    Next i
End Sub

And generally, I prefer to add it with GUID rather than name.

But as pointed out by @Rory,
you should use Late Binding rather than adding References programmatically!

Why?

Because in order to add them programmatically, your users will have to go into :

  1. Options of the Application (Excel, ...) from which it's launched
  2. Trust Center
  3. Trust Center Settings
  4. Macro Settings tab
  5. Tick Trust access to the VBA project object model check box
  6. OK
  7. OK

So you'd better finish your code with references, then :

  1. Remove the references
  2. Change all declarations using those librairies to Dim ??? As Object
  3. Check if you have Option Explicit at the top of the module (add it if not)
  4. Look for app-specific variables (Option Explicit should throw an message on them)
  5. Test your code a lot
  6. Export module to be used by others!
R3uK
  • 14,417
  • 7
  • 43
  • 77