如果在“Sheets”，“ThisWorkbook”和“Modules”中运行VBA代码，它有什么区别？ [英] What difference does it make if one runs a VBA code in `Sheets`, in `ThisWorkbook`, and in `Modules`?

问题描述

如果在表单中运行VBA代码（ Sheet1 ， Sheet2 等），在 ThisWorkbook 和模块（ Module1 等）？

What difference does it make if one runs a VBA code in Sheets (Sheet1, Sheet2, etc.), in ThisWorkbook, and in Modules (Module1 etc.)?

换句话说，在哪种情况下应该使用哪一个？

In other words, which one should be used in which cases?

推荐答案

模块是类似功能和子例程的集合，通常根据功能进行分组。

A module is a collection of similar functions and sub-routines, grouped usually in terms of their functionality.

一个模块子程序/函数
Private ：函数和子例程仅在该模块中可用。
公开：可以从任何地方直接访问。 （另一个模块，不同的宏等）
通常的做法是在模块中存储实用程序函数。

In a module subroutine/function, Private : Functions and Sub-routines are available only within that module. Public : They can be accessed from anywhere, directly. (Another module, different macro etc) It is common practice to store utility functions in modules.

Option Private Module ，这使得模块本身是私有的可以添加到任何标准模块的顶部，但不允许在对象模块上，如ThisWorkbook或Sheet1等。

Option Private Module, which makes the module itself private can be added to the top of any standard module, but is not permitted on an object module, like ThisWorkbook, or Sheet1, etc.

ThisWorkbook是Workbook对象的私有模块。
例如， Workbook_Open（）， Workbook_Close（）例程驻留在此模块中。 （ 工作簿对象参考 ）

ThisWorkbook is a private module of the Workbook Object. For example, Workbook_Open(), Workbook_Close() routine, reside within this module. (Workbook Object Reference)

同样，Sheet1，Sheet2是单个工作表的私人模块。在它们中，您将会放入该表的特定功能。
Worksheet_Activate ， Worksheet_Deactivate ， Workbook_SheetChange 是提供给您的默认事件，以便您可以在相应的私人表格模块。 （ 工作表对象参考 ）

Similarly, Sheet1, Sheet2 are private modules of the individual sheets. In them, you would put in functions specific to that sheet. Worksheet_Activate, Worksheet_Deactivate, Workbook_SheetChange are default events provided to you, so that you can handle them, within the respective private sheet modules. (Worksheet Object Reference)

正如@Daniel Cook在评论中所说，而ThisWorkbook和WorkSheet的模块不能直接用作 subName（）或 functionName（）在模块之外，仍然可以使用 ThisWorkbook.subName（）或 ThisWorkbook.functionName（）

As @Daniel Cook said in the comments, while ThisWorkbook and the WorkSheet's modules aren't available for direct use as subName() or functionName() outside the module, it is still possible to call them using ThisWorkbook.subName() or ThisWorkbook.functionName()

类模块是最接近你可以在VBA中获得OOP。他们有构造函数，析构函数，并且可以实例化来给你类对象。

A class module is the closest you can get to OOP in VBA. They have constructors, destructors, and can be instantiated to give you class objects.

这篇关于如果在“Sheets”，“ThisWorkbook”和“Modules”中运行VBA代码，它有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！