Excel加载项从ActiveWorkbook调用子程序 [英] Excel Add-in to call subroutine from ActiveWorkbook

问题描述

我创建了一个加载项，将工作表添加到工作簿并导入多个模块。然后，我想让加载项运行一个现在处于活动工作簿的子例程。这是我到目前为止，我得到运行时错误'438'：对象不支持此属性或方法。

I created an Add-In that adds a sheet to a Workbook and imports multiple modules. I would then like for the Add-in to run a Subroutine that is now in Active Workbook. This is what I have so far, and I'm getting Run-time error '438': Object doesn't support this property or method.

什么是正确的语法（如果甚至可以完成）。感谢。

What is the correct syntax (if it can even be done). Thanks.

' Class name is EventClassModule
Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    If Wb.Name = "Just To Test.xls" Then
        Wb.Sheets.Add Type:="C:\TestGLPage.xls"
    fname = Dir("C:\Users\Me\Desktop\BAS\*.*", vbNormal)
    While fname <> ""
       If Right(fname, 3) = "frm" Or Right(fname, 3) = "bas" Or Right(fname, 3) = "cls" Then
       ActiveWorkbook.VBProject.VBComponents.Import "C:\Users\Me\Desktop\BAS\" & fname
       End If
       fname = Dir()  'get the next file
    Wend
    Call Application.Workbooks("Just To Test.xls").starter
    End If
End Sub

推荐答案

VBA是不动态语言。当您在运行时更改代码或代码对象的名称时，VBA必须重新编译这些模块才能访问更改。您也可以在进行此类更改后发现断点无法正常工作。

VBA is not a dynamic language. When you alter the code or the names of code objects at runtime VBA has to recompile those modules before the changes can be accessed. You may find breakpoints don't work properly after you've made such changes as well.

此重新编译会自动立即发生，但不能从已经存在的代码中访问执行。您需要让Excel重新输入VBA代码。

This recompilation happens automatically and immediately, but is not accessible from code that is already executing. You need to get Excel to re-enter the VBA code.

您可能可以使用Application.Run了解，但我可能会使用Application.OnTime如果您不需要启动程序来阻止调用（在示例代码中似乎是这种情况），请更安全。

You may be able to get away with using Application.Run, but I would probably use Application.OnTime to be safer if you don't need starter to be a blocking call (which appears to be the case in your example code).

为了清楚起见，调用子例程时使用Application.Run或Application.OnTime，您无法使用模块名称进行限定。然而，您可以使用工作簿的名称使用bang语法对其进行限定。例如。 Application.RunBook1.xlsx！SubNameToBeCalled

Just for clarity, when calling a subroutine using Application.Run or Application.OnTime you cannot qualify it using the module name. You can however qualify it using the workbook's name using bang syntax. Eg. Application.Run "Book1.xlsx!SubNameToBeCalled"

我创建了一个空白的工作簿
我创建了两个模块

I created a blank workbook I created two modules

Public Sub RunMe()
    MsgBox "Test!"
End Sub

主要

Main

Public Sub Run()
  ActiveWorkbook.VBProject.VBComponents.Import "C:\Temp\Imported.bas"
  ' Showing how to do it with Run
  Application.Run ThisWorkbook.Name & "!RunMe"
  ' Showing the safer way with OnTime
  Application.OnTime Now, ThisWorkbook.Name & "!RunMe"
End Sub

然后我将导入模块导出为C ：\Temp\Imported.bas，并将其从项目中删除。

I then exported the "Imported" module to "C:\Temp\Imported.bas" and removed it from the project.

这篇关于Excel加载项从ActiveWorkbook调用子程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！