Managing the Unmanaged!

For the most part, I have been a managed code developer. I have been on the “good” side of the programming world if I may say so . I used to have this thought in my mind all along but never actually put it on paper. I think below table represents how managed code programmers and unmanaged code programmers look at each other. This could be completely off target, but you know when I have those 5 Heinekens my thoughts really start flowing.

	Managed	Unmanaged
Managed
Unmanaged

Phew, feels good to get that out of my system. Anyways, to the content of this blog post now. The meat of this blog is writing Excel addins. There are quite a few ways to write an Excel addin. If you are using Excel 2007 and above, you could use the Microsoft Office Excel project in Visual Studio to create an addin in managed code. If you are stuck with Excel 2003, like most big nasty corporations are, you would have to take different routes,

- If you have a VBA ninja in your team (wonder why ninja is used so often in programming contexts these days…) go ahead write the whole friggin addin in VBA in an xla file. That is the cleanest way to have an Excel addin, no other dependencies. HoHooo!!

- If you and your team thinks VBA is a language of the ancient tribes, you can write managed code in a sexy language like C# may be. Although in order to expose it to Excel you have to tackle a beast called COM.

Yup, that’s right. COM. Compile your C# code into a dll, and register it for COM. There are several sites that can help you on this, may be you can start at StackOverflow http://stackoverflow.com/questions/7092553/turn-a-simple-c-sharp-dll-into-a-com-interop-component. I had used this approach in some projects and I would say it worked well for the most part. The itchy part was upgrades. We could not have multiple versions of addins running due to COM path registration. We ran into some wrong registry entry problems on certain users machines. I mean if it has to go wrong with COM, it will go wrong in all kinds of ways.

- You could use a Managed-Unmanaged bridge that can help integrate .NET into Excel quite easily.  These are available in *.xll formats. They are also called as rapid excel development kits. Some of the xlls I have used is ManagedXLL (expensive), Excel-DNA (free!!). With these all you have to do in general is as follows,

- Create a C# class library project.

- Create a public static class and add all public static methods that you need to call from Excel. One caveat is the arguments and return types should be compatible with Excel for which you need to read the respective kits documentation.

- Place the runtime (possible just the xll file) besides the dll you just created.

- In Excel, go to Tools-> Addins and add the xll file you placed in the earlier step.

- The sheet is ready to call the methods that are exposed in your dll. No fuss no COM.

I know this seems a very rosy picture of xlls in general. Honestly I did have to understand a bit of internal structures Excel uses etc, but that was for advanced methods I was writing. For general purpose one has to just abide to the xll laws documented and write your managed code as you like.

From my personal experience, ManagedXLL gives lots of features out of the box and are easy to configure via configuration file. No doubt its expensive, but I have seen this product being used generally in big corporations where they have a site license. If you are in such a position, I would suggest start using ManagedXLL for created Excel addins. For the freelancers, free-time coders, please go and try Excel-DNA. I know ManagedXLL has support for customizing menus, status bars etc. I bet Excel-DNA would have to but not tried yet.

The biggest gain I have had with ManagedXLL is the upgrades we provide to our users. As we do not register an guff in COM, we are free to have multiple versions of addin and different locations. We need not ask users to restart Excel to get upgrades and many other happy thoughts come to my mind. Of course, not everything is good about this approach, if it was all Excel addins would have been build this way. Things like having custom types in our method arguments or return types is a bit tricky to achieve in xll world. You need to represent your custom type using a data structure or collection of data structures that Excel can plot on a sheet. This can be done via Custom Marshallers. I am just scraping the surface here, but the idea is Excel does not know about your type. You need to somehow tell it via ManagedXLL how to represent it in a worksheet. If primitive types is all you use in your methods, then its a smooth ride with such tools.

So go and play with these life savers,

Happy Managing