在Excel中编写公式时如何防止自定义VBA函数的建议？ [英] How Can I Prevent The Suggestion of Custom VBA Functions When Writing Formulas in Excel?

问题描述

我正在编写一系列可以在许多项目中重用的VBA函数/子集。我的问题是这些功能在使用工作表时在建议的公式函数中列出。

将这些功能设为私有会阻止这种情况，但是它还将做的是从除了存储该功能之外的模块调用时，删除显示函数参数的方便提示。

有谁知道一种方法来防止

解决方案

无参数公开 标准模块的成员是以下两种：

• Sub 程序，它们被显示为宏。


• 函数暴露为UDF。

注意，成员隐含地 Public 如果没有访问修饰符被指定。

A Sub 有参数不能作为宏执行，所以它不会显示为可用的宏。

一个功能 - 影响（例如突变某些模块/全局状态，o r更改其他单元格的值）是不好的代码，UDF无法改变另一个单元格的值。

---

将功能设置为私有会阻止这种情况，但是它还将做的是删除从除存储功能之外的模块调用时显示函数参数的方便提示。

使它私人不仅删除方便的提示，它使其他模块的功能无法访问，您的代码将无法编译。

如果您的代码具有UDF，请将它们全部放在相同的标准模块中，例如 UserFunctions ，并为了可读性而将它们明确地 Public

如果您的代码具有宏，将它们全部放在同一个标​​准模块中，例如 Macros ，并为了可读性而明确公开

如果您的代码具有需要 Public （例如，从UDF和/或宏访问）的函数和过程，请将它们明确地公共为了可读性，将它们放在标准模块中，恰当地命名（即避免 Helper 和 Manager 模块，它们不可避免地成为任何不适合任何地方的垃圾袋，并且变得混乱）。

然后将其放在顶部： p>

  Option Explicit 
选项私人模块
  

该选项（私人模块）可防止所有公开/暴露的成员被拾取为宏和UDF。 Option Explicit 应该在那里。

---

另一种方法是在类模块中实现逻辑;如果没有该类的实例（即对象），则无法访问类模块的成员，因此不会以宏/ UDF的形式公开。

I am writing a range of VBA functions / subs that can be reused in a number of projects. The issue I have is that these functions are listed in the suggested formula functions when using sheets.

Making the functions private will prevent this, but what it will also do is remove the handy hints showing the parameters for the functions when called from a module other than where the function is stored.

Does anyone know of a way to prevent the suggestion of custom functions without making them private?

解决方案

Parameterless Public members of a standard module are one of two things:

• Sub procedures and they're exposed as macros.
• Function procedures and they're exposed as UDF's.

Note that members are implicitly Public if no access modifier is specified.

A Sub that has parameters can't be executed as a macro, so it won't show up as an available macro.

A Function that has side-effects (e.g. mutates some module/global state, or changes other cells' value) is bad code, and a UDF can't change another cell's value anyway.

---

Making the functions private will prevent this, but what it will also do is remove the handy hints showing the parameters for the functions when called from a module other than where the function is stored.

Making it Private not only "removes the handy hints", it makes the function inaccessible to other modules, and your code won't compile.

If your code has UDF's, put them all in the same standard module, e.g. UserFunctions, and make them explicitly Public for readability's sake.

If your code has macros, put them all in the same standard module, e.g. Macros, and make them explicitly Public for readability's sake.

If your code has functions and procedures that need to be Public (e.g. they're accessed from UDF's and/or macros), make them explicitly Public for readability's sake, and put them in standard modules named appropriately (i.e. avoid Helper and Manager modules, they inevitably become a dumping bag of whatever doesn't quite fit anywhere, and grow to a mess).

Then put this at the top:

Option Explicit
Option Private Module

That option (Private Module) prevents all public/exposed members from being picked up as macros and UDF's. Option Explicit should just be there anyway.

---

Another way is to implement logic in class modules; members of a class module can't be accessed without an instance of that class (i.e. an object), and therefore won't be exposed as macros/UDF's.

这篇关于在Excel中编写公式时如何防止自定义VBA函数的建议？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！