VBA 在 Worksheet 模块中调用 Public Sub：Sheet 和 ThisWorkbook 模块之间的串扰

VBA Calling Public Sub in Worksheet Module: Crosstalk between Sheet and ThisWorkbook modules

提问by Cool Blue

I was so pleased that this works that I thought I would share it...

我很高兴这有效，我想我会分享它......

It allows me to have generic event handlers in the ThisWorkbookmodule to initiate worksheet-specific code in an anonymous ActiveSheet.

它允许我在ThisWorkbook模块中拥有通用事件处理程序，以在匿名ActiveSheet.

I use the CalByNamefunction to access user-defined, Public methods in the Sheet objects.

我使用该CalByName函数访问 Sheet 对象中用户定义的公共方法。

I'm using it to re-start a timer that is killed by auto-recover saves and it works great. I've got it on two sheets.

我用它来重新启动一个被自动恢复保存杀死的计时器，它工作得很好。我把它放在两张纸上。

In ThisWorkbookModule:

在ThisWorkbook模块中：

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error GoTo afterSaveFailed
    CallByName ws, "afterSave", VbMethod
    If debugEvents Then Debug.Print timeStamp & ": " & "AfterSave: afterSave called in sheet: " & ws.Name
    On Error GoTo 0
    Exit Sub
afterSaveFailed:
    If debugEvents Then Debug.Print timeStamp & ": " & "AfterSave: afterSave Failed in sheet: " & ws.Name
    Err.Clear
End Sub

then in each applicable Worksheet Module:

然后在每个适用的工作表模块中：

Public Sub afterSave()
    'sheet specific after save handler
End Sub

回答by Siddharth Rout

CallByNameis a very old and well known method. :)

CallByName是一种非常古老和众所周知的方法。:)

Also in your code

也在你的代码中

1. What is debugEventsYou might want to use Option Explicit
2. Why _AfterSaveand not _BeforeSave? What if afterSave()makes a change to the workbook? You will have to save again before closing.

1. 什么是debugEvents您可能想要使用的Option Explicit
2. 为什么_AfterSave不_BeforeSave？如果afterSave()对工作簿进行更改怎么办？您必须在关闭前再次保存。

Also instead of pasting the code in each applicable worksheet and creating duplicate code why not create a common code in a module?

与其在每个适用的工作表中粘贴代码并创建重复代码，为什么不在模块中创建公共代码？

Public Sub afterSave(oWs as Worksheet)
    With oWs
        'sheet specific after save handler
    End With
End Sub

Worst Case Scenario:

最坏情况：

Assuming that the afterSaveis different for every worksheet. You could always use a Select Case. This will ensure that you have your code in one place. You don't have to hop around to check the code.

假设afterSave每个工作表都不同。你总是可以使用Select Case. 这将确保您将代码放在一处。您不必跳来跳去检查代码。

Public Sub afterSave(oWs As Worksheet)
    With oWs
        Select Case .Name

        Case "AAA"

        Case "BBB"

        Case "CCC", "DDD" '<~~ If two or more worksheet have the same code

    End With
End Sub

回答by Cool Blue

thanks for the comments. My responses are inserted below:

感谢您的评论。我的回答插入如下：

CallByName is a very old and well known method. :)

CallByName 是一种非常古老且众所周知的方法。:)

Which is why I didn't entitle the post "CallByName".

这就是为什么我没有为帖子命名“ CallByName”。

If you read the title, thats what its about. I couldn't find a solution to this on the web so I had to figure out my own method. My point is not about CallByName, its about cross-talk between the modules and using CallByNameto maximum effect to achieve this, I'm not suggesting that CallByNameis novel. I guess I could achieve the same thing using RaiseEventsbut I think maybe this is more straight-forward.

如果您阅读标题，那就是它的内容。我在网上找不到解决方案，所以我不得不找出自己的方法。我的观点不是关于CallByName，而是关于模块之间的串扰并使用CallByName最大效果来实现这一点，我并不是说这CallByName是新颖的。我想我可以使用RaiseEvents它来实现同样的事情，但我认为这可能更直接。

I was just happy coz VBA was letting me do what I wanted for a change :q

我很高兴因为 VBA 让我做我想做的改变：q

Also in your code

What is debugEvents You might want to use Option Explicit

也在你的代码中

What is debugEvents You might want to use Option Explicit

It's a global flag to control how noisy the immediate window is. Yes I always use option explicit and do so in this case. I assume that's a given.

这是一个全局标志，用于控制即时窗口的嘈杂程度。是的，我总是使用显式选项并在这种情况下这样做。我认为这是给定的。

Why _AfterSave and not _BeforeSave? What if afterSave() makes a change to the workbook? You will have to save again before closing.

Why _AfterSave and not _BeforeSave? What if afterSave() makes a change to the workbook? You will have to save again before closing.

Because the stuff I'm doing needs to be done after the save has completed, not before it starts. The Autorecoverysave event kills a timer that I'm running so I need to re-start it after the save.

因为我正在做的事情需要在保存完成后完成，而不是在它开始之前。在Autorecovery保存后保存事件杀死一个计时器，我跑，所以我需要重新启动它。

Also instead of pasting the code in each applicable worksheet and creating duplicate code why not create a common code in a module?

Public Sub afterSave(oWs as Worksheet) With oWs 'sheet specific after save handler End With End Sub

Worst Case Scenario:

与其在每个适用的工作表中粘贴代码并创建重复代码，为什么不在模块中创建公共代码？

Public Sub afterSave(ows as Worksheet) With ows 'sheet specific after save handler End With End Sub

最坏的情况：

Because its "sheet specific"

因为它的“特定于表”

Assuming that the afterSave is different for every worksheet. You could always use a Select Case. This will ensure that you have your code in one place. You don't have to hop around to check the code.

Public Sub afterSave(oWs As Worksheet) With oWs Select Case .Name

    Case "AAA"

    Case "BBB"

    Case "CCC", "DDD" '<~~ If two or more worksheet have the same code

End With End Sub

假设每个工作表的 afterSave 都不同。您始终可以使用 Select Case。这将确保您将代码放在一处。您不必跳来跳去检查代码。

Public Sub afterSave(ows As Worksheet) With ows Select Case .Name

    Case "AAA"

    Case "BBB"

    Case "CCC", "DDD" '<~~ If two or more worksheet have the same code

End With End Sub

OK, I'll think about that: thanks for the suggestion :)

好的，我会考虑的：谢谢你的建议:)

When I have generic features, I prefer to handle that with Class Modules. To me that's neater: I just instance the object in the sheet to expose the generic features, and as in your suggestion, I only have to check it once. I also use the Class_Initializeand Class_Terminateevents to transparently manage life cycle of the generic features. I couldn't do that with generic code in the ThisWorkbookobject.

当我有通用功能时，我更喜欢用Class Modules. 对我来说，这更简洁：我只是实例化工作表中的对象以显示通用功能，正如您的建议，我只需要检查一次。我还使用Class_Initialize和Class_Terminate事件来透明地管理通用功能的生命周期。我不能用ThisWorkbook对象中的通用代码做到这一点。

Thats how I handle general functionality, for object-specific features, my preference is to collect the code that is specific to the object, in the actual object: I prefer shy objects. There's really no need to expose that detail to supervisory code. Also, if I'm working on the sheet, I don't have to skip back to a general module and I don't have to try and remember where I put the code or invent some convention, coz it's right there in sheet.

这就是我处理一般功能的方式，对于特定于对象的功能，我的偏好是在实际对象中收集特定于对象的代码：我更喜欢害羞的对象。真的没有必要将这些细节暴露给监管代码。此外，如果我在工作表上工作，我不必跳回通用模块，也不必尝试记住我将代码放在哪里或发明了一些约定，因为它就在工作表中。

Thank you very much for taking the time to respond in detail!

非常感谢您花时间详细回复！