Creating Application.COMAddIns Addins
761 views
Skip to first unread message
aberglas
May 19, 2012, 6:11:53 AM5/19/12
to Excel-DNA
Hello Govert,
I got your code to work by adding the reflection hacks, code below.
One problem I have is that I actually end up with multiple instances
of the add in installed. They all end up in
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins
with random names like AddIn.92bf9e5a30e747b2b35b4f15bb54bd5d
VBA testing for .Object is Nothing seems to find the real one OK.
I think this is a result of not having a ProgId. Is there any way to
set this? Searching through Descriptions does seem dubious anyway.
(The full description needs to be searched, as there can be different
addins for COM and Ribbon etc.)
The next problem is how to actually open the add in. It's path is not
in the registry entries. In XL 2010 Workbooks.Open will say its being
opened, but not actually open it. File > Open does seem to work, but
with macro security warnings, each time. I do not want to resort to a
SendKeys Hack! I suspect this is all to do with no ProgID. (And
possibly GUID, although they do not seem to be used.)
Another issue is that you use .Net 4.0 dynamic magic. I could not see
how to run this with 3.5, which is much more available. That
AddInInst parameter has a weird System.__COMObject type, which
Microsoft.VisualBasic.Information.TypeName(AddInInst) reports to be
"COMAddIn", but I do not believe it. COM must hook deeply into
the .Net type system, pretending that the dynamic COM proxy object is
the real one. Anyway help here would be appreciated.
Thanks for you're great support.
Anthony
==============================
using System;
using System.Reflection;
using System.Runtime.InteropServices; // For ComVisible
using System.Windows.Forms;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI; // ExcelComAddIn lives here
using ExcelDna.Integration.Extensibility; // E.g. ext_ConnectMode
using Excel = Microsoft.Office.Interop.Excel;
// From Govert.
namespace Test {
[ComVisible(true)]
public class MyComAddIn :
ExcelDna.Integration.CustomUI.ExcelComAddIn {
AddInHelper _helper;
public MyComAddIn() {
}
public override void OnConnection(object Application,
ext_ConnectMode ConnectMode, object AddInInst, ref Array
custom) {
// Or reference the Interop library
// and cast to Microsoft.Office.Core.COMAddIn
//var xx = AddInInst.GetType() + ""; // System.__COMObject
//var zz =
Microsoft.VisualBasic.Information.TypeName(AddInInst); //COMAddIn
//var tt = (Microsoft.Office.Core.ComAddIn)AddInInst;
dynamic addInInst = AddInInst;
_helper = new AddInHelper();
addInInst.Object = _helper;
//MessageBox.Show("OnConnection");
// ?? need to call super here to get Application?
}
public override void OnDisconnection(ext_DisconnectMode
RemoveMode,
ref Array custom) {
//MessageBox.Show("OnDisconnection");
}
public override void OnAddInsUpdate(ref Array custom) {
//MessageBox.Show("OnAddInsUpdate");
}
public override void OnStartupComplete(ref Array custom) {
//MessageBox.Show("OnStartupComplete");
}
public override void OnBeginShutdown(ref Array custom) {
//MessageBox.Show("OnBeginShutDown");
}
}
[ComVisible(true)]
public class AddInHelper {
public string SayHello() {
return "Hello from the future!";
}
[ComVisible(true)]
public string ActiveCell3() {
var app = (Excel.Application)ExcelDnaUtil.Application;
Excel.Range r = app.ActiveCell;
return "ActiveCell3: " + r.Value;
}
}
public class AddIn : IExcelAddIn {
//public void AutoOpen() {
// ExcelComAddInHelper.LoadComAddIn(new MyComAddIn()); //
Null pointer here?
//}
public void AutoOpen() {
try {
var com_addin = new MyComAddIn();
// We want to do this:
// com_addin.DnaLibrary =
ExcelDna.Integration.DnaLibrary.CurrentLibrary;
// But the DnaLibrary property is marked 'internal' to
ExcelDna.Integration.
// v0.29 workaround: set by Reflection
com_addin.GetType().InvokeMember("DnaLibrary",
BindingFlags.NonPublic | BindingFlags.Instance |
BindingFlags.SetProperty, null, com_addin, new object[]
{ DnaLibrary.CurrentLibrary });
ExcelComAddInHelper.LoadComAddIn(com_addin);
} catch (Exception e) {
MessageBox.Show("Error loading COM AddIn: " +
e.ToString());
}
}
public void AutoClose() {
}
}
}
/*
' In VBA:
Sub TestDnaComAddIn()
Dim cai As COMAddIn
Dim obj As Object
For Each cai In Application.COMAddIns
' Could check cai.Connect to see if it is loaded.
Debug.Print cai.Description, cai.GUID
If InStr(cai.Description, "DnaDetective (COM Add-in Helper)")
Then
Set obj = cai.Object
If obj Is Nothing Then
Debug.Print "ObjNothing"
Else
Debug.Print obj.SayHello(), obj.ActiveCell3
End If
End If
Next
End Sub
Sub opendd()
Workbooks.Open "D:\Dropbox\1Anthony\V2010k\ExcelDna\DnaDetective
\DnaDetective\bin\Debug\DnaDetective.xll"
End Sub
*/
I got your code to work by adding the reflection hacks, code below.
One problem I have is that I actually end up with multiple instances
of the add in installed. They all end up in
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins
with random names like AddIn.92bf9e5a30e747b2b35b4f15bb54bd5d
VBA testing for .Object is Nothing seems to find the real one OK.
I think this is a result of not having a ProgId. Is there any way to
set this? Searching through Descriptions does seem dubious anyway.
(The full description needs to be searched, as there can be different
addins for COM and Ribbon etc.)
The next problem is how to actually open the add in. It's path is not
in the registry entries. In XL 2010 Workbooks.Open will say its being
opened, but not actually open it. File > Open does seem to work, but
with macro security warnings, each time. I do not want to resort to a
SendKeys Hack! I suspect this is all to do with no ProgID. (And
possibly GUID, although they do not seem to be used.)
Another issue is that you use .Net 4.0 dynamic magic. I could not see
how to run this with 3.5, which is much more available. That
AddInInst parameter has a weird System.__COMObject type, which
Microsoft.VisualBasic.Information.TypeName(AddInInst) reports to be
"COMAddIn", but I do not believe it. COM must hook deeply into
the .Net type system, pretending that the dynamic COM proxy object is
the real one. Anyway help here would be appreciated.
Thanks for you're great support.
Anthony
==============================
using System;
using System.Reflection;
using System.Runtime.InteropServices; // For ComVisible
using System.Windows.Forms;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI; // ExcelComAddIn lives here
using ExcelDna.Integration.Extensibility; // E.g. ext_ConnectMode
using Excel = Microsoft.Office.Interop.Excel;
// From Govert.
namespace Test {
[ComVisible(true)]
public class MyComAddIn :
ExcelDna.Integration.CustomUI.ExcelComAddIn {
AddInHelper _helper;
public MyComAddIn() {
}
public override void OnConnection(object Application,
ext_ConnectMode ConnectMode, object AddInInst, ref Array
custom) {
// Or reference the Interop library
// and cast to Microsoft.Office.Core.COMAddIn
//var xx = AddInInst.GetType() + ""; // System.__COMObject
//var zz =
Microsoft.VisualBasic.Information.TypeName(AddInInst); //COMAddIn
//var tt = (Microsoft.Office.Core.ComAddIn)AddInInst;
dynamic addInInst = AddInInst;
_helper = new AddInHelper();
addInInst.Object = _helper;
//MessageBox.Show("OnConnection");
// ?? need to call super here to get Application?
}
public override void OnDisconnection(ext_DisconnectMode
RemoveMode,
ref Array custom) {
//MessageBox.Show("OnDisconnection");
}
public override void OnAddInsUpdate(ref Array custom) {
//MessageBox.Show("OnAddInsUpdate");
}
public override void OnStartupComplete(ref Array custom) {
//MessageBox.Show("OnStartupComplete");
}
public override void OnBeginShutdown(ref Array custom) {
//MessageBox.Show("OnBeginShutDown");
}
}
[ComVisible(true)]
public class AddInHelper {
public string SayHello() {
return "Hello from the future!";
}
[ComVisible(true)]
public string ActiveCell3() {
var app = (Excel.Application)ExcelDnaUtil.Application;
Excel.Range r = app.ActiveCell;
return "ActiveCell3: " + r.Value;
}
}
public class AddIn : IExcelAddIn {
//public void AutoOpen() {
// ExcelComAddInHelper.LoadComAddIn(new MyComAddIn()); //
Null pointer here?
//}
public void AutoOpen() {
try {
var com_addin = new MyComAddIn();
// We want to do this:
// com_addin.DnaLibrary =
ExcelDna.Integration.DnaLibrary.CurrentLibrary;
// But the DnaLibrary property is marked 'internal' to
ExcelDna.Integration.
// v0.29 workaround: set by Reflection
com_addin.GetType().InvokeMember("DnaLibrary",
BindingFlags.NonPublic | BindingFlags.Instance |
BindingFlags.SetProperty, null, com_addin, new object[]
{ DnaLibrary.CurrentLibrary });
ExcelComAddInHelper.LoadComAddIn(com_addin);
} catch (Exception e) {
MessageBox.Show("Error loading COM AddIn: " +
e.ToString());
}
}
public void AutoClose() {
}
}
}
/*
' In VBA:
Sub TestDnaComAddIn()
Dim cai As COMAddIn
Dim obj As Object
For Each cai In Application.COMAddIns
' Could check cai.Connect to see if it is loaded.
Debug.Print cai.Description, cai.GUID
If InStr(cai.Description, "DnaDetective (COM Add-in Helper)")
Then
Set obj = cai.Object
If obj Is Nothing Then
Debug.Print "ObjNothing"
Else
Debug.Print obj.SayHello(), obj.ActiveCell3
End If
End If
Next
End Sub
Sub opendd()
Workbooks.Open "D:\Dropbox\1Anthony\V2010k\ExcelDna\DnaDetective
\DnaDetective\bin\Debug\DnaDetective.xll"
End Sub
*/
Govert van Drimmelen
May 19, 2012, 7:52:21 AM5/19/12
to Excel-DNA
Hi Anthony,
> VBA testing for .Object is Nothing seems to find the real one OK.
No, no, no. What if another add-in has set the .Object?
Testing the first part of the description is better, because you can
control the name of your add-in, and it is likely to be the only add-
in loaded with that name. I agree it's a bit fragile, but it's my best
suggestion for now. Long term you should just move everything into
managed code ;-)
> I think this is a result of not having a ProgId. Is there any way to
> set this?
No way to set it for the ExcelComAddins in the current versions. I
agree, the auto-generated ProgId is not so useful here. I'l consider
whether this should change (similar to RTD servers). I have to re-look
at the ribbon ProgId stuff also in future. The reason for the auto-
generated ProgId is to make sure different instances of the add-in
don't trash each other (since I'm doing registration at runtime).
The alternative is then to not go the ExcelComAddIn route, but the
real ComServer option, where you can set the ProgId and control what
is instantiated from VBA better. But this has other complications....
So for now I'd first suggest checking BOTH
1. the start of the Description string (which will match your add-in's
name), and
2. that the .Object != null. Excel-DNA does not set it for a Ribbbon
or Rtd helper, so you control whether it is set for ComAddIns with
your add-in's name.
> One problem I have is that I actually end up with multiple instances
> of the add in installed. They all end up in
> HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins
> with random names like AddIn.92bf9e5a30e747b2b35b4f15bb54bd5d
You might get some left-overs here if you are debugging and the add-in
crashes or you stop the debugger. But normal operation should not
leave extra entries here. (Please report if it does.)
> The next problem is how to actually open the add in. It's path is not
> in the registry entries.
Which add-in? The Excel-DNA add-in?
This you can either File->Open, or add as an Excel add-in under (Alt
+t, i), or add into the registry with an installer.
The COM add-in you implement with ExcelComAddIn will be loaded by the
Excel-DNA add-in automatically.
> File > Open does seem to work, but
> with macro security warnings, each time.
Options are: change macro security settings, sign your add-in, or add
as a regular add-in in the add-ins dialog.
> I do not want to resort to a SendKeys Hack!
Please don't.
> I suspect this is all to do with no ProgID.
No. You might be confusing different 'add-ins'.
> Another issue is that you use .Net 4.0 dynamic magic.
> I could not see
> how to run this with 3.5, which is much more available.
Sorry - that was just to make my little test easier.
You can do the same in .NET 2.0 by
1. using reflection (InvokeMember), or
2. adding a reference to the Excel Interop assembly (or the NetOffice
library) and cast the AddInInst to a Microsoft.Office.Core.ComAddIn
type. You seem to have done this in your example.
Write if you need help with either of these options.
> That AddInInst parameter has a weird System.__COMObject type, which
> Microsoft.VisualBasic.Information.TypeName(AddInInst) reports to be
> "COMAddIn", but I do not believe it. COM must hook deeply into
> the .Net type system, pretending that the dynamic COM proxy object is
> the real one. Anyway help here would be appreciated.
COM / .NET interop is an enormous topic. Indeed .NET deeply integrates
with COM, and does a really good job too, but still lots of tricky
issues.
> Thanks for your great support.
It's a pleasure - thanks for your interest in the Excel-DNA library!
Cheers,
Govert
> VBA testing for .Object is Nothing seems to find the real one OK.
Testing the first part of the description is better, because you can
control the name of your add-in, and it is likely to be the only add-
in loaded with that name. I agree it's a bit fragile, but it's my best
suggestion for now. Long term you should just move everything into
managed code ;-)
> I think this is a result of not having a ProgId. Is there any way to
> set this?
agree, the auto-generated ProgId is not so useful here. I'l consider
whether this should change (similar to RTD servers). I have to re-look
at the ribbon ProgId stuff also in future. The reason for the auto-
generated ProgId is to make sure different instances of the add-in
don't trash each other (since I'm doing registration at runtime).
The alternative is then to not go the ExcelComAddIn route, but the
real ComServer option, where you can set the ProgId and control what
is instantiated from VBA better. But this has other complications....
So for now I'd first suggest checking BOTH
1. the start of the Description string (which will match your add-in's
name), and
2. that the .Object != null. Excel-DNA does not set it for a Ribbbon
or Rtd helper, so you control whether it is set for ComAddIns with
your add-in's name.
> One problem I have is that I actually end up with multiple instances
> of the add in installed. They all end up in
> HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins
> with random names like AddIn.92bf9e5a30e747b2b35b4f15bb54bd5d
crashes or you stop the debugger. But normal operation should not
leave extra entries here. (Please report if it does.)
> The next problem is how to actually open the add in. It's path is not
> in the registry entries.
This you can either File->Open, or add as an Excel add-in under (Alt
+t, i), or add into the registry with an installer.
The COM add-in you implement with ExcelComAddIn will be loaded by the
Excel-DNA add-in automatically.
> File > Open does seem to work, but
> with macro security warnings, each time.
as a regular add-in in the add-ins dialog.
> I do not want to resort to a SendKeys Hack!
> I suspect this is all to do with no ProgID.
> Another issue is that you use .Net 4.0 dynamic magic.
> I could not see
> how to run this with 3.5, which is much more available.
You can do the same in .NET 2.0 by
1. using reflection (InvokeMember), or
2. adding a reference to the Excel Interop assembly (or the NetOffice
library) and cast the AddInInst to a Microsoft.Office.Core.ComAddIn
type. You seem to have done this in your example.
Write if you need help with either of these options.
> That AddInInst parameter has a weird System.__COMObject type, which
> Microsoft.VisualBasic.Information.TypeName(AddInInst) reports to be
> "COMAddIn", but I do not believe it. COM must hook deeply into
> the .Net type system, pretending that the dynamic COM proxy object is
> the real one. Anyway help here would be appreciated.
with COM, and does a really good job too, but still lots of tricky
issues.
> Thanks for your great support.
It's a pleasure - thanks for your interest in the Excel-DNA library!
Cheers,
Govert
aberglas
May 20, 2012, 8:02:57 PM5/20/12
to Excel-DNA
Hello Govert,
1. For finding the correct .COMAddIns, I think you are actually
agreeing with my approach in the code I had sent, which is to match
on .Description and then test .Object is not Nothing. Just testing
the .Object without the .Description would obviously be wrong! I
cannot just test the first part because there are multiple, I get one
for the Ribbon as well:-
DnaDetective (COM Add-in Helper) {6BD4B659-702B-4BE0-A16F-
D8C1EA875B84}
Hello from the future! ActiveCell3:
DnaDetective (Ribbon Helper) {B3F4DE51-8F5A-4019-
B22E-3F37407DB7E6}
(And VBA is managed code in the sense of not being needlessly horrible
unsafe C. Moving to .Net would provide no benefit, half the run time
speed, and much harder development. I do not need to restart Excel
every time I make a code change etc. The only reason to use .Net with
Excel is to access new libraries such as WPF, and the task pane,
IMHO.)
2. Today, I cannot reproduce the multiple installation bug that made
testing .Object is Nothing necessary. Whether I run from VS or
directly Excel/File Open, nothing ever appears in the registry under
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins. Even went
back to .Net 3.5. Spooky stuff, I do not think I changed anything.
(If there was an ExcelDna Wiki, I would add a note to warn others of
the need to check Object is Nothing.)
3. I successfully used InvokeMember to avoid the dynamic. Could not
get the cast to work. But then in .Net it is difficult to know what
types you are really dealing with. (Multiple assemblies can load
multiple versions of the same type, and then there is COM magic.)
4. Microsoft.Office.Interop.Excel. I've taken the one out of
"Microsoft Visual Studio 10.0\Visual Studio Tools for Office" which
looks like it is for XL2007, and made that copy local. Seems to work
for XL 2010/.Net 3.5. But I am hoping this will be OK on different
end user configurations. (Incidentally, is there any accurate way in
VS to find out which .dlls have which namespaces in them?)
5. The add-in can be installed using
Application.addins.Add(path).Installed = True
This in turn Creates an OPENn entry in HKEY_CURRENT_USER\Software
\Microsoft\Office\14.0\Excel\Options
I am hoping that an installer can poke this.
Weirdly, it cannot be installed mannually from File > Options >
Addins. Further, one can File Open the .xll interactively, but cannot
do that from code.
6. I think that I would really like to be able to set ProgId, feels
like a source of grief. Do not understand your issue with "multiple
instances", surely it is normally in process COM?
Thanks,
Anthony
1. For finding the correct .COMAddIns, I think you are actually
agreeing with my approach in the code I had sent, which is to match
on .Description and then test .Object is not Nothing. Just testing
the .Object without the .Description would obviously be wrong! I
cannot just test the first part because there are multiple, I get one
for the Ribbon as well:-
DnaDetective (COM Add-in Helper) {6BD4B659-702B-4BE0-A16F-
D8C1EA875B84}
Hello from the future! ActiveCell3:
DnaDetective (Ribbon Helper) {B3F4DE51-8F5A-4019-
B22E-3F37407DB7E6}
(And VBA is managed code in the sense of not being needlessly horrible
unsafe C. Moving to .Net would provide no benefit, half the run time
speed, and much harder development. I do not need to restart Excel
every time I make a code change etc. The only reason to use .Net with
Excel is to access new libraries such as WPF, and the task pane,
IMHO.)
2. Today, I cannot reproduce the multiple installation bug that made
testing .Object is Nothing necessary. Whether I run from VS or
directly Excel/File Open, nothing ever appears in the registry under
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins. Even went
back to .Net 3.5. Spooky stuff, I do not think I changed anything.
(If there was an ExcelDna Wiki, I would add a note to warn others of
the need to check Object is Nothing.)
3. I successfully used InvokeMember to avoid the dynamic. Could not
get the cast to work. But then in .Net it is difficult to know what
types you are really dealing with. (Multiple assemblies can load
multiple versions of the same type, and then there is COM magic.)
4. Microsoft.Office.Interop.Excel. I've taken the one out of
"Microsoft Visual Studio 10.0\Visual Studio Tools for Office" which
looks like it is for XL2007, and made that copy local. Seems to work
for XL 2010/.Net 3.5. But I am hoping this will be OK on different
end user configurations. (Incidentally, is there any accurate way in
VS to find out which .dlls have which namespaces in them?)
5. The add-in can be installed using
Application.addins.Add(path).Installed = True
This in turn Creates an OPENn entry in HKEY_CURRENT_USER\Software
\Microsoft\Office\14.0\Excel\Options
I am hoping that an installer can poke this.
Weirdly, it cannot be installed mannually from File > Options >
Addins. Further, one can File Open the .xll interactively, but cannot
do that from code.
6. I think that I would really like to be able to set ProgId, feels
like a source of grief. Do not understand your issue with "multiple
instances", surely it is normally in process COM?
Thanks,
Anthony
Govert van Drimmelen
May 21, 2012, 6:53:55 AM5/21/12
to Excel-DNA
Hi Anthony,
You should certainly be able to install your add-in via File ->
Options -> Addins. Just be sure to pick the 'Excel Add-Ins' option
rather than the 'COM Add-Ins'. This is equivalent to pressing (Alt+t,
i) to get the add-ins dialog. Browse to the .xll and all should work
as expected.
Application.AddIns.Add(path).Installed = True is also fine.
An installer can indeed write the OPENx value to the registry to
install the add-in (it works like any other .xll add-in in that
respect).
> Further, one can File Open the .xll interactively, but cannot
> do that from code.
To load (or reload) the add-in into the session (like File->Open) you
can run Application.RegisterXLL(...).
Some more installation ideas here:
http://exceldna.codeplex.com/wikipage?title=Installing%20your%20add-in&referringTitle=Documentation
---------------
Using the Excel 2007 interop libraries will work fine for Excel 2010
too, as long as the COM object and methods you use are also present in
the newer version. This is largely fine for 2007 vs. 2010. This is
similar to VBA, where you essentially code to the least common
denominator.
If you are targeting multiple versions of Excel, particularly old
Excel 2003 and newer 2007/2010, and you want to use features that are
not available on one version or the other, you might investigate the
NetOffice libraries (http://netoffice.codeplex.com) as a near drop-in
replacement for the interop assemblies. A particularly nice aspect is
that it has IntelliSense to indicate which Excel versions support a
particular object or method.
---------------
There are many reasons to use .NET with Excel, but I think the most
common reason people start using Excel-DNA is to create high-
performance user-defined function for Excel. The calculation
performance of the .NET code is about 8 - 15 faster than VBA. For just
scripting the COM object model, which VBA does very well, I'd still be
surprised if .NET were half as fast, which you seem to imply.
There are many other advantages in going with .NET, but I'm not here
to sell anything. I'm just a genuine fan of a fantastic platform
(well, two fantastic platforms - Excel and .NET)!
--------
There is an option to allow you to recompile the add-in while it is
loaded in Excel, that way you don't have to restart Excel every time
you make a change.
In the .dna file, to the ExternalLibrary tag, add a
LoadFromBytes="true" attribute:
<ExternalLibrary Path="MyAddInStuff.dll" LoadFromBytes="true" />
With this, the .dll should not by locked when it is running in Excel,
and you can edit the code, recompile and then reload using File->Open,
which will load the new version of the .dll without stopping Excel.
For debugging you then attach to the running Excel and detach to stop
debugging, instead of starting Excel with the debugger.
Clearly this is still pretty fiddly and not user-friendly at all, but
I think the workflow can be improved over the coming years, to the
point where it is nearly as slick as VBA.
--------------
If you send me your CodePlex login, I'll add you as an editor for the
CodePlex documentation wiki.
Cheers,
Govert
You should certainly be able to install your add-in via File ->
Options -> Addins. Just be sure to pick the 'Excel Add-Ins' option
rather than the 'COM Add-Ins'. This is equivalent to pressing (Alt+t,
i) to get the add-ins dialog. Browse to the .xll and all should work
as expected.
Application.AddIns.Add(path).Installed = True is also fine.
An installer can indeed write the OPENx value to the registry to
install the add-in (it works like any other .xll add-in in that
respect).
> Further, one can File Open the .xll interactively, but cannot
> do that from code.
can run Application.RegisterXLL(...).
Some more installation ideas here:
http://exceldna.codeplex.com/wikipage?title=Installing%20your%20add-in&referringTitle=Documentation
---------------
Using the Excel 2007 interop libraries will work fine for Excel 2010
too, as long as the COM object and methods you use are also present in
the newer version. This is largely fine for 2007 vs. 2010. This is
similar to VBA, where you essentially code to the least common
denominator.
If you are targeting multiple versions of Excel, particularly old
Excel 2003 and newer 2007/2010, and you want to use features that are
not available on one version or the other, you might investigate the
NetOffice libraries (http://netoffice.codeplex.com) as a near drop-in
replacement for the interop assemblies. A particularly nice aspect is
that it has IntelliSense to indicate which Excel versions support a
particular object or method.
---------------
There are many reasons to use .NET with Excel, but I think the most
common reason people start using Excel-DNA is to create high-
performance user-defined function for Excel. The calculation
performance of the .NET code is about 8 - 15 faster than VBA. For just
scripting the COM object model, which VBA does very well, I'd still be
surprised if .NET were half as fast, which you seem to imply.
There are many other advantages in going with .NET, but I'm not here
to sell anything. I'm just a genuine fan of a fantastic platform
(well, two fantastic platforms - Excel and .NET)!
--------
There is an option to allow you to recompile the add-in while it is
loaded in Excel, that way you don't have to restart Excel every time
you make a change.
In the .dna file, to the ExternalLibrary tag, add a
LoadFromBytes="true" attribute:
<ExternalLibrary Path="MyAddInStuff.dll" LoadFromBytes="true" />
With this, the .dll should not by locked when it is running in Excel,
and you can edit the code, recompile and then reload using File->Open,
which will load the new version of the .dll without stopping Excel.
For debugging you then attach to the running Excel and detach to stop
debugging, instead of starting Excel with the debugger.
Clearly this is still pretty fiddly and not user-friendly at all, but
I think the workflow can be improved over the coming years, to the
point where it is nearly as slick as VBA.
--------------
If you send me your CodePlex login, I'll add you as an editor for the
CodePlex documentation wiki.
Cheers,
Govert
aberglas
May 24, 2012, 11:40:19 PM5/24/12
to Excel-DNA
Hello Govert,
Thanks again for your detailed reply.
1. I have now got the installation sorted out.
2. I had wondered what LoadFromBytes="true". Is there any downside?
Might be useful to have a page that documents all these options.
3. For performance of VBA vs .Net, see
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/2600e1ff-fa18-483b-885e-8ddf0309f53a
That is VSTO, but I would expect the same results with Excel DNA.
Incidentally, on managed vs unmanaged code long ago I did a benchmark
CMULisp vs gnu C++ on a neural net fragment, CMULisp was faster (due
to a combined div, mod operator). Then more recently I compared .Net
1.1 to C++ for an optimization fragment -- the C++ programmer was
surprised to find .Net much faster as he had forgot to in line a
method. After that, they were the same. So yes, Lisp, Java, .Net
defeat the myth that programming languages must be slow if they are
type safe.
4. My codeplex login is aberglas. If you add me I'll write up what we
have been discussing -- nice to contribute something back.
Anthony
> > > COM /...
>
> read more »
Thanks again for your detailed reply.
1. I have now got the installation sorted out.
2. I had wondered what LoadFromBytes="true". Is there any downside?
Might be useful to have a page that documents all these options.
3. For performance of VBA vs .Net, see
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/2600e1ff-fa18-483b-885e-8ddf0309f53a
That is VSTO, but I would expect the same results with Excel DNA.
Incidentally, on managed vs unmanaged code long ago I did a benchmark
CMULisp vs gnu C++ on a neural net fragment, CMULisp was faster (due
to a combined div, mod operator). Then more recently I compared .Net
1.1 to C++ for an optimization fragment -- the C++ programmer was
surprised to find .Net much faster as he had forgot to in line a
method. After that, they were the same. So yes, Lisp, Java, .Net
defeat the myth that programming languages must be slow if they are
type safe.
4. My codeplex login is aberglas. If you add me I'll write up what we
have been discussing -- nice to contribute something back.
Anthony
> You should certainly be able to install your add-in via File ->
> Options -> Addins. Just be sure to pick the 'Excel Add-Ins' option
> rather than the 'COM Add-Ins'. This is equivalent to pressing (Alt+t,
> i) to get the add-ins dialog. Browse to the .xll and all should work
> as expected.
>
> Application.AddIns.Add(path).Installed = True is also fine.
> An installer can indeed write the OPENx value to the registry to
> install the add-in (it works like any other .xll add-in in that
> respect).
>
> > Further, one can File Open the .xll interactively, but cannot
> > do that from code.
>
> To load (or reload) the add-in into the session (like File->Open) you
> can run Application.RegisterXLL(...).
>
> Some more installation ideas here:http://exceldna.codeplex.com/wikipage?title=Installing%20your%20add-i...
> Options -> Addins. Just be sure to pick the 'Excel Add-Ins' option
> rather than the 'COM Add-Ins'. This is equivalent to pressing (Alt+t,
> i) to get the add-ins dialog. Browse to the .xll and all should work
> as expected.
>
> Application.AddIns.Add(path).Installed = True is also fine.
> An installer can indeed write the OPENx value to the registry to
> install the add-in (it works like any other .xll add-in in that
> respect).
>
> > Further, one can File Open the .xll interactively, but cannot
> > do that from code.
>
> To load (or reload) the add-in into the session (like File->Open) you
> can run Application.RegisterXLL(...).
>
>
> read more »
Govert van Drimmelen
May 25, 2012, 4:08:11 AM5/25/12
to Excel-DNA
Hi Anthony,
2. Setting LoadFromBytes=true is essentially equivalent to what
happens with the ExcelDnaPack packing. Debugging gets a bit tricky,
and with other assemblies referencing your LoadFromBytes assembly you
can end up with more than one instance loaded, which can cause
confusion. So it's not perfect - being an add-in hosted in the
unmanaged Excel-DNA makes for tricky interactions with the .NET
assembly loader.
LoadFromBytes is my best attempt to make the compile-reload cycle
possible without restarting Excel.
3. Some more performance references:
* Charles Williams did some tests on a bunch of different add-in
alternatives: http://fastexcel.wordpress.com/category/udf/page/2/
* I tried to make an example of how you'd use the C API to read and
writes large amounts of data on a sheet - I could easily read, process
and write a million cells in under a second. Look for the second
answer here:
http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-sharp-objects
* This discussion https://groups.google.com/group/exceldna/browse_frm/thread/e331337fcdc22a17
compared some COM calls with the C API equivalent - finding that the
COM calls can even sometimes be a bit faster. No VBA comparison
though.
4. I've added you as an editor on the CodePlex site.
Regards,
Govert
> > > > +t, i), or add into the registry with an installer....
>
> read more »
2. Setting LoadFromBytes=true is essentially equivalent to what
happens with the ExcelDnaPack packing. Debugging gets a bit tricky,
and with other assemblies referencing your LoadFromBytes assembly you
can end up with more than one instance loaded, which can cause
confusion. So it's not perfect - being an add-in hosted in the
unmanaged Excel-DNA makes for tricky interactions with the .NET
assembly loader.
LoadFromBytes is my best attempt to make the compile-reload cycle
possible without restarting Excel.
3. Some more performance references:
* Charles Williams did some tests on a bunch of different add-in
alternatives: http://fastexcel.wordpress.com/category/udf/page/2/
* I tried to make an example of how you'd use the C API to read and
writes large amounts of data on a sheet - I could easily read, process
and write a million cells in under a second. Look for the second
answer here:
http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-sharp-objects
* This discussion https://groups.google.com/group/exceldna/browse_frm/thread/e331337fcdc22a17
compared some COM calls with the C API equivalent - finding that the
COM calls can even sometimes be a bit faster. No VBA comparison
though.
4. I've added you as an editor on the CodePlex site.
Regards,
Govert
> Thanks again for your detailed reply.
>
> 1. I have now got the installation sorted out.
>
> 2. I had wondered what LoadFromBytes="true". Is there any downside?
> Might be useful to have a page that documents all these options.
>
> 3. For performance of VBA vs .Net, see
> http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/2600e1ff-fa...
>
> 1. I have now got the installation sorted out.
>
> 2. I had wondered what LoadFromBytes="true". Is there any downside?
> Might be useful to have a page that documents all these options.
>
> 3. For performance of VBA vs .Net, see
>
> read more »
aberglas
May 25, 2012, 5:20:31 AM5/25/12
to Excel-DNA
Hello Govert,
I don't quite understand the LoadFromBytes=true comment, but I think
you are saying turn this off unless you need it.
The performance links were interesting. Sadly my use case is not
actually get/set value but properties not available through the C
API.
And C++ is fine ... until you make some little error that subtly
corrupts memory, then your project can die. And I wonder what it is
like trying to run Purify etc. in an Excel Add In!
I'll write up our discussions in the docs soon.
Anthony
>
> * This discussionhttps://groups.google.com/group/exceldna/browse_frm/thread/e331337fcd...
> > > > > No way to set it for the ExcelComAddins in the current versions. I...
>
> read more »
I don't quite understand the LoadFromBytes=true comment, but I think
you are saying turn this off unless you need it.
The performance links were interesting. Sadly my use case is not
actually get/set value but properties not available through the C
API.
And C++ is fine ... until you make some little error that subtly
corrupts memory, then your project can die. And I wonder what it is
like trying to run Purify etc. in an Excel Add In!
I'll write up our discussions in the docs soon.
Anthony
> 2. Setting LoadFromBytes=true is essentially equivalent to what
> happens with the ExcelDnaPack packing. Debugging gets a bit tricky,
> and with other assemblies referencing your LoadFromBytes assembly you
> can end up with more than one instance loaded, which can cause
> confusion. So it's not perfect - being an add-in hosted in the
> unmanaged Excel-DNA makes for tricky interactions with the .NET
> assembly loader.
> LoadFromBytes is my best attempt to make the compile-reload cycle
> possible without restarting Excel.
>
> 3. Some more performance references:
>
> * Charles Williams did some tests on a bunch of different add-in
> alternatives:http://fastexcel.wordpress.com/category/udf/page/2/
>
> * I tried to make an example of how you'd use the C API to read and
> writes large amounts of data on a sheet - I could easily read, process
> and write a million cells in under a second. Look for the second
> answer here:http://stackoverflow.com/questions/3840270/fastest-way-to-interface-b...
> happens with the ExcelDnaPack packing. Debugging gets a bit tricky,
> and with other assemblies referencing your LoadFromBytes assembly you
> can end up with more than one instance loaded, which can cause
> confusion. So it's not perfect - being an add-in hosted in the
> unmanaged Excel-DNA makes for tricky interactions with the .NET
> assembly loader.
> LoadFromBytes is my best attempt to make the compile-reload cycle
> possible without restarting Excel.
>
> 3. Some more performance references:
>
> * Charles Williams did some tests on a bunch of different add-in
> alternatives:http://fastexcel.wordpress.com/category/udf/page/2/
>
> * I tried to make an example of how you'd use the C API to read and
> writes large amounts of data on a sheet - I could easily read, process
> and write a million cells in under a second. Look for the second
>
> * This discussionhttps://groups.google.com/group/exceldna/browse_frm/thread/e331337fcd...
>
> read more »
Govert van Drimmelen
May 25, 2012, 5:53:00 AM5/25/12
to Excel-DNA
Hi Anthony,
> I don't quite understand the LoadFromBytes=true comment, but I think
> you are saying turn this off unless you need it.
I'd probably say turn it on, but if you run into any funny loading or
type identity issues, be suspicious of the setting.
Having it on more closely emulates the ExcelDnaPack packed case.
The real help would be for 'someone' to make a Visual Studio add-in
that talks to Excel, and gives you a single button in Visual Studio
that
1. detaches the debugger from Excel if it is attached,
2. recompiles the add-in assembly,
3. attaches the debugger to Excel,
4. reloads the Excel-DNA .xll.
That would get us a huge step closer to VBA-like integration.
-Govert
> > > > > instances",...
>
> read more »
> I don't quite understand the LoadFromBytes=true comment, but I think
> you are saying turn this off unless you need it.
type identity issues, be suspicious of the setting.
Having it on more closely emulates the ExcelDnaPack packed case.
The real help would be for 'someone' to make a Visual Studio add-in
that talks to Excel, and gives you a single button in Visual Studio
that
1. detaches the debugger from Excel if it is attached,
2. recompiles the add-in assembly,
3. attaches the debugger to Excel,
4. reloads the Excel-DNA .xll.
That would get us a huge step closer to VBA-like integration.
-Govert
>
> read more »
Reply all
Reply to author
Forward
0 new messages