Thursday, 15 September 2011

Communicating Between VSTO and UDF's in Excel

This post will explain how to communicate between a VSTO add-in for Excel, and a managed automation add-in containing a user-defined function (UDF).

The situation is this: you need a UDF to add functionality to Excel. You think it would be nice to add some buttons to the Excel Ribbon, to help with some state management. You would probably create a VSTO add-in to store some program state, and you would create a class for the UDF in the same project. From the UDF you would access that program state by accessing ThisAddIn through the Globals.ThisAddIn static property. But everything is not OK. Excel loads your add-in DLL through VSTO and your VSTO add-in works fine. But Excel loads your add-in DLL into a separate AppDomain for the UDF side of the add-in, which means that a nearly impassable AppDomain boundary exists between your VSTO add-in and your UDF. Here is how to properly communicate between the two sides.

1. Create a new 'Excel 2007 Add-in' project in Visual Studio. You will get a simple ThisAddIn class.

using System;
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;

namespace ExcelAddIn1
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #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
    }
}

2. Create a class by which will be used for communication between the two sides. It should either contain all the data that you want to share, or a way to get anything you need to. In this example, it will be a simple integer.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ExcelAddIn1
{
    public class MySharedClass
    {
        public int MyInt
        {
            get;
            set;
        }

        public MySharedClass()
        {
            MyInt = 0;
        }
    }
}

3. It's time to start getting some COM stuff in here, so set the project to register COM classes. In your project's properties, on the Build tab, check 'Register for COM interop'.

4. OK, back to your shared class. This class will be going through a lot of COM stuff, so let's make it COM-compatible. Note: wherever you see a GUID from here on, PLEASE replace it with a new one.

using System;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace ExcelAddIn1
{
    [Guid("B7E6AF59-5D70-420D-A66A-564B0FA4D443")]
    [ComVisible(true)]
    public interface IMySharedClass
    {
        int MyInt
        {
            get;
            set;
        }
    }

    [Guid("B078FCCA-6DDE-43BC-96B6-36CA81DA9305")]
    [ComVisible(true)]
    public class MySharedClass : IMySharedClass
    {
        public int MyInt
        {
            get;
            set;
        }

        public MySharedClass()
        {
            MyInt = 0;
        }
    }
}

5. Now let's initialize our shared class in ThisAddIn. We do this by overriding the RequestComAddInAutomationService method, to expose the class to other add-ins.

using System;
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;

namespace ExcelAddIn1
{
    public partial class ThisAddIn
    {
        public MySharedClass mySharedClass = null;

        protected override object RequestComAddInAutomationService()
        {
            if (mySharedClass == null)
            {
                mySharedClass = new MySharedClass();
            }
            return mySharedClass;
        }

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            Random rnd = new Random();
            if (mySharedClass == null)
            {
                mySharedClass = new MySharedClass();
            }
            mySharedClass.MyInt = rnd.Next(1, 11);
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// 
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// 
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

6. Add a reference to the 'Extensibility' DLL. This is where the IDTExtensibility2 interface is found.

7. Create a new class which will eventually expose your UDF. First we will retrieve the instance of the shared class so we can use it in the UDF.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Extensibility;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelAddIn1
{
    public class Functions : IDTExtensibility2
    {
        static IMySharedClass mySharedClass = null;

        public void OnAddInsUpdate(ref Array custom)
        {
        }

        public void OnBeginShutdown(ref Array custom)
        {
        }

        public void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
        {
            Excel.Application app = Application as Excel.Application;
            mySharedClass = app.COMAddIns.Item("ExcelAddIn1").Object;
        }

        public void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom)
        {
        }

        public void OnStartupComplete(ref Array custom)
        {
        }
    }
}

8. Program your UDF. This example will simply return MySharedClass.MyInt. You also have to enter some registry values to register mscoree.dll so that your UDF will work.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Extensibility;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace ExcelAddIn1
{
    [Guid("3B81B6B7-3AF9-454F-AADF-FAF06E5A98F2")]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    [ComVisible(true)]
    public interface IFunctions
    {
        int MYINT();
    }

    [Guid("F58C591D-A22F-49AD-BC21-A086097DC26B")]
    [ClassInterface(ClassInterfaceType.None)]
    [ComVisible(true)]
    public class Functions : IFunctions, IDTExtensibility2 // I think IFunctions must be the first in the list 
    {
        static IMySharedClass mySharedClass = null;

        public void OnAddInsUpdate(ref Array custom)
        {
        }

        public void OnBeginShutdown(ref Array custom)
        {
        }

        public void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
        {
            Excel.Application app = Application as Excel.Application;
            mySharedClass = app.COMAddIns.Item("ExcelAddIn1").Object;
        }

        public void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom)
        {
        }

        public void OnStartupComplete(ref Array custom)
        {
        }

        [ComRegisterFunctionAttribute]
        public static void RegisterFunction(Type type)
        {
            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));
            RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);
            key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll", RegistryValueKind.String);
        }

        [ComUnregisterFunctionAttribute]
        public static void UnregisterFunction(Type type)
        {
            Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false);
        }

        private static string GetSubKeyName(Type type, string subKeyName)
        {
            System.Text.StringBuilder s = new System.Text.StringBuilder();
            s.Append(@"CLSID\{");
            s.Append(type.GUID.ToString().ToUpper());
            s.Append(@"}\");
            s.Append(subKeyName);
            return s.ToString();
        }

        public int MYINT()
        {
            return mySharedClass.MyInt;
        }
    }
}

9. You are done! You can build and test it if you want.

26 comments:

  1. Hi Kendall,

    I cannot build because for:

    mySharedClass = app.COMAddIns.Item("ExcelAddIn3").Object;

    I have the error:

    The best overloaded method match for 'Microsoft.Office.Core.COMAddIns.Item(ref object)' has some invalid arguments


    Am I doing something wrong? Please help.

    Best wishes,
    David

    ReplyDelete
    Replies
    1. Hi David,

      What version of Excel are you doing this for? 2010?

      If you are, you may need to tweak the code slightly, since it was built for 2007.

      Delete
  2. This worked fine for me on Visual Studio 2010. David, I noticed you are passing in "ExcelAddIn3" to the Item collection. You must make sure this string exactly matches the Prog Id in the registry under the key: HKCU\Software\Microsoft\Office\Excel\Addins\[Addin Prog ID]

    ReplyDelete
  3. Hi Kendall,

    Thank you very much for this useful post. I was wondering if you could elaborate a bit on the "You also have to enter some registry values to register mscoree.dll so that your UDF will work" step. I registered the add-in dll using regasm but Excel does not seem to recognize the UDF function.

    Thanks!
    Seth

    ReplyDelete
    Replies
    1. The RegisterFunction method does that. There is nothing you have to do manually, except the part in step 3.

      Delete
    2. Yup, I registered for COM interop, however, when trying to use the UDF in Excel, I get #NAME as the code never enters the Functions class in the project. Perhaps it has something to do with the fact that I use a 64 bit system? Or there must be some additional trick...?

      Thanks,
      Seth

      Delete
    3. I never tested it on an x64 system. That may be your problem.

      Delete
    4. Turns out my issue was a silly one - I forgot to add the ExcelAddIn1.Functions add-in in Excel...

      Delete
  4. Hi Kendall,
    I have created an Excel 2010 Add-in project using your code above but It seems that there is a problem with the registration process of my add-in. When I'm trying to load the automation add-in ,I cannot find an entry like ExcelAddIn1.Functions in the list of Automation Servers in Excel. Am I missing something? The project is built on an x64 system and I have followed your instructions.

    Thanks,

    ReplyDelete
    Replies
    1. Are you running a 64-bit version of Excel? I haven't tested either a 64-bit version, or Excel 2010, so I can't make any guarantees about the code. It was designed for Excel 2007.

      Delete
    2. I am running a 64-bit version of Excel 2010.. Is there a workaround??

      Delete
    3. I haven't used it, so I have no idea what the issue is. You can try debugging it.

      Delete
    4. Hi Kendall,
      your code works properly. I had to set Platform Target to x64 at the project settings! Thank you for this useful post.

      Delete
  5. Hi Kendall,

    I have a 64 bit Windows 7 system and am running a 32-bit version of Office 2007. I followed all of the steps outlined here, but do not find the add-in in the automation server section (the COM Add-In created from VSTO exists and functions fine).

    Are the any additional pointers?

    My goal is to host in VSTO the ability to call RESTful web service methods and have the Excel UDFs make calls to these methods.

    ReplyDelete
    Replies
    1. Read the above comments and double-check you've completed step 3.

      Delete
    2. I think the problem was that I did not see the add-in under the Excel Add-Ins and had to click the Automation button and find it.

      How would the add-in be deployed so that the registry keys are created? Do you have any suggestions?

      Delete
    3. Deployment and registration is almost another whole topic, but it consists of basically the sum of VSTO registry settings and Automation settings, both of which you should be able to look up on Google. You're essentially registering both sections separately.

      Delete
  6. Hi Kendall,

    I have one more question. I noticed that my UDF only appears in Excel if I click the Insert function. If I begin typing a function with an =[function name] where [function name] is my function name, the function does not show up.

    I have been playing with the XLL approach and the UDF functions show up under both the insert function and in the context of typing the function in. I was wondering if there is some step that I am missing to achieve this.

    Thanks,

    Lee

    ReplyDelete
    Replies
    1. I don't know much about this. I don't get my functions in the autocomplete list either, but I've never attempted it.

      Delete
  7. First off, fantastic tutorial! It's helped me a lot. This example has worked fine when debugging but as soon as I deploy the add-in to another computer or remove the debug COM add-in and only use the installed version of COM add-in, the "mySharedClass" variable is null in the Functions class. UDFs which don't use the shared class are working still. Have you tried deploying this example?

    ReplyDelete
    Replies
    1. I haven't actually deployed this example, but I've used something very similar. It was deployed several years ago, and there haven't been any problems.

      Delete
  8. the article is simple to understand and best! Thanks for this work..
    I do have created a similar add-in. My requirement is to check from an external c# application to fist check if the add-in is loaded and then programmatically call the add-in UDF in excel using c# code.. do you have an idea how this can be done?

    ReplyDelete
    Replies
    1. As far as I know there's no way to directly access an add-in from external code. You can use an existing workbook and use the UDF as part of a cell formula. You could of course create an instance of the Functions class with COM, but it wouldn't be able to access the VSTO add-in.

      Delete
  9. thank you for taking the time to write that down. This is flabbergasting, just like it was 10 years ago.

    ReplyDelete
  10. Hi Everyone,
    I have created the project as described above by Kendall and also added ribbon to project. It works perfect. Now I need to create a installer for this project. I have created a installer using setup wizard in VS 2010. When I open Excel, the ribbon loaded successfully, but Formula not found in automation list. I have searched so many blogs but didn't get any solution.
    Any help will be appreciated!!!

    ReplyDelete