Excel 加载项如何响应任何工作表中的事件? [英] How can an Excel Add-In respond to events in any worksheet?

问题描述

我们的工作簿是服务器生成的 SpreadsheetML，其中不能包含任何 VBA 代码.不幸的是，创建本机 Excel 文件或 Excel 2007 XML 文件也不是一种选择.

因此，我有一个 Excel 加载项(VBA，而不是 XLL)，我们的每个用户都会安装它来添加我们工作簿需要的一些额外的 UDF 等.

这很好用，但现在我需要一个宏，每次用户更改任何单元格中的文本时都需要执行，无论他们当时正在使用什么工作簿.>

到目前为止，我已经在我的加载项中创建了一个新的类模块 (SheetChangeHandler)，代码如下:

选项显式私人 WithEvents 应用程序作为应用程序私有子类_Initialize()设置应用程序 = 应用程序结束子Private Sub App_SheetChange(ByVal Sh As Object, ByVal Source As Range)Debug.Print已更改"出错时转到完成App.EnableEvents = FalseDoWorkOnChangedStuff Sh，来源结束:App.EnableEvents = True结束子

在我的加载项中，我添加了一行来实例化新类:

公共 MySheetHandler 作为新 SheetChangeHandler

我的理解是，这应该使 Excel 为所有打开的工作簿发送 Add-In all SheetChange 事件，而这些工作簿不需要包含任何宏代码.

但它不起作用......没有调试行，并且当我更改任何工作表上的单元格时，不会调用我的 DoWorkOnChangedStuff 代码.

有什么想法吗?

解决方案

不要在 dim 语句中使用 New 关键字.您告诉它在需要时实例化该类，但是您再也不会引用它，因此永远不需要它.相反:

公共 MySheetHandler 作为 SheetChangeHandler子自动_打开设置 MySheetHandler = 新 SheetChangeHandler结束子

Auto_Open(在启动时运行)中的那一行将实例化该类.

Our workbooks are server-generated SpreadsheetML, which cannot include any VBA code. Creating native Excel files or Excel 2007 XML files is also not an option, unfortunately.

So, I have an Excel Add-In (VBA, not XLL) that each of our users installs to add some extra UDFs, etc. that our workbooks need.

This works great, but now I need to have a macro that needs to execute every time the user changes the text in any cell, regardless what workbook they are actively using at the time.

So far, I've created a new Class module (SheetChangeHandler) in my Add-In, with the following code:

Option Explicit
Private WithEvents App As Application

Private Sub Class_Initialize()
    Set App = Application
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Debug.Print "Changed"
    On Error GoTo Finish
    App.EnableEvents = False
    DoWorkOnChangedStuff Sh, Source
Finish:
    App.EnableEvents = True
End Sub

In my Add-In, I've added a line to instantiate the new class:

Public MySheetHandler As New SheetChangeHandler

My understanding is that this should make Excel send the Add-In all SheetChange events for all open workbooks, without those workbooks needing to contain any macro code.

But it's not working... no Debug lines, and my DoWorkOnChangedStuff code isn't being called when I change a cell on any worksheet.

Any ideas?

解决方案

Don't use the New keyword in the dim statement. You're telling it to instantiate the class when it's needed, but then you never refer to it again, so it's never needed. Instead:

Public MySheetHandler As SheetChangeHandler

Sub Auto_Open
   Set MySheetHandler = New SheetChangeHandler
End Sub

That line in the Auto_Open (which runs at startup) will instantiate the class.

这篇关于Excel 加载项如何响应任何工作表中的事件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！