Environment : C#, .NET 3.5 , Windows 7 64, Excel 2007, Visual Studio 2010
I have a VS2010 with 3 projects :
Projet « Business » : A class library with a global static variable
List<BusinessClass>
Project « Add-In » : Extensibility Shared Add-in and the Add-In class:
[GuidAttribute("A9E83593-395F-4112-855D-A68C5088151F"), ProgId("eFrontCubeAddIn.CubeAddIn")]
public class CubeAddIn : Object, Extensibility.IDTExtensibility2, ICustomTaskPaneConsumer {…}
Excel will call OnConnection and I will use the Excel object(ExcelApp = (Excel.Application)application;) to interact. All the interactions within "Add-In" are done via COM (InvokeMember).
The List<BusinessClass> is created and filled from "Add-In"
Project « Formulas » : Class Library. One UDF Excel specific formula. Methods are built using the following model and registered using [ComRegisterFunctionAttribute]RegisterFunction(Type type)…
[Guid("5268ABE2-9B09-439d-BE97-2EA60E103EF6")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class MyFunctions {
public double MyUdfFunction(double number1, double number2) {
{
return number1 * number2;
}
My problem is very simple to explain: I have a Ribbon and a Custom Pane in the project « Add In » filling the List<BusinessClass>.
When the Excel User enters ‘=MyUdfFunction(6 ;7)’ in a cell, my method in « Formulas » is called.
As it comes from a different process (from Excel) the UDF has no access to « Business » instances and List<BusinessClass> is unreachable.
The UDF could be sometimes called more than 500.000 times For speed reasons, I dont want to insert a VBA script calling my UDF in the loop. For speed reasons, I dont want to use Shared memory, Named pipes, WCF or other artifacts. Dont want to use the UDF services (cant use Sharepoint server).
I know that when u are in a different process you cant use data from other processes.
Any idea to give "Formulas" direct access to "Business" instance ? Like having ALL 3 projects in the same process...
Thanks in advance Jean Marie
Your "Formulas" project is an Excel Automation add-in. Add-in Express allows you to have a COM add-in and XLL add-in in the same assembly; they will load into the same AppDomain, check HowTo: Create a COM add-in, XLL UDF and RTD server in one assembly. More precisely, an Automation add-in can be implemented in the same assembly, too but you cannot have the Automation add-in loaded in the same AppDomain in all situations.
Also note another blog: Invoking a COM add-in from an Excel XLL add-in: advanced sample.