Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 6. November 2010 16:21

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.

One way around this is to add a class library project to the same solution – let’s say, AddInApplication, with a main class called AddInManager, like this:

using Microsoft.Office.Interop.Excel;

public class AddInManager
{
   public AddInManager(Application excel)
   {
      excel.SheetActivate += SheetActivated;
      excel.SheetDeactivate += SheetDeactivated;
   }

   public Worksheet PreviousSheet
   {
      get;
      set;
   }

   public Worksheet CurrentSheet
   {
      get;
      set;
   }

   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;
   }
}

Instead of performing the work in the add-in itself, we pass an instance of Excel to the Manager, and move all the functionality there. We can now remove most of the code from the add-in, by adding a reference to the AddInApplication project, and passing the hand to the Manager when the add-in starts up:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
   var excel = this.Application;
   this.Manager = new AddInManager(excel);
}

The benefit is that we can now unit test that piece of functionality. The entire Excel interop is exposed through interfaces, which makes it perfectly suitable for Mocking. We can write a test class along these lines:

using Microsoft.Office.Interop.Excel;
using Moq;
using NUnit.Framework;

[TestFixture]
public class TestsAddInApplication
{
   [Test]
   public void WhenSheetIsActivatedItShouldBecomeCurrentSheet()
   {
      var excel = new Mock<Application>();
      var worksheet = new Mock<Worksheet>();
      var application = new AddInManager(excel.Object);
      excel.Raise(xl => xl.SheetActivate += null, worksheet.Object);

      Assert.AreEqual(worksheet.Object, application.CurrentSheet);
   }

   [Test]
   public void WhenSheetIsDeActivatedItShouldBecomePreviousSheet()
   {
      var excel = new Mock<Application>();
      var worksheet = new Mock<Worksheet>();
      var application = new AddInManager(excel.Object);
      excel.Raise(xl => xl.SheetDeactivate += null, worksheet.Object);

      Assert.AreEqual(worksheet.Object, application.PreviousSheet);
   }
}

In essence, what we are doing here is creating a “fake” version of Excel, which has all the appearances of the real one, via a Mocking framework (Moq in this case). We pass it to the Manager, fire the event from Fake Excel, and verifying that the AddInManager is reacting properly.

The beauty here is that to do this we didn’t even have to launch Excel – you could even run the tests from a machine where Excel isn’t installed – and yet this is validating exactly what we want, namely that if the Manager is given an instance of Microsoft.Office.Interop.Excel.Application, and SheetActivate or SheetDeactivate is fired, the Manager will do the right thing.

I am pretty excited about this approach, because at least, it is now possible to write automated tests. That being said, it is still not a perfect solution. Mocks work well to listen to Excel events, but they will be of limited help to test what the add-in does to Excel. I am still thinking about how to do that, in a way which is economical, but I can’t quite imagine how to write a simple automated test to verify, say, that the add-in has created a chart with a certain title in a workbook.

The other challenge I ran into was the ribbon. Just like I passed Excel to the project that contains the logic, I managed to move the entire TaskPane logic into the testable dll as well, creating an empty task pane in the add-in, dynamically adding and managing controls from the other project afterwards. However, I had no such luck with the Ribbon, because so far I couldn’t dynamically modify the contents of a Ribbon from outside the add-in itself. I would love to hear any suggestions for that one - maybe with the xml ribbon?.

In any case, I haven’t given up, and will keep trying to get some automated tests into VSTO. Any tips or thoughts on the topic are highly welcome!

Comments

1/24/2011 6:41:13 PM #

Jeff

I've been trying to do the same - although I haven't figured out the aspects of integration testing the code that works against the Excel API.  I've come across this post, which suggests using reflection:  blogs.msdn.com/.../756488.aspx.

Have you tried this approach in your tests yet?

Jeff United States | Reply

1/25/2011 3:52:24 AM #

Mathias

Hi Jeff,
Thanks for the reference, this looks like an excellent post. I have heard of this approach, but never tried it - I'll definitely look into it, and share my findings.
One idea I have been obsessing about lately is building an emulator for Excel - a headless, UI-less fake Excel that could be used for testing. I am still not sure it's a good idea, and it would certainly be lots of work...
And I don't have a good solution yet for integration testing a VSTO project. So far, the most I have done was using test files to check reads from Excel, but testing writing from .NET to Excel I have left alone.
Thanks for your comment and the link, and happy testing,

Mathias

Mathias United States | Reply

1/25/2011 5:19:12 AM #

Jeff

I just walked through the solution in my own project and have mixed feelings about the approach.  Right now I'm getting COM exceptions from Excel (through the remoting channel the article suggested we create) - and that's just from making a Worksheets.Count call.   I tried exposing my Excel objects through accessors in the "ThisWorkbook" class and accessing them directly through my remoting channel, but I didn't make much progress there either.

In general, I'm not so keen with the approach as it leaves instances of Excel.exe open in memory - even when I'm invoking Application.Quit() - so at the moment I think I would need to run a "taskkill" command in the fixture/test setup to cleanup.  

How do integration tests work for you?  Do you have a post on how you set things up?  Right now getting separation (as you show in your posts) allows me to unit test my custom logic, but I'm still left with an empty feeling since I can't be sure code that interacts with Excel - so I still get runtime errors left and right when I launch my "thoroughly tested" app Smile

Jeff United States | Reply

2/18/2011 4:23:47 PM #

trackback

Codebix.com - Your post is on Codebix.com

This post has been featured on Codebix.com. The place to find latest articles on programming. Click on the url to reach your post's page.

Codebix.com | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS