如何使用 VBA 以编程方式添加引用 [英] How to add a reference programmatically using VBA

问题描述

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

我似乎无法弄清楚如何使用 VBA 以编程方式向 Excel 2007 添加引用.我知道如何手动完成:打开 VBE -->工具 -->参考资料 -->浏览 -->文件位置和名称.但这对我的目的不是很有用.我知道有一些方法可以在 .

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

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

'来源:Ken Puls子添加引用()'宏目的:使用 GUID 添加对项目的引用'参考图书馆Dim strGUID As String, theRef As Variant, i As Long'在下面更新您需要的 GUID.strGUID = "{00020905-0000-0000-C000-000000000046}"'设置为在出错时继续出错时继续下一步'删除任何缺失的引用For i = ThisWorkbook.VBProject.References.Count To 1 Step -1设置 theRef = ThisWorkbook.VBProject.References.Item(i)如果 theRef.isbroken = True 那么ThisWorkbook.VBProject.References.Remove theRef万一接下来我'清除所有错误，以便可以评估 GUID 添加的错误捕获错误清除'添加引用ThisWorkbook.VBProject.References.AddFromGuid _GUID:=strGUID, 主要:=1, 次要:=0'如果遇到错误，通知用户选择案例错误号案例是 = 32813'引用已在使用中.无需任何操作Case Is = vbNullString'参考添加没有问题其他情况'遇到未知错误，提醒用户MsgBox "尝试时遇到问题" &vbNewLine _&添加或删除此文件中的引用"&vbNewLine &请检查"_&您的 VBA 项目中的引用！"，vbCritical + vbOKOnly，错误！"结束选择出错时转到 0结束子

<小时>

方式二(直接引用dll)

此代码添加了对 Microsoft VBScript 正则表达式 5.5

的引用

选项显式子添加引用()Dim VBAEditor 作为 VBIDE.VBE将 vbProj 调暗为 VBIDE.VBProjectDim chkRef 作为 VBIDE.ReferenceDim BoolExists As Boolean设置 VBAEditor = Application.VBE设置 vbProj = ActiveWorkbook.VBProject'~~>检查是否已添加Microsoft VBScript 正则表达式 5.5"对于 vbProj.References 中的每个 chkRef如果 chkRef.Name = "VBScript_RegExp_55" 然后布尔存在 = 真转到清理万一下一个vbProj.References.AddFromFile "C:\WINDOWS\system32\vbscript.dll\3"清理:如果 BoolExists = True 那么MsgBox "引用已经存在"别的MsgBox "引用添加成功"万一设置 vbProj = 无设置 VBAEditor = 无结束子

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

EDIT 被 mischab1 打败 :)

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

解决方案

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

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

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

这篇关于如何使用 VBA 以编程方式添加引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！