Search code examples
excelvbaregasmxllexcel-dna

Excel DNA - Can I avoid doing a regasm all together?

I am trying to evaluate Excel DNA to use it in one of my excel add-in. I use C# functions (.NET 4.0) and want to invoke these functions from Excel. The reason I am interested is, the users of my addin are non-admins and hence would be a breakthrough, if I can find a solution not to do a regasm on my .NET dll, to get my addin working.

I understand that if it is like a worksheet function (with simple return types and arguments), as in: private string Add (int a, double b) I can easily wrap expose them using excel dna. Also, I understand that, I can also call these simple functions using Application.Run from VBA.

But, if I have a complex type involved in the API and want to use this from VBA then do I require to regasm that assembly and types? example as in this: 

private MyType AddLogic (myType1 A,  myType2 B)

Or is there any way in Excel DNA, that I can also consume such kinds of functions in VBA without any regasm or regsvr32 ?

Thanks Mani

Solution

  • You are referring to the built-in COM server support in Excel-DNA. There are a few options, all of them work fine for users without administrator rights.

    • You can register the COM types at runtime in your AutoOpen - they will then be available late-bound from VBA (so everything in VBA calling those COM types will be 'Variant' and you get no intellisense).
    • You can register the COM types with regsvr32, using the .xll as your COM server. The Excel-DNA registers its types in the HKEY_LOCAL_USER part of the registry, which the user can always write to.Then the VBA project that uses the COM-exposed types will run even if the .xll add-in is not loaded.
    • To add type library information for the COM-exposed types, you will have to do the regsvr32 registration, which again works without admin rights, and then you get intellisense etc. in VBA.

    In none of these cases do you use RegAsm - which registers managed assemblies for runtime activation - since the native Excel-DNA .xll mediates the COM activation of your .NET types itself.

    If you are not interested in providing worksheet functions, ribbons etc. you probably don't need Excel-DNA for this. You can register .NET assemblies for use by non-admin from VBA by just making a .reg script which will right the registry entries in HKEY_LOCAL_USER instead of HKEY_CLASSES_ROOT. I mean to say that the non-admin registration of Excel-DNA is no special magic. The main reason for integration of this feature in Excel-DNA is to ensure that those objects are activated in the same AppDomain as the rest of the Excel-DNA add-in, which is tricky and sometimes important.