确保公共 VBA 方法不会出现在 Excel 宏列表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/296696/
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
Making sure public VBA methods don't show up in the list of Excel macros
提问by AR.
In Excel VBA (2003), I've noticed that any Publicor FriendSubmethod in either a module or ThisWorkbookthat 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.
在 Excel VBA (2003) 中，我注意到模块或ThisWorkbook中没有任何参数的任何Public或Friend Sub方法都将显示为可由用户运行的宏。即，当用户转到工具 --> 宏 --> 宏...（或 Alt+F8）时，该方法将显示在可以运行的列表中。
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.
为了理智（组织、维护等），我确实需要将代码分成模块。我希望找到一种非 hacky 的方式来向用户隐藏某些方法，但仍然允许它们对其他代码模块可见。请注意，所有代码都包含在同一个应用程序中，因此不会调用任何外部代码。
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.
我目前的解决方法是使用Functions 返回一个布尔值而不是Subs，并忽略返回值。eJames 建议在Sub中使用可选参数的选项，这也将从宏列表中隐藏该方法。
Neither of these feel quite right, however, so any advice about how to structure a non-trivial Excel VBA application would be much appreciated.
但是，这两种方法都不是很正确，因此非常感谢有关如何构建非平凡 Excel VBA 应用程序的任何建议。
回答by Jason Z
Add the following to the top of your module:
将以下内容添加到模块的顶部：
Option Private Module
From MSDN:
从MSDN：
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.
当一个模块包含 Option Private Module 时，公共部分，例如在模块级别声明的变量、对象和用户定义的类型，在包含该模块的项目中仍然可用，但它们对其他应用程序或项目不可用。
回答by e.James
One solution is to give the method an Optionalargument.
一种解决方案是给方法一个Optional参数。
Public Sub myPublicSub(Optional dummy_var As Integer)
...
End Sub回答by Ant
Just a small addendum to Jason Z's answer: methods hidden by Option Private Moduleare still visible if you use Application.Run()to invoke the method.
只是 Jason Z 答案的一个小附录：Option Private Module如果您Application.Run()用来调用该方法，隐藏的方法仍然可见。
回答by Sam
Also worth noting a side effect of both Option Private Moduleand adding Optional Params is that the Sub will no longer work as a target of a shortcut keybinding.
另外值得注意的是Option Private Module和添加 Optional Params的副作用是 Sub 将不再作为快捷键绑定的目标工作。
Restated: If you have a keybinding to set a keyboard shortcut to launch a Macro. That Macro cannot be hidden. Otherwise the keybinding will not work.
重申：如果您有一个键绑定来设置启动宏的键盘快捷键。该宏无法隐藏。否则键绑定将不起作用。
