Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 19. February 2012 15:37

This post is part of a series providing commentary on the VSTO Stocks project. I initially developed it for theExcel Developers Conference in London, to illustrate some of the benefits or interesting features of VSTO add-ins compared to traditional VBA automation. The add-in is a work in progress, and is by no means production ready, but it is functional; I will update the code and add comments over time. Feel free to ask questions in the comments!

Level: intermediate.

Code version: 39134382823e 

The straightforward way to organize an Add-In solution is to just create a single project of type Excel 2007 Add-In (or whichever Office application / version you may target). So why would I go through the pain of structuring my Solution into four distinct Projects in the VSTO Stocks add-in?

Having a single project is a perfectly valid way to proceed, with things kept simple and tight. However, a drawback of going that route is that it makes it easy to write untestable code, with poor separation of concerns.

Note: in the past, I also ran into testing issues with one single project, because I couldn’t reference the Add-In project itself in my automated tests suite, which was problematic. This problem seems to be gone now.

Specifically, one source of such problems is to directly reference either ThisAddIn or Globals classes to access the Excel object model. They behave more or less as static classes would, which is a testing nightmare. For instance, in order to access the Active worksheet in a method, I could write code like this:

public void DoSomeStuff()
   var addIn = Globals.ThisAddIn;
   var excel = addIn.Application;         
   var workbook = excel.ActiveWorkbook;
   var activeSheet = workbook.ActiveSheet;
   // do stuff with the sheet

As is, this method is virtually untestable: we do not control the instantiation of Globals, and cannot replace it with fakes to emulate various situations that could arise in the application. What if, for instance, we wanted to test the behavior of that function when no worksheet is active?

That problem would be avoided altogether if instead of accessing Excel through the Add-In, we injected a reference to Excel in the method:

public void DoSomeStuff(Application excel)
   var workbook = excel.ActiveWorkbook;
   var activeSheet = workbook.ActiveSheet;
   // do stuff with the sheet

Now we are free to substitute Application with our own version, set it up to emulate whatever scenario we see fit, and test what the behavior of the method should be in that case. I’ll revisit that question later when discussing automated tests, and leave it at that for now. For that matter, note also that we could perfectly well re-use that method anywhere, as there is no dependency on the add-in.

By separating into multiple projects, we can enforce that rule. In our Solution, the add-in acts purely as a bootstrapper: its only role is to kick things off when Excel starts, and initialize the UserInterface project, where the core of the application logic resides.

Note: I may rename the UserInterface project to ExcelApplication later on, the name would be more fitting.

The UserInterface project has a reference to Microsoft.Office.Interop.Excel, but not the Add-In, and therefore cannot use Globals or ThisAddin. In the ThisAddIn_Startup method, the running instance of Excel is retrieved and passed to the TaskPaneBuilder in the UserInterface project. From that point on, the Add-In itself doesn’t do anything: the ExcelController in the UserInterface project holds a reference to Excel, and handles all the interactions with Excel going forward.

So what is the Domain project about, then?

This one may or may not be a luxury item, depending on your project. The intent of the Domain project is to represent the business model you are dealing with; it should have no knowledge of Excel (hence the lack of reference to the Excel interop). For instance, StockHistory or MovingAverage are concepts which exist and can be modeled independently of Excel. Our add-in is currently using Excel as a client, but nothing would preclude developing a web-based version later, for instance. In that case, having a separate library which contains the Domain would prove very valuable, because it could be used as-is: the “only” effort required would be to write a different client / user interface to interact with that Domain model.

The name of the fourth project, UnitTests, should give away its purpose: it hosts the automated tests that verify certain behavior of the application. We’ll revisit it later on.

Of course, you don’t have to follow that organization - I also write add-ins which live in one single project! The right organization for your solution depends on each individual project, their complexity, and how they may grow over time. The structure/ideas I describe here are on the heavy side, but have served me well over the years - I hope they will help you think about your projects as well!

by Mathias 17. February 2012 06:27
I finally finished “Working effectively with legacy code”, reading it a few pages at a time every morning on my way to work. Legacy code is one of these topics you know are important, but which you never really want to hear about, so the book has stayed on the backlog for a while. Recently, I helped out someone establish tests on a legacy code base, and began following Michael Feather’s tweets with great enjoyment, and decided it was time to read it.

Who should read it?

The developer who is already familiar with unit testing, comfortable with his language, object-oriented concepts, and what makes code maintainable - and wants to expand his thoughts and tools on testing and testability.

3 things I liked about it

  • The chapter titles are awesome – just like good naming is a hallmark of Clean Code, the chapter titles convey very clearly what the intent is. “I need to change a Monster method and I can’t write tests for it”, “It takes forever to make a change”, “How do I know that I am not breaking anything”, “I am changing the same code all over the place” – they all evoke situations we have been through one time or another, and the corresponding chapters do address these questions head-on.
  • Clear concepts and vocabulary: if anything, the one sentence that will stay with me is “legacy code is simply code without tests”, a wonderfully clear and opinionated definition, which not everyone may agree with. Feathers defines a few concepts (like a Seam or a Pinch Point), which provide a helpful language to think and and discuss legacy code.
  • Multiple languages: I write primarily in C# and F#, so in principle, learning about specific issues of testing legacy C code isn’t high on my concerns list. Still, I found that going through examples in languages I am not familiar with was interesting, in that it provided both a broader perspective on testing and on the relative strengths and weaknesses of various languages. It also made me think of techniques I seldom (if ever) use in C#, like pre-processor directives.

3 things I didn’t like that much

  • Multiple languages: covering multiple languages provides a broader perspective, but it also comes at the expense of each individual language. If you are specifically interested in, say, C#-specific techniques, this book may disappoint you - it is fairly general.
  • A bit dated: for a book published in 2004, it aged remarkably well. Still, 8 years is a long time in computer-years. From a C# developer perspective, there have been a few major releases of both the language and the IDE, with implications on testing and refactoring. I would assume (hope) that today, most language/IDEs do support refactorings like Extract Method. On the language side, the book touches on using function pointers to achieve decoupling, but the context is mostly C. With the emergence of functional concepts (Func<T> in modern C# for instance), I think this would warrant a bigger discussion today.
  • A somewhat tedious read: this book is not exactly a page-turner. Reading legacy code examples (a good part of them probably not in a language you are comfortable with, unless you are a polyglot) and figuring out mechanical steps to disentangle it isn’t material that will be turned into a Hollywood movie any time soon.

Parting thoughts

I really enjoyed this book, but I would recommend it with an asterisk. Depending on how you want to look at it, a polyglot book will either lose specificity, or gain generality. Personally, I think in this case, the gain in generality easily compensates for the lack of depth in each individual language. Yes, I would like a C#-specific book which points to useful, up-to-date tools – but that book would be obsolete in 2 years at best. By covering a variety of languages, Feathers illustrates very different solutions or ideas, and because he uses only fairly simple features in each language, the ideas remain easy to understand and convert into other “coding dialects”.

My personal bent is for concepts and language, because they last longer than recipes and tools, which is why I really enjoyed this book: it helped me create / articulate a mental map. I don’t have many computer books published in 2004 that I read for insight, today – and this one feels like one of these “timeless classics”.

That being said, I think it takes a certain experience with unit testing and code maintenance to appreciate the book, and I wouldn’t recommend it to someone who is just starting with tests and wants to find quick solutions to their problems. It may work (the book is very clear on steps and methodology), but I suspect it may be potentially frustrating.

Totally unrelated note: this is the first technical book I read on Kindle, and I have mixed feelings about it. I was hoping that the Kindle could serve as a portable library for all these massive technical bricks. On one hand, it’s nice to have the possibility to carry around searchable books; on the other hand, clearly, it’s not the best way to read through code samples, where good old paper still has an edge.

by Mathias 6. February 2012 04:24

This post is part of a series providing commentary on the VSTO Stocks project. I initially developed it for the Excel Developers Conference in London, to illustrate some of the benefits or interesting features of VSTO add-ins compared to traditional VBA automation. The add-in is a work in progress, and is by no means production ready, but it is functional; I will update the code and add comments over time. Feel free to ask questions in the comments!

Level: beginner.

Code version: 346c1bd9394e

One of the key benefits I find in using VSTO for Office automation instead of VBA is that it enables using Source Control tools.

During a development effort, regardless of the technology used, lots of things can go wrong. A code change which initially looked like a great idea progressively degenerates into chaos, something goes awry with a file which becomes irrecoverably corrupt, a hard drive suddenly decides it is time to call it quits – all these happen. When they do, it’s nice to have a safety net, and know that somewhere, safe and warm, a snapshot of the code taken in happier times is waiting and can be restored, giving you a safe point to restart from, with only a few hours of work lost.

What I have often seen done with Excel development goes along these lines: on a regular basis, the developer saves the workbook somewhere “safe” with a time-stamp convention, like “MyWorkbook-2010-12-24.xlsx”.

On the plus side, this is a very lightweight process, which addresses some of the issues. At the same time, it is cumbersome: the developer needs to be diligent, the process is manual and error-prone (messing up the timestamp, or accidentally over-writing archives is very possible), and recovering the right version from a folder that contains multiple versions only identified by a timestamp is impractical.

Developers working in other ecosystems have been facing the same issue, and address it with specialized tools: source control systems. In a nutshell, the idea of source control is to operate like a library: the source code is stored in a “vault” (known as the Repository), developers check out a local copy of the current version on their machine, edit it, and check in/commit the modified code back into the vault if they are happy with the result.

Put differently, whatever code is currently being modified on the developer’s machine is “scratch paper”; it become “real” only once it is committed.

There are a few obvious benefits. The entire history of the project is saved for posterity, and its state at any point in time can be instantly restored. The system generates timestamps automatically, and each commit has comments attached to it, which helps navigation between versions. This encourages experimenting with code ideas: check out the code, spike something – if it works, great, if not, discard it and revert to the previous repository version.

[pic of repo on CodePlex]


Overview of the project history with Mercurial + Tortoise on Windows

More interestingly, version control systems typically store the difference between the current version and the previous one, and not the file itself. Besides keeping the size of the repository minimal, it also allows to produce “diffs”, i.e. code differences: the source control system can easily produce a view that highlights all the differences between two versions of the code, which is invaluable.


The “Diff” highlights what has been added or removed between versions.


Diff view of the changes to a specific file on CodePlex

Why hasn’t the traditional Excel developer community embraced source control?

The main reason, in my opinion, is that Source Control systems are at their best when dealing with text files. While this is the case for most development platforms, Excel is peculiar in that aspect: the code is embedded in the Workbook, in multiple forms (Excel formulas, code-behind worksheets, macro modules…), and the overall project isn’t a collection of text files containing code. Up to version 2003, workbooks were saved as a proprietary binary file, which couldn’t be used to produce meaningful "differences” – and the Open XML format adopted since version 2007 still isn’t very practical for differentiation purposes.

By contrast, a VSTO add-in like VSTO Stocks consists entirely of .NET code, which is ultimately a collection of text files – there is nothing attached to a specific Workbook. As a result, it is a perfect fit for Source Control, with automatic archival of successive versions, and highly detailed “audit” of changes between versions.

Note that nothing prevents using Source Control tools for “classic” Excel development – I do it all the time, even when working with Excel 2003 workbooks. You won’t get the full benefits of source control (no diffs), but you will still get a history of all the code changes in the project. Also, if you tend to re-use VBA utilities like UDFs, .bas files are perfect candidates for source control: store the utilities .bas files in a repository, and import them in workbooks when you need them.

How to get started with Souce Control?

A nice thing about Source Control tools is that some of the best and most widely used systems are open source and totally free. The two systems I use are Subversion (for the past 7 years or so) and Mercurial since a few months – they are both great. The other name that comes up a lot is Git, which as far as I know is very similar to Mercurial.

The main difference is that Subversion has a centralized model (there is a central “source of truth” where the official code resides, and where all changes get committed), whereas Mercurial is a distributed system (developers can work with the full  benefits of version control even disconnected from the “central”, and can merge their work with any other clone of the repository). Both are worth looking into, and offer different advantages. There are plenty of discussions comparing the two approaches, so I won’t go further into it.

On the other hand, regardless of what system you pick, I recommend installing Tortoise (TortoiseHg for Mercurial, TortoiseSVN for Subversion); it’s an extension which integrates source control with Windows, so that you can manage your repositories directly via the graphical user interface. It’s a great way to start without having to struggle with arcane command-line tools.

TortoiseHg in action

Tortoise integrates your Version Control system right into Windows.


Comment RSS