How to Automatically set Excel VBA References?

Question

I understand that the references I've used within my script require extra references to run all of the functions I am using but I now need this to be shared with many other people. They are all common references that everyone should have installed, just not enabled.

I have found this post1 with the same issue but I'm struggling to understand how to apply this to my own needs.

Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 'adUseClient

I think I just need to change the "ADODB" in the above code to the right name for the references I'm using but I don't know how to find it. ADODB doesn't seem to exist as an option anymore and I don't know if this was the exact name of the .olb/.dll or a specific part within a library.

I have also found Post2 to add the references, but it is very different from the first post and is a lot more complicated.

Can anyone point me in the right direction?

I'm currently using the following references:

Microsoft Office 16.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Outlook 16.0 Object Library
Microsoft Scripting Runtime
Microsoft Word 16.0 Object LIbrary
NucleusNativeMessagingLib

Post2 suggested adding Microsoft Visual Basic for Applications Extensibility 5.3 so that I could add the other references via code, but if I need to manually add that now, how do other uses add that Reference with code?

Any help with this is appreciated

All the best, Will

I believe the reference `Microsoft Office 16.0 Access database Engine object library` is the reference you can use for DAO/ADODB - it at least is the one I use - How do others get the code without the references? - VBA code is usually part of a Workbook? and references are saved as part of that workbook. - So how are you intending to distribute the code that would require manual reference activation? — Lord-JulianXLII, Jul 13 '23 at 11:12
This post might help, as well. Late binding avoids the need to establish references but because you must use enumerations instead of names, programming with late binding takes longer. You can program with early binding and then edit to use late binding after everything is working.https://stackoverflow.com/questions/26304768/what-is-the-scope-of-excel-add-ins-and-vba-references/26304942#26304942 — bugdrown, Jul 13 '23 at 11:21
The meaning of the post from the first link you posted is to use **Late Binding** **in your code**, which does not need references. The second one shows how to add them for the usual references. But what is `NucleusNativeMessagingLib`? You probably should use `AddFromFile` way. The references can be added from guide and from file. What reference couldn't you automatically install looking to the second link thread? I will try helping with that(s) one(s)... — FaneDuru, Jul 13 '23 at 11:26

FaneDuru · Answer 1 · 2023-07-15T11:04:32.930

In order to extract from your installation the necessary references to be added to "other people" installations, you can use the next function:

Function getReference(wb As Workbook, refName As String) As Variant
    Dim i As Long
    
    For i = 1 To wb.VBProject.References.count
        If wb.VBProject.References(i).name = refName Or _
                      wb.VBProject.References(i).Description = refName Then
             With wb.VBProject.References(i)
                getReference = Array(.name, .Description, .FullPath, .GUID, .Major, .Minor)
             End With
        End If
    Next i
End Function

It returns an array containing the reference name, description, full path, Guid and its Major/minor version. Sometimes, what you can see in the References window and it should be the reference description, is its name (very seldom) but the above function is able to solve this cases, too:

Sub TestgetReference()
  Dim refName As String: refName = "Microsoft Word 16.0 Object Library" 'use here a reference as you can see in the References window
                        refName = "mscorlib.dll"
                        'refName = "System_Windows_Forms" 'not having a description - this is its name
                        'refName = "PDFCreator"           'not having a description - this is its name
                        'refName = "test not existing if all above ones also exist in the tested installation..."
  Dim wb As Workbook: Set wb = ThisWorkbook 'use here the workbook you need
  Dim arrRef
  
  arrRef = getReference(wb, refName)
  If Not IsEmpty(arrRef) Then
    Debug.Print Join(arrRef, "|")
  Else
    MsgBox """" & refName & """ is not added to """ & wb.name & """ VBProject.", vbInformation, _
           "Reference missing"
  End If
End Sub

Of course, you have to use the references you need. I only played with mines when needed to define the function...

How to Automatically set Excel VBA References?

1 Answers1