如何使用Visual Studio 2010创建的Excel加载项单击在excel中创建的自定义上下文菜单上执行操作 [英] How to perform an action on clicking a custom context menu created in excel using Excel Add-In created with visual studio 2010

问题描述

我正在使用Visual Studio 2010创建一个Excel加载项.我的目的是向一个单元格添加一个上下文菜单，并对选定的一个或多个单元格执行一些操作.这是到目前为止我得到的代码

I am creating an Excel Add-In using Visual Studio 2010. My intention was to add a context menu to a cell and perform some action on the selected cell or cells. Here is the code I have got as of now

    Public Class CC

    Private Sub ThisAddIn_Startup() Handles Me.Startup
        AddMenu()
    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
        DeleteMenu()
    End Sub

    'AddMenu add context menu to excel
    Sub AddMenu()
        On Error Resume Next
        Dim Bar As Microsoft.Office.Core.CommandBar
        Dim NewControl As Microsoft.Office.Core.CommandBarControl
        Application.CommandBars("Cell").Controls("A").Delete()
        Bar = Application.CommandBars("Cell")
        NewControl = Bar.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlPopup, Id:=1, Temporary:=True)

        With NewControl
            .Caption = "A"
            .BeginGroup = True
            .TooltipText = "Change case of selected cells."
        End With

        With NewControl.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlButton)
            .Caption = "A1"
            .FaceId = 1144
            .OnAction = "A1"
        End With

        With NewControl.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlButton)
            .Caption = "A2"
            .FaceId = 1145
            .OnAction = "A2"
        End With

        With NewControl.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlButton)
            .Caption = "A3"
            .FaceId = 1155
            .OnAction = "A3"
        End With

    End Sub

    'DeleteMenu deletes the context meny added to excel
    Sub DeleteMenu()
        On Error Resume Next
        Application.CommandBars("Cell").Controls("A").Delete()
    End Sub

    Sub A1()
        MsgBox "A1"
    End Sub

    Sub A2()
        MsgBox "A2"
    End Sub

    Sub A3()
        MsgBox "A3"
    End Sub

End Class

当我安装此外接程序时，上下文菜单在excel中出现，但是当我单击菜单按钮时，我收到一条错误消息，指出该宏在工作簿中不可用.谁能告诉我如何使其工作?

When I install this Add-In the context menu appears in excel, but when I click on the menu buttons I get an error saying that the macro is not available in the workbook. Can anyone please tell me how to make it work?

推荐答案

您的方法A1，A2和A3不会自动在Excel中注册为宏.结果，将其名称设置为按钮的OnAction字符串无效-Excel不知道名为"A1"的宏.因此，从这个意义上讲，VSTO加载项的行为完全不像VBA中的代码那样.

Your methods A1, A2 and A3 will not automatically be registered as macros with Excel. As a result setting their names into the OnAction strings of the buttons have no effect - Excel doesn't know about a macro called "A1". So in this sense the VSTO add-in does not behave like the code in VBA would at all.

不过，还有另一种方法:对于CommandBar Button，您可以添加事件处理程序-您可以使用WithEvents关键字，然后处理按钮的Click事件.可能使您入门的一些示例在这里: http://msdn.microsoft .com/en-us/library/aa189726(v = office.10).aspx

There is another approach though: For the CommandBar Buttons you can add event handlers - you'd use the WithEvents keyword and then handle the Click event of the buttons. Some examples that might get you started are here: http://msdn.microsoft.com/en-us/library/aa189726(v=office.10).aspx

使用 Excel-DNA (我开发的开源.NET/Excel集成库)中的方法和用户定义的函数.NET代码通过C API在Excel中注册.结果，其行为更接近于VBA，并且您的带有OnAction ="..."字符串的代码也可以工作.

Using Excel-DNA (an open source .NET / Excel integration library that I develop) the methods and user-defined functions in your .NET code are registered with Excel through the C API. As a result the behaviour is closer to that of VBA, and your code with the OnAction="..." strings would work too.

这篇关于如何使用Visual Studio 2010创建的Excel加载项单击在excel中创建的自定义上下文菜单上执行操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！