Moving from an Excel xla add-in to a C# add-in

At my day job, we use Microsoft Excel spreadsheets as a pseudo "specification document" (spec sheet) for the websites, which are actuarial in nature, we create.  At the time (several years back), since we chose Excel, obviously we needed an add-in for the few automated processes we supported and we needed something immediately (you know how it goes in small companies).  The easiest way for us to create the add-in we needed was to create an Excel add-in file (*.xla).  My background (5-6 years ago) was from VB6 anyway, so even though I’d switched to C#, VB6 was still fresh in my mind and writing VBA was a breeze – whether the code was clean or not, I’ve got not comment ;).  I’ve recently made the decision to migrate an existing Microsoft Excel Add-In (*.xla) file to managed C# code.  There were several motivating factors to this decision along with almost as many speed bumps

Why convert?

To me, there were a some compelling reasons to convert along with a few reasons that just turned out to be nice ‘bonuses’.

• Coding stability – This is probably my most important reason of all.  Our add-in had grown in both functionality, size, and importance over the years and had become a pretty large behemoth.  I don’t really know what causes the problem, but over the last year, Excel has become very temperamental while coding.  I can’t count the number of hours of work I’ve lost due to Excel GPFs.  These GPFs happened at the worst times…upon clicking File – Save from the VBA editor or Debug – Compile VBAProject (it should go without saying, but I have the Compile On Demand option unchecked, so I think File – Save implicitly compiles anyway).  So getting all this code out of Excel and into the more stable Visual Studio environment was a major driving factor.  Additionally, some of the times after a GPF, Excel would corrupt my *.xla file and I could no longer open it.  To alleviate this ‘pain’, every time my add-in was saved, it did a little local ‘versioning’.  I automatically saved a ‘cycle’ of 10 copies of my *.xla.  I did this so that if I was working on the file for an extended period of time before actually checking in a valid copy into a source control tool (we use Visual SourceSafe), I was protecting myself so that I could at least recover to the last save action instead of rolling all the way back to the last version in source control.  Of course, to aid in this process, you have to ‘train’ yourself to hit Ctrl-S (save) about every 15 minutes.  That was fun (sarcasm) and productive (the *.xla had grown to 2MB, so saving, renaming, etc. too several seconds).
• Version Control – By default, since *.xla files are binary files, they can be managed in a source control tool but you can not view differences for the file (let alone the VBA code files) from version to version.  To get around this, you could export each of your code files and check them into source control each time you have a revision.  Of course you’d want to do this in an automated fashion.  I added a menu to my add-in to automatically checked out the VBAProject files, exported files from Excel, and check files back into SourceSafe.  The only draw back to this automation is that if you have worksheets in your *.xla and they contain code, you can not export the code from these files (or at least I never bothered to figure out how since the worksheets in my *.xla do not contain code).
• Code Security – A low priority for our company, but *.xla files makes viewing your code / intellectual property available to everyone.  Even if password protected, your *.xla is not secure.  There are Excel password crackers that will destroy that false sense of security in seconds.  Of course, if your add-in is in C# (like mine is going to be), you’d have to obfuscate your code to protect it from Reflector but that is beyond scope of this post (and my knowledge).
• Development Experience – As I said, I was a VB programmer for a long time before I switched to C#.  I don’t have the links to the documentation now, but when .NET came out, I vaguely remember 2-3 things you could do in C# that you couldn’t in VB.NET, so I decided to move to C# then to get the ‘full’ benefit of the .NET CLR.  I’m not sure if that is still the case, but I have to say that the C# language seems so much cleaner than the VB/VBA code I used to use or the VB.NET code I’ve peeked at a couple of times.  Let’s not turn this post into a religious war of C# vs VB.NET.  I just like the syntax of C# better.  But for you VB’ers that went on to VB.NET, I’m willing to bet you like VB.NET better than legacy VB/VBA syntax.  Disregarding your language syntax preference, comparing Visual Studio (especially 2008) versus Excel VBA Editor (and MS Forms…I think that’s what they are called) is hardly worth mentioning.  The richer, more productive experience you will get in the former compared to the later is laughable.
• Better support for Office 2007 – Excel 2007 (the entire Office suite) introduced a new UI that was dramatically different than previous versions.  You can find a pretty entertaining, albeit long, presentation given by Jensen Harris that is very informative.  With the limited Google-ing I did, I’m not sure an *.xla add-in could even manipulate the ribbon, but even if it could, I think it will be supported much better in managed code.

Okay, you’ve convinced me, now what?

If you’ve made it this far and agree with me that you want to get out of *.xla add-ins, you have to decide the architecture/frameworks you are going to use.  To develop the add-in you have a few choices:

• Excel dll add-in (*.xll) – This is a C++ dll that…ah hell, I know nothing of C++ and you shouldn’t either based on the title of this blog.  This option is out.  (If you really do want to read up on this, you can view the Developing Add-ins (XLLs) in Excel 2007 article at MSDN…good luck!)
• Create a COM add-in using Visual Basic 6 – To do this, you’d probably have the ‘fastest’ migration seeing that all of your existing VBA code would port directly to VB6 AFAIK.  For an example of how to do this, you could read a nice article by Chip Pearson.
• Create a COM add-in using C# / VB.NET – Since one of our reasons for converting was to use the Visual Studio environment, minimally, we’d want to choose this path.  MSDN has an article explaining how to do this: How to build an Office COM add-in by using Visual C# .NET.
• Visual Studio Tools for Office (VSTO) – If you Google for "Excel add-in C#", almost all your page hits are going to revolve around VSTO.  Admittedly, I had no experience with VSTO at the time of my decision, but given its popularity and looking at the VSTO portal, it was obvious to me that this was a ‘hot technology’ and Microsoft seems to be putting a lot of effort into it.  I assumed this would be the route I would take.  You can find a very informative ‘review’ of VSTO from ‘Mike R’ on the Xtreme Visual Basic Tal
  k website.

So there were my choices, and as I mentioned, I immediately chose VSTO and built a simple ‘Hello World’ add-in (CommandBar and message box) with the Excel 2003 Add-In template taking all the default settings and viola, I had my first C# add-in 😉

The architecture I chose is…

For anyone that has done any automation of Excel or regular COM Interop to Excel, you know that performance can come to a grinding halt when you start accessing the COM objects in a repetitive fashion.  This was one of the reasons for our original *.xla in the first place.  To delegate the repetitive COM object access calls via ‘one automation/interop call’ back into the *.xla and then let it internally do all the looping.  For an more information on this performance degradation, read another one of Mike R’s articles.  So given this knowledge, I knew that I was not going to be able to put 100% of my code into C# and I had get a plan of attack for this.

To eliminate (or at least drastically reduce) the performance problem with automation/interop, I knew I was going to have to have a legacy VB6 COM dll in the picture.  Obviously I want to reduce the amount of code in this library to only repetitive COM object access code being called during a performance sensitive process.  I estimate that about only 5-6% of my code will end up in the library.  With that, we get something like the following:

Basically, the automation client will instantiate an Excel Application which in turn, loads its add-ins.  The line between AutomationClient and CSharpAddIn does not ‘really’ exist.  The automation client always talks with Excel, but it can call add-in methods via the Excel.Application.Run method.  Then our C# dll would reference the VB6DLL library and delegate all heavy COM object processing to it.  So whether our add-in is invoked via Excel automation or directly from within Excel by a user, we have the VB6DLL COM library at our disposable to overcome any performance issues that automation/interop imposed on us.

So where are the speed bumps?

So everything sounded great so far.  I was going to prototype some of the different functional aspects our current *.xla add-in to prove that the C# add-in could accomplish the same things.  Of course things are never as easy as they appear.  Using the VSTO template, I ran into several hurdles.  One being even before I had my ‘Hello World’ version working.  C# add-in assemblies must be granted full trust in order to work.  I didn’t know this when I created my simple add-in and ran the included setup project in the solution.  At first, the setup completed and when I ran Excel, ‘nothing’ happened.  I didn’t see any new CommandBar objects or any failure messages.  I found an article from Misha Shneerson that eventually led me to the solution – that the included setup project does not grant trust by default.  The most important pieces of information that I got from this article were setting the VSTO_SUPPRESSDISPLAYALERTS environment variable to 0 (once I did this I indeed got a dialog complaining about requiring full trust) and a link to Mad Nissen’s (I’m guessing at last name) custom installer class to add/remove full trust during install/uninstall.  So ‘technically’, after all this I had my Hello World add-in working.

Note: I came across this article as well from MSDN that seems to address this same issue and explain how to get the setup project customized to grant trust.  But the easiest walk through for this was written by Thomas Tingsted Mathiesen who dummied it down for all of us.

The next hurdle I had is that VSTO setup project installs the add-in only for the current user.  Our requirement was that it installed for all users on a machine.  I don’t remember how I found this so quickly in my prototyping, but fixing the setup project was easy enough…just cut and paste the ‘registry’ entries from HKCU to HCLM.

Now after running the setup, the add-in loaded and functioned properly, but I could not find my add-in within the Tools – COM Add-Ins… dialog in Excel.  I Googled around and found this article that explained it was designed behavior.  So even though it will not show up in the COM Add-Ins dialog in Excel 2003, I’m declaring that acceptable.  So thus far, I’d spent the better part of a day floundering through a couple of issues that should have been no brainers and I hadn’t even started writing any code to test my architecture.  Now this is where the show stopper emerged…

I made my VB6DLL library with a simple public class/method exposed for my C# add-in to call.  Just simple loop:

Public Sub Test(ws As Excel.Worksheet, cnt As Long)   
	Dim i As Double   
	Dim j As Double
	Dim r As Excel.Range   

	For i = 1 To 50
		For j = 1 To cnt
			Set r = ws.Cells(j, i)
			r.Value = i * j
		Next 
	Next  
End Sub

However, upon calling this from my VSTO C# add-in, I received an "Class does not support Automation or does not support expected interface" error on the Set r = ws.Cells() line. But what was even more strange was the fact that when I examined the ws object in the Watch Window I got the following results:

So immediately I was concerned with the marshalling of the Worksheet object from C# to VB6 since some properties worked and some didn’t.  I tried changing the declaration type to ‘object’ and that didn’t help matters at all.  This is the point I discovered the Xtreme Visual Basic Talk website and I would strongly recommend this site to anyone.  Reading several of the posts, there seem to be a few good ‘experts’ that are more than willing to provide you with excellent information.  If you want to read the thread regarding this issue, you can view it here.  It turns out there is some sort of marshalling issue with VSTO.  I don’t know exactly the reason, but I think it has to do with the VSTO add-in loader that creates a separate AppDomain for each loaded add-in.  I was able to prove VSTO was the culprit (event if I didn’t fully understand why) by simply creating a COM callable C# library that referenced my VB6DLL library.  I could then successfully reference the COM callable C# assembly from Excel VBA and call a method that in turn called the VB6DLL Test method.

So where did I end up?

After 3-4 days of floundering, I finally was able to create the add-in architecture that I wanted (note that I have not actually done any of the actual porting code yet…doh!) and in regards to how to accomplish it, unfortunately at this time I was going to have to eliminate VSTO from my toolbox and just create a C# add-in the old fashioned way.  When I do get to implementing an Excel 2007 version, I am hopeful that I can either programmatically manipulate the ribbon pretty easily (as opposed to being dependent upon the VSTO designer) or simply use the VSTO designer to create a layout then export some XML/code that I can then ‘just use’ in my non-VSTO implementation.  Even Mike R, who at first was praising VSTO, is now a little leery about how promising VSTO is going to be:

I should clarify my experience with VSTO: I have yet to use it other than for a few test projects. But I do foresee using it in a future project for Excel 2007 only because I just love the visual Ribbon designer, but otherwise I don’t have a need for it. Given the problems you highlighted here, I’m less sure that I’ll be using it myself.

In summary, even after all this I am still happy and excited to migrate off of the old *.xla technology and into primarily C# code.  If you’ve made it this far, I hope I’ve provided you with some gotcha’s (obscure as they might be) to watch out for when coding a C# add-in along with some good reasons to possibly migrate your *.xla add-ins should you feel up to it!