将用户定义的函数（UDF）添加到Excel中的内置类别

When I add a public function to a standard code module in Excel it shows up in the Insert Function wizard in the catch-all category "User Defined."

当我向Excel中的标准代码模块添加公共函数时,它将显示在“全部”类别“用户定义”的“插入函数”向导中。

Is there any way to:

有没有办法:

Keep the function public (so it may be called from other modules) but hide it from the Excel function wizard?

保持函数公共(因此可以从其他模块调用)但是将它从Excel函数向导中隐藏?

Make the function appear in a built-in category (e.g., "Financial", "Statistical", "Logical")?

使功能显示在内置类别中(例如,“财务”,“统计”,“逻辑”)?

Create my own category (e.g., "Nonsensical") and have my function appear there?

创建我自己的类别(例如,“荒谬”)并让我的功能出现在那里?

I only need to be able to make one of the above cases work, but I'd be curious to know if each one is possible.

我只需要能够使上述案例之一起作用,但我很想知道每一个案例是否可行。

Note: I will gladly split this into three separate questions if the community feels that is more appropriate. I am thinking there may be a single answer with only slight variation among the three questions.

注意:如果社区认为更合适,我很乐意将此分为三个单独的问题。我想可能只有一个答案,三个问题之间只有轻微的差异。

4 个解决方案

#1

Application.MacroOptions is the command you are after

Application.MacroOptions是您追求的命令

This example adds a user-defined macro called TestMacro to a custom category named My Custom Category.

此示例将名为TestMacro的用户定义宏添加到名为My Custom Category的自定义类别中。

Function TestMacro()
    MsgBox ActiveWorkbook.Name
End Function

Sub AddUDFToCustomCategory()
    Application.MacroOptions Macro:="TestMacro", Category:="My Custom Category"
End Sub

#2

Is there any way to [...] Keep the function public (so it may be called from other modules) but hide it from the Excel function wizard?

有没有办法[...]保持函数公开(所以它可以从其他模块调用)但是从Excel函数向导中隐藏它?

Put Option Private Module at the top of your module. Done.

将Option Private Module放在模块的顶部。完成。

http://msdn.microsoft.com/en-us/library/aa266185(v=vs.60).aspx

#3

I answered this in a foonote to a question about VBA Date functions: here's a straight copy-and-paste of what I wrote there, on using VB 'Attribute' statements to create function descriptions and a Function Wizard category ID:

我在foonote中回答了关于VBA日期函数的问题:这里是我在那里写的内容的直接复制和粘贴,使用VB'Attribute'语句来创建函数描述和函数向导类别ID:

A Tip for people still using Excel 2003:

仍然使用Excel 2003的人的提示:

If you (or your users) are going to call IsDateEx() from a worksheet, put these two lines in, immediately below the function header, using a text editor in an exported .bas file and reimporting the file, because VB Attributes are useful, but they are not accessible to the code editor in Excel's VBA IDE:

如果您(或您的用户)要从工作表中调用IsDateEx(),请将这两行放在函数头的正下方,使用导出的.bas文件中的文本编辑器并重新导入该文件,因为VB属性很有用,但Excel的VBA IDE中的代码编辑器无法访问它们:

Attribute IsDateEx.VB_Description = "Returns TRUE if TestDate is a date, and is within ± 20 years of the system date.\r\nChange the defaulte default ± 20 years boundaries by setting values for LimitPastDays and LimitFutureDays\r\nIf you are checking an array of dates, ALL the values will be tested: set FirstColumnOnly TRUE to check the leftmost column only."

属性IsDateEx.VB_Description =“如果TestDate是日期,则返回TRUE,并且在系统日期的±20年内。\ r \ n通过设置LimitPastDays和LimitFutureDays的值来更改默认的±20年边界\ r \ n如果您正在检查日期数组,将测试所有值:设置FirstColumnOnly TRUE仅检查最左边的列。“

That's all one line: watch out for line-breaks inserted by the browser! ...And this line, which puts isDateEX into the function Wizard in the 'Information' category, alongside ISNUMBER(), ISERR(), ISTEXT() and so on:

这就是一行:注意浏览器插入的换行符! ...这一行,它将isDateEX放入'信息'类别的函数向导中,与ISNUMBER(),ISERR(),ISTEXT()等一起:

Attribute IsDateEx.VB_ProcData.VB_Invoke_Func = "w\n9"

属性IsDateEx.VB_ProcData.VB_Invoke_Func =“w \ n9”

Use "w\n2" if you prefer to see it under the Date & Time functions: beats hell outta losing it in the morass of 'Used Defined' functions from your own code, and all those third-party add-ins developed by people who don't do quite enough to help occasional users.

如果您希望在日期和时间功能下看到它,请使用“w \ n2”:在您自己的代码中使用“使用过的定义”函数进行失败并将其丢失,以及由人们开发的所有第三方加载项谁做得不够,不能帮助偶尔的用户。

I have no idea whether this still works in Office 2010.

我不知道这是否仍然适用于Office 2010。

...And that's all I can suggest. Function Wizard categories and the associated function (and parameter!) descriptors aren't really accessible to VBA developers. Some of this is available to old-school VB coders, who have access in their IDE to VB.Attribute statements; and you can sort-of slip it into your code with a bit of text file manipulation.

......这就是我所能提出的建议。 VBA开发人员实际上无法访问函数向导类别和相关的函数(和参数!)描述符。其中一些可供老派VB编码人员使用,他们可以在IDE中访问VB.Attribute语句;你可以通过一些文本文件操作将它分类到你的代码中。

#4

Public Functions are only visible to Excel's formula bar is they reside in a "regular" *.bas Module. Public Functions belonging to classes are not visible to the formula bar. If you add this together with the fact that we can simulate a static class, you can effectively hide functions.

公共函数仅对Excel的公式栏可见,它们位于“常规”* .bas模块中。属于类的公共函数对公式栏不可见。如果将这一点与我们可以模拟静态类的事实相加,则可以有效地隐藏函数。

If you create a class module and then export it, you will find Attribute VB_PredeclaredId = False in the header. Setting this to true creates a default global instance of the class that can be used much like a static class in a more modern language.

如果您创建一个类模块然后将其导出,您将在标题中找到属性VB_PredeclaredId = False。将此设置为true会创建该类的默认全局实例,该实例可以像更现代语言中的静态类一样使用。

For example, create a new class module named Math and add this function to it.

例如,创建一个名为Math的新类模块并将此函数添加到其中。

Public Function Add(a As Integer, b As Integer) As Long
    Add = a + b
End Function

Export and remove the file, then open it in notepad. Change the predeclared Id to true.

导出并删除文件,然后在记事本中打开它。将预先声明的Id更改为true。

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Math"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Save and Import the file back into your project.

保存并将文件导回到项目中。

Now, in a regular module, you can call the function with a qualified call. Just like any other class method. Only now, we have a default instance of the class to work with. There's no mucking around with New or Set, it just works.

现在,在常规模块中,您可以使用合格的调用来调用该函数。就像任何其他类方法一样。只有现在,我们有一个要使用的类的默认实例。 New或Set没有任何问题,它只是有效。

Sub Test()
    Debug.Print Math.Add(1,2)
End Test

#1