COM, API, Interop and UDFs?

[aberglas]

Hello,

Thank you for providing Excel DNA. I have been looking to replace
VSTO with Excel DNA for safer deployment, but am confused by the
architecture.

As background, I have a large VBA add-in which calls into VSTO/WPF in
order to create a custom task pane (cannot be done with VBA). I
currently avoid the MS deployment nightmare by simply copying the .dll
files out of the debug folder, and explicitly setting HKCU\Software
\Microsoft\Office\Excel\Addins\. Works OK, is installable by users
without admin rights, but not a supported method. VSTO is also about
half the already slow speed of VBA in my benchmarks accessing the
Excel API.

As far as I can tell, there are two distinct ways to run ExcelDNA,
namely as an .xll AddIn, or as a COM Server.

There are also two distinct ways to access Excel, the xlCall API and
the COM/Interop. xlCall is fast but essentially undocumented (by
Microsoft). It is also very limited?, cannot do much more than set
and get cell values, cannot? manipulate Shapes, Cell background colors
etc. The COM/Interop is the familiar statically typed interface as
also used by VBA.

There are posts that suggest that COM cannot be used (Client and
Server?) by ExcelDNA when deployed as an Addin. Further a COM server
cannot? use the xlCall API. But there is a sample InteropReference
which seems to use COM client as an add-in.

Deploying aseparate COM server requires Admin rights to run regsrv32,
no good for me anyway. But there does not appear to be any other
clean way to call from VBA into an ExcelDna add-in? Evaluating a
magic UDF might be a hack that works. Is that the right approach? Is
it possible to run a local COM server inside an XLL, like I now do
with VSTO?

In any case I need to access the Task Pane (that being the whole
point), which needs? the addin approach to work.

(I assume that Application.Run will enable .Net to call VBA code if it
can use COM client at all.)

What would be wonderful is a sample that showed these interactions.

As feedback while having samples that put the code in .dna files might
be cute, I think they are a distraction. I would be very surprised if
anyone at all uses Excel DBA without an IDE, about 99% of which will
be Visual Studio (possibly Express). I for one have been struggling
to make the COM example work as a normal project, will try again
tomorrow.

This is difficult stuff, requires programmers with an understanding of
the xlCall API, COM, Interop, etc. and that are prepared to read
through your sophisiticated, reflective, source code to figure out
what is really going on. Such developers do not need step by step
Hello World instructions, and can easily read between vb & cs code,
but they do need good samples and, ideally, some documentation. For
simple tasks, people do and should use the now unfasionable VBA with
its close, effortless intgration with Excel, compile time checks,
macro recorder etc.

Regards,

Anthony

[Govert van Drimmelen]

Hi Anthony,

Indeed, it's all getting quite complicated. Any help you can offer
with creating better documentation and samples would be appreciated. I
believe programmers at all levels should prefer .NET over VBA, and
eventually I hope we will have something that makes this viable for
all tasks. Of course getting to the effortless integration with Excel
still needs work. I like the .dna samples because the are completely
self-contained in a single text file.

If you are stuck getting a particular example or feature to work, I'm
happy to help - this is the right place to ask.

If you post a public sample add-in where you have tried to incorporate
the features and interactions you need, (maybe on GitHub or
somewhere), I'd be happy to help fix it up and get everything working.

=================================================
Back to you more specific queries...

1. COM Server
-------------
I think you are being distracted by the Excel-DNA COM Server feature.
This was introduced in v 0.29, and allows you to:
* create COM libraries inside your Excel-DNA add-in, that can be
instantiated and used directly as COM objects from VBA, and
* create RTD servers that are instantiated by Excel directly, without
having the Excel-DNA add-in loaded, which is needed to support the
'old values' feature of RTD>

Apart from these two specialised cases, you need not bother with the
COM Server stories. Certainly for porting from VSTO, you do not need
this.

Calling regsvr32 to register an Excel-DNA COM Server does not require
admin rights (we write to the user's hive if need be). Registration of
the COM Server can also be done automatically when your add-in is
loaded - then the add-in must be loaded before the VBA code runs.

As you say you can most easily call into your Excel-DNA add-in from
VBA by Application.Run("MyMacro") where MyMacro is a static void
method in your add-in, or a function or whatever.

I'm not sure what this means: "Is it possible to run a local COM

server inside an XLL, like I now do with VSTO?"

If you mean accessing some third-party COM server from your .NET code,
the answer is probably 'Yes'. That part has nothing to do with VSTO
though.

2. Excel APIs
-------------
Excel has a C API and the COM object model. You can fully use both
from within your Excel-DNA add-in. Some calls are not allowed by Excel
in some contexts, for example you can't call the C API from a Ribbon
handler. But from a ribbon handler you can call into a macro using
Application.Run, and then call the C API from inside the macro.

The entry point into the COM object model is via the
ExcelDnaUtil.Application property, which gives you the correct
instance of the Application object, matching the Excel instance your
add-in is running in.

The full C API is exposed in Excel-DNA via the XlCall class, using the
ExcelReference type to represent regions of a sheet. The C API does
not have the full capabilities of the COM object model. Whether it is
faster or not in a particular case needs to be tested, often it is not
faster. The C API is exposed through the XlCall class, using the
ExcelReference type to represent regions of a sheet.

I'd recommend the following:
* use the C API inside user-defined function,
* generally use the COM object model for 'macro' type work, like
applying formatting, creating new sheets, etc.
* sometimes use the C API (via the ExcelReference class) in a macro to
read / write large amounts of data.

3. Ribbon and Custom Task Pane support
--------------------------------------
Excel-DNA has support for Ribbon and Custom Task Panes without
requiring admin rights or registration before running your add-in. The
required COM registration is done at runtime, and into the user's hive
in the registry if need be.

The Custom Tasl Pane support is via a single call to

var ctp =
CustomTaskPaneFactory.CreateCustomTaskPane(typeof(MyUserControl), "My
Super Task Pane");

where MyUserControl is a System.Windows.Forms.UserControl, hosting
your WPF stuff.

The MyUserControl class must be marked [ComVisible(true)] or in an
assembly that is marked [assembly:ComVisible(true)] - the default
Visual Studio projects sets it up as [assembly:ComVisible(false)] in
AssemblyInfo.cs.

The MyUserControl class will be registered and loaded as needed at
runtime - you should _never_ set your Excel-DNA project to 'Register
for COM interop'. You need no pre-registration of you assemblies or
add-ins for the CTP support to work.

I'm not sure what exactly you mean by:

"In any case I need to access the Task Pane (that being the whole
point), which needs? the addin approach to work."

4. ExcelComAddIn helper
-----------------------
You should ignore this initially. Initially this class was made as a
helper for the Ribbon support, but it can also give you a way to get
the COM Add-In events, allow you to deal properly with Excel shutdown
(the C API has some bugs that makes this difficult).

=================================================
Eventually your life will probably be easiest by porting you whole VBA
add-in to VB.NET.
Patrick O'Beirne has written up some of his experiences doing this:
http://sysmod.wordpress.com/.

I suggest if you have a follow-up questions on any of these aspects,
you ask as a new thread on the group - these catch-all discussions
tend to get lost easily.

Good luck,
Govert

[aberglas]

P.S. I should add that my VSTO plugin is accessed from VBA using
Application.COMAddIns(...).Objecty instead of CreateObject(...). I'm
not exactly sure what COMAddIns does, but it appears to avoid the need
for any global registration by accessing the add in directly, and I
could not get this to work with Excel DNA.

[Govert van Drimmelen]

Hi Anthony,

Normally your Excel-DNA add-in would not be a COM add-in. However, you
can use the ExcelComAddIn helper class to instantiate a COM add-in
from inside your Excel-DNA .xll, which you could then access from VBA.

1. Create a class, say MyComAddIn, that derived from
ExcelDna.Integration.CustomUI.ExcelComAddIn, and mark it as
[ComVisible(true)].

2. Create a class that implements IExcelAddIn, and in the AutoOpen
method call:
ExcelComAddInHelper.LoadComAddIn(new MyComAddIn());
(This works in recent check-ins of Excel-DNA. For v. 0.29 you need the
workaroudn discussed here: http://groups.google.com/group/exceldna/browse_frm/thread/143bd77dec8e6579)

3. In MyComAddin.Connect, set AddInInst.Object to something (another
helper object, or 'this' to just set it to your MyComAddIn instance)

4. Take note of the Name of your Excel-DNA add-in. It defaults to the
name of the .xll, but can be set in the .dna file
<DnaLibrary Name="TestDnaComMix" ....>

5. In your VBA, look for the COM AddIn by the Description, which will
start with your Excel-DNA add-in name. Excel-DNA does not use the Guid
or ProgId of your ExcelComAddIn class directly, so you need to match
on the ComAddIn.Description. From there you can retrieve the
ComAddIn.Object, and use the helper or whatever object you set up in
the ExcelComAddIn.Connect.

Sub TestDnaComAddIn()
Dim cai As COMAddIn
Dim obj As Object

For Each cai In Application.COMAddIns
If InStr(cai.Description, "TestDnaComMix") Then
Set obj = cai.Object
Debug.Print obj.SayHello()
End If
Next
End Sub

I append a complete .dna example below.

Regards,
Govert

<DnaLibrary Name="TestDnaComMix" RuntimeVersion="v4.0" Language="C#">
<Reference Name="System.Windows.Forms" />
<![CDATA[
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

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
dynamic addInInst = AddInInst;

_helper = new AddInHelper();
addInInst.Object = _helper;

MessageBox.Show("OnConnection");
}
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!";
}
}

public class AddIn : IExcelAddIn
{
public void AutoOpen()
{
ExcelComAddInHelper.LoadComAddIn(new MyComAddIn());
}

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.
If InStr(cai.Description, "TestDnaComMix") Then
Set obj = cai.Object
Debug.Print obj.SayHello()
End If
Next
End Sub
*/

]]>
</DnaLibrary>

[Patrick O'Beirne]

The .dna file is best for isolating one piece of behaviour to illustrate
a test.
I've done that in my submissions to Sebastian at Netoffice. BTW he's
issued an update I have yet to test because of other pressures, but will
get back to.
Govert has summarised the detail very well, as always, and I appreciate
his help in getting to grips with this stuff.

P
http://sysmod.wordpress.com