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

问题描述

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

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.

所以，我有一个Excel加载项（VBA，而不是XLL）我们的用户安装添加一些我们的工作簿需要的额外的UDF等。

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.

到目前为止，我已经创建了一个新的我的加载项中的类模块（SheetChangeHandler）包含以下代码：

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

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

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.

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

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

任何想法？

推荐答案

不要在dim语句中使用New关键字。你正在告诉它在需要的时候实例化课程，但是你再也不会再提及它，所以它是永远不需要的。相反：

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

Auto_Open（启动时运行）中的那一行将实例化该类。

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

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