如何使用 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屋!