The question of how to unit test VSTO projects has been bugging me (terrible pun, I know) for a while. I am a big fan of automated tests, if only because they remind you when new features you added wrecked havoc in existing functionality. But whenever I work on Excel VSTO add-ins, I end up writing very little tests, because, quite frankly, these projects are a massive pain to test. Excel behaves in many respects both like a user interface and a database, two notoriously hard-to-test areas – and on top of that, you cannot directly instantiate and tear down the add-in, because that happens through Office.

I am still very far from a satisfactory solution, but recently I began organizing my projects differently, and this is showing some potential. I limit as much as possible the role of the add-in project itself, and move the application logic, including interactions with Excel, into a separate project, using the add-in only for "quick-starting” things. The benefit is that unlike the add-in itself, the other project is perfectly suitable for unit testing.

As an illustration, imagine that your add-in, among other things, kept track of the current worksheet you are in, as well as the previous worksheet that was active. You could implement that along these lines:

public partial class ThisAddIn
{
   public Worksheet PreviousSheet
   {
      get; set;
   }

   public Worksheet CurrentSheet
   {
      get; set;
   }

   private void ThisAddIn_Startup(object sender, System.EventArgs e)
   {
      var excel = this.Application;
      excel.SheetActivate += SheetActivated;
      excel.SheetDeactivate += SheetDeactivated;
   }

   private void SheetDeactivated(object sheet)
   {
      var worksheet = sheet as Worksheet;
      this.PreviousSheet = worksheet;
   }

   private void SheetActivated(object sheet)
   {
      var worksheet = sheet as Worksheet;
      this.CurrentSheet = worksheet;
   }

You could easily debug that project and check that it works; however, you won’t be able to write an automated test for that.

More...

Thanks to all of you who attended my sessions on Mocking and on TDD at Silicon Valley Code Camp 2010; I had a great time presenting, in large part because you were awesome and asked great questions!

I uploaded the slides and code for the session on Mocks here. There wasn’t much “written” material for the TDD session, so I didn’t upload it. If someone wants it, let me know in the comments and I’ll add it, too.

Feel free to let me know if there are things I could have done better in the comments below, and don’t forget to fill in your evaluations on the Code Camp website. It’s very helpful for speakers, and… you can win an iPad, courtesy of Dice.com!

On a side note, hats off to Peter Kellner and the whole crew of volunteers. Silicon Valley Code Camp gets bigger every year, and yet, the organization was flawless, and the whole event very fun. Congratulations!

The most awesome free developer event on the west coast is back again, and it looks like it will be even more awesome this year. The 5th edition of Silicon Valley Code Camp is taking place Saturday and Sunday Oct 9th and 10th, 2010, at Foothill College, in Los Altos Hill. There are 194 sessions, already over 1,600 registrations, with topics for all tastes - .NET, Java, Cloud Computing, Python, Javascript, mobile development, and more.

The 2010 schedule is up here. I will be giving 2 talks, on Test-Driven Development, and on Mocking. Both are on Sunday – hope to see you there!

I am very honored that the East Bay chapter of the Bay Area .Net user group will have me as a speaker this upcoming Wednesday. I’ll be talking “For Those About to Mock”, about Mocks and Stubs in .Net, after a presentation of the unit testing features of Visual Studio by Deborah Kurata – an apt opening topic, if you ask me.

You can find more information about this event here; Hope to see you on Wednesday!

A few weeks back, Michael asked an interesting question in a comment: how do you go about unit testing a VSTO project? One of the reasons I prefer working with VSTO over VBA, is that it makes it possible to write automated tests. What I realized with this question, though, is that I unit test heavily the .Net functionality of my add-ins, but not much (if at all) the interaction with Excel.

Note: I am aware of the existence of a VBA unit testing solution, xlUnit; I found the project conceptually pretty cool, but from a practical standpoint, it doesn’t seem nearly as convenient as NUnit or the other established frameworks, which isn’t much of a surprise, given the maturity of unit testing in the .Net world.

The reason for this is double. First, most of my VSTO projects focus on generating heavy computation outside of Excel, and writing results to Excel; as a result, the meat of the logic has little to do with Excel, and there isn’t all that much to test there.

Then, testing against VSTO is a bit of a pain. By definition, a VSTO project comes attached with a giant external dependency to Excel, which we have limited control over, and which is also rather unpleasant to deal with from .Net. To illustrate one aspect of the issue, let’s consider this code snippet:

[TestFixture]
public class TestsThisAddIn
{
   [Test]
   public void WeCannotInstantiateTheAddInProperly()
   {
      var addIn = new ThisAddIn();
      var excel = addIn.Application;
      Assert.IsNotNull(excel);
   }
}

This test will fail: if we instantiate the add-in directly, it does not automatically hook up to Excel. The VSTO add-in is started up by Excel itself, and we cannot replicate that simply in our test code, and access the Excel object to verify that things behave as expected.

So how could we approach the problem? Unit testing our code means that we want to validate that pieces under our control (classes we wrote) work properly; the challenge is that some of them interact with Excel. We are not concerned with testing the system in its entirety (add-in code + Excel) here, which is an important issue, but not a unit-testing one.

The words “unit test” and “external dependency” together suggest one technique – Mocking. In a nutshell, Mocking consists of replacing the external dependency with a fake, an object which behaves the same way as the “real thing”, but is easier to work with.

There are three ways our classes can interact with Excel that I can think of:

• react to Excel events
• read/query from Excel
• write/command to Excel

More...