Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
6. November 2011 15:20

I am somewhat tests-obsessed, and as a result, often find Excel frustrating to work with, because writing automated tests against it isn’t trivial. So recently, while perusing the chapter on Scripting in Programming F#, I came across an Office automation example, and started wondering whether this would be a practical way to write automated tests against Excel.

The use case I have in mind is an existing Excel Workbook, which contains a model (say, your typical Financial model), with a fixed structure, and maybe a sprinkle of VBA, and no .NET.

For illustration purposes, let’s work with the following: our workbook, Model.xlsx, contains one worksheet, “Finances”, with a Profit cell in B3, computed as the difference between the revenue and cost named cells. Pretty impressive stuff.

What I want is a way to automatically set the Revenue and Cost to some arbitrary value, and check that the result in Profit is what it should be – so that I don’t have to do it myself by hand, and don’t have to remember how this Workbook was supposed to work later on.

Here is how this could look like in a F# script – create a Script file, say WorkbookTest.fsx, with the following code inside:

#r "Microsoft.Office.Interop.Excel"

open System
open Microsoft.Office.Interop.Excel

Console.WriteLine("Press [Enter] to start")

let excel = new ApplicationClass(Visible=false)
let workbooks = excel.Workbooks

let workbookPath = @"C:\Users\Mathias\Desktop\Model.xlsx"

let workbook = workbooks.Open(workbookPath)
let worksheets = workbook.Worksheets
let sheet = worksheets.["Finances"]
let worksheet = sheet :?> Worksheet

let revenueCell = worksheet.Range "Revenue"
revenueCell.Value2 <- 100

let costCell = worksheet.Range "Cost"
costCell.Value2 <- 10

let profitCell = worksheet.Range "Profit"
let profit = profitCell.Value2

Console.WriteLine("Check profit calculations")
Console.WriteLine("Expected: {0}, Actual {1}", 90, profit)

workbook.Close(false, false, Type.Missing)
excel.Quit()

Console.WriteLine("Done, press [Enter] to close")
Console.ReadLine()

The script launches Excel in Invisible mode, opens the workbook, sets the Revenue and Cost to 100 and 10, retrieves the value from Profit, printouts the value it found as well as the expected value – and closes back the Workbook without saving any of the changes.

The nice thing here is that I can now drop that file on my desktop, and simply right-click and select “Run with F# Interactive” to execute it, without building anything, and I’ll see something like this happen:

Nothing earth shattering, but still pretty nice: now I got a script which I can run anytime I want, to check whether the Workbook is behaving properly. Furthermore, what’s nice is that I don’t need to open Visual Studio to work with it: I can simply open WorkbookTest.fsx with Notepad, edit my code, and run it again.

There are some clear issues with the code in its current form. For instance, if anything goes wrong in the code (say, for instance, that I mis-typed a name which doesn’t exist with the workbook), the script will crash miserably, and let the hidden Excel instance hang in the background, waiting for someone to kill it manually. This would require some work to make sure that if exceptions are raised, everything is properly disposed, and no matter what, the file gets closed without saving any modification.

In any case, I thought it was worth sharing, even in its rough state – if only because it was fun, and also because the F# code looks surprisingly more appealing than the usual C# Interop code. Now the fun part would be to turn this into a decent testing framework for Excel…

13. February 2011 13:46

I have been a fan of Test-Driven Development for a long time; it has helped me write better code and keep my sanity more than once. However, until now, I haven’t really looked into Behavior-Driven Development, even though I have often heard it described as a natural next step from TDD. A recent piece in MSDN Magazine re-ignited my interest, and helped me figure out one point I had misunderstood, namely how BDD and TDD fit together, so I started looking into existing frameworks.

Most of them follow a similar approach: write in a plain-text file a description of the feature in Gherkin, a “feature description” language that is human readable, let the framework generate test stubs which map to the story, and progressively fill the stubs as the feature gets implemented.

I am probably (too) used to writing tests as code, but something about the idea of starting from text files just doesn’t feel right to me. I understand the appeal of Gherkin as a platform-independent specification language, and of letting the product owner write specifications – but the thought of having to maintain two sets of files (the features and the actual tests) worries me. I may warm up to it in due time, but in the meanwhile I came across StoryQ, a framework which felt much easier to adopt for me.

StoryQ is a tiny dll, which permits to write stories as tests in C#, using a fluent interface, with all the comfort and safety of strong typing and intellisense; Gherkin stories can be produced as an output of the tests, and a separate utility allows you to create code templates from Gherkin.

Rather than talk about it, let’s see a quick code example. I have a regular NUnit TestFixture with one Test, which represents a Story I am interested in: when I pay the check at the restaurant, I need to add a tip to the check. There are 2 scenarios I am interested in: when I am happy, I’ll tip a nice 20%, but when I am not, there will be zero tip. This is how it could look like in StoryQ:

using NUnit.Framework;
using StoryQ;

[TestFixture]
public class CalculateTip
{
[Test]
public void CalculatingTheTip()
{
new Story("Calculating the Tip")
.InOrderTo("Pay the check")
.AsA("Customer")

.WithScenario("Unhappy with service")
.Given(CheckTotalIs, 100d)
.When(IAmHappyWithService, false)
.Then(TipShouldBe, 0d)

.WithScenario("Happy with service")
.Given(CheckTotalIs, 100d)
.When(IAmHappyWithService, true)
.Then(TipShouldBe, 20d)

.Execute();
}

public double CheckTotal { get; set; }

public bool IsHappy { get; set; }

public void CheckTotalIs(double total)
{
this.CheckTotal = total;
}

public void IAmHappyWithService(bool isHappy)
{
this.IsHappy = isHappy;
}

public void TipShouldBe(double expectedTip)
{
var tip = TipCalculator.Tip(CheckTotal, IsHappy);
Assert.AreEqual(expectedTip, tip);
}
}

(The TipCalculator class is a simple class I implemented on the side).

This test can now be run just like any other NUnit test; when I ran this with ReSharper within Visual Studio, I immediately saw the output below. Pretty nice, I say.

## What I liked so far

• Painless transition for someone used to TDD. For someone like me, who is used to write unit tests within Visual Studio, this is completely straightforward. No new language to learn, a process pretty similar to what I am used to – a breeze.
• Completely smooth integration with NUnit and ReSharper: no plugin to install, no tweaks, it just worked.
• Fluent interface: the fluent interface provides guidance as you write the story, and hints at what steps are expected next.
• Passing arguments: I like the API for expressing the Given/When/Then steps. Passing arguments feels very natural.
• xml report: I have not played much with it yet, but there is an option to produce an xml file with the results of the tests, which should work well with a continuous integration server.

## What I didn’t like that much

• Execute: at some point I inadvertently deleted the .Execute() call at the end of the Story, and it took me a while to figure out why all my tests were passing, but no output was produced. More generally, I would have preferred something like Verify(), which seems clearer, but that’s nitpicking.
• Multiple scenarios in one test: once I figured out that I could chain multiple scenarios in one story, I was a happy camper, but all the examples I saw on the project page have one story / one scenario per test method. It’s only when I used the WPF story converter that I realized I could do this.
• Crash of the WPF converter: the converter is awesome – but the first time I ran it it crashed.

So where do I go from there? So far, I really enjoyed playing with StoryQ – enough that I want to give it a go on a real project. I expect that the path to getting comfortable with BDD will be similar to TDD: writing lots of tests, some of them fairly bad, until over time a certain feeling for what’s right or very wrong develops… In spite of my reservations, I am skeptical but curious (after all, I have been known to be wrong sometimes…), so I also plan to give SpecFlow a try.

5. April 2010 11:59

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]
{
[Test]
{
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
• write/command to Excel

More...

27. January 2010 18:26

Mark Needham recently published a series of posts around TDD, and one caught my attention. He is mocking a series of calls to a method SomeMethod() of a service IService, and doesn’t really care about the arguments, but:

For the sake of the test I only wanted 'service' to return a value of 'aValue' the first time it was called and then 'anotherValue' for any other calls after that.

His solution is to ditch his mocking framework (Rhino.Mocks, as far as I can tell) for that one test, and hand-roll his stub – and his example is a good case for why you might want to do that, sometimes.

However, this got me curious, and I wondered if this was indeed possible using Rhino. As recently as last week, I struggled with mocking repeat calls; but I had never actually considered a situation where one might want to mock a method, focusing only on the fact that the method is called, without paying attention to the specific arguments passed. Fun stuff.

After some digging into the documentation, I came across IgnoreArguments(), which seems to do the job:

public void SpecifyFirstReturnThenReturnSameThingForeverAfter()
{
var fakeService = MockRepository.GenerateStub<IService>();
fakeService.Expect(f => f.SomeMethod(null)).IgnoreArguments().Return("First").Repeat.Once();
fakeService.Expect(f => f.SomeMethod(null)).IgnoreArguments().Return("SecondAndAfter");

var first = fakeService.SomeMethod("ABC");
var second = fakeService.SomeMethod("DEF");
var third = fakeService.SomeMethod( "GHI" );

Assert.AreEqual("First", first);
Assert.AreEqual("SecondAndAfter", second);
Assert.AreEqual("SecondAndAfter", third);
}

IgnoreArguments() seems to be a potentially convenient way to make some tests lighter. That being said, arguably, the setup here is cumbersome, and the hand-rolled version is clearer: when you reach the point where you wonder if your mock is doing what you think it should, you enter perilous territory…

9. January 2010 08:09

I will be presenting “For Those About to Mock” on Tuesday, January 19th at the North Bay .Net user group in Sebastopol – and I am very excited about it, because it is my first time in this user group, and because I really enjoy the topic. As a long-time fan of TDD, I came across mocking a long time ago, but it took me a while to incorporate it in my daily code, not really because it’s complicated, but rather, because I didn’t quite understand what issue mocking frameworks were solving. Also, explanations on the topic are often fairly intimidating.

My goal will be to spare you the misunderstandings I went through, so that you understand where mocking can help you, and how to use frameworks like Rhino.Mocks and Moq in your own code. Hope to see you there, and if you have specific requests or questions you would want me to address, please let me know in the comments!

#### Need help with F#?

The premier team for
F# training & consulting.