Excel Array Formulas

I just found a feature of Excel that I’ve never heard of before: Array Formulas!

Essentially, you can use ranges in criteria of functions such as COUNTIF. Full details available: http://www.cpearson.com/excel/ArrayFormulas.aspx

I used it to find the number of distinct values in a column (http://www.cpearson.com/excel/Duplicates.aspx).

All you need in the formula is:


The curly braces around the formula make this an array formula.

I reckon I could do some pretty fancy processing using this in the future.. Not sure what though.


The ride that didn’t happen


Not really fair to leave her feeling like death looking after 2 kids and having to pack and drive them to Bristol. Oh well, there’ll be next time.





Taking the long way home

After having to commute by car all week, I thought it would be good to take the long way home for my one day of cycling. This is the longest long way home that I’ve taken and was rather long for a commute, but I might to it again sometime.

Had some terror locking the back wheel on a slimy lane when there was a large truck coming the other way. Do other people have trouble locking the rear wheel very easily?

The stats:


Developing (and deploying) Excel Addins in C#

I recently had the “joy” of developing an Excel plugin. This pulled data off one worksheet, sent it off to a web service, wrote some values back to multiple worksheets and created a report that was a worksheet designed to be printed on A4. I know Excel is the least hip development thing at the moment, but let’s face it, it’s used by everyone in business for everything (even though it shouldn’t), so it makes sense being able to develop for it properly. Also, plugins let us turn Excel into a cloud enabled application which let’s us do cloud through the “back door”. Now that’s cool.

While doing this, I learned a load of good practices and ways to avoid potential pitfalls. Also, all the information I needed was scattered across the web, so I though this would be a good place to bring all the strands together, as well as share tips and tricks if ever you have the same “joy” imposed on you.

Getting Started

Visual Studio 2008 and 2010 make getting started very easy. Just create the correct project type. This seems like a useful resource for getting started: http://msdn.microsoft.com/en-us/office/hh128771.aspx

A good starting point for playing with functionality is to create a “ribbon” (add / new item / office) and stick some buttons on it. You can then double click these buttons to add on-click events (al-la Windows forms). From that point you’re free to mess about.

One thing to mention that isn’t initially clear is that the moment you compile your add-in, when you start Excel, your add-in will start too (even when you don’t start debugging). Still not entirely sure how this is done. Note that if you change code and re-compile you have to fully re-start Excel (not jut close and re-open the workbook) before it picks up the changes. That means closing all instances of Excel and re-opening.

Anything you actually do with Excel will be via the COM interop library (Microsoft.Office.Interop.Excel) and will all seem remarkably untyped in the C# auto completion. You’ll be asked to pass in and receive “objects” back from most functions. Don’t be fooled into thinking this is some easy going scripting type scenario though, exact types are required, which can only be found through (rather sparse) examples.

Reading and writing to cells / rows / ranges

The easiest way to get data from and insert data into cells is to use the get_Range() function, then to use it’s “Value2” property (no, I don’t know why it’s 2).

For example, to get and set the value in cell “A1”, use the following call (don’t worry about the Excel prefix, that’s a shortened namespace that’s added automatically when you’re developing add-ins):

Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
object cellValue = activeWorksheet.get_Range("A1", System.Type.Missing).Value2;
activeWorksheet.get_Range("A1", System.Type.Missing).Value2 = "My new value";

To get the value of the cell as a string (which it usually is), stick a ToString() on the end of the Value2. But be careful in this, if the cell is blank, Value2 will be null, so ToString() will blow up. Also, note, that because I’m picking a single cell, I pass “System.Type.Missing” as the second parameter. I came unstuck a few times, passing in null before I discovered this.

You’re not limited to selecting a single cell, you can select a range (as the name implies). Value2 get’s abit more tricky when you do this. Value2 can be a 2 dimensional array of objects when you get it, and all cells in the range will be set to Value2. This example gets the square of cells A1-B2, then writes B2 to all of A3-B4 (it’s worth mentioning at this point that Excel is generally 1 indexed, rather than 0):

Excel.Range range1 = activeWorksheet.get_Range("A1", "B2");
object[,] s = (object[,]) range1.Value2;
Excel.Range range2 = activeWorksheet.get_Range("A3", "B4");
range2.Value2 = (string)s[1, 2];

If you then want to tweak individual characters in cells, you can call get_Characters() on a range, for example, to make the first character of all the cells A3-B4 to bold (italics, underline etc are all accessible this way):

range2.get_Characters(1, 1).Font.Bold = true;

To get whole rows and columns (as ranges) use the following:

(Excel.Range)reportWorksheet.Columns["A", System.Type.Missing]
(Excel.Range)reportWorksheet.Rows[0, System.Type.Missing]

Notice that Rows are zero indexed, just to confuse things. Lastly, to get more formatting options, use the Style attribute on you’re range, for example, setting horizontal alignment in a range:

((Excel.Style)(range1.Style).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

The style should let you get at fonts, colours and pretty much anything else you’d want.

All that should get you started. To add formulas, just assign a string with an =’s in front of it to Value2 (like you would typing into a cell in Excel), simple.

Using images from project resources

First thing you’ll be asked to do, is have some sort of image being placed in your worksheet. This will have to come from the resources of your project, not somewhere on the client machines disk. Annoyingly, Excel’s functions don’t support this, so you could waste a lot of time figuring out this trick.

The idea is to place the image from the project resources into a temporary location, then read that into Excel. The CreateTemplateImage() function takes a passed in (System.Drawing) image saves it to the disk and passes back the path of the image ready for Excel to use. I’ve also created a singleton MyLogo, that does this writing to disk and stores the path so it doesn’t have to do it again. Finally, I use the MyLogo property to pass into the Excel function that creates the image on the worksheet:

private static string CreateTempImage(System.Drawing.Bitmap image, System.Drawing.Imaging.ImageFormat format, string fileName)
    //create temp file path
    StringBuilder path = new StringBuilder(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData));
    path.Append("\\" + fileName);
    //save resource into temp location in HD
    image.Save(path.ToString(), format);
    return path.ToString();

private static string myLogoPath = null;
public static string MyLogoPath
        if (myLogoPath == null)
            myLogoPath = CreateTempImage(Properties.Resources.myLogo, System.Drawing.Imaging.ImageFormat.Png, "myLogo.png");
        return myLogoPath;

activeWorksheet.Shapes.AddPicture(MyLogoPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 1, 1, 100, 100);

This way, you can monkey about with the images to your hearts content and the main code just uses the property.

Placing images and charts

When you place images and charts (a full listing of a customised chart is available in Appendix 1), you have to supply: top; left; height and width, as you would expect. However, the values you supply aren’t rows and columns, they aren’t pixels, they’re some other whacky measure I still don’t understand (pixels * 0.7 or so). To save myself having to worry about this and having to re-jig all my values when I entered a new row or piece of text, I tied my image and chart sizes and locations to ranges. All I have to do now is say that an image is going to fill a certain range (with a margin perhaps) and then I can play with that range on the worksheet.

I start with a class that holds the definition of a rectangle as floats:

public class FloatRect
    public float Top { get; set; }
    public float Left { get; set; }
    public float Height { get; set; }
    public float Width { get; set; }

    public FloatRect(double top, double left, double height, double width)
    	Top = (float) top;
    	Left = (float) left;
    	Height = (float) height;
    	Width = (float) width;

To populate this I use the following function that takes a worksheet, the “top left” and “bottom right” definitions of a range and an optional margin parameter (null for none) .

private static FloatRect GetRangeBoundaries(Excel.Worksheet worksheet, object rangeTopLeft, object rangeBottomRight, float? margin)
    Excel.Range range = worksheet.get_Range(rangeTopLeft, rangeBottomRight);

    FloatRect rect = new FloatRect((double) range.Top,
                                   (double) range.Left,
                                   (double) range.Height,
                                   (double) range.Width);

    if (margin.HasValue)
        rect.Top += margin.Value;
        rect.Left += margin.Value;
        rect.Height -= (margin.Value * 2);
        rect.Width -= (margin.Value * 2);

     return rect;

With the image path trick used above, I can make the following two calls to place the MyLogo image in cells A1-B2 with a small margin:

FloatRect imageArea = GetRangeBoundaries(reportWorksheet, "A1", "B2", 4);
activetWorksheet.Shapes.AddPicture(MyLogoPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue,
                                   imageArea.Left, imageArea.Top, imageArea.Width, imageArea.Height);


The order in which you construct a worksheet is vital to avoid being driven bonkers by things re-sizing randomly. From experience, use the following order:

  • Worksheet content
  • Size columns in reverse order
  • Place images and charts

Modifying portions of text

One of the requirements I had was that portions of text had to be underlined in certain cells. The first time I did this, I just counted the number of characters for use in the offset of get_Characters(). I did this again the first few times the client changed the text and what was underlined, then I got sick of it and worked out a better way. I used regular expressions to match and perform operations on text. The example below is to underline text, but it would be very simple to generalise this and to have it act on a range, rather than just one cell.

private static void UnderlineText(Excel.Worksheet worksheet, object cell, string toUnderline)
    Excel.Range range = worksheet.get_Range(cell, System.Type.Missing);

    if (range.Value2 != null)
        string strValue = range.Value2.ToString();

        Regex regex = new Regex(@toUnderline);
        MatchCollection matches = regex.Matches(strValue);

        foreach (Match match in matches)
            // + 1 because characters are 1 indexed
            range.get_Characters(match.Index + 1, toUnderline.Length).Font.Underline = true;


I didn’t use events much, but I did come across an annoying ambiguity in names that took me a while to resolve. Basically, “Calculate” is a method on a worksheet and also a delegate. The delegate is kicked off when a worksheet is forced to re-calculate a formula (for example when one of the input values is changed. Anyway, in the end I had to cast the worksheet to access the delegate when assigning a new one in what seemed a most un-natural way:

((Microsoft.Office.Interop.Excel.DocEvents_Event)dataWorksheet).Calculate += new Microsoft.Office.Interop.Excel.DocEvents_CalculateEventHandler(MyProjectRibbon_Calculate);

with the associated handler:

void KnowledgeBaseRibbon_Calculate() 

Creating a setup project

There is an ability to do some sort of one click publishing of AddIns, but almost inevitably in an organisation there’ll be some reason why you have to produce an MSI and setup package that also install all pre-requisites. This section pulls together a whole bunch of articles and solutions that should actually let you do this without p***ing away days.

Background Information

A background of how to create a setup project for an Excel plugin can be found here:


Getting information on plugin errors

If (and when) the Excel plugin fails to load (ie your new menus etc do not appear), try the following in order to get more detailed information.

Open a command prompt and type:

“c:\Program Files\Microsoft Office\Office12\Excel.exe”

This should popup error dialogs with details for failing plugins.

Setup project pre-requisites

All prerequisites for the setup project may not exist initially. To see the pre-requisites, right click on the setup project and click “properties”, then click the “prerequisites” button. Some of the pre-requisites may have an exclamation mark next to them meaning that these “bootstrapper” packages are not on your machine.

Some are common that can be copied from other peoples/build machine (find & copy them from/to C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages), but VSTOR30SP1 is a custom bootstrapper package that was created to deliver the msi located at: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=1132

To create a bootstrapper package around this file, we had to use the Bootstrapper Maifest Generator (http://archive.msdn.microsoft.com/bmg). The setup file used for this (VSTORuntime.3.0.SP1.VSBootstrapperManifes) has been placed in Source Safe under the CatoAddIn root directory. When you build this file using the BMG, a new folder will appear in your bootstrapper packages directory (C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages). Once the package is there, it should also appear in Visual Studio (restart VS?).

Other issues

These are other issues that I encountered while creating the deployment project that might also help.

To get the plugin working with Excel, the setup project will add the following registry key containing values: HKCU\Software\Microsoft\Office\Excel\Addins\Atkins.MyAddIn

It is important that the name is Atkins.MyAddIn, not just MyAddIn, because in the latter case the “Manifest” registry entry is automatically overwritten when the project is built (lost the URL for this problem).

The “Manifest” must contain the file:/// prefix (http://blogs.msdn.com/b/vsod/archive/2011/06/14/vsto-4-0-sp1-will-cause-a-vsto-addin-to-not-find-its-config-file.aspx), only settings embedded in the project, not from the .config file will be used. This means that the service references will not be picked up correctly.

Appendix1: Example of a customised chart

Examples of customising your charts are abit thin on the ground. This should give you a start.

A list of the variables from elsewhere:

  • dataWorksheet: the worksheet that contains the data that will be rendered in the chart
  • first/lastChartRow: the start and end of the rows I want the chart to cover
  • DataWorksheetItems: the number of  data points in my dataWorksheet
  • Chart style and layout (35 & 2): these can be found by hovering over the style and layout in Excel
If you change something and it crashes, it may well be because some options (eg: where the labels go) are incompatible with some styles and layouts of charts.
FloatRect chartArea = GetRangeBoundaries(reportWorksheet, "A" + firstChartRow, "B" + lastChartRow, imageMargin);

Excel.Range chartRange;

Excel.ChartObjects xlCharts = (Excel.ChartObjects)reportWorksheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(chartArea.Left, chartArea.Top, chartArea.Width, chartArea.Height);
Excel.Chart chartPage = myChart.Chart;
chartRange = dataWorksheet.get_Range("A1", "B" + (DataWorksheetItems + 1).ToString());
chartPage.SetSourceData(chartRange, System.Type.Missing);
chartPage.ChartType = Excel.XlChartType.xlBarClustered;
chartPage.ChartStyle = 35;
chartPage.ApplyLayout(2, chartPage.ChartType);

chartPage.ChartTitle.Text = "My Chart";

chartPage.ChartArea.Font.Name = "Arial";
chartPage.ChartArea.Font.Size = 8;

chartPage.ChartTitle.Font.Name = "Arial";
chartPage.ChartTitle.Font.Size = 9;
myChart.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
chartPage.PlotArea.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
	Type.Missing /*legend key */,
	Type.Missing /*autotext*/,
	Type.Missing /*header lines */,
	Type.Missing /*series name*/,
	Type.Missing /*cat name*/,
	Type.Missing /*show val*/,
	Type.Missing /*show %*/,
	Type.Missing /* show bubble size*/,
	Type.Missing /*separator*/);

Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
for (int seriesIndex = 1; seriesIndex <= oSeriesCollection.Count; seriesIndex++)
	Excel.Series oSeries = (Excel.Series)oSeriesCollection.Item(seriesIndex);
	Excel.Points points = (Excel.Points)oSeries.Points(Type.Missing);

	//oSeries.MarkerBackgroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); // for other types of graphs
	oSeries.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(0, 145, 207));

	System.Collections.IEnumerator iptEn = points.GetEnumerator();

	while (iptEn.MoveNext())
		Excel.Point p = (Excel.Point)iptEn.Current;

		if (p.HasDataLabel)
			p.DataLabel.HorizontalAlignment = Excel.Constants.xlRight;
			p.DataLabel.Position = Microsoft.Office.Interop.Excel.XlDataLabelPosition.xlLabelPositionOutsideEnd;
			p.DataLabel.NumberFormat = "#,##0.00;[Red]#,##0.00";
			//p.DataLabel.Left = p.DataLabel.Left; // Blows up because the chart is not yet rendered.

Why bikes are “expensive”

I’ve come up quite frequently against people who are aghast that I paid £700 for a bike (or £3000 for a trike, but let’s not go into that) when you could get a second hand car for less that would do several thousand miles before needing an overhaul.

Alot of this you can put down to the TCO of the car vs the bike with the car needing MOTs, fuel etc, etc that will cost hundreds if not thousands. However, I recently had abit of a revelation.

I think my trike:


Is heavy at 18kg, but I recently got my old kart out of the garage for my son to have a play in.

I struggled to move the thing (without it’s engine). The chassis must have been close to 50kg. Fair enough, it has to put up with alot of lateral forces when cornering at 1.5g, but there’s no reason it should be so much heaver, apart from the fact that the thing doesn’t need to be powered by my legs and there’s a 140kg driver + kart (minimum) weight limit in the class.

To get that kart weighing 20kg, would have needed F1 components crafter from titanium and carbon fibre and the kart chassis would have cost more than the £700 I paid for it. In fact, car racing components tend to cost a ludicrous amount, like 10k for a set brake disks.

So, in conclusion, when I pay £700 for a bike, I’m getting a bargain, F1 spec technology in a super-lightweight package for the cost of 10 tanks of petrol.

The shortest trip to Wales ever

On a recent holiday at my parents in Winterbourne, near Bristol, I was all set to do a ride to discover the “mountains” at Wotton-under-Edge. However, there wasn’t time for a half day ride. I had happened on the fact that it was possible to cycle across the “old” Severn bridge, so I though what better to replace a long ride, than a ride to another country!

The details and stats of the journey are available here: http://connect.garmin.com/activity/118129908. Not sure what was going on with the altitude measures, because thankfully, the bridge is not 100ft below sea level. I like the fact that you can see the hump of the bridge on the way out and back.




Modify URL parameters using Javascript

I recently had a situation where I had an anchor tag with a long URL containing a lot of parameters. I wanted to change one and wanted a generic way I could do it without string parsing my life away.

After some web searching and modification, I came up with the following:

var URLMash = new function () {
// Takes a whole url as a parameter and returns an object with fields set to the key value pairs of the url parameters
    this.URLParamsToObject = function (url) {
        var vars = [], hash;
        var hashes = url.slice(url.indexOf('?') + 1).split('&');
        for (var i = 0; i < hashes.length; i++) {
            hash = hashes[i].split('=');
            vars[hash[0]] = hash[1];
        return vars;

    // Takes a javascript object and writes its fields as url parameters.
    this.ObjectToURLParams = function (obj) {
        var u = [];
        for (x in obj) {
            if (obj[x] instanceof Array) {
                u.push(x + "=" + encodeURI(obj[x].join(",")));
            } else if (obj[x] instanceof Object) {
            } else {
                u.push(x + "=" + encodeURI(obj[x]));
        return "?" + u.join("&");

Thank you to:http://jquery-howto.blogspot.com/2009/09/get-url-parameters-values-with-jquery.html and http://alexcline.net/2010/04/10/recursive-json-to-url-function/ for most of those.

I could then call this on the following anchor:

<a id=”reSearchLink” href=”/FileManagement/Search?page=1&isAscendingOrder=False&orderBy=ID&admin=True&searchUserOnly=False&searchImages=True&searchDocuments=True&tagFilterIds=-35″>Re-search</a>

and do the following Javascript wizardry:

var searchLnkParameters = URLMash.URLParamsToObject($('#reSearchLink').attr('href'));
searchLnkParameters.searchUserOnly = true;
var newParameters = URLMash.ObjectToURLParams(searchLnkParameters);
setActionLink('lnkFileReSearch', newParameters);

That’ll change the URL of the anchor to have “&searchUsersOnly=true”.

I’m cheating abit here by using “setActionLink” (yep .Net MVC) which grabs the first part of the URL and appends the parameters. The functions in URLMash could easily be changed to first store and restore “path” as a special case.

Happy coding.