Excel 2007 UDF:如何添加函数说明，参数帮助? [英] Excel 2007 UDF: how to add function description, argument help?

查看：116
本文介绍了Excel 2007 UDF:如何添加函数说明，参数帮助?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

说明

我正在COM服务器中编写几个Excel UDF.我想获取按 fx 时获得的标准帮助(插入功能对话框).是的，我可以在类别下拉列表中看到我的COM服务器，但是

  • 我还看到了Equals，GetHashCode，GetType和ToString(这对于公开给Excel用户是非常不希望的)，
  • 选择我的COM Server会弹出* Function Arguments * [1]对话框，其中没有参数信息，也没有函数描述.

这是我的la子.

插入功能"对话框http://www.iwebthereforeiam.com/files/插入％20function％20dialog.gif

"Excel函数参数"对话框http://www.iwebthereforeiam.com/files/Function%20Arguments%20dialog.gif

问题

我是否可以将.NET属性放在将其传递给Excel的方法上?

  • 我可以提供功能说明吗?
  • 我可以提供参数的描述吗?
  • 我可以为函数提供一个类别名称，以便获得比ProgID更好的东西吗?

(我发现在ExcelDNA中看起来很容易实现，但是我没有走这条路线.模拟govert的代码[自定义属性，某种加载程序等]看起来会很困难.)

其他背景

如果您以前没有使用过Excel + COM Server，那么以下一些有用的资源可以帮助您快速入门:

以前的StackOverflow问题:
如何获得以VB.NET编写的Excel COM Server安装并在Automation Servers列表中注册?
如何添加COM -将.NET项目公开到VB6(或VBA)引用对话框?

其他资源:
在.NET中为Excel编写用户定义的函数
构建并部署一个.NET COM组装
编写自定义C#中的Excel工作表功能

编辑2009-10-20 14:10

我尝试在Sub New()中调用Application.MacroOptions.

  1. 没有子New()
    半可接受的:功能列在ProgID类别下.
  2. Shared Sub New()
    不可接受:构建时错误.

    Cannot register assembly "...\Foo.dll".
Exception has been thrown by the target of an invocation.

  3. Sub New()
    不可接受:在插入函数"对话框中未列出类别.

我怀疑这对MacroOptions以及Charles建议的更复杂的路线都是一个问题.

编辑2009-10-20 14:55

从好的方面来说，迈克(Mike)建议创建一个要实现的接口，确实消除了暴露的烦人的额外方法.

编辑2009-10-20 15:00

这篇Microsoft文章从2007年初开始(通过

1 呵呵，一个StackOverFlow错误.看起来您无法在显式HTML ul-list中的斜体字符串为斜体?

解决方案

其中一些很容易纠正，而其他部分则很难.不过，如果您愿意花时间，所有这些操作都是可行的.

您写道:

我还看到了Equals，GetHashCode， GetType和ToString(这是 完全不希望暴露于 Excel用户)

是的，这是绝对不希望的，但可以避免.发生这种情况的原因是，您的类像所有.NET类一样都是从"System.Object"继承的，并且公开给COM的默认接口包括这些成员.例如，如果您使用'ClassInterfaceAttribute'，并使用'ClassInterfaceType.AutoDual'设置，则会发生这种情况.

例如在C#中:

[ClassInterface(ClassInterfaceType.AutoDual)]

在VB.NET中:

<ClassInterface(ClassInterfaceType.AutoDual)>

但是，应避免使用"ClassInterfaceType.AutoDual"，以防止暴露从"System.Object"继承的成员(以及防止将来出现潜在的版本问题).而是定义您自己的接口，在您的类中实现该接口，然后使用值为'ClassInterfaceType.None'的'ClassInterface'属性标记您的类.

例如，使用C#:

[ComVisible(true)]
[Guid("5B88B8D0-8AF1-4741-A645-3D362A31BD37")]
public interface IClassName
{
    double AddTwo(double x, double y);
}

[ComVisible(true)]
[Guid("010B0245-55BB-4485-ABAF-46DF4356DB7B")]
[ProgId("ProjectName.ClassName")]
[ComDefaultInterface(typeof(IClassName))]
[ClassInterface(ClassInterfaceType.None)]
public class ClassName : IClassName
{
    public double AddTwo(double x, double y)
    {
        return x + y;
    }
}

使用VB.NET:

<ComVisible(True)> _
<Guid("5B88B8D0-8AF1-4741-A645-3D362A31BD37")> _
Public Interface IClassName
    Function AddTwo(ByVal x As Double, ByVal y As Double) As Double
End Interface

<ComVisible(True)> _
<Guid("010B0245-55BB-4485-ABAF-46DF4356DB7B")> _
<ProgId("ProjectName.ClassName")> _
<ComDefaultInterface(GetType(IClassName))> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class ClassName
    Implements IClassName

    Public Function AddTwo(ByVal x As Double, ByVal y As Double) As Double _
        Implements IClassName.AddTwo
        Return x + y
    End Function
End Class

通过使用值为'ClassInterfaceType.None'的'ClassInterfaceAtribute'，可以排除继承的'System.Object'成员，因为该类的接口不是COM可见的.而是仅将已实现的接口(在此示例中为"IClassName")导出到COM.

以上内容还利用了"ComDefaultInterfaceAttribute".这不是很重要，如果仅实现一个接口(如本例所示)，则什么也不做，但是如果以后再添加一个接口(例如IDTExtensibility2)，则是个好主意.

有关此的更多详细信息，请参见:

(1)托管自动化添加-ins 由安德鲁·怀特查佩尔(

(2) Application.MacroOptions 方法.此方法使您可以提供功能的描述，并指定要在其下显示的类别.不幸的是，这种方法不允许您为函数参数指定任何信息，但是允许您这样做的技术非常复杂，我将在后面介绍. [更正:'Application.MacroOptions'方法仅适用于通过VBA创建的UDF，不能用于自动化加载项.继续阅读以了解更复杂的方法来处理自动加载项中UDF容器的注册-Mike Rosenblum 2009.10.20]

请注意， Excel 2003的帮助文件和 Excel 2007的帮助文件指出可以将字符串提供给category参数，以便提供您选择的自定义类别名称.但是请注意， Excel 2002的帮助文件不要.我不知道这是Excel 2002帮助文件中的遗漏之处，还是Excel 2003中的一项新功能.我猜是后者，但是您必须进行测试才能确定.

使参数信息进入功能向导的唯一方法是使用涉及'Excel.Application.ExecuteExcel4Macro'方法的相当复杂的技术.但请注意:许多Excel MVP都在努力使用此方法，但未能产生可靠的结果.不过，最近，Jan Karel Pieterse(JKP)似乎已经解决了问题，并在此处发布了详细信息:使用Excel注册用户定义的功能.

跳过该文章，您会发现它不是为胆小的人而设计的.问题的一部分是他为VBA/VB 6.0编写的，因此所有这些代码都必须转换为VB.NET或C#.但是，关键命令是'Excel.Application.ExecuteExcel4Macro'方法，该方法在.NET中公开，因此一切正常.

但是，实际上，我非常喜欢使用"Excel.Application.MacroOptions"方法，因为它既简单又可靠.它不提供参数信息，但是我还没有强烈的动机激励我采用"ExecuteExcel4Macro"方法.

因此，祝您好运，但是我的建议是使用"MacroOptions"，除非您按小时付费. ;-)

-迈克

关注休的答复

我尝试打电话 子程序中的Application.MacroOptions New().

无Sub New()半可接受:函数 列在ProgID类别下.

Shared Sub New()不可接受: 建立时错误.无法注册 程序集"... \ Foo.dll".有例外 被一个目标扔了 调用.

Sub New()不可接受:类别为 未在插入功能"对话框中列出. 我怀疑这都是一个问题 MacroOptions和更多涉及 查尔斯推荐的路线.

将类暴露给COM时，不能使用共享(也称为静态")类或构造函数，因为COM不了解此概念，因此无法编译-如您所知！您可能可以将值为"False"的"COMVisibleAttribute"应用于共享构造函数，以至少允许其进行编译.但这在任何情况下都无济于事...

尝试通过自动化加载项本身注册自动化加载项可能会很棘手.我意识到，将其保留为单个独立组件是理想的，但可能无法实现.否则至少这并不容易.

问题是自动化加载项按需加载.也就是说，直到Excel尝试从自动化加载项访问第一个工作表功能之前，它们才真正存在.有两个与此有关的问题:

(1)如果将注册代码放在类的构造函数中，那么根据定义，直到第一次调用该函数时，函数向导信息才存在.

(2)当Excel尚未准备好接受自动化命令时，您的构造函数可能正在执行.例如，当用户开始键入自动化加载项中定义的用户定义功能(UDF)之一的名称时，通常会按需加载自动化加载项.结果是，当您的自动化加载项首次加载时，该单元格处于编辑模式.如果在编辑模式下构造函数中包含自动化代码，则许多命令将失败.我不知道'Excel.Application.MacroOptions'或'Excel.Application.Excel4Macro'方法是否存在问题，但是在单元格处于编辑模式下尝试执行时，许多命令会阻塞.而且，如果由于在打开功能向导时调用了自动加载项而第一次加载了自动化加载项，我不知道这些方法是否可以正常工作.

如果您希望自动化插件完全独立且没有其他支持，则没有简单的解决方案.但是，您可以创建一个托管的COM加载项，在Excel启动时将通过"Excel.Application.MacroOptions"或"Excel.Application.Excel4Macro"方法为您注册自动化加载项.托管COM外接程序类可以与自动化外接程序位于同一程序集中，因此您仍然只需要一个程序集.

顺便说一句，您甚至可以使用VBA工作簿或.XLA加载项来执行相同操作-使用VBA中的Workbook.Open事件来调用注册代码. Excel启动时，您只需要 something 即可调用您的注册码.在这种情况下使用VBA的好处是您可以利用Jan Karel Pieterse的注册中的代码可以按原样使用带有Excel 文章的用户定义函数，而不必将其转换为.NET.

从好的方面来说，迈克的 建议创建一个界面 实施确实消除了烦人的事情 公开的其他方法.

大声笑，我很高兴能成功！

这篇来自2007年初的Microsoft文章 (通过迈克的链接)似乎是一个相当 有关该主题的完整答案:

自动化加载项和功能 向导

每个自动化加载项都有自己的 Excel函数向导中的类别". 类别名称是针对的ProgID 加载项；您不能指定一个 自动化的不同类别名称 附加功能.另外，那里 没有办法指定功能 说明，参数说明， 或帮助自动化加载项 功能向导中的功能.

这仅是对"Excel.Application.MacroOptions"方法的限制. (我道歉，我在上面写我的原始答案时，已经忘记了'Excel.Application.MacroOptions'方法相对于自动化加载项的限制.)比较复杂的'Excel.Application.但是，ExecuteExcel4Macro方法对于自动化加载项绝对有效.它也应该也适用于.NET(托管")自动化加载项，因为Excel不知道是否要加载通过VB 6.0/C ++创建的COM自动化加载项，而不是加载使用VB 6.0/C ++创建的COM自动化加载项. VB.NET/C#.从Excel的方面看，机制完全相同，因为Excel不知道什么是.NET，甚至不知道.NET的存在.

那就是说，"Excel.Application.Excel4Macro"方法肯定会做很多工作……

The description

I am writing a couple of Excel UDFs in COM Servers. I'd like to get the standard help (Insert Function dialog) that you get when you press fx. Yes, I can see my COM Server listed in among the Category drop down, but

  • I also see Equals, GetHashCode, GetType, and ToString (which are fairly undesirable to expose to the Excel user),
  • selecting my COM Server brings up the *Function Arguments*[1] dialog with no argument information and no description of the function.

Here is the lameness that I get:

Insert Function dialog http://www.iwebthereforeiam.com/files/Insert%20function%20dialog.gif

Excel Function Arguments dialog http://www.iwebthereforeiam.com/files/Function%20Arguments%20dialog.gif

The question

Are there .NET attributes I could put on the methods to pass this through to Excel?

  • Can I provide a description of the function?
  • Can I provide a description of the parameters?
  • Can I provide a category name for my functions, so that I get something better than just the ProgID?

(I see that it looks sadly easy to do in ExcelDNA, but I am not going that route. Emulating govert's code [custom attributes, a loader of some sort, etc.] looks like it would be pretty hard.)

Additional background

If you have not done work with Excel + COM Servers before, here are some useful resources to get up to speed:

Previous StackOverflow questions:
How to get COM Server for Excel written in VB.NET installed and registered in Automation Servers list?
How Add a COM-Exposed .NET Project to the VB6 (or VBA) References Dialog?

Other resources:
Writing user defined functions for Excel in .NET
Build and Deploy a .NET COM Assembly
Writing Custom Excel Worksheet Functions in C#

Edit 2009-10-20 14:10

I tried out calling Application.MacroOptions in a Sub New().

  1. No Sub New()
    Semi-acceptable: Function is listed under category ProgID.
  2. Shared Sub New()
    Not acceptable: build-time error.

    Cannot register assembly "...\Foo.dll".
Exception has been thrown by the target of an invocation.

  3. Sub New()
    Not acceptable: category is not listed in Insert Function dialog.

I suspect this is a problem both for MacroOptions and for the more involved route recommended by Charles.

Edit 2009-10-20 14:55

On the plus side, Mike's recommendation to create an interface to implement did kill off the annoying extra methods that were exposed.

Edit 2009-10-20 15:00

This Microsoft article from early 2007 (via Mike's link) seems a rather complete answer on the topic:

Automation Add-ins and the Function Wizard

Each Automation Add-in has its own category in the Excel Function Wizard. The category name is the ProgID for the Add-in; you cannot specify a different category name for Automation Add-in functions. Additionally, there is no way to specify function descriptions, argument descriptions, or help for Automation Add-in functions in the Function Wizard.

1 Huh, a StackOverFlow bug. It looks like you cannot italicize a string inside an explicit HTML ul-list?

解决方案

Some of this is easy to correct, other parts of it is rather hard. All of it is do-able, though, if you are willing to put the time in.

You wrote:

I also see Equals, GetHashCode, GetType, and ToString (which are fairly undesirable to expose to the Excel user)

Yes, agreed, this definitely undesirable, but it can be prevented. This is occurring because your class is inheriting from 'System.Object', as all .NET classes do, and your default interface that is exposed to COM is including these members. This occurs, for example, if you use the 'ClassInterfaceAttribute', using the setting 'ClassInterfaceType.AutoDual'.

E.g. in C#: 

[ClassInterface(ClassInterfaceType.AutoDual)]

In VB.NET:

<ClassInterface(ClassInterfaceType.AutoDual)>

The use of 'ClassInterfaceType.AutoDual' should be avoided, however, in order to prevent the members inherited from 'System.Object' from being exposed (as well as to prevent potential versioning issues in the future). Instead, define your own interface, implement the interface in your class, and then mark your class with the 'ClassInterface' attribute with a value of 'ClassInterfaceType.None'.

E.g., using C#:

[ComVisible(true)]
[Guid("5B88B8D0-8AF1-4741-A645-3D362A31BD37")]
public interface IClassName
{
    double AddTwo(double x, double y);
}

[ComVisible(true)]
[Guid("010B0245-55BB-4485-ABAF-46DF4356DB7B")]
[ProgId("ProjectName.ClassName")]
[ComDefaultInterface(typeof(IClassName))]
[ClassInterface(ClassInterfaceType.None)]
public class ClassName : IClassName
{
    public double AddTwo(double x, double y)
    {
        return x + y;
    }
}

Using VB.NET:

<ComVisible(True)> _
<Guid("5B88B8D0-8AF1-4741-A645-3D362A31BD37")> _
Public Interface IClassName
    Function AddTwo(ByVal x As Double, ByVal y As Double) As Double
End Interface

<ComVisible(True)> _
<Guid("010B0245-55BB-4485-ABAF-46DF4356DB7B")> _
<ProgId("ProjectName.ClassName")> _
<ComDefaultInterface(GetType(IClassName))> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class ClassName
    Implements IClassName

    Public Function AddTwo(ByVal x As Double, ByVal y As Double) As Double _
        Implements IClassName.AddTwo
        Return x + y
    End Function
End Class

By making use of the 'ClassInterfaceAtribute' with a value of 'ClassInterfaceType.None', the inherited 'System.Object' memebers are excluded, because the class's interface is not made COM-visible. Instead, only the implemented interface ('IClassName' in this example) is exported to COM.

The above is also making use of the 'ComDefaultInterfaceAttribute'. This is not very important, and does nothing if you implement only one interface -- as in this example -- but it is a good idea in case you add an interface later, such as IDTExtensibility2.

For more detail on this, see:

(1) Managed Automation Add-ins by Andrew Whitechapel.

(2) Writing Custom Excel Worksheet Functions in C# by Gabhan Berry.

Ok, now to the hard part. You wrote:

Selecting my COM Server brings up the Function Arguments[1] dialog with no argument information and no description of the function.

Can I provide a description of the function?

Can I provide a description of the parameters?

Can I provide a category name for my functions, so that I get something better than just the ProgID?

The easiest approach here is to make use of the Application.MacroOptions method. This method enables you to provide a description of the function and specify which category under which you want it to be displayed. This approach does not allow you to specify any information for the functions parameters, unfortunately, but techniques that allow you to do so are very complicated, which I'll get to later. [Correction: The 'Application.MacroOptions' method only works for UDFs created via VBA and cannot be used for automation add-ins. Read on for more complex approaches to handle registration of UDFs containe in an automation add-ins -- Mike Rosenblum 2009.10.20]

Note that the help files for Excel 2003 and help files for Excel 2007 state that a string can be provided to the category argument in order to provide a custom category name of your choice. Beware, however, that the help files for Excel 2002 do not. I do not know if this is an omission in the Excel 2002 help files, or if this is a new capability as of Excel 2003. I'm guessing the latter, but you would have to test to be sure.

The only way to get your parameter information into the Function Wizard is to use a rather complex technique involving the 'Excel.Application.ExecuteExcel4Macro' method. Be warned though: many Excel MVPs have struggled with this approach and failed to produce a result that is reliable. More recently, though, it appears that Jan Karel Pieterse (JKP) has gotten it worked out and has published the details here: Registering a User Defined Function with Excel.

Skimming that article you'll see that it is not for the faint of heart. Part of the problem is that he wrote it for VBA / VB 6.0 and so all that code would have to be translated to VB.NET or C#. The key command, however, is the 'Excel.Application.ExecuteExcel4Macro' method, which is exposed to .NET, so everything should work fine.

As a practical matter, however, I vastly prefer using the 'Excel.Application.MacroOptions' approach because it is simple and reliable. It does not provide parameter information, but I have not yet had a strong need to motivate me to take on the 'ExecuteExcel4Macro' approach.

So, good luck with this, but my advice would be to utilize the 'MacroOptions', unless you are being paid by the hour. ;-)

-- Mike

Follow-up to Hugh's Replies

I tried out calling Application.MacroOptions in a Sub New().

No Sub New() Semi-acceptable: Function is listed under category ProgID.

Shared Sub New() Not acceptable: build-time error. Cannot register assembly "...\Foo.dll". Exception has been thrown by the target of an invocation.

Sub New() Not acceptable: category is not listed in Insert Function dialog. I suspect this is a problem both for MacroOptions and for the more involved route recommended by Charles.

You can't use shared (aka "static") classes or constructors when exposing your classes to COM because COM has no knowledge of this concept and so it cannot compile -- as you found out! You might be able to apply a 'COMVisibleAttribute' with a value of 'False' to the shared constructor, to at least allow it to compile. But this wouldn't help you in this case anyway...

Trying to register your automation add-in via the automation add-in itself might prove tricky. I realize that this is desirable in order to keep it as a single, stand-alone component, but it might not be possible. Or at least this won't be easy.

The issue is that automation add-ins are demand loaded. That is, they are not really there until Excel attempts to access the first worksheet function from your automation add-in. There are two issues related to this:

(1) If you put your registration code within the constructor for your class, then, by definition, your function wizard information cannot exist until the function has been called for the first time.

(2) Your constructor might be executing when Excel is not ready to accept automation commands. For example, an automation add-in is typically demand-loaded when the user begins to type in the name of one of the user-defined functions (UDFs) defined in the automation add-in. The result is that the cell is in edit-mode when your automation add-in first loads. If you have automation code within your constructor during edit mode, many commands will fail. I do not know if the 'Excel.Application.MacroOptions' or 'Excel.Application.Excel4Macro' methods have a problem with this, but many commands will choke when trying to execute while the cell is in edit mode. And if the automation add-in is being loaded for the first time because it is being called while the Function Wizard is open, I have no idea if these methods can work right.

There is no easy solution to this if you wish to have your automation add-in to be completely stand-alone with no other support. You can, however, create a managed COM add-in that will register your automation add-in for you via 'Excel.Application.MacroOptions' or the 'Excel.Application.Excel4Macro' approach when Excel starts up. The managed COM add-in class can be in the same assembly as that of your automation add-in, so you still only need one assembly.

By the way, you could even use a VBA workbook or .XLA add-in to do the same -- use the Workbook.Open event in VBA to call the registration code. You just need something to call your registration code when Excel starts up. The advantage to using VBA in this case is that you could utilize the code from the Jan Karel Pieterse's Registering a User Defined Function with Excel article as-is, without having to translate it to .NET.

On the plus side, Mike's recommendation to create an interface to implement did kill off the annoying extra methods that were exposed.

lol, I'm glad something worked!

This Microsoft article from early 2007 (via Mike's link) seems a rather complete answer on the topic:

Automation Add-ins and the Function Wizard

Each Automation Add-in has its own category in the Excel Function Wizard. The category name is the ProgID for the Add-in; you cannot specify a different category name for Automation Add-in functions. Additionally, there is no way to specify function descriptions, argument descriptions, or help for Automation Add-in functions in the Function Wizard.

This is a limitation for the 'Excel.Application.MacroOptions' approach only. (My apologies, I had forgotten about this limitation of the 'Excel.Application.MacroOptions' method with respect to automation add-ins when I wrote my original answer, above.) The more-complex 'Excel.Application. ExecuteExcel4Macro ' approach, however, absolutely does work for automation add-ins. It should also work for .NET ("managed") automation add-ins as well, because Excel has no idea whether it is loading a COM automation add-in created via VB 6.0/C++ versus a managed COM automation add-in created using VB.NET/C#. The mechanics are exactly the same from the COM side of the fence because Excel has no idea what .NET is, or that .NET even exists.

That said, the 'Excel.Application.Excel4Macro' approach would definitely be a lot of work...

这篇关于Excel 2007 UDF:如何添加函数说明，参数帮助?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆