Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
1. December 2012 13:55

I have been obsessing about the following idea lately – what if I could run a FSI session from within Excel? The motivation behind this is double. First, one thing Excel is good at is creating and formatting charts. If I could use F# for data manipulation, and Excel for data visualization, I would be a happy camper. Then, I think F# via FSI could provide an interesting alternative for Excel automation. I’d much rather leverage existing .NET libraries to, say, grab data from the internet, than write some VBA to do that – and the ability to write live code in FSI would be less heavy handed that VSTO automation, and closer to what people typically do in Excel, that is, explore data. Having the ability to execute F# scripts would be, at least for me, very useful.

Seeing Tim Robinson’s awesome job with FsNotebook.net kicked me out of procrastination. Even though FsNotebook is still in early development, it provides a very nice user experience – on the web. If something that nice can be done on the web, it should be feasible on a local machine.

As an aside, Tim is looking for feedback and input on FsNotebook – go try it out, it’s really fun:

Anyways – this is the grand plan, now we need to start with baby steps. If I want to embed FSI in Excel (presumably via a VSTO add-in), I need a way to talk to FSI from .NET, so that I can create a Session and send arbitrary strings of code to be evaluated.

As usual, StackOverflow provided two good starting points (this answer, and this answer) – so I set out to look into the Process class, which I didn’t know much about, and attempted to spawn a FSI.EXE process, redirecting input and output. Turns out it’s not overly complicated – here are the 34 lines of code I ended up with so far (see it on GitHub):

namespace ClearLines.FsiRunner

open System.Diagnostics

type public FsiSession(fsiPath: string) =

let info = new ProcessStartInfo()
let fsiProcess = new Process()

do
info.RedirectStandardInput <- true
info.RedirectStandardOutput <- true
info.UseShellExecute <- false
info.CreateNoWindow <- true
info.FileName <- fsiPath

fsiProcess.StartInfo <- info

[<CLIEvent>]

[<CLIEvent>]

member this.Start() =
fsiProcess.Start()

fsiProcess.StandardInput.WriteLine(line)

member this.Evaluate() =
fsiProcess.StandardInput.Flush()


This is a fairly straightforward class. The constructor expects the path to FSI.EXE, and sets up the process in the constructor (the do block) to run headless and redirect the stream of inputs and outputs. Start() simply starts the process, and begins reading asynchronously the output of FSI, AddLine(line) is used to add an arbitrary string of F# code, and Evaluate() sends all lines currently buffered to FSI for evaluation – and flushes the buffer. The 2 events OutputReceived and ErrorReceived are provided for the client to listen to the FSI results.

More...

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 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!

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.

Tortoise integrates your Version Control system right into Windows.

27. November 2011 07:59

Earlier this month, on Simon Murphy’s blog, the project to organize a UK Excel Developer conference 2012 in London quickly took shape. As of now, the planned date is in the January 24 to 26 range, with a pretty cool agenda, covering most of the recent developments Excel developers should know about. I am looking forward to it!

I’ll be talking for about an hour on VSTO (Visual Studio Tools for Office). VSTO can be both awesome and painful: I plan on demoing building an Excel add-in from the ground up, illustrating some of the benefits and drawbacks/pitfalls of that approach, so that you know when it’s the right time to go that route.

If there are any topics or questions you are specifically interested in, please let me know in the comments – I’ll be happy to take requests.

As an aside, I usually talk at .NET developers events, where I need to convince the audience that developing for Excel (or Office) isn’t a terrible idea. This will be my first time with an Excel developers audience, and I expect the opposite challenge, namely why bother with C#/VB.NET and Visual Studio when VBA is free and works just fine?

Check out the Excel Conference page and Simon’s blog for updates.

Picture from the amazing series “The Kitten Covers

7. September 2010 16:56

In my previous posts, I explored how to identify text in a PowerPoint slide and use the Google .NET API to translate it; let’s put it all together in a simple VSTO add-in for PowerPoint 2007.

The translation functionality is displayed in a Custom Task Pane, where you can pick the language of origin, and the language to translate to. I used the same general design I presented in my Excel add-in tutorial, using WPF controls in the task pane with the MVVM pattern, leveraging the small yet very useful MVVM foundation framework. When running, this is how the add-in looks like:

I added only 3 languages in there, but it is fairly easy to modify the code and get it to work with any language pair supported by Google translate.