Excel-DNA + ("Analysis ToolPak - VBA" OR "Solver Add-in") Addins crashing Excel?
Albert
I'm writing a C#.NET COM component that exposes a few Excel functions
using ExcelDNA. This all works fine most of the time.
However, there are some exceptional cases where Excel would crash on
startup with an unhandled exception if I were to enable at least one
of the following Add-ins.
"Analysis ToolPak - VBA" (atpvbaen.xlam)
"Solver Add-in" (solver.xlam)
A few more details:
- The MyAssembly.dna file contains the following:
<DnaLibrary RuntimeVersion="v4.0">
<ExternalLibrary Path="MyAssembly.dll" ComServer="true" />
</DnaLibrary>
- In the same folder are MyAssembly.dll and MyAssembly.xll files.
- Registered using:
regasm /codebase MyAssembly.dll
regsvr32 MyAssembly.xll (to give Excel the fully qualified path to the
MyAssembly.xll shim)
- Interestingly, other add-ins appear to work fine ("Analysis
ToolPak", "Euro Currency Tools", "Lookup Wizard", etc.) with my add-
in.
- When running Excel from the Visual Studio debugger, I'm shown a tab
saying:
"No Source Available
No Symbols are loaded for any call stack frame. The source code cannot
be displayed"
along with an unhandled exception window:
"TargetInvocationException was unhandled
Exception has been thrown by the target of an invocation."
- If I place a break point in the first entry point within my
assembly, which I believe is the "IDTExtensibility2.OnConnection"
method, it doesn't get hit prior to Excel crashing. So I'm not sure
how I should go about debugging this crash.
Tried writing a simple C# function within the dna file itself (using
the JoinThem example http://exceldna.typepad.com/blog/2006/01/getting_started.html)
and including those "problematic" externals and that seemed to work
fine.
Do you have any ideas on where I should start looking to fix this
crash?
Thanks,
Albert
Govert van Drimmelen
This must have something to do with the COM Server support - otherwise
I should have heard of this problem before.
I have no idea why the particular add-ins you mention might interfere.
Maybe they do interesting things in their initialization that clash
with something your add-in does.
I see you register your MyAssembly.dll with RegAsm. This means
the .NET assembly is also visible to COM, independent of the Excel-DNA
support in the .xll which you register with RegSvr32 MyAssembly.xll.
This makes me nervous.
1. Is MyAssembly.dll perhaps trying to act as a COM Add-in via some
IDTExtensibility2 interface? That's probably a bad idea if it is also
used from Excel-DNA.
2. Are you doing anything in an AutoOpen handler?
3. Are you implementing an ExcelDna.Integration.ExcelComAddIn-derived
class in your add-in? Maybe this discussion is relevant:
http://groups.google.com/group/exceldna/browse_frm/thread/143bd77dec8e6579/184d109c6486da42.
4. Could you grow the .dna file that works into a problem case, by
changing the RuntimeVersion and ComServer=true, and implementing
similar code to your add-in?
5. Can you simplify your add-in to one that you can post or send to me
directly so that I can have a look?
Regards,
Govert
> the JoinThem examplehttp://exceldna.typepad.com/blog/2006/01/getting_started.html)
Albert Teoh
You mentioned:
"This means the .NET assembly is also visible to COM, independent of the Excel-DNA
support in the .xll which you register with RegSvr32 MyAssembly.xll.
This makes me nervous."
I then tried to call regsvr32 on my .xll file, which did appear to change the (Default) value in the registry to the full path of the .xll file, as I expected. However, when starting up Excel, my addin ribbon disappeared.
Can you suggest a good way to get more Debugging information out of Excel DNA? I've just managed to download the source code, so perhaps I can add some additional logging to check that my .xll file is being called?
Also to answer your questions:
1. See above. Thanks for the good suggestion. Perhaps I should remove all references to Microsoft.Office and use the ExcelDNA equivalents?
2. Nope, I'm not doing anything with an AutoOpen Handler. I don't implement IExcelAddIn. (Should I be?) Currently the entry point in my example is the OnConnection method. Is this correct?
3. Yes I am now thanks. Unfortunately, I'm not able to see my addin at all after calling regsvr32.
4. See below.
5. Yup, see below.
Sorry for the long email. Thankyou very much for reading this far and really appreciate your help.
Regards,
Albert
--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.
Albert Teoh
I'd also like to add that the rationale behind using Excel DNA is to be able to run two very similar addins (using similar if not the same libraries) concurrently on Excel in separate app domains.
Without Excel DNA, the add-ins would potentially share the same libraries, leading to some problems with incompatible versions.
Thanks,
Albert
Govert van Drimmelen
One big advancement in Excel-DNA this year was exactly to allow all
the add-in parts you need - UDFs, Ribbon, RTD Server and COM objects
from VBA - to run in the same Excel-DNA AppDomain.
-Govert
On Nov 23, 10:08 pm, Albert Teoh <albert.t...@gmail.com> wrote:
> Hi Govert,
>
> > On Wed, Nov 23, 2011 at 3:41 AM, Govert van Drimmelen <gov...@icon.co.za>wrote:
>
> >> Hi Albert,
>
> >> This must have something to do with the COM Server support - otherwise
> >> I should have heard of this problem before.
> >> I have no idea why the particular add-ins you mention might interfere.
> >> Maybe they do interesting things in their initialization that clash
> >> with something your add-in does.
>
> >> I see you register your MyAssembly.dll with RegAsm. This means
> >> the .NET assembly is also visible to COM, independent of the Excel-DNA
> >> support in the .xll which you register with RegSvr32 MyAssembly.xll.
> >> This makes me nervous.
>
> >> 1. Is MyAssembly.dll perhaps trying to act as a COM Add-in via some
> >> IDTExtensibility2 interface? That's probably a bad idea if it is also
> >> used from Excel-DNA.
> >> 2. Are you doing anything in an AutoOpen handler?
> >> 3. Are you implementing an ExcelDna.Integration.ExcelComAddIn-derived
> >> class in your add-in? Maybe this discussion is relevant:
>
> >>http://groups.google.com/group/exceldna/browse_frm/thread/143bd77dec8...
> >> .
> >> 4. Could you grow the .dna file that works into a problem case, by
> >> changing the RuntimeVersion and ComServer=true, and implementing
> >> similar code to your add-in?
> >> 5. Can you simplify your add-in to one that you can post or send to me
> >> directly so that I can have a look?
>
> >> Regards,
> >> Govert
>
> >> On Nov 22, 10:28 am, Albert <albert.t...@gmail.com> wrote:
> >> > Hi Govert,
>
> >> > I'm writing a C#.NET COM component that exposes a few Excel functions
> >> > using ExcelDNA. This all works fine most of the time.
>
> >> > However, there are some exceptional cases where Excel would crash on
> >> > startup with an unhandled exception if I were to enable at least one
> >> > of the following Add-ins.
>
> >> > "Analysis ToolPak -
>
> ...
>
> read more »
Govert van Drimmelen
Hi Albert,
From your example I can clarify some things:
1. You need to get rid of the 'regasm MyAssembly.dll' registration.
Whatever it does for you, we need to do in a different way if you are
to make an Excel-DNA addin.
2. You can get rid of the Com RegisterFunction and UnregisterFunction
- they shouldn't be used and you should not write those entries to the
registry for your add-in to work with Excel-DNA.
3. If you want to make a Ribbon, derive a class from
ExcelDna.Integration.CustomUI.ExcelRibbon directly. Don't implement
IRibbonExtensibility yourself - ExcelRibbon implements that interface
already.
4. ExcelRibbon also inherits from ExcelComAddIn, so you can override
the OnConnection and other events in your ExcelRibbon-derived class.
Then you need not implement ExcelComAddIn with the funny hack
directly.
5. Don't load the Excel-DNA addin library in OnStartupComplete - it is
already loaded, currently you are loading the .dll again as an
Automation Add-In. That's not good.
6. To expose UDF functions via Excel-DNA, make the functions "public
static". This is different to how functions exposed via Automation Add-
Ins worked. For exposing to VBA we follow a different route.
7. To call the UDF functions from VBA first check that you can use
MyResult = Application.Run("JoinThem", "asd", "qwe").
8. To make classes in your Excel-DNA library available as COM classes
in VBA, once everything above works perfectly, check this thread:
http://groups.google.com/group/exceldna/browse_frm/thread/4c5a71efbe96d885/c9daf0c11ac5f398,
and this thread http://exceldna.codeplex.com/discussions/252721.
Then either register the .xll with "Regsvr32 MyAddin.xll" (still never
RegAsm!) or call ComServer.DllRegisterServer() in an AutoOpen handler
to register on the fly.
To enable early-binding (Tools->References) in VBA you'll need to make
a .tlb file from MyAssembly.dll using the tlbexp.exe utility.
The .dna file, your .dll and the .tlb you generate can eventually be
packed into a single .xll file by ExcelDnaPack, so that you have to
distribute only a single file.
Now answering your questions directly:
1. Excel-DNA needs no registration to work since it does not integrate
with Excel using COM - it uses the native Excel C interface. Excel
will load your .xll if your double-click on it, or if you select it
from File->Open. You can set the add-in to load automatically when
Excel starts by going to the Excel add-ins dialog (press Alt+t, i),
then browse to your .xll file. For some of the advanced integration
with VBA registration as a COM server via Regsvr32 can be useful, but
the same registration can also be run from inside the add-in at
startup. The one case where registration is required is if you want to
use an RTD server or COM class without having the add-in loaded. I
doubt you have to worry about that at this stage.
2. You had problems with the Ribbon because your .dll was still being
loaded directly as a COM add-in. With Excel-DNA the COM stuff is done
behind the scenes - you just derive from ExcelRibbon and Excel-DNA
will do the required registration and hook-up when needed. Mixing the
different ways of loading a single .dll causes the confusion you see.
The Ribbon disappeared after 'Regsvr32 MyAddIn.xll' registration
because the COM add-in way was no longer loading the .dll, and Excel-
DNA doesn't show the Ribbon since it does not inherit from
ExcelRibbon.
3. It doesn't matter for the ribbon and COM events (values like
Extensibility.ext_ConnectMode) whether you use the Microsoft.Office
namespace of the ExcelDna.Integration.CustomUI namespace. The values
and COM interfaces are equivalent.
4. You need not implement IExcelAddIn and its AutoOpen method, but it
can be useful for doing some startup stuff like doing the COM
registration for VBA. It is the real 'entry point' of your add-in. The
ExcelRibbon COM class with it's OnConnection happens only if you have
an ExcelRibbon that is successfully loaded. Simple add-ins only expose
some functions and won't have this.
Anyway, so your add-in might simplify to something like I put below.
There's a lot here to get used to, and it's not really documented and
understandably confusing when you come from a COM add-in.
But I'm sure the questions you have are common, and I look forward to
any follow-ups you might have.
Regards,
Govert
MyAddIn.dna
-----------
<DnaLibrary Name="My Add-in" RuntimeVersion="v4.0" >
<ExternalLibrary Path="MyAssembly.dll" ComServer="true"
Pack="true"
</DnaLibrary>
/////////////////////////////
// MyAddin.cs:
////////////////////////////
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
namespace MyAssembly
{
public class MyAddIn : IExcelAddIn
{
public void AutoOpen()
{
// Maybe add this to register types when add-in is
loaded,
// instead of calling "regsvr32 MyAddIn.xll". It is
exactly the code that runs when "regsvr32" is run.
// ComServer.DllRegisterServer();
}
public void AutoClose()
{
}
}
[ComVisible(true)]
[Guid("D818FDF8-5DB4-4295-B431-8A077327D374")]
public class MyAddinRibbon : ExcelRibbon
{
public static Microsoft.Office.Interop.Excel.Application
ExcelApp;
#region Overrides of
ExcelDna.Integration.CustomUI.ExcelComAddIn
public override void OnConnection(object application,
Extensibility.ext_ConnectMode connectMode, object AddInInst, ref Array
custom)
{
ExcelApp =
(Microsoft.Office.Interop.Excel.Application)application;
}
public override void
OnDisconnection(Extensibility.ext_DisconnectMode RemoveMode, ref Array
custom)
{
}
public override void OnAddInsUpdate(ref Array custom)
{
}
public override void OnStartupComplete(ref Array custom)
{
}
public override void OnBeginShutdown(ref Array custom)
{
}
#endregion
#region Implementation of IRibbonExtensibility
// If you want to override how the xml string is found
public override string GetCustomUI(string RibbonID) {...}
#endregion
// Other Ribbon handlers here.....
public void LoadMyForm(IRibbonControl control)
{
MessageBox.Show("Form loaded");
}
}
}
///////////////////////
// MyUdfs.cs
//////////////////////
namespace MyAssembly
{
public class MyUdfs
{
[ExcelFunction(Category = "MyUdfs")]
public static object JoinThem(object string1, object string2)
{
return string1.ToString() + string2.ToString();
}
}
}
//////////////////
// NewComServer.cs
// From VBA easiest is to instantiate via late-binding as
// Dim MyHelper As Variant =
CreateObject("TestComServer.FunctionHelper")
// result = MyHelper.JoinThem("asd", "qwe")
// Could also make Type Library for early binding.
// Need to call Regsvr32 MyAddIn.xll to register, or from AutoOpen as
above.
//////////////////
namespace MyAssembly
{
public class NewComServer
{
[ComVisible(true)] // Could set it for the whole assembly too
- [assembly:ComVisible(true)]
[ComDefaultInterface(typeof(IMyUdfs))] // Not sure about
this ...?
[Guid("E2219316-E90E-4FA7-BEF4-B3975E0DA29F")]
[ProgId("TestComServer.FunctionHelper)] // Might not need
this? Then it uses NameSpace.ClassName?
[ClassInterface(ClassInterfaceType.AutoDual)] // might not
need this - Allows late-bound access
public class MyUdfs : IMyUdfs
{
#region Implementation of IMyUdfs
public object JoinThem(object string1, object string2)
{
// Delegate this function to the normal (static)
implementation
return MyUdfs.JoinThem(string1, string2);
Albert
that Excel-DNA does most of the work for me. Tried it out on my
simplified add-in and worked like a charm.
I'll avoid late binding so our users can stick with their old ways. :)
Just out of curiosity, what's the advantage of late binding as opposed
to early binding, besides the convenience of not having to manually
add a reference to the tlb?
Calling ComServer.DllRegisterServer() from the AutoOpen is awesome.
I'm glad you pointed out that the JoinThem ExcelFunction needs to be
public static in order to access them as a UDF.
Thanks again.
Kind regards,
Albert
> in VBA, once everything above works perfectly, check this thread:http://groups.google.com/group/exceldna/browse_frm/thread/4c5a71efbe9...,
> and this threadhttp://exceldna.codeplex.com/discussions/252721.
Govert van Drimmelen
The late binding means you don't have to generate and manage a .tlb.
And it also means that the VBA is more robust to changes in your COM
server classes. If you generate .tlbs that are referenced by VBA the
VBA project caches or stores somewhere stuff like the CLSIDs, so you
have to be a bit more careful with versioning. And you should keep in
mind the COM interfaces can never change - once you have create
IMyClass and 'published' it with a CLSID, you should never change the
methods or signatures of IMyClass. Any changes would have to go into
IMyClassEx or something.
Late-binding always works through the dispatch interface, which is
more tolerant to changes.
Making a type library and Tools->Referencing from VBA means you get
the best performance and intellisense in VBA.
Cheers,
Govert
chicagogrooves
ExcelDna file from automation, for testing purposes. I'm using
automation from a command line script via ruby:
XL = WIN32OLE.new('Excel.Application')
XL.visible = true
puts "loading addin.."
XL.registerxll 'c:/src/ExcelToolkit/Addin.xll'
puts "addin loaded.."
XL.workbooks.open Dir.pwd + '/../examples/SavedSubject.xlsx'
sleep 5
val = XL.run 'CanBeCalledFromAutomation'
puts "got: " + val
(In Addin.dna)
[Dna.ExcelFunction(Description="For testing purposes")]
public static string CanBeCalledFromAutomation(string other = "")
{ return String.Format("Yup: {0}", other); }
Excel does pause for a second on the registerxll line, yet when I
observe the UI during the 'sleep' statement, I dont see my addin's
menu.
When we get to the XL.Run line, I get the dreaded "Cannot run the
macro 'CanBeCalledFromAutomation'. The macro may not be available in
this workbook or all macros may be disabled."
Of course, when the addin is loaded via File Open in excel, I have the
menu items, as well as I can write formulas which call the function.
Is there a 'standard' function I can call to rule out the possibility
that all macros are disabled ?
Am I right in my expectation that I should be able to call my function
via Application.Run, per the "JoinThem" example ?
Thanks for your great help..
> > > MyResult =Application.Run("JoinThem", "asd", "qwe").
> ...
>
> read more »
Govert van Drimmelen
I don't yet have a good grip on what the situation is when loading
Excel and add-ins via COM automation.
1. Can you load the workbook before loading the add-in, to see whether
it makes a difference?
2. Does it make a difference if you pass a string in for your
function, since Excel-DNA ignores your 'default' value.
val = XL.run 'CanBeCalledFromAutomation', 'Test'
-Govert
> > > > public override void OnBeginShutdown(ref Array custom)...
>
> read more »
chicagogrooves
embarassing :)
> > > MyResult =Application.Run("JoinThem", "asd", "qwe").
> ...
>
> read more »
Dean Radcliffe
- and no error was thrown (or at least it didn't appear as an
exception) - so the addin was never loaded.