Exposing classes to Excel macros

Apr 6, 2011 at 6:34 AM
Edited Apr 6, 2011 at 6:41 AM

Is it possible to declare classes in .NET (e.g. a Customer class with public properties) and expose this to Excel so that it can be consumed in macros?

E.g. I would like users to be able to do this:

Sub ExampleMacro()

  Dim Customer as Customer
  Set Customer = SomeClass.GetCustomer("12345")
  Debug.Print Customer.Name

End Sub

I've been able to create UDF's that can be used in the spreadsheet. I've created an Excel ribbon toolbar (awesome!) and a Rtd Server (again, awesome!). The "rich" classes that users can consume in their VBA macros from within Excel seem to be the missing link.

At present we could do this using COM by creating an Excel add-in (i.e. not using Excel DNA), but the big problem is it runs in a different app domain or CLR instance to Excel DNA.

Coordinator
Apr 6, 2011 at 8:40 AM

I understand this to mean having the Excel-DNA add-in be a COM server that gets referenced from VBA macros. In theory this would be possible, and in fact I think you could do it in your own add-in already without changes to Excel-DNA, using the code that implements that Ribbon and RTD support.

But surely it must be easier just to move those VBA macros to the add-in, using VB.NET? Is there any blocking issue with this?

-Govert

Apr 6, 2011 at 9:34 AM

>> Is there any blocking issue with this?

The users are familiar with Excel's macro editor and I very much doubt they would want to change to anything else.

When you say this is possible without change to Excel-DNA, what do you mean exactly? As far as I understand, in order to make .NET classes available in Excel you need to create a *.tlb file from the COM classes. Visual Studio does this automatically when you enable the "Register for COM Interop" option, and then you can "Add Reference" in Excel to include those types.

But doing this places those classes into a different .NET instance or app domain from the Excel DNA functions which is a deal breaker.

Charlie

Coordinator
Apr 6, 2011 at 10:31 AM

You'd need to make a copy of the code in ExcelDna.Integration that registers the Ribbon and RTD classes. Basically you can register a .NET class to be the ClassFactory for a particular COM class. Then when a new object is to be instantiated it will call your class and you can return the new object from within your AppDomain. 

But I find the .NET / COM interop stuff quite tricky to work on.

-Govert

Apr 7, 2011 at 4:37 AM

I created a COM class in .NET to act as my 'COM server' and tried to register it into Excel based on your Ribbon/RTD examples (e.g. using SingletonClassFactoryRegistration, ProgIdRegistration, and ComAddInRegistration). I think this part works, in that I can see it in the Add-Ins window (Office 2007) showing as active. However it still won't show up as an available reference in the References window. I suspect this is showing type libraries anyway, not classes, so something else is missing here.

I think I'm stuck without a solid example, but I realise this may be asking a lot.

Coordinator
Apr 7, 2011 at 8:52 AM
Edited Apr 7, 2011 at 8:55 AM

I think you still have to make the .tlb from your assembly and Tools->Reference it in the VBA Project. But if you've done the ClassFactory registration in your add-in before the New MyClass() gets called in VBA, Excel will use your ClassFactory instead of trying to activate the .dll directly. That's where you get to return an instance from your AppDomain.

If you can get this to work, I should in a future version of Excel-DNA be able to add something like an [ExcelComClass] attribute and do this kind of export and hook-up automatically.

-Govert

Apr 8, 2011 at 12:43 AM

I managed to get this working today! I actually tried this yesterday and it didn't work because there was one vital piece missing.

The GUID of the class (as per the *.tlb) must match the GUID used by ExcelDna when registering the singleton factory. Because ExcelDna generates the GUID randomly, these were never matching.

For now I hard coded a GUID attribute into my class, and hardcoded the same GUID into ExcelDna, and voila it works!

I also had to change the REGCLS_SINGLEUSE to multiple use (otherwise only the first call would work correctly).

If you could wrap this up into an easy to use attribute, that would be awesome! Hope others find this useful also.

Coordinator
Apr 8, 2011 at 7:04 AM

Fantastic Charlie!

Could you perhaps send me an example I could include in the distribution?

-Govert

Coordinator
May 2, 2011 at 8:10 PM

Hi,

I have now added some support for creating a COM Server in Excel-DNA. 

There is a new attribute, ExcelDna.Integration.ExcelComClass. A class that would have been visible to COM - ComVisible(true), marked as ExcelComClass, with a ProgId and a Guid, can be served directly by the .xll. Registration of the .xll is either with "regsvr32 <MyAddIn>.xll" or by calling ComServer.DllRegisterServer().

Such classes can then be instantiated from VBA with CreateObject("TheProgId") and will load into the same AppDomain as the rest of the add-in.

There is no special support for type libraries, but these can be created as usual with tlbexp.exe. If useful, these could in future be embedded as a resource in the .xll too.

-Govert

May 3, 2011 at 12:02 PM

Hi Govert,

This is great news! I ran into some difficulties when it came to registering the COM server, mainly because I need to create a type lib and hence have enabled "Register for COM Interop".

This seems to create a conflict as regasm wants to set the registry value to mscoree.dll, whereas Excel-Dna wants this set to the .xll name. Obviously I want Excel-Dna to be the handler, so I tried calling ComServer.DllRegisterServer() in my [ComRegisterFunction].

This did not work however because "registeredComClassTypes" is empty at registration time. I'm not sure when/how you expected DllRegisterServer to be called?

My workaround was to write the .xll key manually, in much the same way that I imagine regsvr32 works. I am now able to use regasm to both create the type lib and register it for use with Excel-Dna in the one step. :)

Charlie

Coordinator
May 4, 2011 at 5:53 AM

Hi Charlie,

Thank you for checking!

I hadn't thought of how to incorporate the TypeLib generation yet.

The .xll's registration (with that list you see in registeredComClassTypes) will only work for classes that:

1. Are marked with [ExcelComClass] or implement IRtdServer, AND

2. Have BOTH a ProgId and a GUID attribute (the example you sent me did not have a ProgId - I decided to require these to be stated explicitly for now).

These should register fine - Regsvr32 on <MyAddIn>.xll just redirects to ComServer.DllRegiserServer() internally. This should add a ProgId and Guid keys under HKEY_LOCAL_USER\Software\Classes, setting <MyAddIn>.xll as the InProcServer32. You can add additional keys and settings.

In your project you should disable "Register for COM Interop". Then you could add a post-build step that calls TlbExp.exe on <MyCoolStuff>.dll to make the library. To register, you then call Regsvr32 <MyAddIn>.xll, or you can put the Registration in the AutoOpen.

 

Do you know whether the TypeLib key in the registry is useful to you?

-Govert

May 4, 2011 at 12:41 PM

Hi Govert,

I have already set the ProgId but still couldn't get ComServer.DllRegiserServer() to work. Note I am calling it from a [ComRegisterFunction] method, and at this point it appears Excel-Dna has not run through the initialization as the list is empty.

When I start Excel with the .xll, I see my COM class added to "registeredComClassTypes" so I know it is declared correctly. Have you tried calling it from a [ComRegisterFunction] method? (Note this is not actually a problem for me, I'm just curious as to how this is supposed to work.)

The typelib is definitely useful to me, hence why I use "Register for COM Interop". It makes deployment easier (regasm takes care of creating the .tlb, registering COM servers and the typelib). I can get what I need by modifying the registry key manually in the [ComRegisterFunction] method. It all works with a bit of trickery. ;)

P.S. I noticed you do not sign your .NET assemblies. This makes your distribution assemblies unusable if the parent project is signed.

Charlie

Coordinator
May 4, 2011 at 1:11 PM
Edited May 4, 2011 at 1:17 PM

Hi Charlie,

Excel-DNA does not look for a [ComRegisterFunction] at all.

From what you say, I expect regsvr32 <MyAddIn>.xll to register the add-in, and then it should be usable from VBA late-bound (using CreateObject("My.ProgId")). This is the first scenario for registration, where you are happy to do some installation work.

If you want to .xll to be self-contained, and work just by File->Open or double-clicking, with no other installation, you can add a class that implements ExcelDna.Itegration.IExcelAddIn, and then you have an AutoOpen() function which will get called when the add-in is started by Excel. In this AutoOpen() method you can call ComServer.DllRegisterServer() to do this registration. At this point you have an add-in that needs no other registration. Remember that "Register from COM Interop" only helps on the development machine; it does not make deployment easier. I'm trying to figure out how to do deployment without any registration step on the client. 

For the typelib, we need to distinguish between making it and using it. To make it, you can run "tlbexp.exe YourAssembly.dll" and it will generate a .tlb. You can do this in a Post-Build step in Visual Studio. Once this is in place, you no longer need to use "Register for COM interop" to get a typelib.

I understand the TypeLib is useful for you. But in you VBA project you can Tools->References and browse to the TypeLib .tlb file created by tlbexp. So I was wondering how important the TypeLib key and related entries in the registry are for you. So maybe I'm asking what you'd do in the [ComRegisterFunction], to keep VBA happy.

Signing: I've never given much thought to signing the assemblies. I presume you could sign them as easily if you're signing your parent project too. I also think the model I prefer is not signing the .NET assemblies, but running the packing tool to get a single .xll file, then signing packed .xll add-in for Excel. I have not experience with any of this though, so any advice would be useful.

-Govert

May 5, 2011 at 7:40 AM

Hi Govert,

What I meant is regasm.exe calls the [ComRegisterFunction] method, and then I call DllRegisterServer() which does nothing because it has not been initialised yet. It would be nice if this worked for those of us who want to use regasm.exe instead of regsvr32.exe.

I understand what you are saying regarding the AutoOpen() and using CreateObject(), but this won't be useful if we need to have a typelib as this needs to be pre-registered (based on the tests I did).

I don't do anything fancy in the [ComRegisterFunction] method to register the TypeLib. Regasm.exe does this automatically. I understand that "Register for COM Interop" works only on the developer workstation. When it comes to deployment we use regasm.exe to do the same thing.

I think the reason for our difference in thoughts is I prefer the typelib whereas you prefer late-binding. Don't get me wrong though. Having the ability to load COM servers into Excel-Dna is very useful and opens many doors whether one wants to use regsvr32, regasm, typelibs or late-binding.

Regarding signing, you cannot sign a binary once it has already been created (not without disassembling it and re-assembling it with a signature). You also cannot use unsigned assemblies in a project/solution that uses signatures. Most 3rd party assemblies I've run into (NHibernate, SQLite, etc) provide their assemblies signed. Signing gives it a "strong name" which also helps with versioning. I think it's a good idea and don't see any down sides, but I don't want to tell you how to run your project. ;)

Charlie

Coordinator
May 5, 2011 at 5:36 PM

Hi Charlie,

Ah! - I understand some of the confusion. For the COM instantiation support to work through the .xll - so that Excel-DNA has a chance to load the COM exported types into the add-in's AppDomain - the .xll becomes the COM server for the class (instead of mscoree.dll). The .xll is a native COM server, not a managed code assembly, and thus the registration does not work through the managed registration service that RegAsm uses. That's why you need to switch off the 'Register for COM Interop' on your project, and why we need to follow the unmanaged registration route (through regsvr32.exe or a custom function that writes the registry entries). The [ComRegisterFunction] is something that RegAsm would have called, and we can emulate that call by adding some more code in ComServer.DllRegisterServer(). But it won't just work automatically.

The danger to adding a call to the [ComRegisterFunction] from this specialized Excel-DNA context is that it is not obvious how to distinguish the registration environment during that function call. So I decided not to have something that can interfere for now.

So I need to understand how the type library needs to be registered for it to be useful from VBA, including how to find the type library to register and writing the right registry keys. Since we don't have RegAsm.exe to help, that logic needs re-implemented somewhere. One option for now is that you can put it into your AutoOpen. If you can figure out what registry keys need to be added, and how I should deal with the tlb(s) given that there might be more than one assembly exported by an .xll, it would help me to put that support in place. For example, I could probe for a name that matches or I could add a TypeLibPath=... attribute to the ExternalLibrary tag I was hoping for a way to embed the type library into the .xll too - this is easy if there is only one type library, but I'm not sure how to do it if there are mutliple .tlbs.

If you're able to investigate what is actually needed to register the .tlb so that VBA is happy, that could help a lot. Else I'll try to get to it at some stage.

Signing: I believe you can sign the compiled assemblies by calling al.exe. I just don't understand how signing the .NET libraries can help you when making an Excel add-in. For this you need to sign the completed .xll. You can't really use the same libraries that reference Excel-DNA in any other context, and the signing doesn't help when it's loaded by the runtime hosted by Excel-DNA in the Excel process. It's not that I want to be difficult, I just don't understand what reasonable scenario this helps with - (and I don't have a certificate etc.) I appreciate you raising this issue.

Thank you,

Govert

May 7, 2011 at 6:57 AM
Edited May 7, 2011 at 6:58 AM

Hi Govert,

>> thus the registration does not work through the managed registration service that RegAsm uses.

It works if you use regasm, but only if you change the registry key to point to the xll rather than mscoree.dll.

As far as I can tell, registering a type lib involves creating a registry key here: HKEY_CLASSES_ROOT\TypeLib. I suspect if you don't create this registry key in advance before Excel is started (or if you try to use AutoOpen) it will not work. You can still browse to the tlb manually, but it won't appear in the list of registered libraries.

In my particular case I'm happy to pre-register the type lib as I don't need a regstration-free approach.

Regarding signing assemblies, al.exe takes source code as input so that doesn't appear to work. Not having a certificate is not an issue, as you can easily generate one in Visual Studio. These are free and do not need to be issued by a certification authority.

As an example, say my organisation security policy is to fully trust assemblies signed by our particular strong name only. Are you saying if I don't sign MyCoolAddIn.dll, then Excel-Dna will still load it into Excel without a problem? I assumed the same rules applied whether it was loaded directly by mscoree.dll or by Excel-Dna. I guess I can try this when I have some time.

Charlie

Coordinator
May 7, 2011 at 7:34 AM

Hi Charlie,

I mean when you run RegAsm, Excel-DNA is not initialized and is not involved with the registration process. So even though you try to call ComServer.DllRegisterServer from your [ComRegisterFunction], there is no initialized Excel-DNA to talk to.

I'll try to work out some improved TypeLib support. It looks like the packing might not pose too many problems - multiple typelibs can be used from resources it the TypeLib registration is done right. I'll just have to figure out exactly what all those keys under TypeLib mean, check that HKEY_CURRENT_USER\Software\Classes\TypeLib is a good alternative. Then I can probe for an existing .tlb next to the .dll when it's registered, and add the registry entries it if it's there, else get some way to generate the TypeLib from an assembly on the fly.

I don't think add-ins with Excel-DNA will run under your security policy (absolutely certainly not under .NET 4, and I don't think under .NET 2.0 either). Once you've allowed Excel to load your unmanaged .xll, I assume all dependencies are trusted. You can enforce security by packing into the .xll and signing it. 

-Govert

May 7, 2011 at 10:07 AM

Yep that's exactly right (about Excel-DNA not being initialized via regasm), hence why it doesn't work.

HKEY_CLASSES_ROOT, in case you didn't know, is just an alias to HKEY_CURRENT_USER\Software\Classes, so these are the same thing.

>> else get some way to generate the TypeLib from an assembly on the fly.

That would be the bee's knees. We could get could rid of regasm altogether. However that might be quite some work for Excel-DNA to get right.

>> Once you've allowed Excel to load your unmanaged .xll, I assume all dependencies are trusted.

I'll have to test it to be sure. I suspect you are right but I find that quite surprising if that is the case.

Charlie

Coordinator
May 7, 2011 at 11:35 AM

HKEY_CLASSES_ROOT is an alias for HKEY_LOCAL_MACHINE\Software\Classes. But non-admins typically can't write to this one, whereas the HKEY_CURRENT_USER hive is always writable. COM checks in both places.

I'll see what I can do with the TypeLibs.

May 8, 2011 at 2:03 AM

Yes I meant HKEY_LOCAL_MACHINE (silly brain). It makes sense then to write it under HKEY_CURRENT_USER where the user is guaranteed to have write permissions.

May 8, 2011 at 10:12 PM

I am very interested to here how this all turns out.   Is it asking too much to recieve a simple and detailed example of what was decided on. 

I have a DLL that contains the seismic spectra (per USGS) stored as a dictionary with US Postal Zip Code as the Key.  

This DLL works fine, in a perfect world I would like to access this data via an Excel UDF. 

I have read and attempted to understand the discussion above, however I am not an experienced programmer you lost me when talking about the need or lack thereof for COM server, registration... bla, bla, bla.

It is my greatest hope that you will provide a simple example of a dll that exposes some public function and somehow some way (either via VBA or elsewise) make that functinoality available as a UDF.

Coordinator
May 9, 2011 at 7:58 AM
Edited May 9, 2011 at 10:51 AM

Hi visProcessEngg,

The issue here is about making objects defined in your add-in also available to VBA from within the same AppDomain. I don't think it's applicable to what you are trying to do.

I think in your case, making some UDFs that access your .dll will already work fine using the existing version of Excel-DNA. There are a few things to figure out - is the .dll a C library, a .NET library or a COM library, etc. If you have any problems getting this to work, I'd be happy to help you work through it on the Excel-DNA Google group at http://groups.google.com/group/exceldna

-Govert

Coordinator
May 15, 2011 at 8:53 PM

Hello,

I've made some changes to the COM server support in the latest version (Check-in 66649):

* The [ExcelComClass] attribute is removed.

* ComServer attribute in .dna file determines which libraries are registered and supported for COM: <ExternalLibrary Path="MyLib.dll" ComServer="true" />

* All ComVisible classes with a public constructor are registered and can be created. Optionally use a Guid attribute to ensure a stable registration, and ProgId attribute is also optional. 

* TypeLibrary registration is implemented. If MyLib.tlb exists next to MyLib.dll, it will also be registered. To make the TypeLib, call tlbexp.exe, (maybe from a post-build step if Register for COM Interop is off.) I think [assembly:AssemblyDescription("My lib")] sets the TypeLib friendly name - not sure....

* TypeLibs will also be packed by ExcelDnaPack if they exist. So <DnaLibrary><ExternalLibrary Path="MyLib.dll" ComServer="true" Pack="true"/> </DnaLibrary> will allow the creation of a single .xll file that can be registered with  "regsvr32 MyAddin.xll" and will register the COM types and their type library - no other files need to be distributed.

* Registration is still either by regsvr32.exe or by calling ExcelDna.ComInterop.ComServer.(Un)RegisterServer.

* A ComServer sample has been added to the distribution.

Any feedback would be most appreciated.

-Govert

May 16, 2011 at 1:58 AM

Wow, awesome work! Haven't played with it yet but the change list looks promising.

From memory [assembly:AssemblyDescription("My lib")] does set the friendly name.

Looks like I can do away with regasm all together now.