如何加载VBA库引用并在同一过程中使用它? [英] How can I load a VBA library reference and use it in the same procedure?

问题描述

我有一个用于Excel的VBA脚本，该脚本在活动工作簿中添加了一个小程序.最终版本将添加一个自动保存工作簿备份副本的过程.

I have a VBA script for Excel that adds a small procedure in the active workbook. The final version will add a procedure that auto-saves backup copies of the workbook.

代码需要我可以手动添加的Microsoft Visual Basic for Applications Extensibility 5.3库，但是我对拥有一个脚本可以将该库添加到Excel中然后使用它感兴趣.

The code requires the Microsoft Visual Basic for Applications Extensibility 5.3 library which I can add manually, but I'm interested in having a single script that can add that library into Excel and then use it.

以下是将库引用添加到Excel的代码.可以.

Here's the code that adds the library reference to Excel. It works.

On Error Resume Next    ' If library already referenced, ignore the error
    ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3
On Error GoTo 0         ' Resume normal error handling

下面是使用此库将VBA过程添加到活动工作簿中的代码.它有效，但前提是必须先将必需的库引用添加到Excel中:

Below is the code that uses this library to add a VBA procedure into the active workbook. It works, but only if the required library reference has first been added to Excel:

Sub AddProcedureToModule()
' This script adds a sample VBA procedure to the active workbook

    ' Add Library Reference:  Microsoft Visual Basic for Applications
    '   Extensibility 5.3
    On Error Resume Next    ' If library already referenced, ignore the error
        ThisWorkbook.VBProject.References.AddFromGuid _
            GUID:="{0002E157-0000-0000-C000-000000000046}", _
            Major:=5, Minor:=3
    On Error GoTo 0         ' Resume normal error handling

    Dim VBProj As VBIDE.VBProject   ' requires Ref: MS VB for Apps Extensibility 5.3
    Dim VBComp As VBIDE.VBComponent ' requires Ref: MS...5.3
    Dim CodeMod As VBIDE.CodeModule ' requires Ref: MS...5.3

    Dim LineNum As Long
    Const DQUOTE = """" ' one " character

    Set VBProj = ActiveWorkbook.VBProject       ' requires Ref: MS...5.3
    Set VBComp = VBProj.VBComponents("Module1") ' requires Ref: MS...5.3
    Set CodeMod = VBComp.CodeModule             ' requires Ref: MS...5.3

    ' Insert code into workbook
    With CodeMod
        LineNum = .CountOfLines + 1
        .InsertLines LineNum, "Public Sub SayHello()"
        LineNum = LineNum + 1
        .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
        LineNum = LineNum + 1
        .InsertLines LineNum, "End Sub"
    End With
End Sub

当我尝试在单个过程中将两者结合时，就会出现问题. Excel引发编译错误未定义用户定义的类型".如果我理解正确，我的代码将使用称为早期绑定的东西. Excel在执行任何代码之前先查找该库，然后找不到它.

The problem arises when I try to combine the two in a single procedure. Excel throws compile error "User defined type not defined". If I understand correctly, my code uses something called early binding. Excel looks for the library before any code is executed and can't find it.

答案可能是修改我的代码以使用后期绑定，以使Excel在脚本的一部分执行完毕且库可用后才寻找该库.

The answer may be to adapt my code to use late binding so that Excel won't look for that library until after part of the script has executed and the library is available.

使用这篇文章作为指导，我修改了部分代码，使其看起来像这样:

Using this post as a guide I modified part of the code to look like this:

Dim VBProj As Object
Dim VBComp As Object
Dim CodeMod As Object
Dim LineNum As Long
Const DQUOTE = """" ' one " character

Set VBProj = CreateObject("ActiveWorkbook.VBProject")
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = CreateObject("VBProj.VBComponents(""Module1"")")
Set CodeMod = CreateObject("VBComp.CodeModule")

Excel抛出错误运行时错误'429':ActiveX组件无法创建对象".

Excel threw error "Run-time error '429': ActiveX component can't create object".

有什么主意我可以修改此代码以利用后期绑定，或者以其他方式加载库引用并在同一过程/模块中运行其余代码吗?

Any idea how I can either modify this code to utilize late binding, or otherwise load the library reference and run the rest of the code in the same procedure/module?

推荐答案

您的原始代码中有一个错误的前提:

There is a false premise in your original code:

Set VBProj = ActiveWorkbook.VBProject       ' requires Ref: MS...5.3
Set VBComp = VBProj.VBComponents("Module1") ' requires Ref: MS...5.3
Set CodeMod = VBComp.CodeModule             ' requires Ref: MS...5.3

此代码本身不需要任何引用.需要引用的是上面的变量声明:

This code does not require any references per se. What does require a reference is the variable declaration above:

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent 
Dim CodeMod As VBIDE.CodeModule 

将它们替换为As Object后，您可以直接运行其余代码，而无需添加引用.确保顶部有Option Explicit来捕获您可能正在使用的所有未声明的常量.

As soon as you have replaced them with As Object you can run the rest of the code as is without adding a reference. Make sure you have Option Explicit at the top to catch any now-undeclared constants you might be using.

但是，我建议您考虑使用另一种方法，例如将要放入文件中的代码移到文件可以引用的添加项中.自动将VBA代码添加到文件是一项安全问题，因为这是传播恶意软件的常见方法，可能会关闭防病毒功能，并且需要

However I would advise you consider a different approach, such as moving the code you want to putting into files into an adding that the files can refer to. Automatically adding VBA code to files is a security concern because it is a common way of spreading malware, it might set antiviruses off, and it requires the Trust access to the VBA project object model setting to be set in the user interface (which I would personally never set).

这篇关于如何加载VBA库引用并在同一过程中使用它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！