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.
