将 Excel-VBA 代码放在模块或工作表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3255478/
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
Put Excel-VBA code in module or sheet?
提问by Jean-Fran?ois Corbett
What is good practice and good code hygiene? Putting code in Modules or Sheets?
什么是良好的实践和良好的代码卫生？将代码放在模块或表格中？
I have this Excel Workbook, with user interfaces in each sheet. Each sheet within the workbook does a different part of some overall task. Should I place the code relevant to each sheet inside the Sheet objects, or in Modules? Group into one module, or separate modules?
我有这个 Excel 工作簿，每个工作表中都有用户界面。工作簿中的每个工作表执行某个整体任务的不同部分。我应该将与每个工作表相关的代码放在工作表对象中还是模块中？分组为一个模块，还是单独的模块？
I'm using Excel 2003.
我正在使用 Excel 2003。
回答by jevakallio
Definitely in Modules.
绝对在模块中。
- Sheets can be deleted, copied and moved with surprising results.
- You can't call code in sheet "code-behind" from other modules without fully qualifying the reference. This will lead to coupling of the sheet and the code in other modules/sheets.
- Modules can be exported and imported into other workbooks, and put under version control
- Code in split logically into modules (data access, utilities, spreadsheet formatting etc.) can be reused as units, and are easier to manage if your macros get large.
- 可以删除、复制和移动工作表，结果令人惊讶。
- 您不能在没有完全限定引用的情况下从其他模块调用“代码隐藏”表中的代码。这将导致工作表和其他模块/工作表中的代码耦合。
- 模块可以导出和导入到其他工作簿中，并置于版本控制之下
- 逻辑拆分为模块（数据访问、实用程序、电子表格格式等）的代码可以作为单元重用，并且如果宏变大则更易于管理。
Since the tooling is so poor in primitive systems such as Excel VBA, best practices, obsessive code hygiene and religious following of conventions are important, especially if you're trying to do anything remotely complex with it.
由于该工具在 Excel VBA 等原始系统中非常糟糕，因此最佳实践、严格的代码卫生和宗教性的遵守约定很重要，尤其是当您尝试用它做任何远程复杂的事情时。
This articleexplains the intended usages of different types of code containers. It doesn't qualify whythese distinctions should be made, but I believe most developers trying to develop serious applications on the Excel platform follow them.
本文解释了不同类型代码容器的预期用途。这并不能说明为什么要做出这些区分，但我相信大多数试图在 Excel 平台上开发严肃应用程序的开发人员都会遵循它们。
There's also a list of VBA coding conventionsI've found helpful, although they're not directly related to Excel VBA. Please ignore the crazy naming conventions they have on that site, it's all crazy hungarian.
还有一个我发现有用的VBA 编码约定列表，尽管它们与 Excel VBA 没有直接关系。请忽略他们在该网站上的疯狂命名约定，都是疯狂的匈牙利语。
回答by Michael
In my experience it's best to put as much code as you can into well-named modules, and only put as much code as you need to into the actual worksheet objects.
根据我的经验，最好将尽可能多的代码放入命名良好的模块中，并且只将所需的代码放入实际工作表对象中。
Example: Any code that uses worksheet events like Worksheet_SelectionChange or Worksheet_Calculate.
示例：使用 Worksheet_SelectionChange 或 Worksheet_Calculate 等工作表事件的任何代码。
回答by Ben McCormack
I would suggest separating your code based on the functionality and purpose specific to each sheet or module. In this manner, you would only put code relative to a sheet's UI inside the sheet's module and only put code related to modules in respective modules. Also, use separate modules to encapsulate code that is shared or reused among several different sheets.
我建议根据每个工作表或模块的特定功能和目的来分离您的代码。通过这种方式，您只需将与工作表 UI 相关的代码放入工作表模块中，并且仅将与模块相关的代码放入相应模块中。此外，使用单独的模块来封装在多个不同工作表之间共享或重用的代码。
For example, let's say you multiple sheets that are responsible for displaying data from a database in a special way. What kinds of functionality do we have in this situation? We have functionality related to each specific sheet, tasks related to getting data from the database, and tasks related to populating a sheet with data. In this case, I might start with a module for the data access, a module for populating a sheet with data, and within each sheet I'd have code for accessing code in those modules.
例如，假设您有多个工作表，负责以特殊方式显示数据库中的数据。在这种情况下，我们有哪些功能？我们有与每个特定工作表相关的功能、与从数据库获取数据相关的任务以及与用数据填充工作表相关的任务。在这种情况下，我可能会从一个用于数据访问的模块开始，一个用于用数据填充工作表的模块，并且在每个工作表中我都有用于访问这些模块中的代码的代码。
It might be laid out like this.
可能是这样布置的。
Module: DataAccess:
模块：数据访问：
Function GetData(strTableName As String, strCondition1 As String) As Recordset
'Code Related to getting data from the database'
End Function
Module: PopulateSheet:
模块：PopulateSheet：
Sub PopulateASheet(wsSheet As Worksheet, rs As Recordset)
'Code to populate a worksheet '
End Function
Sheet: Sheet1 Code:
工作表：工作表 1代码：
Sub GetDataAndPopulate()
'Sample Code'
Dim rs As New Recordset
Dim ws As Worksheet
Dim strParam As String
Set ws = ActiveSheet
strParam = ws.Range("A1").Value
Set rs = GetData("Orders",strParam)
PopulateASheet ws, rs
End Sub
Sub Button1_Click()
Call GetDataAndPopulate
End Sub
