none
method from Excel-Addin not visible RRS feed

  • Question

  • Hi

    Im new to VSTO. Trying to create an Excel-Addin that provides a method I can call from an excel cell.

    The code looks like this:

    using System;
    using System.Windows.Forms;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Xml.Linq;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using Microsoft.Office.Tools.Excel;
    using System.Runtime.InteropServices;
    
    namespace ExcelAddIn1
    {
         [ClassInterface(ClassInterfaceType.AutoDual)]
        public partial class ThisAddIn
        {
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                MessageBox.Show("Tax value = " + Tax(2999));
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
            }
    
            public   static double Tax(double income)
            {
                if (income > 0 && income <= 7000) { return (.10 * income); }
                if (income > 7000 && income <= 28400) { return 700.00 + (.15 * (income - 7000)); }
                if (income > 28400 && income <= 68800) { return 3910.00 + (.25 * (income - 28400)); }
                if (income > 68800 && income <= 143500) { return 14010.00 + (.28 * (income - 68800)); }
                if (income > 143500 && income <= 311950) { return 34926.00 + (.33 * (income - 143500)); }
                if (income > 311950) { return 90514.50 + (.35 * (income - 311950)); }
                return 0;
            }
    
            [ComRegisterFunctionAttribute]
            public static void RegisterFunction(System.Type t)
            {
                Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
                    ("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
            }
    
            [ComUnregisterFunctionAttribute]
            public static void UnregisterFunction(System.Type t)
            {
                Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
                    ("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
            }
    
            #region VSTO generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(ThisAddIn_Startup);
                this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
            }
            
            #endregion
        }
    }

    When I start the Addin in debug mode the message-box is showing "Tax value = 299.9" which proves that the addin is being loaded and the Tax-method is working.

    However when I enter the formula "= Tax(2999)" into a cell excel appears not to know the Tax-method as I get "#Name?" as the result. Has anyone got an idea how to fix this?

    I'm using Microsoft Visual Studio 2010 Premium and Excel 2007. Any help would be appreciated.

    Kind regards

    Hants


    Thursday, November 22, 2012 4:10 PM

Answers

  • Hi Hants

    If you want something that can be used as a formula in a cell, that's called a "User-Defined Function", UDF for short.

    VSTO is not designed to provide UDFs for Excel. A different technology should be used.

    The best place to get assistance for this would be the Excel for Developers forum:
    http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

    It's probably better if you post there again, rather than my moving this discussion, as some may not read beyond your original question, rather than concentrate on what you really need...


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, November 22, 2012 4:40 PM
    Moderator
  • Hi Tom

    The article to which you link does not discuss AutomationAddins / UDFs, only exposing code to the VBA interface, which is a very different proposition.

    However, there was a somewhat convoluted discussion on this topic a month or so ago:
    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/637c90fa-c84c-428e-b411-800cbed0612f

    Here's an additional references concerning UDF on Adrew Whitechapel's blog (with additional links on the topic):
    http://blogs.msdn.com/b/andreww/archive/2008/01/23/managed-automation-add-ins.aspx


    Cindy Meister, VSTO/Word MVP, my blog


    Friday, November 23, 2012 5:28 PM
    Moderator
  • Hi Hants,

    The easiest way to create Excel UDFs wth .NET is to use the (free) Excel-DNA library.

    This could be the entire code for your add-in:

    using ExcelDna.Integration;
    
    public class MyFunctions
    {
        [ExcelFunction(Description="The tax calculation",
                       Category="My calculations")]
        public static double Tax(double income)
        {
            if (income > 0 && income <= 7000) { 
                return (.10 * income); }
            if (income > 7000 && income <= 28400) { 
                return 700.00 + (.15 * (income - 7000)); }
            if (income > 28400 && income <= 68800) { 
                return 3910.00 + (.25 * (income - 28400)); }
            if (income > 68800 && income <= 143500) { 
                return 14010.00 + (.28 * (income - 68800)); }
            if (income > 143500 && income <= 311950) { 
                return 34926.00 + (.33 * (income - 143500)); }
            if (income > 311950) { 
                return 90514.50 + (.35 * (income - 311950)); }
            return 0;
        }
    }

    In addition, because Excel-DNA uses the native Excel API, you need no COM registration or admin permissions to install your add-in. And it's fast.

    Regards,

    Govert

    Excel-DNA - Free and easy .NET for Excel


    Tuesday, November 27, 2012 8:35 PM

All replies

  • Hi Hants

    If you want something that can be used as a formula in a cell, that's called a "User-Defined Function", UDF for short.

    VSTO is not designed to provide UDFs for Excel. A different technology should be used.

    The best place to get assistance for this would be the Excel for Developers forum:
    http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

    It's probably better if you post there again, rather than my moving this discussion, as some may not read beyond your original question, rather than concentrate on what you really need...


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, November 22, 2012 4:40 PM
    Moderator
  • Hi Hants,

    Thanks for posting in the MSDN Forum.

    I will supplement what Cindy's side. We can add UDF via add-in, however you need override the RequestComAddInAutomationService method of ThisAddIn class and implement IDTExtensibility2 Interface in your UDF class. I would recommend you take a look at the reference: http://blogs.msdn.com/b/andreww/archive/2007/01/15/vsto-add-ins-comaddins-and-requestcomaddinautomationservice.aspx It has detailed steps to approach your goal.

    @Cindy,

    Thanks for you great work.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, November 23, 2012 8:22 AM
    Moderator
  • Hi Tom

    The article to which you link does not discuss AutomationAddins / UDFs, only exposing code to the VBA interface, which is a very different proposition.

    However, there was a somewhat convoluted discussion on this topic a month or so ago:
    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/637c90fa-c84c-428e-b411-800cbed0612f

    Here's an additional references concerning UDF on Adrew Whitechapel's blog (with additional links on the topic):
    http://blogs.msdn.com/b/andreww/archive/2008/01/23/managed-automation-add-ins.aspx


    Cindy Meister, VSTO/Word MVP, my blog


    Friday, November 23, 2012 5:28 PM
    Moderator
  • Hi Hants,

    The easiest way to create Excel UDFs wth .NET is to use the (free) Excel-DNA library.

    This could be the entire code for your add-in:

    using ExcelDna.Integration;
    
    public class MyFunctions
    {
        [ExcelFunction(Description="The tax calculation",
                       Category="My calculations")]
        public static double Tax(double income)
        {
            if (income > 0 && income <= 7000) { 
                return (.10 * income); }
            if (income > 7000 && income <= 28400) { 
                return 700.00 + (.15 * (income - 7000)); }
            if (income > 28400 && income <= 68800) { 
                return 3910.00 + (.25 * (income - 28400)); }
            if (income > 68800 && income <= 143500) { 
                return 14010.00 + (.28 * (income - 68800)); }
            if (income > 143500 && income <= 311950) { 
                return 34926.00 + (.33 * (income - 143500)); }
            if (income > 311950) { 
                return 90514.50 + (.35 * (income - 311950)); }
            return 0;
        }
    }

    In addition, because Excel-DNA uses the native Excel API, you need no COM registration or admin permissions to install your add-in. And it's fast.

    Regards,

    Govert

    Excel-DNA - Free and easy .NET for Excel


    Tuesday, November 27, 2012 8:35 PM