vba 如何在用户定义的函数上放置工具提示

How to put a tooltip on a user-defined function

提问by Marc Thibault

In Excel 2007, how do I add a description and parameter hints to a user-defined function? When I start typing a function invocation for a built-in function, Excel shows a description and parameter list--a tooltip. I'd like to do the same for the functions I define.

在 Excel 2007 中，如何向用户定义的函数添加说明和参数提示？当我开始为内置函数键入函数调用时，Excel 会显示说明和参数列表——工具提示。我想对我定义的函数做同样的事情。

Not just for the formula insert wizard, but in the formula box, so if I key "=myFun(", at the "("the tooltip pops up just like it does for "=average("

不仅用于公式插入向导，而且在公式框中，因此如果我键入"=myFun("，"("则工具提示会弹出，就像它所做的那样"=average("

There's no help in VBA Help, none on MSDN and none on any of the Excel and VBA dedicated forums I can find, so this is clearly a long shot.

在 VBA 帮助中没有帮助，在 MSDN 上没有帮助，在我能找到的任何 Excel 和 VBA 专用论坛上也没有帮助，所以这显然是一个长镜头。

采纳答案by Fionnuala

Professional Excel Development by Stephen Bullen describes how to register UDFs, which allows a description to appear in the Function Arguments dialog:

Stephen Bullen 的 Professional Excel Development 描述了如何注册 UDF，它允许在“函数参数”对话框中显示描述：

Function IFERROR(ByRef ToEvaluate As Variant, ByRef Default As Variant) As Variant
    If IsError(ToEvaluate) Then
        IFERROR = Default
    Else
        IFERROR = ToEvaluate
    End If
End Function

Sub RegisterUDF()
    Dim s As String
    s = "Provides a shortcut replacement for the common worksheet construct" & vbLf _
    & "IF(ISERROR(<expression>), <default>, <expression>)"

    Application.MacroOptions macro:="IFERROR", Description:=s, Category:=9
End Sub

Sub UnregisterUDF()
    Application.MacroOptions Macro:="IFERROR", Description:=Empty, Category:=Empty
End Sub

From: http://www.ozgrid.com/forum/showthread.php?t=78123&page=1

来自：http: //www.ozgrid.com/forum/showthread.php?t=78123& page=1

To show the Function Arguments dialog, type the function name and press CtrlA. Alternatively, click the "fx" symbol in the formula bar:

要显示“函数参数”对话框，请键入函数名称并按CtrlA。或者，单击公式栏中的“fx”符号：

回答by AutomationMan

Not a tooltip solution but an adequate workaround:

不是工具提示解决方案，而是适当的解决方法：

Start typing the UDF =MyUDF(then press CTRL+ Shift+ Aand your function parameters will be displayed. So long as those parameters have meaningful names you at-least have a viable prompt

开始输入 UDF，=MyUDF(然后按CTRL+ Shift+A将显示您的函数参数。只要这些参数具有有意义的名称，您至少有一个可行的提示

For example, this:

例如，这个：

=MyUDF(+ CTRL+ Shift+ A

=MyUDF(+ CTRL+ Shift+A

Turns into this:

变成这样：

=MyUDF(sPath, sFileName)

=MyUDF(sPath, sFileName)

回答by will

I know you've accepted an answer for this, but there's now a solution that lets you get an intellisense style completion box pop up like for the other excel functions, via an Excel-DNA add in, or by registering an intellisense server inside your own add in. See here.

我知道你已经接受了这个答案，但现在有一个解决方案可以让你通过 Excel-DNA 插件或通过在你的内部注册一个智能感知服务器，像其他 excel 函数一样弹出智能感知风格的完成框自己的加载项。请参见此处。

Now, i prefer the C# way of doing it - it's much simpler, as inside Excel-DNA, any class that implements IExcelAddinis picked up by the addin framework and has AutoOpen()and AutoClose()run when you open/close the add in. So you just need this:

现在，我更喜欢 C# 的方式 - 它更简单，就像在 Excel-DNA 中一样，任何实现的类IExcelAddin都由插件框架选取，AutoOpen()并AutoClose()在您打开/关闭插件时运行。所以你只需要这个：

namespace MyNameSpace {
    public class Intellisense : IExcelAddIn {
        public void AutoClose() {
        }
        public void AutoOpen() {
            IntelliSenseServer.Register();
        }
    }
}

and then (and this is just taken from the github page), you just need to use the ExcelDNA annotations on your functions:

然后（这只是取自 github 页面），您只需要在您的函数上使用 ExcelDNA 注释：

[ExcelFunction(Description = "A useful test function that adds two numbers, and returns the sum.")]
public static double AddThem(
    [ExcelArgument(Name = "Augend", Description = "is the first number, to which will be added")] 
    double v1,
    [ExcelArgument(Name = "Addend", Description = "is the second number that will be added")]     
    double v2)
{
    return v1 + v2;
}

which are annotated using the ExcelDNA annotations, the intellisense server will pick up the argument names and descriptions.

使用 ExcelDNA 注释进行注释，智能感知服务器将获取参数名称和描述。

There are examples for using it with just VBA too, but i'm not too into my VBA, so i don't use those parts.

也有使用 VBA 的例子，但我不太喜欢 VBA，所以我不使用这些部分。

回答by Scott K

I just create a "help" version of the function. Shows up right below the function in autocomplete - the user can select it instead in an adjacent cell for instructions.

我只是创建了该函数的“帮助”版本。显示在自动完成功能的正下方 - 用户可以在相邻的单元格中选择它以获取说明。

Public Function Foo(param1 as range, param2 as string) As String

    Foo = "Hello world"

End Function

Public Function Foo_Help() as String

Foo_Help = "The Foo function was designed to return the Foo value for a specified range a cells given a specified constant." & CHR(10) & "Parameters:" & CHR(10)
& "  param1 as Range   :   Specifies the range of cells the Foo function should operate on." & CHR(10)
&"  param2 as String  :   Specifies the constant the function should use to calculate Foo"
&" contact the Foo master at [email protected] for more information."

END FUNCTION

The carriage returns improve readability with wordwrap on. 2 birds with one stone, now the function has some documentation.

回车提高了自动换行的可读性。2 鸟一石，现在该功能有一些文档。

回答by Rafa Barragan

Also you can use, this Macro to assign Descriptions to arguments and the UDF:

您也可以使用此宏将描述分配给参数和 UDF：

Private Sub RegisterMyFunction()
Application.MacroOptions _
    Macro:="SampleFunction", _      '' Your UDF name
    Description:="calculates a result based on provided inputs", _
    Category:="My UDF Category", _  '' Or use numbers, a list in the link below
    ArgumentDescriptions:=Array( _  '' One by each argument
        "is the first argument.  tell the user what it does", _
        "is the second argument.  tell the user what it does")
End Sub

Credits to Kendalland the original post here. For the UDF Categories

感谢Kendall和这里的原始帖子。对于UDF 类别

回答by Fisher

@will's method is the best. Just add few lines about the details for the people didn't use ExcelDNA before like me.

@will 的方法是最好的。对于像我这样以前没有使用 ExcelDNA 的人，只需添加几行有关详细信息的行。

Download Excel-DNA IntelliSense from https://github.com/Excel-DNA/IntelliSense/releases

从https://github.com/Excel-DNA/IntelliSense/releases下载 Excel-DNA IntelliSense

There are two version, one is for 64, check your Excel version. For my case, I'm using 64 version.

有两个版本，一个是64位的，查看你的Excel版本。就我而言，我使用的是 64 版本。

Open Excel/Developer/Add-Ins/Browse and select ExcelDna.IntelliSense64.xll.

打开 Excel/Developer/Add-Ins/Browse 并选择 ExcelDna.IntelliSense64.xll。

Insert a new sheet, change name to "IntelliSense", add function description, as https://github.com/Excel-DNA/IntelliSense/wiki/Getting-Started

插入一张新表，将名称更改为“ IntelliSense”，添加功能描述，如https://github.com/Excel-DNA/IntelliSense/wiki/Getting-Started

Then enjoy! :)

那就享受吧！:)

回答by RexBarker

A lot of dancing around the answer. You can add the UDF context help, but you have to export the Module and edit the contents in a text editor, then re-import it to VBA. Here's the example from Chip Pearson: Adding Code Attributes

很多人围绕答案跳舞。您可以添加 UDF 上下文帮助，但您必须导出模块并在文本编辑器中编辑内容，然后将其重新导入到 VBA。这是 Chip Pearson 的示例： 添加代码属性

回答by Charles Williams

Unfortunately there is no way to add Tooltips for UDF Arguments.
To extend Remou's reply you can find a fuller but more complex approach to descriptions for the Function Wizard at
http://www.jkp-ads.com/Articles/RegisterUDF00.asp

不幸的是，无法为 UDF 参数添加工具提示。
要扩展 Remou 的回复，您可以在http://www.jkp-ads.com/Articles/RegisterUDF00.asp找到更完整但更复杂的函数向导描述方法

回答by riderBill

I tried @ScottK's approach, first as a side feature of my functional UDF, then as a standalone _Help suffix version when I ran into trouble (see below). In hindsight, the latter approach is better anyway--more obvious to a user attentive enough to see a tool tip, and it doesn't clutter up the functional code.

我尝试了@ScottK 的方法，首先作为我的功能 UDF 的一个附带功能，然后在遇到问题时作为一个独立的 _Help 后缀版本（见下文）。事后看来，无论如何，后一种方法更好——对于足够细心以查看工具提示的用户来说更明显，并且它不会使功能代码变得混乱。

I figured if an inattentive user just typed the function name and closed the parentheses while he thought it over, help would appear and he would be on his way. But dumping a bunch of text into a single cell that I cannot format didn't seem like a good idea. Instead, When the function is entered in a cell with no arguments i.e.

我想如果一个粗心的用户只是在他思考的时候输入函数名称并关闭括号，就会出现帮助，他就会上路。但是将一堆文本转储到我无法格式化的单个单元格中似乎不是一个好主意。相反，当在没有参数的单元格中输入函数时，即

   = interpolateLinear() 
or
   = interpolateLinear_Help()

a msgBox opens with the help text. A msgBox is limited to ~1000 characters, maybe it's 1024. But that's enough (barely 8^/) for my overly tricked out interpolation function. If it's not, you can always open a user form and go to town.

一个 msgBox 与帮助文本一起打开。一个 msgBox 被限制为 ~1000 个字符，也许是 1024 个。但这对于我过度欺骗的插值函数来说已经足够了（仅仅 8^/）。如果不是，您可以随时打开用户表单并前往镇上。

The first time the message box opened, it looked like success. But there are a couple of problems. First of course, the user has to know to enter the function with no arguments (+1 for the _Help suffix UDF).

第一次打开消息框，看起来成功了。但是有几个问题。首先，用户必须知道输入不带参数的函数（_Help 后缀 UDF 为 +1）。

The bigproblem is, the msgBox reopens several times in succession, spontaneously while working in unrelated parts of the workbook. Needless to say, it's veryannoying. Sometimes it goes on until I get a circular reference warning. Go figure. If a UDF could change the cell formula, I would have done that to shut it up.

的大问题是，在MSGBOX重新打开连续多次，而自发地在工作簿中的不相关的部分工作。不用说，这很烦人。有时它会一直持续到我收到循环引用警告。去搞清楚。如果 UDF 可以更改单元格公式，我会这样做以将其关闭。

I don't know why Excel feels the need recalculate the formula over and over; neither the _Help standalone, nor the full up version (in help mode) has precedents or dependents. There's not an application.volatilestatement anywhere. Of course the function returns a value to the calling cell. Maybe that triggers the recalc? But that's what UDFs do. I don't think you can notreturn a value.

我不知道为什么Excel觉得需要一遍又一遍地重新计算公式；_Help 独立版和完整版（在帮助模式下）都没有先例或依赖项。任何地方都没有application.volatile语句。当然，该函数会向调用单元格返回一个值。也许这会触发重新计算？但这就是 UDF 所做的。我不认为你不能返回一个值。

Since you can't modify a worksheet formulafrom a UDF, I tried to return a specific string --a value--to the calling cell (the only one you can change the value of from a UDF), figuring I would inspect the cell value using application.calleron the next cycle, spot my string, and know not to re-display the help message. Seemed like a good idea at the time--didn't work. Maybe I did something stupid in my sleep-deprived state. I still like the idea. I'll update this when (if) I fix the problem. My quick fix was to add a line on the help box: "Seek help only in an emergency. Delete the offending formula to end the misery.

由于您无法从 UDF修改工作表公式，因此我尝试将特定字符串--a值 --返回到调用单元格（唯一可以从 UDF 更改值的单元格），认为我会检查在下一个循环中使用application.caller 的单元格值，发现我的字符串，并知道不要重新显示帮助消息。当时似乎是个好主意——但行不通。也许我在睡眠不足的状态下做了一些愚蠢的事情。我还是喜欢这个主意。当（如果）我解决问题时，我会更新它。我的快速解决方法是在帮助框中添加一行：“仅在紧急情况下寻求帮助。删除有问题的公式以结束痛苦。

In the meantime, I tried the Application.MacroOptions approach. Pretty easy, and it looks professional. Just one problem to work out. I'll post a separate answer on that approach later.

与此同时，我尝试了 Application.MacroOptions 方法。很简单，看起来很专业。只需解决一个问题。稍后我将发布有关该方法的单独答案。