将Excel-VBA代码放在模块或工作表中？ [英] Put Excel-VBA code in module or sheet?

问题描述

什么是良好的做法和良好的守则卫生？将代码放在模块或表格中？

我有这个Excel工作簿，每个工作表中都有用户界面。工作簿中的每个工作表都是一些整体任务的不同部分。我应该将与Sheet中的每个工作表相关的代码放在Sheet对象中，还是在Modules中？分组到一个模块或单独的模块？

我正在使用Excel 2003.

解决方案

绝对在模块中。

• 可以删除，复制和移动表格，令人惊讶的结果。


• 您不能在没有完全限定引用的情况下从其他模块中调用代码代码隐藏。这将导致其他模块/工作表中的工作表和代码的耦合。


• 模块可以导出并导入到其他工作簿中，并进行版本控制


• 将逻辑分为模块（数据访问，实用程序，电子表格格式等）的代码可以重复使用，并且如果您的宏变大，则更容易管理。

由于原始系统（如Excel VBA）中的工具非常差，因此最佳做法，强制性代码卫生和宗教信仰遵循很重要，特别是如果您尝试远程复制

本文解释不同类型代码容器的预期用途。它不符合为什么要这些区别，但我相信大多数开发人员试图在Excel平台上开发严重的应用程序。

还有一个 VBA编码约定的列表，我发现有帮助，虽然它们是与Excel VBA无直接关系。请忽略他们在该网站上的疯狂命名约定，这一切都是疯狂的匈牙利语。

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?

I'm using Excel 2003.

解决方案

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.

This article explains the intended usages of different types of code containers. It doesn't qualify why these distinctions should be made, but I believe most developers trying to develop serious applications on the Excel platform follow them.

There's also a list of VBA coding conventions I'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.

这篇关于将Excel-VBA代码放在模块或工作表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！