如何确保公共VBA方法不会显示在Excel宏列表中？ [英] How do I make sure public VBA methods don't show up in the list of Excel macros?

问题描述

在Excel VBA（2003）中，我注意到模块或中的任何公开或朋友 Sub 没有任何参数的ThisWorkbook 将显示为可由用户运行的宏。即当用户进入工具 - >宏 - >宏...（或Alt + F8）时，该方法将显示在可以运行的列表中。

In Excel VBA (2003), I've noticed that any Public or Friend Sub method in either a module or ThisWorkbook that doesn't have any arguments will show up as a Macro that can be run by the user. I.e. when the user goes to Tools --> Macro --> Macros... (or Alt+F8) the method will show up in the list that can be run.

为了理智（组织，维护等），我需要将代码分成模块。我希望找到一个非恶意的方法来隐藏用户的一些方法，但仍然允许它们对其他代码模块可见。请注意，所有代码都包含在相同的应用程序中，因此不会调用外部代码。

For sanity's sake (organization, maintenance, etc) I do need to separate the code into modules. I'm hoping to find a non-hacky way to hide some methods from the user, but still allow them to be visible to other code modules. Note that all the code is contained within the same application, so no external code is called.

我目前的解决方法是使用 Function 返回一个布尔值，而不是 Sub ，并忽略返回值。 eJames建议在 Sub 中使用可选参数的选项，这也可以从宏列表中隐藏该方法。

My current work-around is to use Functions that return a boolean instead of Subs, and just ignore the return value. eJames suggested the option of using an optional argument in the Sub, which will also hide the method from the list of macros.

感觉很正确，但是，关于如何构造一个不平凡的Excel VBA应用程序的任何建议都将不胜感激。

Neither of these feel quite right, however, so any advice about how to structure a non-trivial Excel VBA application would be much appreciated.

推荐答案

将以下内容添加到模块的顶部：

Add the following to the top of your module:

Option Private Module

从 MSDN ：

当一个模块包含Option Private
Module时，公共部分，
变量，对象和在模块级别声明的用户定义的
类型，
仍然可用在项目
中，包含模块，但它们是
不可用其他应用程序或
项目。

When a module contains Option Private Module, the public parts, for example, variables, objects, and user-defined types declared at module level, are still available within the project containing the module, but they are not available to other applications or projects.

这篇关于如何确保公共VBA方法不会显示在Excel宏列表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！