如何更改excel 2010中的默认vba引用 [英] How to change the default vba references in excel 2010

问题描述

我想将一个vba函数添加到personal.xlsb，但是这个函数里面有一个ADODB.Connection对象。

我可以通过（在VBA编辑器）选择工具 - >引用，然后选中Microsoft ActiveX数据对象6.0库框

我的问题是，我如何使Microsoft ActiveX数据对象6.0图书馆我的默认引用之一，以便每当我启动excel时总是可用的

解决方案

虽然Siddharth的解决方案非常方便，我以为我会从我推出的工具中提供这些功能，因为许多可能的原因，我们不断收到与Excel中版本的VBE中没有正确的引用相关的调用。 p>

只是把它作为一个选项和一些东西来看。有一些非常项目特定的功能，但是您可以很容易地修改它以满足您的需求。

 功能ValidateReferences （）As Boolean 
'确保用户已经安装了正确的引用，以通过API下载数据
 
 Dim arrDescription（）As String，arrGUID（）As String 
 Dim arrMajor ）As Long，arrMinor（）As Long 
 Dim strMessage As String 
 
 
 ValidateReferences = True 
 
'删除任何破损的引用
对于i = ThisWorkbook.VBProject.References.Count到1步-1 
如果ThisWorkbook.VBProject.References.Item（i）.IsBroken Then ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References.Item（i）
下一个
 
'将尝试为用户添加引用
 arrGUID（）= Split（{2A75196C-D9EB-4129-B803-931327F72D5C}，{E6C9285A-7A87- 407A-85E7-D77A70C100F5}，{45A929B3-E493-4173-B6E5-0CD42041C6DC}，{F24B7FA2-8FB9-48B7-825F-7C9F4A82F917}，{7A80DAB5-1F6 1-4F9A-A596-561212ACD705}，{18142BD6-1DE1-412B-991C-31C7449389E6}，{C3ED6DC2-BED0-4599-A170-B1E1E32C627A}，，，vbTextCompare）'{2A75196C-D9EB-4129-B803 -931327F72D5C} 
 arrDescription（）= Split（Microsoft ActiveX数据对象2.8库，myDummyRef COM 3.8.0，myDummyRef COM 3.8.0，myDummyRef COM 3.8.0，myDummyRef 3.6.0库，myDummyRef，myDummyRef库 ，，，vbTextCompare）'Microsoft ActiveX数据对象2.8库
 ReDim arrMajor（6）
 arrMajor（0）= 0'0 
 arrMajor（1）= 3'3 
 arrMajor（2）= 3'3 
 arrMajor（3）= 3'3 
 arrMajor（4）= 3'3 
 arrMajor（5）= 1'1 
 arrMajor（6）= 1'1→> ADODB = 2 
 ReDim arrMinor（6）
 arrMinor（0）= 0 
 arrMinor（1）= 8 
 arrMinor（2）= 8 
 arrMinor（3） = 8 
 arrMinor（4）= 6 
 arrMinor（5）= 0 
 arrMinor（6）= 0' - > ADODB = 8 
 
对于i = LBound（arrGUID（））到UBound（arrGUID（））
错误GoTo ErrCheck 
如果i = 0那么'adodb不工作在AddFromFile 。可以在所有引用中使用文件中的add - >稍后重构
 ThisWorkbook.VBProject.References.AddFromFile（C：\Program Files\Common Files\System\ado\msado15.dll）
 Else 
 ThisWorkbook.VBProject .Reference.AddFromGuid arrGUID（i），arrMajor（i），arrMinor（i）
 End If 
 Next 
 
如果ValidateReferences = False则MsgBox以下引用不能添加到VB项目中：&右（strMessage，Len（strMessage） -  2）&  &安培; vbNewLine& vbNewLine&有关详细信息，请参阅Connectivity_Help选项卡上的相应DLL文件。 
 
退出函数
 
 ErrCheck：
选择案例Err.Number 
案例= 32813'引用已被使用，没有报告
 Case Else 
 ValidateReferences = False 
 strMessage = strMessage& ，& arrDescription（i）
结束选择
简历Next 
 
结束功能
  

此功能将打印您的参考信息，以帮助您找到它的dll，但必须检查该引用以打印它。

<$ p $对于i = ThisWorkbook.VBProject.References.Count到1步-1
Dim strName As String，strPath As String，strGUID as String
strName = ThisWorkbook.VBProject.References.Item（i）.name
Debug.Print strName
strPath = ThisWorkbook.VBProject.References .Item（i）.FullPath
Debug.Print strPath
strGUID = ThisWorkbook.VBProject.References.Item（i）.GUID'认为这是正确的，可能需要更改
下一个

End Sub

I want to add a vba function to personal.xlsb, but this function has an ADODB.Connection object in it.

I can resolve that by (in the VBA editor) selecting tools -> references and then checking the box "Microsoft ActiveX Data Objects 6.0 Library"

My question is, how do I make "Microsoft ActiveX Data Objects 6.0 Library" one of my default references so that it is always available whenever I start up excel?

解决方案

While Siddharth's solution is very handy, I thought I would offer these functions from a tool I rolled out where we were constantly getting calls that related to the fact the user didn't have the proper references checked in the VBE in their Excel version, for many possible reasons.

Just throwing it out as an option and something to look it. There are some very project specific features in it, but you could modify it to work with your needs probably pretty easily.

Function ValidateReferences() As Boolean
'makes sure that user has the proper references installed for downloading data through API

Dim arrDescription() As String, arrGUID() As String
Dim arrMajor() As Long, arrMinor() As Long
Dim strMessage As String


ValidateReferences = True

'removes any broken references
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
    If ThisWorkbook.VBProject.References.Item(i).IsBroken Then ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References.Item(i)
Next

'will attempt to add the reference for the user
arrGUID() = Split("{2A75196C-D9EB-4129-B803-931327F72D5C},{E6C9285A-7A87-407A-85E7-D77A70C100F5},{45A929B3-E493-4173-B6E5-0CD42041C6DC},{F24B7FA2-8FB9-48B7-825F-7C9F4A82F917},{7A80DAB5-1F61-4F9A-A596-561212ACD705},{18142BD6-1DE1-412B-991C-31C7449389E6},{C3ED6DC2-BED0-4599-A170-B1E1E32C627A}", ",", , vbTextCompare) ' {2A75196C-D9EB-4129-B803-931327F72D5C}
arrDescription() = Split("Microsoft ActiveX Data Objects 2.8 Library,myDummyRef COM 3.8.0,myDummyRef COM 3.8.0,myDummyRef COM 3.8.0,myDummyRef 3.6.0 Library,myDummyRef,myDummyRef Library", ",", , vbTextCompare) 'Microsoft ActiveX Data Objects 2.8 Library
ReDim arrMajor(6)
    arrMajor(0) = 0 '0
    arrMajor(1) = 3 '3
    arrMajor(2) = 3 '3
    arrMajor(3) = 3 '3
    arrMajor(4) = 3 '3
    arrMajor(5) = 1 '1
    arrMajor(6) = 1 '1 -> ADODB = 2
ReDim arrMinor(6)
    arrMinor(0) = 0
    arrMinor(1) = 8
    arrMinor(2) = 8
    arrMinor(3) = 8
    arrMinor(4) = 6
    arrMinor(5) = 0
    arrMinor(6) = 0 '-> ADODB = 8

For i = LBound(arrGUID()) To UBound(arrGUID())
    On Error GoTo ErrCheck
    If i = 0 Then 'adodb not working on AddFromFile. I could use add from file on all references, perhaps? -> refactor later
        ThisWorkbook.VBProject.References.AddFromFile ("C:\Program Files\Common Files\System\ado\msado15.dll")
    Else
        ThisWorkbook.VBProject.References.AddFromGuid arrGUID(i), arrMajor(i), arrMinor(i)
    End If
Next

If ValidateReferences = False Then MsgBox "The following references could not be added to the VB Project: " & Right(strMessage, Len(strMessage) - 2) & "." & vbNewLine & vbNewLine & "Please refer to 4. Appropriate DLL Files on the 'Connectivity_Help' tab for more information."

Exit Function

ErrCheck:
    Select Case Err.Number
        Case Is = 32813 'reference already in use, nothing to report
        Case Else
            ValidateReferences = False
            strMessage = strMessage & ", " & arrDescription(i)
    End Select
    Resume Next

End Function

This function will print your reference information to help you find the dll for it, but the reference has to be checked for it to print it.

Sub print_ref_path()

Dim i As Integer

For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
    Dim strName As String, strPath As String, strGUID as String
    strName = ThisWorkbook.VBProject.References.Item(i).name
    Debug.Print strName
    strPath = ThisWorkbook.VBProject.References.Item(i).FullPath
    Debug.Print strPath
    strGUID = ThisWorkbook.VBProject.References.Item(i).GUID 'think this is right, might need to change
Next

End Sub

这篇关于如何更改excel 2010中的默认vba引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！