Methods In Excel

So if you read the first post hopefully you’ll be convinced that using dot Net is a viable option for an Excel developer. The next logical question you face is what method should you use to deploy your dot Net code. Let’s start by looking at what options are available:

A Managed dll called from VBA
It’s easy enough to write a plain old dll, mark it COM callable and call it into Excel from VBA. In this way you can use of all of the dot Net stuff and don’t have to do to much in the way of interfaces or set up. There are different methods that can be used, Marshall Object is probably the best if you want to work with the Excel object model directly – see here for more details.

Managed COM addins
This is a like for like managed* equivalent of a classic COM addin written in VB6. It uses IDTExtensibility2 as the interface into Excel. It can do all the things that an unmanaged COM adding can do. You should note that COM addins need to be isolated, more details can be found in Dennis superb tutorial, here.

VTSO addins
This is the way Microsoft would like us to go. It’s a managed com addin, which has been specifically design to work with a single Office application type. A lot of the plumbing and set up stuff that you have to do with a Managed COM addin is done for you in VSTO. This comes at a price of making the addin not wholly compatible across different Office versions. (Although at the time of writing, VTSO in Visual Studio 2010 and dot Net 4 should change this.) Microsoft has been working hard to make VSTO much slicker in the last few years, and these days it’s a pretty good option. It won’t do UDF (easily) though, and you will need the professional version of Visual studio, or better.
You can find a fuller discussion on the differences between VSTO addin and Managed COM addin in PED2.

Addin Express
Addin Express is a company, they make a few different products to wrap up managed COM addins, and VSTO type addins and have an excellent reputation for making the developers life easier. Prices typically start from around £220 for a life time licence.

Other dot Net wrappers.
This includes Excel DNA and other products like XLW, Excel4Net, Managed XLL and the like**. I’ve grouped these together here, but there could be a separate entry of each one just as easily. Excel DNA, XLW and Excel4Net are free, Managed XLL is quite expensive, £1000 plus, I believe.

OK, so there the main options, but which one? Well to be honest they all have there pros and cons, and any of them would be a good way to start using dot Net code with Excel, but here are some things to consider.

Cost.
Most of these options are expensive. Managed Com addins can be written in SharpDevlop which is a free IDE, but shimming*** them is hard, if you use Visual studio you get the shim for free. VSTO also needs Visual Studio, Addin express cost some cash as does Managed XLL. XLW, Net4Excel, and Excel DNA are free, and as a result they might not be quite as fully featured as the paid for options.

Deployment.
VSTO and managed COM addin used to be extremely hard to deploy, VSTO is now much better. Certainly the environment and the control you have on that environment plays a part here. If you know all your target PC’s are set up in a particular way, with a particular Office version, the headaches reduce somewhat. Excel DNA is simple to deploy, it’s a XLL. Calling a managed COM dll isn’t to hard either. I can’t talk too much about all the others, so let assume they’ve all got good deployment stories.

Development.
As far as I know Excel DNA is the only one that will allow you to write code into a text file, and then compile that code at run time – this means you don’t even need an IDE to write a Excel DNA addin – how cool is that? So if you can’t get anything installed on your PC, you’re not beaten yet! (Although you will need the dot Net framework v2 or higher). Since all of these solutions can make use of some sort of IDE, then you get all the benefits of that. Addin Express, VSTO and Managed XLL also come with many other tools and wizards that make development much simpler and quicker.

Support.
All of the options are fairly well supported. Excel DNA has an active forum on Google groups, VSTO forums are common. There is some support on the internet for XLW, mostly from a quant community, and Addin Express and managed XLL have good support web pages, and offer extended support directly from the ISV.

Open source.
If you consider this to be important then you could factor it in. I’m not sure I would count it against something – it not being open – but it is really handy to be able to dig into the source code if you hit a problem, or need to expand/hack something.

Longevity.
The last thing you want to do is learn something new, then it dies 2 years down the line. Open source in theory is good from this perspective, as other developers can pick up the project if the original ones stop working on the code base. This doesn’t mean they will! Likewise if you buy into a “big” company then they will support the product for X years… but what does that actually mean? I think we could get ourselves in to knots on this one. The good news is that what ever you use you will be using the dot Net framework, so these skills are transferable.

Architecture.
I’m not going to go into the ins and outs of how each of these solutions work, but what I will say is that Excel DNA is one of the more flexible and agile ways to implement managed code into unmanaged applications. COM Interop**** isn’t. VSTO, Managed COM addins and indeed managed dll all use interop and a COM interfaces. Managed XLL uses the same methods to interface with Excel as Excel DNA, LXW is slightly different, but its still spits out a normal XLL at the end. Net4Excel is more of a manager of dot Net code, and works is a completely different way to the other products. Addin express does a few different things depending on what type of solution you are using, sometimes COM some times via the C interface similar to Excel DNA.

There might be other things to think about too, but I’ll leave it here for now, the important thing is to try and have at least some understanding of what the options are, and the pros and cons of each. Of course your situation might require something that only one or two products can offer, so your hand might be forced, but for most of us we have some flexibility.

Here’s my summary. If you want to get started, you might like to do that for free, and also create some solutions that can be used in the wild. In my view the easiest way to do that is with Excel DNA. There are some down sides to be aware of; some of the tooling is not as good in Excel DNA as in VSTO or Addin Express for example. Some of the features are missing, or not yet 100% stable, like they might be in Manged XLL for instance, but over all, pound for pound it’s a pretty good bet!

Now, that’s only my view, it might be wrong, I often am, you’re welcome to your own views, why not share them in the comments?

Thanks
Ross

* In this context – when talking about dot Net – Managed means that the code you write is “controlled” by the dot Net frame work. In practice what this means is there’s some part of the framework responsible for making sure your code runs smoothly, it catches small bugs and handles them in a way that won’t kill your app, it cleans up memory so you don’t have to, and lot of other stuff that I don’t know about!

** JetXLL and ex hale, don’t seem to be quite there yet, but I might be wrong…

***Shimming is where a managed component is interfaced by an unmanaged one. This is needed when working with Office applications because if the managed code in your addin errors, it can cause all the other managed code to stop working (if its not shimmed). VSTO has this build in, managed COM addins don’t, but there is a free template file for VS that adds this extra layer.

****COM Interop lets managed dot Net code be called and used by COM type code; in essence it converts types between native COM types and Managed types.