Excel VBA开发最佳实践

Question

我们是一家ISV，具有使用VB6编写桌面应用程序的经验。我们现在正在开发包含VBA代码的Excel电子表格工具，这些工具将免费提供给各种用户，包括地方政府机构。

我们以前很少在我们自己的组织之外分发电子表格。我们应该遵循哪些最佳实践？我们应该注意哪些陷阱？

我知道以下内容。

• 使用Authenticode对电子表格进行数字签名。有什么建议吗？
• 我担心IT部门可能会实施安全规则，即使电子表格被签名，也会阻止用户下载包含宏的电子表格。这种情况会发生吗？
• 确保代码可以在不同版本的Excel（2000、2002、2003、2007）上正常工作。有什么建议？
• 密码保护（锁定）电子表格。弱保护总比没有好，如果有人无论如何都能获得源代码，也不是灾难性的。
• 版本控制。
• ...也许我应该阅读权威指南？

- MarkJ

---

如果您的电子表格链接了一个命令栏/菜单栏，您将需要更改以支持Excel 2007功能区UI。 - A9S6

从 Excel 2007 开始，包含 VBA 的文件会出现烦人的警告消息，并且 VBA 在 Mac 上不再受支持。 - Alexandre C.

Answer 1

有两件事需要注意：

• 如果某个单元格中的公式或文本长度超过255个字符，请尝试重构它，因为Excel在复制工作表时可能会将其截断。这取决于复制方式，它甚至不会通知您。
• 如果使用命名区域，请避免使用三个字母的命名区域，或者以三个字母后跟数字的方式命名。例如，在Office 2007中，BAD1是一个有效的单元格引用。

- Codezy

Answer 2

与其开发电子表格，我发现创建一个“Add Ins”更容易分发。通常我会有一个单一的菜单，其中包含文档选项和创建所需函数模板的选项。将您的电子表格更改为“应用程序式”意味着更多的工作，但它具有以下几个优点：

1. 可以更轻松地保持外观和感觉的一致性
2. 所有代码、模板等都在一个文件中（即Add In）
3. 您可以轻松地重复使用代码（可能是最重要的部分）

有关此示例的详细信息，请参见此演示。

另一个技巧是添加一些版本控制和使用日志记录。每个主要功能在运行时都会命中 web 服务器并记录其使用情况。这不适用于每种情况，但在企业环境中，了解谁在运行哪些 Excel 小应用程序以及频率非常方便。

- Mark Nold

---

我们计划将所有的代码和模板放入一个文件中。电子表格将作为免费下载，以帮助进行特定的计算 - 我们需要说服用户使用它。我有点担心用户可能会对插件比电子表格更加怀疑，并且很难开始使用。对于这个应用程序，我认为他们需要在下载后的几分钟内获得有趣的结果，否则他们可能会放弃。+1，有趣的建议。 - MarkJ

Answer 3

在为广泛的非技术用户创建电子表格时，我发现以下几点很有用。这适用于Excel 2007及更早版本（不确定在后续版本中是否仍然存在问题），即使所有内容都受到保护，用户也可能会破坏其他工作表中的公式。

1. 使用命名区域，这样它们可以轻松地调整大小或移动位置，以便满足用户的需求。
2. 使用数组公式，因为当人们剪切和粘贴单元格时，他们将修改任何依赖于这些单元格的公式。如果您使用数组，则必须剪切整个数组才能破坏您的公式。
3. 将您的数组设置为顶部和底部各一行以及左侧和右侧各一列受到保护，用户无法更改它们，从而避免破坏您的公式。

我从未想到如何确保格式不被破坏。

- tarriel