如果在“Sheets”、“ThisWorkbook”和“Modules”中运行 VBA 代码有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12955461/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What difference does it make if one runs a VBA code in "Sheets", in "ThisWorkbook", and in "Modules"?
提问by Mehper C. Palavuzlar
What difference does it make if one runs a VBA code in "Sheets" ("Sheet1", "Sheet2", etc.), in "ThisWorkbook", and in "Modules" ("Module1" etc.)?
如果在“Sheets”（“Sheet1”、“Sheet2”等）、“ThisWorkbook”和“Modules”（“Module1”等）中运行 VBA 代码有什么区别？
In other words, which one should be used in which cases?
换句话说，在什么情况下应该使用哪一个？
回答by Anirudh Ramanathan
A module is a collection of similar functions and sub-routines, grouped usually in terms of their functionality.
一个模块是一组相似的函数和子程序，通常根据它们的功能进行分组。
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.
在模块子例程/函数中， Private：函数和子例程仅在该模块内可用。 公共：它们可以从任何地方直接访问。（另一个模块，不同的宏等）在模块中存储实用函数是常见的做法。
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.
Option Private Module，这使得模块本身私有可以添加到任何标准模块的顶部，但不允许在对象模块上，如 ThisWorkbook 或 Sheet1 等。
ThisWorkbook is a private module of the Workbook Object. For example, Workbook_Open(), Workbook_Close()routine, reside within this module. (Workbook Object Reference)
ThisWorkbook 是 Workbook 对象的私有模块。例如，Workbook_Open()、Workbook_Close()例程驻留在此模块中。（工作簿对象参考）
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_SheetChangeare default events provided to you, so that you can handle them, within the respective private sheetmodules. (Worksheet Object Reference)
同样，Sheet1、Sheet2 是各个工作表的私有模块。在它们中，您将放入特定于该工作表的函数。 Worksheet_Activate、Worksheet_Deactivate、Workbook_SheetChange是提供给您的默认事件，以便您可以在各自的私有工作表模块中处理它们。（工作表对象参考）
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()
正如@Daniel Cook 在评论中所说，虽然 ThisWorkbook 和 WorkSheet 的模块不能直接用作模块subName()或functionName()模块之外，但仍然可以使用ThisWorkbook.subName()或ThisWorkbook.functionName()
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.
类模块是您在 VBA 中最接近 OOP 的方式。它们具有构造函数、析构函数，并且可以被实例化以提供类对象。
回答by Mike Kellogg
I know atleast that Moduleshave to be run and ThisWorkBookis used for events such as SheetChangeevent etc.
我知道至少Modules必须运行并ThisWorkBook用于事件等SheetChange事件。
Common code spit out in ThisWorkBook:
常见代码吐出ThisWorkBook：
Private Sub Workbook_Open()
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub
this question is also already answered here:
这个问题也已经在这里回答：
http://www.pcreview.co.uk/forums/macro-module-function-sub-and-workbook-and-sheets-t980275.html
http://www.pcreview.co.uk/forums/macro-module-function-sub-and-workbook-and-sheets-t980275.html
