如何在Excel 2010中更改默认的VBA引用

Question

如何在Excel 2010中更改默认的VBA引用

excelexcel-2010vba

4

我想在personal.xlsb中添加一个vba函数，但这个函数里有一个ADODB.Connection对象。

我可以通过（在VBA编辑器中）选择工具 -> 引用，然后勾选“Microsoft ActiveX Data Objects 6.0 Library”复选框来解决问题。

我的问题是，如何使“Microsoft ActiveX Data Objects 6.0 Library”成为我的默认引用之一，以便每次启动Excel时都可用？

- Graham

3

使用LateBinding吗？例如 Set rs = CreateObject("ADODB.Recordset") 这样你就不需要添加任何引用。 - Siddharth Rout

3个回答

3

这里是一种替代方法。可以作为按钮或“on open”命令添加。虽然并不太完美，但是用最少的代码完成了工作：

Sub AddReference()

On Error GoTo ErrHandler:

'VBA
References.AddFromGuid "{000204EF-0000-0000-C000-000000000046}", 0, 0
'Access
References.AddFromGuid "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}", 0, 0
'stdole
References.AddFromGuid "{00020430-0000-0000-C000-000000000046}", 0, 0
'DAO
References.AddFromGuid "{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}", 0, 0
'Excel
References.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 0, 0
'Scripting
References.AddFromGuid "{420B2830-E718-11CF-893D-00A0C9054228}", 0, 0
'IWshRuntimeLibrary
References.AddFromGuid "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B}", 0, 0
'Office
References.AddFromGuid "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}", 0, 0

ErrHandler:
Select Case Err.Number
Case 32813
Resume Next
Case Is <> 0
 MsgBox "Run-time error '" & Err & "' : " & vbNewLine & vbNewLine & Error(Err)
Case Else
End Select

End Sub

- Mumford

0

你安装的最后一个版本将成为默认版本。

如果你想让2010成为默认版本，那么你必须卸载两个版本，然后先安装2013，再安装2010。 Iamhhp☻♥

- Hassanpour

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Scott Holtzman · Accepted Answer

虽然Siddharth的解决方案非常方便，但我想提供这些函数，这是我推出的一个工具，我们不断接到来自用户的电话，这些电话与其Excel版本中未正确检查VBE中的引用有关，可能由于多种原因。

只是将其作为一种选择和需要查看的内容。它具有一些非常特定于项目的功能，但您可以轻松修改它以适应您的需求。

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

这个函数将打印您的参考信息，以帮助您找到与之对应的dll文件，但是必须先检查参考信息才能打印出来。

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