寻找 Excel 自定义函数工具提示

这个问题一直是 问道 之前，但每次接受的答案只是一个辞职，以提供使用 Application.MacroOptions(VBA6)(VBA7)的函数描述，但这个信息实际上并没有作为工具提示出现，所以它不能解决我的问题。

目标

我们都希望能够通过任何方式(VBA、 VSTO 或 COM 外接程序)定义自定义函数，并给用户提供函数及其参数的弹出/工具提示描述，就像每个内置 Excel 函数一样，无论是在内联中还是在公式栏中:

enter image description here

enter image description here

对这一需求的普遍接受的答案是，定制功能是不可能的，但我希望对这一信念提出质疑。

问题

目前，我见过的最好的方法就是定义函数(通常使用上面的 MacroOptions 调用) ，这样当打开函数对话框(公式栏中的 fx 按钮)时，函数和参数的描述如下所示:

enter image description here

如您所见，这是一个有许多参数的复杂函数。如果用户没有意识到这个“函数参数”对话以及如何提出它，而只是熟悉 Excel 的标准工具提示，他们只会看到公式名称，没有额外的帮助:

enter image description here

它们没有机会正确地提供所需的参数。(当然，没有用户会阅读文档。)

现在，高级用户可能知道，通过键入 Ctrl + Shift + A，他们将获得一个自动完成的函数参数列表，如下所示:

enter image description here

但是当然，我们也有同样的问题，那就是标准的 excel 用户只会习惯于第一张图片的默认行为，而且很可能永远不会学会这个特性。

这一点应该很清楚，为什么这是不够的，我们希望每一个内置的功能有-在线工具-提示，告诉用户如何使用的功能。

挑逗

起初，我可能确信这是不可能的，除非使用本机 Excel 应用程序函数。外接程序和 VBA 是可扩展性特性，而这个工具提示可能根本就不可扩展。但是这个理论受到了分析工具包插件的挑战。当然，它是在微软内置的，但是 AnalyS32.XLL 是一个独立的 XLL 插件，就像那些可以在 VB、 C、 C + + 和 C # 中生成的插件一样。毫无疑问，当这个 XLL 加载到应用程序中时，它提供的 功能具有与本机 Excel 函数相同的工具提示:

enter image description here

当然，如果这些信息以某种方式编码在这个 XLL 文件中并传递给 Excel，那么有办法用我们自己的外接程序复制它吗？现在我要开始教自己一些反编译的知识，看看我是否可以对分析工具包中发生的事情进行逆向工程。

你能帮什么忙

我几乎可以肯定，我已经研究了所有关于这个问题的公开信息。如果有人知道一些我不知道的事情，可能会对此有所帮助，请随时加入我们。我对反向工程编译的 dlls/xlls 非常不熟悉，所以如果有人想打开他们本地的 Analysis32.xll 并弄清楚它的自定义函数定义是怎么回事，我会非常感激。否则，我会继续自己调查直到我查到所有的死胡同然后汇报我的发现。

21533 次浏览
小开

How about

  1. Capture inputting text on key press event of cell. like this
  2. Check if the text matches with custom functions.
  3. If matches, then show something like label or shape to pretend be a tooltip. like this

Is this acceptable?

It is a little ugly but easier.

小开
最佳答案

I've posted a proof-of-concept project to GitHub as the Excel-DNA IntelliSense project, implementing this.

Using the UI Automation classes to monitor the appropriate Excel user interface events, a form is displayed when appropriate.

The code is wrapped as an Excel-DNA add-in, and works on my Excel 2013 / Windows 8 machine. I've tested on one other configuration (64-bit Excel 2010 on Windows Server 2008) and had a serious problems.

For a C# function defined with the Excel-DNA attributes like this:

[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;
}

we get both the function description

Function Description

and when selecting the function, we get argument help

Argument Help

That looks nice, but it's all still very flaky, only works on my machine and sometimes crashes Excel. It might be a start, though...

Update 9 May 2014:

I've made some progress figuring out how to make the argument help work under older Excel and Windows versions. However, it still needs quite a lot of work to get everything reliable. Anyone who would like to help with this should please contact me directly.

Update 18 June 2016:

Excel UDF IntelliSense support for both Excel-DNA add-ins and VBA functions is now being tested. See the Getting Started page on GitHub for instructions.

小开

What is XLL?

An XLL is a DLL that exports several procedures that are called by Excel or the Excel Add-in Manager. http://msdn.microsoft.com/en-us/library/office/bb687861.aspx

How you develop the XLL?

Excel XLL SDK with Visual C++ (or anything that can compile a DLL and call the SDK procedures)

Where I can find a quick guide for creating a simple XLL?

http://support.microsoft.com/kb/178474

How I get tooltips?

  1. Implement your function as a procedure and export it
  2. Implement and export procedure xlAutoOpen(void) - http://msdn.microsoft.com/en-us/library/office/bb687860.aspx
  3. xlAutoOpen just needs to call xlfRegister - http://msdn.microsoft.com/en-us/library/office/bb687900.aspx
  4. For tooltips, special attention for: pxArgumentText, pxFunctionHelp, pxArgumentHelp1

Sadly, the Analysis Toolpak add-in doesn't have tooltips either.

My solution was wrong, but with wrong information posted by the original poster. Showing a picture with BINOMDIST, if his link clearly shows that this is not a function of ANALYS32.xll.

This means I was trying to solve an impossible question. Because there is no XLL that can do what the poster requested. If you find a version of Excel that show tooltips of XLLs, please let me know.

About what the poster asked, trying to mimic the XLL behavior, I am sure my answer was the most correct in relation of what ANALYS32.xll do.


提交答案