ExcelDNA for MSOffice Suite?

Mar 4, 2013 at 8:33 PM

I hate to ask what is probably a dumb question, but am having a hard time determining which route to take.

I have been programming (VBA) for about a year now, and am at the point where I am creating a bunch of classes in order to reuse my code in different ways. A big downfall is managing these classes from project to project (not to mention the OOP limitations of Vb6/VBA). I know that having DLL's, XLL's, and Add-ins will make managing my code base a lot easier.

I am seriously considering a move to .Net to gain the convenience of mature OOP and to help any future transitions if my applications need to be migrated to a more modern system.

So my question is this. Most of my solutions use multiple office Apps to achieve the end result. Does Excel-DNA lend itself to general Office development solutions beyond Excel?
Mar 4, 2013 at 9:04 PM

I'm a huge fan of .NET, but there is a steep learning curve in getting there from VBA. One should take it step-by-step and expect to be frustrated now and then. But the rewards are big after a while, it really is a more dynamic and powerful environment, and the integration with Office is fine.

The first question is whether you make user-defined worksheet functions for Excel. If you do, you need Excel-DNA for (at least some part of) the Excel add-ins. Patrick O'Beirne's guide to porting from VBA to VB.NET with Excel-DNA is a great start: http://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/

Excel-DNA itself has no role beyond making Excel add-ins, and if you make no worksheet UDFs you probably don't need to bother with Excel-DNA at all.

To talk to Office apps in general, you need some 'interop assemblies' to expose the COM automation model that you know from VBA to your .NET code.
The question here is whether you need to support a wide range of Office versions. If you do, you should try the NetOffice libraries to do the interop. They cover all the Office versions with a single set of interop libraries.

If you target only one or two Office versions, you can use the official Primary Interop Assemblies, and all will be fine. Making the COM add-in itself is not likely to be a big issue.

I'd probably not use the VSTO tools myself. These are add-in libraries and templates in Visual Studio, that sit on top of the Office COM automation interfaces. I think VSTO adds more complexity than the value it adds.

A commercial option that might suit you is 'Add-In Express'. It's not too expensive, gives you wizard etc. to get started, and I believe they give good support. But you basically get something a bit like NetOffice, plus a bit of (an old version of) Excel-DNA, plus some wizards. There's nothing you can't do with the open-source tools directly, though it's packaged to be a bit more friendly to get started.

Mar 4, 2013 at 9:33 PM
Thanks so much for the excellent response, govert!

I can attest to the learning curve and version support issues with .Net.

It took me almost a weekend to create a front-loader app for a widely used company workbook. Even then, I had to switch to late-binding because I couldn't figure out how to get PIA's for both '03 and '07 into the project!

Most of my development is for task automation, dashboards, database queries, Access Forms, and reporting. I make a living in the Office Object Model, but need a more modern way to develop/maintain my code. I have yet to create a UDF for an Excel spreadsheet, and don't think I will need to in the near future.

So based on your advice, I am going to start with NetOffice. Thanks a bunch for that suggestion, as I was looking into Add-In Express but did not look forward to buying that on top of VS.

I really appreciate your help : )