vba 如何以编程方式添加引用

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/9879825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 12:50:28  来源:igfitidea点击:

How to add a reference programmatically

excelvbareference

提问by Ommit

I've written a program that runs and messages Skype with information when if finishes. I need to add a reference for Skype4COM.dllin 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.

我编写了一个程序,该程序在完成时运行并向 Skype 发送信息。我需要添加一个参考Skype4COM.dll以便通过 Skype 发送消息。我们在网络上有十多台计算机和一个共享文件服务器(除其他外)。所有其他计算机都需要能够运行此程序。我希望避免手动设置参考。我曾计划将引用放在共享位置,并在程序运行时以编程方式添加它。

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.netand code similar to this kept popping up, but I'm not sure I understand it, or if it's relevant:

我似乎无法弄清楚如何使用 VBA 以编程方式向 Excel 2007 添加引用。我知道如何手动完成:打开VBE --> Tools --> References --> browse --_> File Location and Name. 但这对我的目的不是很有用。我知道在Access Vb.net 中有一些方法可以做到这一点,并且类似的代码不断弹出,但我不确定我是否理解它,或者它是否相关:

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.

任何进一步的想法都会很棒。

回答by Siddharth Rout

Ommit

忽略

There are two ways to add references via VBA to your projects

有两种方法可以通过 VBA 将引用添加到您的项目中

1)Using GUID

1)使用 GUID

2)Directly referencing the dll.

2)直接引用dll。

Let me cover both.

让我来涵盖两者。

But first these are 3 things you need to take care of

但首先这些是你需要注意的三件事

a)Macros should be enabled

a)应启用宏

b)In Security settings, ensure that "Trust Access To Visual Basic Project" is checked

b)在安全设置中,确保选中“信任对 Visual Basic 项目的访问”

enter image description here

在此处输入图片说明

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

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

enter image description here

在此处输入图片说明

Way 1 (Using GUID)

方式一(使用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.

我通常会避免这种方式,因为我必须在注册表中搜索 GUID……我讨厌 LOL。更多关于 GUID在这里

Topic: Add a VBA Reference Library via code

主题:通过代码添加VBA参考库

Link: http://www.vbaexpress.com/kb/getarticle.php?kb_id=267

链接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)

方式二(直接引用dll)

This code adds a reference to Microsoft VBScript Regular Expressions 5.5

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

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

注意:我没有添加错误处理。建议在您的实际代码中使用它:)

EDITBeaten by mischab1:)

编辑被击败mischab1:)

回答by mischab1

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 .AddFromFilebecause the things I am referencing are other Excel VBA Projects and they aren't in the Windows Registry.

有两种使用 VBA 添加引用的方法。.AddFromGuid(Guid, Major, Minor).AddFromFile(Filename)。哪个最好取决于您尝试添加引用的内容。我几乎总是使用,.AddFromFile因为我引用的东西是其他 Excel VBA 项目,它们不在 Windows 注册表中。

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

您显示的示例代码将添加对代码所在工作簿的引用。我通常认为这样做没有任何意义,因为 90% 的时间,在您添加引用之前,代码已经无法编译因为缺少引用。(如果它没有编译失败,则您可能正在使用后期绑定,并且不需要添加引用。)

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.)
  1. 为了轻松使用 VBE 的对象模型,您需要添加对Microsoft Visual Basic for Application Extensibility的引用。(VBIDE)
  2. 为了运行可以更改 VBProject 中任何内容的 Excel VBA 代码,您需要信任对 VBA 项目对象模型的访问。(在 Excel 2010 中,它位于信任中心 - 宏设置中。)

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.

除此之外,如果您能更清楚地了解您的问题是什么,或者您尝试做的事情是行不通的,我可以给出更具体的答案。

回答by hennep

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.

浏览注册表以获取 guid 或使用路径,哪种方法最好。如果不再需要浏览注册表,这不是使用 guid 的更好方法吗?Office 并不总是安装在同一目录中。安装路径可以手动修改。版本号也是路径的一部分。在 64 位处理器推出之前,我从来没有预料到微软会在“程序文件”中添加“(x86)”。如果可能,我会尽量避免使用路径。

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.

下面的代码源自 Siddharth Rout 的答案,并带有一个附加功能来列出活动工作簿中使用的所有引用。如果我在更高版本的 Excel 中打开我的工作簿怎么办?如果不修改 VBA 代码,工作簿是否仍然有效?我已经检查过 Office 2003 和 2010 的指南是否相同。让我们希望 Microsoft 在未来版本中不会更改 guid。

The arguments 0,0 (from .AddFromGuid) should use the latest version of a reference (which I have not been able to test).

参数 0,0(来自 .AddFromGuid)应该使用最新版本的参考(我无法测试)。

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 > .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

The code above does not need the reference to the "Microsoft Visual Basic for Applications Extensibility" object anymore.

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

回答by Chad Crowe

Here is how to get the Guid's programmatically! You can then use these guids/filepaths with an above answer to add the reference!

这是以编程方式获取 Guid 的方法!然后,您可以使用这些 guids/filepaths 和上述答案来添加参考!

Reference: http://www.vbaexpress.com/kb/getarticle.php?kb_id=278

参考: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

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" & " | " & "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