Some help for a ExcelDna beginner

Jan 23, 2012 at 2:42 PM

Hello,

First I want to congratulate Govert for the quality of his work. He had made simple and intuitive something that is very complex when your COM knowledge is very limited. I need advice on how properly use ExcelDna UDF.

I summarize my context: I have a C# 2010 (.Net 3.5 runtime) project with 2 class libraries (DLLs). One is called "B" for Business and holds my data, the second one is called "F" for Formulas and holds the C# UDF. Excel is 2007, I run Windows 7 64bits and build with AnyCpu.

So far so good, I can write and call UDFs. The problem is when "F" needs to access "B" instances.  ("B" is a static class, so only 1 instance).

It seems that when the code inside an UDF (in "F" DLL) invokes data from "B", a new instance of "B" is created 1st. From the UDF I have no access to the data previously held in "B" when I launched my program.

I added a static constructor to "B" and it is called twice (when my project starts and the 1st time an UDF is executed). It does not seems unlogical, but it makes my life miserable..

What is your advice so I can access to class "B" instance ?

Thanks in advance,

Jean-Marie

Coordinator
Jan 23, 2012 at 3:13 PM

Hi Jean-Marie,

The issue is that B.DLL is being loaded into different contexts, by the .NET runtime and by the Excel-DNA runtime. I have actually fixed some scenarios in the latest check-in (Change Set 71871 here: http://exceldna.codeplex.com/SourceControl/list/changesets), in particular the scenario where everything is packed and there are no extra copies of the B.DLL assembly in the directory should now be fine.

It depends on the .dna file configuration, and whether you have packed the assemblies in to the .xll file, whether "B.DLL" is loaded multiple times.

 

Some examples and my current understanding, in all cases F.DLL references B.DLL:

* only F.DLL in the .dna file -> B.DLL is loaded only once.

* both F.DLL and B.DLL as ExternalLibraries in the .dna file, not packed, no LoadFromBytes='true' -> B.DLL is loaded once.

* both F.DLL and B.DLL as ExternalLibraries in the .dna file, not packed, with LoadFromBytes='true' -> B.DLL is loaded twice.

* both F.DLL and B.DLL as ExternalLibraries in the .dna file, packed, a copy of B.DLL as a file next to the .xll -> B.DLL is loaded twice.

* both F.DLL and B.DLL as ExternalLibraries in the .dna file, packed, no copy of B.DLL -> B.DLL is loaded twice in v 0.29, and once in latest version.

* Some combinations where B.DLL is not an ExternalLibrary but just a <Reference> might behave differently to the scenarios above.

 

How does this compare with your testing?

What does your .dna file look like?

Are you packing the .dlls into the .xll, or use the LoadFromBytes option?

Can you test with the latest check-in, and compare?

 

Regards,

Govert

Jan 23, 2012 at 4:48 PM

Thank you Govert for your Quick answer.

I am investigating more my code, what I did not tell you is that B (business DLL) is launched by a VSTO add-in. It seems that excel makes a local copy of the B and  F (Formulas DLL) DLLs so process explorer finds 2 instances of F and B !!! This also can be the source of the problem.

My .DNA file contains only 1 line, a reference to F DLL (<ExternalLibrary Path="f.dll"/>). I prefer to have all the code in my project.

I will investigate, use your latest check-in and tell you more tomorow.

Regards
Jean-Marie

Coordinator
Jan 23, 2012 at 5:02 PM
Edited Jan 23, 2012 at 5:10 PM

Hi Jean-Marie,

Indeed - this is another case where the assembly is loaded more than once. VSTO loads the library into the default AppDomain (or if you have made a shim, into the AppDomain created by the shim).

Excel-DNA creates an AppDomain for each add-in, and will load the library F.DLL there, and the runtime will load a code of the referenced assembly B.DLL into that AppDomain too. Your VSTO add-in and Excel-DNA won't be able to share an AppDomain.

Some other options are to

1. port the other add-in to Excel-DNA, which might be easy or hard depending on how much of the VSTO object model extensions you use, or

2. put in place some kind of cross-AppDomain communication, e.g. using .NET remoting, or

3. understand that the two add-ins live in separate AppDomains, and there will be two 'B' instances.

At least the behaviour you see makes sense now, but the latest check-in will not make a difference to your case.

Regards,

Govert 

Jan 24, 2012 at 3:27 PM

Hi Govert,

I finally decided to get rid of the VSTO AddIn and use COM code to create an AddIn with all the IDTExtensibility2 stuff.
I now have a single instance of "B" DLL loaded and everything works fine.

I have too much code already written in VSTO and I will stick to it.
But I will definitely use your great UDF support.

If my project goes live (probability 90%), I will donate to participate to the great development you have achieved.

A small advice (if I may): ExcelDNA is great and very powerful, but features are being discovered bits by bits and sometimes very randomly. Actually I did not realize that you could replace VSTO AddIn.

To see the big picture, it would be great to have a complete example of a C# project showing all ExcelDNA possibilities -Ribbons, CustomPanes, Events, UDF, ... (with a minimum of .dna code, because unless I am mistaken this code not debuggable). And if that project could install the addin automatically and also automatically load the UDF Xll that would be perfect.

Regards and thanks for ur support.

Jean-Marie

Coordinator
Jan 24, 2012 at 3:54 PM
Edited Jan 24, 2012 at 7:45 PM

Hi Jean-Marie,

 

I'm not sure I understand both of:

    "I finally decided to get rid of the VSTO AddIn"

        and

    "I have too much code already written in VSTO and I will stick to it"

Certainly if you have a COM add-in with IDTExtensibility2, it can be part of your Excel-DNA add-in (even packed, and needing no registration) and load in the Excel-DNA AppDomain.

 

VSTO adds some powerful wrappers for Excel types, like the ListObject, and gives a rich object model for the Ribbon integration. If you use a lot of these features, it is not so easy to move away from VSTO. Is this your case?

 

I agree very much that Excel-DNA could do with better documentation, especially of the overview kind you suggest.

Any help in that regard would be greatly appreciated.

 

The best project that includes nearly all of the Excel-DNA features, and is published under a liberal open-source license is the FinAnSu library (http://code.google.com/p/finansu/). There are Ribbons, RTD servers, functions and macros.

 

Kind regards,

Govert

Jan 26, 2012 at 5:21 AM

Hello Govert,

Damn you are right, I just did an initial try without VSTO "_excel.ActiveCell" and I did not have to modify my code. But later I tried "_excel.ScreenUpdating=false" and CRASH. I then perfectly understood the meaning of your reply...

So I am into rewritting stuff with Invokers... and will keep using your brilliant UDF support

Thanks for your support.

Coordinator
Jan 26, 2012 at 5:41 AM

I don't think VSTO (the Microsoft.Office.Tools.Excel assembly) is involved needed to access the Excel COM automation interfaces (like Application.ActiveCell and Application.ScreenUpdating).

One key to accessing the COM interfaces from an Excel-DNA add-in is to get the right 'root' Application object (what you are calling _excel). Excel-DNA gives you access to the Application object of the instance your add-in is running in via the ExcelDnaUtil.Application property. 

Then to access the COM automation interfaces there are basically three alternatives:

  1. Do your own late-binding, via Type.InvokeMember, or
  2. Use the Primary Interop Assemblies (PIA) for a specific Excel version (the Microsoft.Office.Interop.Excel assembly), or
  3. Use version-independent interop assemblies, like the NetOffice project (http://netoffice.codeplex.com).

All three approaches are compatible with Excel-DNA and allow you to access the COM automation object model from your add-in, and I can support you if you use any of these approaches.

A final note is that you have to be really careful when accessing Excel from other threads - there nature of COM and how Excel processes COM requests from other threads means you should be careful to follow the COM rules, and be prepared to deal with the possible errors on each COM call.

-Govert

Jan 26, 2012 at 3:13 PM

Hi Govert,

I have finished translating my VSTO code to InvokeMembers. Not a very gratifying dvp task...

I am stuck with something that is mandatory for me : Create, Access receive events from NamedArea.
I was using var namedRange = (area.Sheet.GetVstoObject()).Controls.AddNamedRange(cubeRange, areaName);

I cant find the equivalent in COM inerop ? Do you have it in ExcelDna ?

Regards

Jean-Marie

 

 

Coordinator
Jan 26, 2012 at 4:39 PM
Edited Jan 26, 2012 at 7:28 PM

Hi Jean-Marie,

If you are trying to define a name referring to a range, this would work in VBA:

Set myName = Application.Names.Add("MyRange", Range("A1"))

For the events, you'd have to hook up the WorkSheet events, and check whether the particular event's range intersects with your named range.

 

If is also possible with the C API, but if you are already doing the COM interop that would be easiest.

Regards,

Govert

Feb 5, 2012 at 1:30 PM
Edited Feb 5, 2012 at 1:31 PM

Hello Govert,

I have finally decided to have my AddIn talk to my formulas using excel cells. Its not perfect but it works.
I will be using ExcelDna UDFs.

Just to summarize, I have done a 15 lines program that summarize my problem in case you have some magical idea this weekend.
AddIn express claims that they have a solution to my problem, do you have an idea what trick they could use ? (http://www.add-in-express.com/creating-addins-blog/2010/03/24/addin-xll-rtd-one-assembly/)

My DNA file:
<DnaLibrary>
 <ExternalLibrary Path="OneAssemblyAddIn.dll" />
</DnaLibrary>

Have a good day.

using System;
using System.Collections.Generic;
using ExcelDna.Integration;
namespace OneAssemblyAddIn {

public partial class ThisAddIn {

public class CarClass {
public string Model { get; set; }
public int Power { get; set; }
}

public static List Cars = new List();

private void ThisAddInStartup(object sender, EventArgs e) {
Cars.Add(new CarClass { Model = "Smart", Power = 60 });
Cars.Add(new CarClass { Model = "Range Rover", Power = 210 });
var count = Cars.Count; //Count=2
}

private void ThisAddInShutdown(object sender, EventArgs e) { }

[ExcelFunction(Description = "Test1", Category = "Useful functions")]
public static double Test1(double x, double y) {
var count = Cars.Count; //Count=0 because Test1 will create a new instance of ThisAddIn
return x * y;
}

private void InternalStartup() { Startup += ThisAddInStartup; Shutdown += ThisAddInShutdown; }

}
}

Coordinator
Feb 5, 2012 at 3:48 PM
Edited Feb 5, 2012 at 3:50 PM

Hi Jean-Marie,

The issue is not to put COM add-ins, RTD servers and UDF functions in a single add-in - Excel-DNA supports this perfectly, even with extended support for COM servers accessible from VBA. Excel-DNA uses a technique similar to Add-In Express in having an unmanaged loader which controls the AppDomain that your add-in is loaded into. I'm not sure whether Add-In Express supports loading your add-in into the default AppDomain (I doubt it), but it is not something I am keen to support in Excel-DNA. In fact, Excel-DNA is structured to allow the Ribbon COM add-in, RTD servers and the UDF parts to load into the same AppDomain, to alleviate issues you'd otherwise have and that sometimes arise in Add-In Express. Loading your add-in (including VSTO add-ins) into a separate AppDomain via a COM shim is also the configuration recommended by Microsoft.  

However, it seems your example still uses the Microsoft Visual Studio Tools for Office (VSTO) libraries - so you are making a VSTO application add-in (this is in the other part of your 'partial class'). Your use of VSTO makes the VSTO side of the add-in load into the default AppDomain, and causes the original issue you asked about. Add-In express is an alternative to VSTO, and my understanding is that your resulting add-in will also not be able to use the VSTO classes. If you continue to use VSTO, you will have to consider one of the options listed above for communication between the AppDomains. If you are able to move away from VSTO to using only the 'plain' COM add-in interfaces, Excel-DNA can contain all aspects of your add-in and will load everything into the same AppDomain.

It might be that you are confusing the COM interop assemblies (they have namespace Microsoft.Office.Interop.Excel) with the VSTO assemblies (Microsoft.Office.Tools.Excel). Excel-DNA supports using the COM interfaces via the COM interop assemblies, but does not support using the VSTO assemblies in your add-in.

I have given some details of the options for working around these issues and structuring your add-in in my previous replies to this thread. I am happy to support any of the alternatives you choose.

If you have any questions about my previous replies I'd be happy to clarify.

Regards,

Govert 

Feb 6, 2012 at 9:46 AM

Hello Govert,

Using VSTO for the code sample was done on purpose of minimizing the # of lines.
Rewriting the VSTO code with Invokers (starting from a Shared AddIn) just solved the problem of the local copy of the DLLs that VSTO does.

With or without VSTO my data instances are unaccessible. I have found a workaround. And will keep you posted when I am done.

Again thanks for all your help.

JM

Coordinator
Feb 6, 2012 at 9:50 AM

Hi Jean-Marie,

I still think you are confusing VSTO with the COM interop assemblies.

Can you post a list or screenshot of the References of your library?

-Govert

Feb 6, 2012 at 10:10 AM

Hi Govert

My rewritten COM (Invoker) addin references:
Microsoft.Office.Core
Microsoft.Office.Interop.Excel

My yesterday sample (VSTO) references: (the VS template did it automatically)
Microsoft.Office.Interop.Excel
Microsoft.Office.Tools.Common.v9.0
Microsoft.Office.Tools.Excel.v9.0
Microsoft.Office.Tools.v9.0
Microsoft.VisualStudio.Tool.Applications.Runtime.v9.0

JM

Coordinator
Feb 6, 2012 at 10:18 AM

Hi Jean-Marie,

OK, for the rewritten COM addin, we will be able to load everything into the Excel-DNA add-in's AppDomain.

Can you explain if you have any problem in this configuration?

-Govert

Feb 6, 2012 at 12:36 PM

Hello Govert,

I will rewrite the 15 lines (above) sample with Microsoft.Office.Interop.Excel tonight and test it.

Regards

JM

Coordinator
Feb 6, 2012 at 1:13 PM

Hi Jean-Marie,

Instead of the ThisAddInStartup you'd add a class to your Excel-DNA add-in that implements IExcelAddIn:

public class MyAddIn : IExcelAddIn 

{    

    public void AutoOpen()    

   { 

        // Initialize your Cars here....
   }    

   // Not called when Excel closes normally, but is called if the add- in is removed from the add-in list.  

    // Good for undoing any UI changes.    

   public void AutoClose()    

   { 

   } 

 

Regards,

Govert

Feb 7, 2012 at 5:51 AM

Hello Govert,

I have got the point and its exiting. Only 1 instance of my DLL then I have access to all my data. (I have included my code at the end of this post, I think I did what u suggested)

Now a serie of Questions:
a) I need to do add a simple Ribbon that has access to the Cars instance (I will use ur ExcelDna Ribbon)
b) I need to include a Custom Task Pane (WPF inside FormsHost) that has access to the Cars Instance
c) I need to create Named Ranges (that appears in the combo above cell A1). My Custom Task Pane need to interact with the Named range (i.e. u click in a control in the Custom Task Pane and the Name Range is selected).
d) I need access to the Excel Application, Excel WorkBooks and Excel WorkSheet
e) The final package (XLL?)should load automatically when Excel and be provided to the user as a ClickOnce or Installshield package ?

I hope I am not crying for the moon...

Regards


Thanks

using System.Collections.Generic;
using ExcelDna.Integration;

namespace DnaAddIn {
public class AddIn {

public class CarClass {
public string Model { get; set; }
public int Power { get; set; }
}
public static List Cars = new List();

[ExcelFunction(Description = "Add two numbers", Category = "DnaAddIn")]
public static double MyUdf(double a, double b) {
var count = Cars.Count; //Count=2 return a + b;
}

public class MyDnaAddIn : IExcelAddIn {
public void AutoOpen() {
Cars.Add(new CarClass { Model = "Smart", Power = 60 });
Cars.Add(new CarClass { Model = "Range Rover", Power = 210 }); var count = Cars.Count;
//Count=2 }

public void AutoClose() {
}
}
}
}

Coordinator
Feb 7, 2012 at 6:40 AM

Hi Jean-Marie,

OK, everything in your list should be fine, except I can't help you with the ClickOnce / Installshield part. But with Excel-DNA you can pack everything into a single file .xll including all your functionality, which could even register itself as an add-in so that it opens automatically. So instead of giving your users a .msi installer file, you just give them the .xll which the double-click to open (and install).

For all the other topics, I suggest you check through the small samples in the distribution, and search here and on the Google group (http://groups.google.com/group/exceldna) for information.

Of course if you have any specific questions I'd be happy to help, here or on the Google group.

Regards,

Govert

Feb 7, 2012 at 4:34 PM

Hi Govert,

I was busy today and I just started to work from your AddIn. This is what I did after looking in the Google Group and it works fine.

Please just confirm, it the right way to work ?

using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); //Mandatory if Windows in English but regional settings are not
Application _excel = (Application) ExcelDnaUtil.Application;
Workbook _workbook = _excel.Workbooks.Add();
WorkSheet _sheet = (Worksheet)_excel.ActiveSheet;
_sheet.Cells[1, 1] = "Test OK";

Thanks again,

JM