Your best friend, "record macro"?

In my previous post, I described how to programmatically add an embedded chart to an Excel worksheet, and what issues I encountered in the process. I was not very happy with the solution, and figured out a much better way to do this, inspired largely by this. Rather than add a chart to the workbook, and then set its location, it is possible to directly add the chart to a worksheet, by using the following syntax:

public static Excel.Chart AddEmbeddedChart(Excel.Worksheet worksheet, 
Excel.XlChartType chartType, Excel.Range dataRange, Excel.XlRowCol byRowOrCol, 
string title, double left, double top, double width, double height)
{
    Excel.ChartObjects chartObjects = (Excel.ChartObjects)(worksheet.ChartObjects(Missing.Value));
    Excel.ChartObject chartObject = chartObjects.Add(left, top, width, height);
    Excel.Chart embeddedChart = chartObject.Chart;
    embeddedChart.ChartType = chartType;
    embeddedChart.SetSourceData(dataRange, byRowOrCol);

    embeddedChart.HasTitle = true;
    embeddedChart.ChartTitle.Text = title;

    return embeddedChart;
}

Instead of creating a chart sheet first, and then re-locating (a copy of) the chart into the target worksheet, this version directly creates the chart in the right place. The cherry on the cake: you can set the location and size of the chart immediately, instead of having to perform acrobatics to retrieve the chart in the sheet…

So why did I go the wrong path first? Because of my best but not always reliable friend, the Macro Recorder.

When writing some C# automation for Excel, rather than trying to figure out beforehand the right properties and method names to use in the Excel object model, I usually first launch the macro recorder and do manually something similar to what I want the automation to perform, and scan the VBA code produced by the recorder. This is a great way to get a quick sense for one way to get the desired results, and what objects are involved.

There are two issues with that approach. First, figuring out which classes should be used in the C# equivalent code can be tricky, because the VBA code is generally not very explicit, and because most things are untyped in the Interop, intellisense isn’t of much help either. And then, of course, the VBA code itself isn’t always top quality, either - which is exactly what happened to me. This is the code generated by the Macro Recorder on Excel 2003:

Range("B2:D3").Select
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B2:D3"), PlotBy:= _
    xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" 

Note that it does exactly what I described yesterday: add the chart, then change its location. Which is a pretty bizarre approach, if you ask me: why have a default setting which adds charts as embedded to a worksheet, and then record a macro as if the default was to add a chart in its own sheet? I was also pretty surprised to see that the code generated was different when I used Excel 2007

Range("B3:D4").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$B$3:$D$4")
ActiveChart.ChartType = xlPie 

This code is actually much better, in that it directly adds the chart to the shapes of a worksheet, instead of the odd two-step process of Excel 2003. On the other hand, when inspecting Worksheet.Shapes in C#, there is no AddChart property or method - so what should you use? In the end, Google gave me the ChartObjects class, which I believe is the correct collection to work off, because it is the “most typed” version.

The moral of this story? The Excel VBA macro recorder is your friend, and something I really miss in other office applications (If you have tried to write some automation for PowerPoint, you know what I am talking about) - but… take its word with a big grain of salt, check your favorite search engine, too, and maybe record the macro in both Excel 2003 and Excel 2007!