Excel 2003 Add-in development Tricks and Tips

When developing an Excel add-in, much time is spend on figuring out how to programmatically implement things possible by hand or in a VBA macro. Here are some tips and tricks to help out masterin Excel using C#!

Value Validation

When reading the value of a cell (specially numeric values), you should always start by validating it.  The reason is simple, if a cell contains an error caused by an invalid formula or reference (e.g. #N/A, #REF ,#NAME, #DIV/0, #NULL, #VALUE and #NUM), the cell value will return a error code (e.g. -2146826273) instead of a value you might be expecting (0 or null).

You can find all the common Excel formula functions in Application.WorksheetFunctions. The following example uses the IsError function for converting a error value to null.

Range cell = range.Cells[row, col] as Range;

if (!Application.WorksheetFunction.IsError(cell))

{
    value = cell.Value2;
}
else
{
    value = null;
}

For more information on handling cells with errors, visit the VSTO & .NET & Excel blog

Controlling the User Interface

When processing cells you might in some situations need to prevent the user from editing the workbook and seeing the cells being updated. For this I usually used the following functions:

public void BeginUpdate()
{
    Application.ScreenUpdating = false;
    Application.Interactive = false;
}
public void EndUpdate()
{
    Application.ScreenUpdating = true;
    Application.Interactive = true;
}

Make sure you call the EndUpdate in a finally block, or you risk having the user lose all his work if an unhanled exception occurs!

Better Performance By Caching

Looping repeatedly through collections provided by the Excel API can be very very slow, thanks to the COM interface.

If you have a lot of named ranged in your workbook, caching the Names collection might speed up your application 10x or more.

private Dictionary<string, Range> _NamedRanges;
public Dictionary<string, Range> NamedRanges
{
    get
    {
        if (_NamedRanges == null)
            LoadNamedRanges();
        return _NamedRanges;
    }
}
private void LoadNamedRanges()
{
    _NamedRanges = new Dictionary<string, Range>();
    foreach (Name name in Application.ActiveWorkbook.Names)
    {
        try
        {
            _NamedRanges.Add(name.Name, name.RefersToRange);
        }
        catch (Exception)
        {
            // Ignored
        }
    }
}
public void Refresh()
{
    if(_NamedRanges != null)
        _NamedRanges.Clear();
    _NamedRanges = null;
}

Note that reading the ReferesToRange value of some internal Excel ranges might cause an exception to be thrown.

Storing Data

Many applications support the concept of custom (file) properties. These provide a easy way to store custom application data (key-value-pairs)  into a data file.

Like all the Office applications, also Excel supports custom properties enabling you to store data into the workbook. One thing to bare in mind though is that every property can only hold up to 255 characters.

By implementing the following easy steps, you can store basically any kind of object data into the workbook (I will maybe post some code later):

  1. Tag your class(es) using the standard XML attributes and use the XmlSerializer to serialize the objects to a string
  2. Just to be on the sure side, encode the string using Base64 (this also hides the data from the user)
  3. Split the data to chunks of 255 characters and store them into custom properties. Name each property with a running number to help you re-assemble the data string.

To read back the custom properties to objects, all you need is to run the steps in a reverse order.

Event Handling

The COM interface causes many problems when it comes to managing Excel events. One reason to this is must be the automatic GAC and that event handlers are hard to marshal, this results in the managed side being out-of-sync with the unmanaged Excel API. The symptoms of these problems are random exceptions and loss of event handlers (e.g. the event handler of a menu is usually lost after the first click).

In my experience the only way to be safe is not to play around with the Excel events. Also make sure you place all your menu handling code in the main add-in class!

Advertisements