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

Sometimes, you need to know when your user decided to move to another Worksheet in Excel. Fortunately, Excel exposes some events for this. At the workbook level, Workbook.SheetActivate and Workbook.SheetDeactivate are fired when the user activates or deactivates a sheet in the Workbook, and at the application level, Application.WorkbookActivate and Application.WorkbookDeactivate are triggered when the user changes Workbooks.

This looks all nice and simple, except that there is a small catch, which caused me a bit of grief on my current project. I naively thought that when a user activated a new Workbook, it would fire WorkbookActivate, and SheetActivate. Wrong – when you activate a new Workbook, only WorkbookActivate is triggered.

The following VSTO code illustrates the point: Excel traps when a new Workbook is added, and begins tracking the Sheet activation/deactivation for that new Workbook.

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
var excel = this.Application;
excel.WorkbookActivate += WorkbookActivated;
excel.WorkbookDeactivate += WorkbookDeactivated;
}

{
workbook.SheetActivate += SheetActivated;
workbook.SheetDeactivate += SheetDeactivated;
}

private void WorkbookActivated(Excel.Workbook workbook)
{
MessageBox.Show("Workbook activated.");
}

private void WorkbookDeactivated(Excel.Workbook workbook)
{
MessageBox.Show("Workbook deactivated.");
}

private void SheetActivated(object sheet)
{
MessageBox.Show("Sheet activated.");
}

private void SheetDeactivated(object sheet)
{
MessageBox.Show("Sheet deactivated.");
}

If you run this code, you will note that when you change sheets within a Workbook, the Message Box “Sheet deactivated” pops up, followed by “Sheet activated”. However, if you add multiple workbooks, and start changing workbooks, only “Workbook activated” / “Workbook deactivated” shows up.

The morale of the story is that if you are interested in tracking when a user changed the active worksheet across workbooks, you can’t simply rely on SheetActivated: you will need to look out for Workbook level events, and when these occur, figure out through the Workbook active worksheet which sheet has been activated or deactivated.

I think the reason this caught me off-guard is that I had this Worksheet-centric mental image of Excel: when I am changing workbooks, my goal is to select a Worksheet in that Workbook, the Workbook is simply a means to an end – and I expected the events to reflect that. However, if you consider the Workbook as its own isolated entity, it makes sense: when I leave a Workbook, it simply becomes invisible, but otherwise nothing changed: the Worksheet that is active remains active, and will still be active when I come back later.

The other interesting pitfall is that when you start Excel, there is a Workbook active – but because it is created before you can begin trapping events, you have to register it manually if you want to track its behavior as well.

22. November 2010 17:54

One of the reasons I like .NET extension methods is that they provide a nice way to work with existing libraries, and tweak the public API to create custom methods and extend existing objects without modifying them. For instance, I regularly end up creating a few when working with the Office interop. Imagine for instance that you had an Excel project where you wanted to apply a consistent format to some ranges; you could write an extension method like this one:

public static void ApplyStandardFormat(this Range range)
{
range.Font.Bold = true;
range.Font.Color = ColorTranslator.ToOle(System.Drawing.Color.White);

range.Interior.Pattern = XlPattern.xlPatternSolid;
range.Interior.Color = ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
}

The nice thing here is that because of the addition of the this keyword in the signature “this Range range”, you can now use this method as if it was naturally exposed by the Range object, like this:

myRange.ApplyStandardFormat();

Arguable, this isn’t the greatest example, and doesn’t necessarily warrant an extension, but you get the idea.

More...

14. November 2010 18:06

I ran into something odd in Excel in my current project; it seems a lot like a bug to me, but that might also be simply that I know less about Excel than I think I do. Anyways, I have reproduced the issue with a simpler scenario.

The problem goes along these lines: in a spreadsheet, I create headers for data, and write out a few records below through code. Everything works fine – even if I hide a column, data gets properly written to the worksheet.

When I add a Filter to the header row, everything still goes great. But when I filter out some of the values in the filter of one of the columns and hide that column, then the code just doesn’t write any values in that column.

Let’s illustrate: here is some quick and dirty VBA code that generates rows of random numbers, and writes them to a sheet, starting in row 2 (my original code was C# / VSTO, but I figured it would be smart to try out VBA as well):

Dim CurrentRow As Integer

Public Sub WriteData()
If CurrentRow = 0 Then CurrentRow = 2
Dim data(1 To 3) As Integer
data(1) = Int(Rnd * 5)
data(2) = Int(Rnd * 5)
data(3) = Int(Rnd * 5)
Dim sheet As Worksheet
Set sheet = ActiveSheet
Dim startCell As Range
Set startCell = sheet.Cells(CurrentRow, 1)
Dim endCell As Range
Set endCell = sheet.Cells(CurrentRow, 3)
Dim targetRange As Range
Set targetRange = sheet.Range(startCell, endCell)
targetRange.Value2 = data
CurrentRow = CurrentRow + 1
End Sub

If I run the macro WriteData on a sheet, I’ll see something like this:

More...

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.

More...