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 Smile. 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 JohnnyBravo19

Orks

Unmanaged nose-picker aragon

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.

hell-beast_wide

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 Smile

Abhang Rane


Long time….

Boy! Its been ages since I have blogged. I owe an apology ( I know no one is reading this, I am just talking to myself) to my readers. Basically I was running behind earning more bucks and slacked on the part I loved the most. I have to say its been a hell of a ride for last 8 months or so. One thing that I have learnt the hard way, if you are a developer who loves writing good software, always are responsible for your code and all other good stuff “The Pragmatic Programmer” has outlined, then Banks are not the place for you. If you already are working in one, well you are already in Deep Shit.

You may ask, why??? Now this would be my perception of the Banking world, but I think most would agree to it. Politics overwrites almost every decision. Now this might be the case more or less in most organizations but Banks are just exclusive for these things. Basically it reminds of Joel Spolsky’s statement about the kind of job you should choose. If you are looking for just money and are ready to work in a politically motivated environment, work like a dog for long hours and are ready to get fired anytime, you are made for Banks.

There is no doubt that Banks these days heavily invest on technology. Although the kind of investment seen sometimes is just seem so wasteful. So Mr. White Shirt is Ultra Global Senior (….) President of a bank. And you know he just loves Silverlight. It should not be surprising to see all kinds of projects converting their existing working HTML code to Silverilght, because hey that is the new Thing. Every thing starts looking shiny, Mr. White Shirt gets another adjective in his titles. Few months later, a rogue trader causes the bank to loose billions of dollars, shittt!!! Layoffs begin, Mr. White Shirt is made redundant. Those Silverlight projects look quite boring now and are maintained by some outsourcing firm in India. Who wants to work on that stuff anyways. Few months later, bank is doing ok, they hire Mr. Pink Shirt. Mr Pink Shirt is high on HTML 5. So he starts an initiative to convert all the existing Silverlight projects to HTML 5 because, well that is how it should have been all along you know! And the story continues…

I think the process I explained in the above paragraph is the reason why software companies are able to sell their latest products to most banks. It is the reason why Indian outsourcing firms keep increasing their revenues year after year. The question is, as a minion in all this, you DEVELOPER, what do you do. One option is to keep minting the money, keep open mind to change technologies and be ready to get fired with your notice period. The other is to think what exactly you want to do in life. If money is the only aim, you are in a very good place. If building good software and following good processes, working in a fun environment is your thing, its time to get your resume out on the job board.

Happy Thinking!!!

Abhang Rane