Self-contained XLL with early binding in VBA

Aug 6, 2015 at 2:02 PM
Hi (govert),

I have a C# project with Excel-DNA defined UDFs which is working as expected. In addition to these I've exposed a few classes to be ComVisible in order to access the objects from VBA.

When I build my project I generate
  • .xll
  • .tlb
In order for the VBA developers to access the ComVisible classes from their spreadsheets they currently add the .tlb file as reference to the workbook (to get early binding). This also works as expected, but I want to automate it.

Question
How can one integrate the .tlb file into the .xll? I found this documentation which works, but I cannot get it to work with early binding.

Any ideas?

Thanks
aflyw
Coordinator
Aug 6, 2015 at 2:12 PM
You might look at the instructions here: http://mikejuniperhill.blogspot.fi/2014/03/interfacing-c-and-vba-with-exceldna_16.html

Basically, if you generate the .tlb so that it is next to the .dll and .xll when running ExcelDnaPack, it will also be packed in the .xll file. (You can check the output of ExcelDnaPack to confirm.) Then you can Tools->Reference the .xll file from VBA.

-Govert
Aug 6, 2015 at 2:29 PM
Hi govert,

Once again, thanks for the quick reply.

Yes, I've tested that, but it still requires the same extra step of adding a reference to the workbook. Ideally, I would like the users to double click the .xll and no more actions needed (which is the case in the documentation I linked to). Isn't that possible with early binding?

Regards
aflyw
Coordinator
Aug 6, 2015 at 3:17 PM
For early binding of any COM library you need the VBA project to add a reference to the library.

I don't know of a way to set the default References that a workbook would have, or anything like that.

I guess your code could add the reference using the COM object model, maybe using a menu button or something.

-Govert
Aug 7, 2015 at 6:28 AM
Thanks govert.

I've tried to google how to do it via the COM object model but can't seem to find anything.

If anyone else has input on how to automate the process it is greatly appreciated. As mentioned, currently the VBA developers add the .tlb file as reference to the workbook (or, it's done automatically in Workbook_Open).

Regards
aflyw
Coordinator
Aug 7, 2015 at 7:22 AM
Aug 7, 2015 at 7:28 AM
Yes, that is what we currently do in Workbook_Open. Sure, it's somewhat automated but is still "a manual step" for the VBA developer. Since the .tlb file is strongly coupled with the .xll I don't want the VBA developers to have the option to choose the .tlb file at all.

I found an article how to embed tlb as resource. Any experience from this with Excel-DNA govert?

Regards
aflyw
Coordinator
Aug 7, 2015 at 5:49 PM
Excel-DNA will embed the .ylb as a resource, but that just allows you to Tools-> Reference the .xll instead.

I don't know of a way to get early binding in VBA without some kind of reference. That's kind of what References are for.

-Govert