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

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专用论坛上没有任何帮助，所以这显然是一个很长的尝试。

9 个解决方案

#1

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

Stephen Bullen的专业Excel开发描述了如何注册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:

要显示函数参数对话框，输入函数名并按下ctrl。或者，点击公式栏中的“fx”符号:

#2

Not a tooltip solution but an adequate workaround:

不是工具提示解决方案，而是一个适当的解决方案:

Start typing the UDF =MyUDF( then press CTRL + Shift + A and 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)

#3

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.

我只是创建了一个“帮助”版本的函数。在autocomplete的函数下面显示——用户可以在相邻的单元格中选择它作为指令。

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 [e-mail] for more information."

END FUNCTION

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

车厢返回提高了文字包装的可读性。一石二鸟，现在功能有一些文献。

#4

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函数一样，通过一个excel - dna添加，或者在您自己的add in中注册一个智能感知服务器。在这里看到的。

Now, i prefer the C# way of doing it - it's much simpler, as inside Excel-DNA, any class that implements IExcelAddin is 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的类都由addin框架接收，并在打开/关闭addin时运行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，所以我不使用那些部分。

#5

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中找到一个更完整但更复杂的函数描述。

#6

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的例子:添加代码属性

#7

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

@will的方法是最好的。只要在细节上多写几行就可以了。

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

从https://github.com/exceldna/intellisense/release下载exceldna 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! :)

然后享受!:)

#8

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 big problem is, the msgBox reopens several times in succession, spontaneously while working in unrelated parts of the workbook. Needless to say, it's very annoying. 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.volatile statement 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 not return a value.

我不知道为什么Excel觉得需要反复计算公式;_Help独立版本和完整版本(在帮助模式中)都没有先例或依赖项。没有一个应用程序。挥发性声明。当然，函数向调用单元格返回一个值。这可能会触发recalc吗?但这就是udf所做的。我不认为你不能返回一个值。

Since you can't modify a worksheet formula from 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.caller on 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修改工作表公式，所以我尝试将一个特定的字符串——一个值——返回给调用单元格(惟一一个可以从UDF修改值的字符串)，并认为我将使用应用程序检查单元格值。下一个周期的调用者，找到我的字符串，并知道不要重新显示帮助消息。当时看起来是个不错的主意——没起作用。也许我在睡眠不足的状态下做了一些蠢事。我还是喜欢这个主意。当我修复这个问题时，我将更新它。我的快速解决办法是在帮助框上加一行:“只有在紧急情况下才寻求帮助。”删除恼人的公式结束痛苦。

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.

同时，我尝试了这个应用程序。MacroOptions方法。很简单，看起来很专业。只有一个问题需要解决。稍后我将对此方法给出一个单独的答案。

#9

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 Kendall and the original post here. For the UDF Categories

这要归功于肯德尔和这里的原始帖子。UDF的类别