奥米特
有两种方法可以通过VBA将引用添加到工程中

• • 1）**使用GUID
• • 2）**直接引用dll。

让我涵盖两者。
但首先有三件事你需要注意

• • a）**应启用宏
• • b）**在安全设置中，确保选中“信任对Visual Basic项目的访问”

• • c）**您已手动设置了对“Microsoft Visual Basic for Applications Extensibility”对象的引用

• • 方式1（使用GUID）**

我通常避免这种方式，因为我必须在注册表中搜索GUID。我讨厌LOL。关于GUID here的更多信息。

• • 主题：通过代码添加VBA参考库**
• • 链接**：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

• • 方式2（直接引用dll）**

此代码添加了对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

• • 注意**：我没有添加错误处理。建议在实际代码中使用它：）
• • 编辑**被mischab1打败：）

使用VBA添加参照有两种方法。.AddFromGuid(Guid, Major, Minor)和.AddFromFile(Filename)。哪一个是最好的取决于你试图添加一个引用。我几乎总是使用.AddFromFile，因为我引用的东西是其他Excel VBA项目，它们不在Windows注册表中。
您所显示的示例代码将添加对代码所在工作簿的引用。我通常不认为这样做有任何意义，因为90%的情况下，在您可以添加引用之前，代码已经因为引用丢失而无法编译。（如果它没有编译失败，那么您可能正在使用后期绑定，并且您不需要添加引用。
如果您在运行代码时遇到问题，可能有两个问题。
1.为了方便地使用VBE的对象模型，您需要添加对 Microsoft Visual Basic for Application Extensibility 的引用。（VBIDE）
1.要运行Excel VBA代码以更改VBProject中的任何内容，您需要 * 信任对VBA Project对象模型的访问 *。（在Excel 2010中，它位于信任中心-宏设置中。）
除此之外，如果你能更清楚地说明你的问题是什么，或者你想做的事情是不起作用的，我可以给予你一个更具体的答案。

浏览注册表中的guid或使用路径，哪种方法最好。如果不再需要浏览注册表，那么使用guid不是更好的方法吗？Office并不总是安装在同一目录中。可以手动更改安装路径。版本号也是路径的一部分。我从来没有预料到微软会在推出64位处理器之前将“（x86）”添加到“程序文件”中。如果可能的话，我会尽量避免使用路径。
下面的代码是从Siddharth Rout的答案中派生出来的，其中有一个额外的函数来列出活动工作簿中使用的所有引用。如果我在更高版本的Excel中打开工作簿会怎么样？如果不调整VBA代码，工作簿是否仍能正常工作？我已经检查了office 2003和2010的guid是相同的。让我们希望微软在未来的版本中不会改变指南。
参数0，0（来自. AddFromGuid）应该使用引用的最新版本（我还没有测试过）。
你的想法是什么？当然，我们无法预测未来，但我们可以做些什么来证明我们的代码版本？

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 > .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'" & vbCrLf & vbCrLf & 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, """ & .Item(i).GUID & """, """ & .Item(i).Name & """"
        Next i
    End With
End Sub

上面的代码不再需要引用“Microsoft Visual Basic for Applications Extensibility”对象。

===============

• • 编辑于2023 - 06 - 17：**
    ===============

我无法抗拒这个挑战：-）
我已经修改了代码，以便向AddRef Sub提供版本信息。major和minor参数是可选的，以实现向后兼容。
我注意到vbscript.dll是唯一一个在版本中重用guid的引用，我没有找到另一个。尽管Microsoft在文档中写道0.0版本将安装最新版本，但这似乎不适用于VBScript v 5.5不幸的是，我再也找不到那份文件了。
就像我之前说的，有些事情是无法预测的。
如果来自另一个版本的相同guid已经存在，Sub将要求确认替换引用并在即时窗口中显示已删除引用的完整路径：

AddRef thisworkbook, "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", "VBScript_RegExp_10", 1, 0       ' install v. 1.0  
The reference to C:\Windows\System32\vbscript.dll\3 was removed.  
  
AddRef thisworkbook, "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", "VBScript_RegExp_55", 5, 5       ' install v. 5.5  
The reference to C:\Windows\System32\vbscript.dll\2 was removed

对于具有不同guid的相同库的引用，这同样会失败。为了避免错误，例如，我们想要添加ADODB v。6.1和ADODB v. 2.8仍然存在，我们需要首先删除2.8。
对于“Microsoft ActiveX对象库”，我发现了6个版本，所有这些版本都有不同的GUID。当我添加对其中任何一个的引用时，如果已经存在另一个版本，则会显示错误。为了防止这种情况发生，我们必须尝试删除所有旧版本。不幸的是，我们必须自己维护旧引用的列表：

DelRef thisworkbook, "{00000200-0000-0010-8000-00AA006D2EA4}"                                   ' remove reference to ADODB v. 2.0
DelRef thisworkbook, "{00000201-0000-0010-8000-00AA006D2EA4}"                                   ' remove reference to ADODB v. 2.1
DelRef thisworkbook, "{00000205-0000-0010-8000-00AA006D2EA4}"                                   ' remove reference to ADODB v. 2.5
DelRef thisworkbook, "{00000206-0000-0010-8000-00AA006D2EA4}"                                   ' remove reference to ADODB v. 2.6
DelRef thisworkbook, "{EF53050B-882E-4776-B643-EDA472E8E3F2}"                                   ' remove reference to ADODB v. 2.7
DelRef thisworkbook, "{2A75196C-D9EB-4129-B803-931327F72D5C}"                                   ' remove reference to ADODB v. 2.8
AddRef thisworkbook, "{B691E011-1797-432E-907A-4D8C69339129}", "ADODB", 6, 1                    ' install v. 6.1

我修改了“Sub AddRef”并添加了“Sub DelRef”和“Sub DebugPrintExistingRefsWithVersion”

Sub AddRef(wbk As Workbook, sGuid As String, sRefName As String, Optional varMajor As Variant, Optional varMinor As Variant)
    Dim i As Integer
    On Error GoTo EH
    With wbk.VBProject.References
        If IsMissing(varMajor) Or IsMissing(varMinor) Then
           For i = 1 To .Count
               If .Item(i).Name = sRefName Then
                  Exit For
               End If
           Next i
           If i > .Count Then
              .AddFromGuid sGuid, 0, 0 ' 0,0 should pick the latest version installed on the computer
           End If
        Else
           For i = 1 To .Count
               If .Item(i).Guid = sGuid Then
                  If .Item(i).Major = varMajor And .Item(i).Minor = varMinor Then
                     Exit For
                  Else
                     If vbYes = MsgBox(.Item(i).Name & " v. " & .Item(i).Major & "." & .Item(i).Minor & " is currently installed," & vbCrLf & "do you want to replace it with v. " & varMajor & "." & varMinor, vbQuestion + vbYesNo, "Reference already exists") Then
                        DelRef wbk, sGuid
                     Else
                        i = 0
                        Exit For
                     End If
                  End If
               End If
           Next i
           If i > .Count Then
              .AddFromGuid sGuid, varMajor, varMinor
           End If
        End If
    End With
EX: Exit Sub
EH: MsgBox "Error in 'AddRef' for guid:" & sGuid & " " & vbCrLf & vbCrLf & Err.Description
    Resume EX
    Resume ' debug code
End Sub

Public Sub DelRef(wbk As Workbook, sGuid As String)
    Dim oRef As Object
    For Each oRef In wbk.VBProject.References
        If oRef.Guid = sGuid Then
           Debug.Print "The reference to " & oRef.FullPath & " was removed."
           Call wbk.VBProject.References.Remove(oRef)
        End If
    Next
End Sub

Public Sub DebugPrintExistingRefsWithVersion()
    Dim i As Integer
    With Application.ThisWorkbook.VBProject.References
        For i = 1 To .Count
            Debug.Print "   'AddRef wbk, """ & .Item(i).Guid & """, """ & .Item(i).Name & """" & Space(30 - Len("" & .Item(i).Name)) & " ' install the latest version"
            Debug.Print "    AddRef wbk, """ & .Item(i).Guid & """, """ & .Item(i).Name & """, " & .Item(i).Major & ", " & .Item(i).Minor & Space(30 - Len(", " & .Item(i).Major & ", " & .Item(i).Minor) - Len("" & .Item(i).Name)) & " ' install v. " & .Item(i).Major & "." & .Item(i).Minor
        Next i
    End With
End Sub

针说，我真的不喜欢VBA引用，我会尽量避免他们在可能的情况下。

以下是如何以编程方式获取GUID的方法！然后，您可以使用这些guid/filepaths和上面的答案来添加引用！
参考：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 > References, then run this code to get GUID etc.
    Dim rw As Long, ref
    With ThisWorkbook.Sheets(1)
        .Cells.Clear
        rw = 1
        .Range("A" & rw & ":D" & rw) = Array("Reference","Version","GUID","Path")
        For Each ref In ThisWorkbook.VBProject.References
            rw = rw + 1
            .Range("A" & rw & ":D" & rw) = Array(ref.Description, _
                   "v." & ref.Major & "." & ref.Minor, ref.GUID, ref.FullPath)
        Next ref
        .Range("A:D").Columns.AutoFit
    End With
End Sub

下面是相同的代码，但如果您不想将工作表专用于输出，则会打印到终端。

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" & " | " & "Full path to reference" & " | " & "Reference GUID" 

For i = 1 To ThisWorkbook.VBProject.References.Count 
  With ThisWorkbook.VBProject.References(i) 
    Debug.Print .Name & " | " & .FullPath  & " | " & .GUID 
  End With 
Next i 
On Error GoTo 0 
End Sub