Excel Programming

A Custom Task Pane (CTP) is a user interface component in Office which is used to provide a non-modal window. Some of Excel's features, such as the PivotTable field list and Reseach tool, use CTPs.  

CTPs are exposed to customer extensions via the Office extensibility COM libraries. Any Excel developer can develop their own CTP and have it work just like the built-in CTPs.

Despite what some people think, you do not need VSTO to build a CTP.

In this post I will develop an Excel feature called: annotations. Annotations (as implemented here) are basically free-from textual comments that can be added to a workbook. They are added into the file but are not displayed on the grid. You can use them to store notes or commentary in the workbook. They are displayed and edited in a Custom Task Pane as shown below.

The annotations are stored within the CustomXMLParts collection of the workbook. CustomXMLParts are a new feature in Excel 2007 which enable Excel developers to store their own XML within a workbook. I will cover CustomXMLParts in my next post.

What are Custom Task Panes?

A CTP is a simple window that contains an ActiveX control. Excel manages the CTP window (creates it, destroys it, handles its window messages etc.) and the ActiveX control provides all the features. The CTP is really just a container, but a container that is nicely integrated into Excel. A CTP can be docked inside the main Excel window or can float. The user can resize it, move it and close it and all of this is handled for us by Excel.

Building a Custom Task Pane

Creating a CTP itself is very simple. CTP functionality is provided via a COM interface called ICustomTaskPaneConsumer. From a .NET point of view, this interface is implemented in Microsoft.Office.Core. When Excel loads our COM addin it performs a QueryInterface call to see whether or not our addin implements the ICustomTaskPaneConsumer interface. If it does, Excel calls the ICustomTaskPaneConsumer.CTPFactoryAvailable method. It is in this method that our addin creates the CTP.

Let's take a look at the code.

public class Connect : Object, Extensibility.IDTExtensibility2, IRibbonExtensibility, ICustomTaskPaneConsumer {

  public void CTPFactoryAvailable(ICTPFactory CTPFactoryInst) {

    _ctpFactory = CTPFactoryInst;

     CustomTaskPane ctp = _ctpFactory.CreateCTP("ExcelExtensions.AnnotationsCtrl", "Annotations", Missing.Value);

  }

}

The Connect class implements the ICustomTaskPaneConsumer interface. Its implementation of the CTPFactoryAvailable method does two things. Firstly, it stores the ICTPFactory object passed to it by Excel and secondly it creates a CTP using the ICTPFactory.CreateCTP method. The first parameter to the CreateCTP method is the ProgId of the ActiveX control that the CTP is to host. The CTP will create an instance of this ActiveX control and place it within the CTP window.  

CreateCTP returns a CustomTaskPane object which we store for later use. The CustomTaskPane object is how our addin can interact with the CTP window itself. In the example above, we store the CustomTaskPane object inside an  AnnotationsExtension object but we can actually store it anywhere we like. The AnnotationsExtension object is a class inside our addin - the details of this class will be covered in the next post - for now, all we need to know is that it is a conceptual wrapper of our CTP.

So, the basic workflow in creating a Custom Task Pane is as follows:

Displaying a Custom Task Pane

So far we have created a CTP. We have a reference to it and the CTP knows which ActiveX control it is hosting. However, we need to explcitly display the CTP in order for it to be visible.

We do this via the CustomTaskPane.Visible property. Setting this property to true or false will display or hide the CTP (notice that the CTP is hidden - not destroyed).

This makes sense because we'd typically want to show or hide our CTP based on some user action, like a user clicking a button on the Ribbon. In this example we'll add an Annotations button onto the Review tab of the Ribbon.

<tab idMso="TabReview">

  <group id="AnnotationsGroup" label="Notes" insertBeforeMso="GroupComments">

    <toggleButton id="AnnotationsButton" onAction="OnAnnotationsClicked" label="Annotations"

      imageMso="ExchangeFolder" supertip="Add new and view existing annotations." size="large"/>

  </group>

</tab>

We then implement the OnAnnotationsClicked event handler in our COM addin.

Because we added a toggleButton control to the Ribbon, our event handler gets passed a boolean which indicates whether the toggleButton is pressed or not. All we need to do is set the Visible property of the CTP equal to this value.

Writing the ActiveX Control

We now have enough code to create and display a CTP when a button on the Ribbon is pressed. The final bit (and the harder bit) is writing the ActiveX control ... this is the thing that actually implements our custom features.

In this example, our ActiveX control reads and write annotations to the CustomXMLParts collection of the active workbook. I'll be covering the CustomXMLParts collection, how it is stored in the new Office XML file formats and how the ActiveX control works next time.

What's great is that the CTP can host any ActiveX control. So even though the CTP itself is native code, we can write our ActiveX control in .NET code and it all works.

Summary

To create a Custom Task Pane using managed code:

• Add a reference to Microsoft.Office.Core;
• Implement the ICustomTaskPaneConsumer interface on your addin class (i.e., the Connect class);
• In your implementation of ICustomTaskPaneConsumer.CTPFactoryAvailable call CreateCTP on the supplied ICTPFactory object;
• In the call to CreateCTP, specify the ProgId of the ActiveX control you want to host in the CTP;
• Store the CustomTaskPane object returned from CreateCTP and toggle its Visible property to show and hide the CTP as and when required; 

Links

The following link gives more information on creating Custom Task Panes:

• http://msdn.microsoft.com/en-us/library/aa942864.aspx

Next Time ...

We'll take a look at how the Annotations ActiveX control uses the CustomXMLParts collection to store the annotations entered by the user.

One of the controls frequently used by Excel is the reference edit box. This control is used to gather a range address from the user. The user specifies the range either by typing in its address or by using the mouse and clicking on the actual cells they want to use.

Here's a screenshot of the inbuilt reference edit box:

Whenever the user is required to specify a range, using the reference edit box is my preferred way to gather the address from the user because it enables (what I call) visual selection of ranges. Range addresses can be tedious to work with whereas visually clicking on the cells you want is a much easier and more efficient way of working.  

Custom solutions can also use visual range selection because Excel ships a control called the RefEdit control. This control is implemented in REFEDIT.DLL located in the Office folder (on my laptop this is: C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE12) and mimics the inbuilt reference edit control.

Well that's the good news ... now the bad: the REFEDIT.DLL cannot be used from managed code. In fact, it can only be used from VBA. Years ago, that wasn't a problem because most solutions were VBA based. However, these days we live in a .NET world and more and more solutions are being written in managed code. 

So, what are the options if we want to use the RefEdit control from managed code?

The way I see it, there are three options. Firstly, we could write an XLA containing a VBA userform on which we place the RefEdit control. We would then have to link the managed code and the VBA userform via macros somehow. The downsides of this option are that we  create a mixed code solution i.e., we have some VBA and some managed code. This may or may not be an issue for us.

The second option is to buy a commercial RefEdit control that can be used by .NET WinForms. Actually, I don't know if such a thing exists but let's assume that it does.

The third option is to write a managed code RefEdit control from scratch. This option is probably the most work and might be quite hard. However, I decided to give it a go...

In this post, I present a managed code RefEdit control written in C#. I have implemented the control as a .NET UserControl which can be added into the Visual Studio toolbox and dragged and dropped onto any WinForm just like any other .NET control.

The control I present does not behave exactly like Excel's reference edit boxes but I think it behaves close enough that most people wouldn't notice the difference.

Here's a screenshot of the control in action:

This example uses the managed code addin I presented in a previous post (here) but the control can be used on any WinForm.

You are free to download the control and use it in your solutions. It is totally free (in all senses of the word) and you can take the code and modify it without restriction. All I ask is that you let me know if you improve it or find any bugs so that I can incorporate those improvements for the benefit of the community.

Note: The RefEdit control also uses the ExcelUtility class I published previously in this post.

How to code a RefEdit Control in C#

Let's first think a bit about the behaviour of the RefEdit control. It does some fancy things in very subtle ways. It's one of those wonderful UI concepts that we use everyday without even thinking about its complexities.

When we click on the button in the RefEdit control, the window on which the RefEdit control resides is minimised and all controls on the window are not displayed. Clicking on the button again restores the window to its original state.

The other feature to note is how the visual selection works. When the text box in the RefEdit control has focus, the address of the selected cells in Excel is displayed. Even if multiple, discrete ranges are selected the addresses are displayed. The user can even change sheets and select ranges on different sheets. This all takes place while the window containing the RefEdit control is still visible but is in its minimised state and all the time the addresses of all the selected ranges are displayed in the RefEdit control.

These two features are the killer features of the RefEdit control. If we can code these features then we are well on our way to our goal of having a managed code RefEdit control.

So, let's take a look at how we do this.

First, we need to have a function that can minimise its parent window and hide all controls on the window except the RefEdit control. Also, this function needs to remember the window's previous state and restore the window to that state once the user has selected their range.

protected void ResizeParent() {

  Form parentForm = (Form)Parent;                               

  foreach (Control c in parentForm.Controls)

    c.Visible = _state.ParentMinimised;

  Visible = true;

  if (!_state.ParentMinimised) {

      _state.ParentClientSize = parentForm.ClientSize;

      _state.PrevX = Left;

      _state.PrevY = Top;

      _state.Anchor = Anchor;

      Anchor = AnchorStyles.None;

      parentForm.ClientSize = new Size(Width, Height);

      Left = 0;

      Top = 0;               

      _state.ParentPrevBorder = parentForm.FormBorderStyle;

      parentForm.FormBorderStyle = FormBorderStyle.FixedDialog;

      _state.ParentControlBox = parentForm.ControlBox;

      parentForm.ControlBox = false;

    }

    else {               

        parentForm.ClientSize = _state.ParentClientSize;

        Anchor = _state.Anchor;

        Left = _state.PrevX;

        Top = _state.PrevY;

        parentForm.FormBorderStyle = _state.ParentPrevBorder;

        parentForm.ControlBox = _state.ParentControlBox;

    }

}

Notice that the first line of code casts the control's Parent to a Form object. This is our first limitation. If this cast fails then our control won't work. Why would the cast fail? It would fail if the parent is not a Form. This means that our RefEdit control's direct parent must be a Form. In other words, we can't place our RefEdit control within another control (such as a group box); it must be placed directly on a Form.

The _state object is an instance of the following struct:

struct RefEditState {

        public Size ParentClientSize;

        public bool ParentMinimised;

        public int PrevX;

        public int PrevY;

        public FormBorderStyle ParentPrevBorder;

        public bool ParentControlBox;

        public AnchorStyles Anchor;

        public Excel.Worksheet Sheet;

        public bool UseSheetAddress;

    }

We use this struct to store state information while we are minimising the parent window. Any property of the parent window that we change during the minimising we first save in the _state object. If the window is to be minimised (this happens when the user clicks on the button in the RefEdit control), all controls except the RefEdit control are hidden and the window is resized so that it is fits snuggly around the RefEdit control. That is, the window is made to look like this:

When the window is to be restored (when the user clicks on the RefEdit button again) we re-apply the settings stored in _state.

Updating the RefEdit Control with the Selected Range

The RefEdit control now resizes and restores its parent window whenever the user clicks on the button. Now we need to display the address of the ranges the user selects in Excel, while the user is selecting them.

So, if the user clicks $A$1 the RefEdit should display $A$1 in its text box. If the user selects the range $A$1:$B$3 the RefEdit should display $A$1:$B$3. Also, we want to support selecting multiple ranges. So, if the user selects $A$1 and $F$3 the RefEdit control should display $A$1,$F$3.  

We detect which ranges the user is selecting by sinking the SheetSelectionChange event on the Excel.Application object.

private void SinkEvents() {

  _con.ExcelApp.SheetSelectionChange += new Excel.AppEvents_SheetSelectionChangeEventHandler(ExcelEvent_SheetSelectionChange);

  _con.ExcelApp.SheetActivate += new Excel.AppEvents_SheetActivateEventHandler(ExcelEvent_SheetActivate);

}

However, we don't want to always sink these events. We only want to sink them while the RefEdit is active. By active I mean the user has either clicked inside the RefEdit's text box or has clicked the RefEdit's button. In other words, we only sink these events while the user is using the RefEdit control.

We trap the Enter event of the RefEdit's address textbox and sink the Excel events whenever this event fires. We also save the active sheet in the _state object. 

void _addressTxt_Enter(object sender, EventArgs e) {

  SinkEvents();

  if (_state.Sheet == null)

    _state.Sheet = ExcelUtility.ApplicationInvoke<Excel.Worksheet>(_con.ExcelApp, "ActiveSheet");

} 

We detach from the events when the stops using the RefEdit control.

private void UnsinkEvents() {

  _con.ExcelApp.SheetSelectionChange -= ExcelEvent_SheetSelectionChange;

  _con.ExcelApp.SheetActivate -= ExcelEvent_SheetActivate;

}

I know that posts have been few and far between (ok ..non-existant) this month. I hope this isn't something that will be repeated ... this month has just been crazy busy with deadlines (I'm sure you know that feeling...)

Anyway, I have a healthy backlog of topics that I want to blog about over the summer:

• Multi-threaded UDFs in Excel 2007;
• RefEdit control for COM addins written in managed code;
• C API enhancements in Excel 2007;
• PivotTable stuff
• I get lots of questions about PivotTable programming so there is plenty I want to talk about here;
• Ribbon customisation;

I'm at TechEd next week and am hoping that the flight time there will give me time to get started on some of these posts.

Feel free to drop me a line if there is something you would like covered. 

Busy times at Microsoft has meant that I've been slower than usual in posting recently. My current expectation is to be over my heavy workload by about mid-June. I'll continue to post in the meantime; but at a slower pace.

TechEd is being held in Orlando in June this year. There are a lot of very interesting sessions being held and the key note will be done by Bill Gates. If you can make it, I'd recommend coming along. You can find out more about TechEd here.

I have two speaking slots at this year's show: one in the Developer track and one in the IT Professional track.

In the Developer session I'll be talking about the improvements we made to XLLs in Excel 2007. In the IT Professional session I'll be talking about the Business Intelligence capabilities of Excel 2007 and Excel Services.

Writing our own, custom worksheet functions is a great way to extend Excel. Before Excel 2002 we developed custom functions by either writing them using VBA inside an XLA or by using C/C++ inside an XLL. Excel 2002 introduced a new type of addin called an automation addin. An automation addin enables Excel to call functions on COM objects from cells on a worksheet. In other words, it enabled us to call COM functions just as if they were normal, built-in Excel functions.

This opened up the world of custom worksheet functions to COM developers. So, now we can now write custom functions in any COM language; including C#.

In this article we'll write a custom worksheet function in C# called UNIQUEVALUES. 

What will our Custom Function Do?

The UNIQUEVALUES function will return an array of the unique values in a specified range. For example, if the range A1:A5 contains the values Red, Yellow, Red, Blue, Yellow, then {=UNIQUEVALUES(A1:A5)} will return the array {Red, Yellow, Blue}.

The function will also be able to cope with a mixture of numerical data and text data. So, if A1:A5 contains the values: Red, 1, 1, Red then {=UNIQUEVALUES(A1:A5)} will return the array {Red, 1}.

Because UNIQUEVALUES is a worksheet function that references the source data, whenever the values in the source range change the function automatically extracts the new unique values from the range. So we know that the values returned from the function are always up-to-date (once the worksheet has calculated).

Notice that the UNIQUEVALUES function returns an array. This means that we array enter the funtion by pressing Ctrl-Shift-Enter rather than just Enter when we type the function into the worksheet.

Coding the UNIQUEVALUES Function 

There are two tasks we need to do in order to make a C# class's methods be callable as worksheet functions:

• Define our worksheet functions in the way Excel expects;
• Add the Programmable key to the registry for our class's CLSID;

The first task seems intriguing: Define our worksheet functions in the way Excel expects. What does that mean?

Well, this is where we have to remember that Excel is speaking to our automation addin via COM. So Excel expects to be able to use COM techniques to discover and access our functions. Once we know what these COM techniques are, we can make sure that our C# class interoperates with COM in the appropriate way.

When an automation addin is loaded, Excel needs to discover which functions that addin supports. For example, our automation addin will have a UNIQUEVALUES function that we want to use in worksheets. How does Excel discover that our addin supports that function and how does it discover information about the parameters of the function?

Excel discovers this information using a COM interface called ITypeInfo.

ITypeInfo is an interface that is used to access metadata about a COM type. It is similar in concept to .NET reflection, but is very different in implemenation.

Excel uses ITypeInfo to discover the names and the parameter details of the functions exposed by our automation addin. This is how Excel knows to call our addin whenever it comes across a call to UNIQUEVALUES.   

But Excel will only query the default interface of our COM class. So we need to make sure that the functions we want to expose as worksheet functions are defined on the default interface of our class.

There are two ways of doing this in C#:

• By specifying the ClassInterfaceType.AutoDual attribute on our C# class;
• By defining our worksheet functions on a dedicated interface and using the ComDefaultInterface attribute to make that interface the default interface of our class;

To understand what the ClassInterfaceType.AutoDual attribute value does to our COM interface, we need to understand a little more about COM interfaces. 

In COM, the IDispatch interface enables clients to dynamically call your functions at runtime. Instead of the compiler checking that functions exist and that the parameters types match at compile-time, IDispatch enables clients to do so at runtime. This enables components to be extremely loosely coupled. This type of function call is termed: late-bound.

Support of IDispatch is optional. COM classes do not have to support it. However, by default, C# classes support IDispatch. In fact (unless we specify otherwise) the default interface created for a C# class is a dispatch interface. 

Why do we need to care about this? Well, since the default interface is a dispatch interface our worksheet functions are not explicitly defined on the interface and are therefore not discoverable by Excel. So, even though our C# class may have a public function named UNIQUEVALUES Excel would not be able to call it.

A dual interface allows a dispatch interface to explicitly define custom functions in addition to those already defined by IDispatch. The ClassInterfaceType.AutoDual attribute turns our COM interface into a dual interface. This effectively takes all public functions defined on our class (including those inherited from base classes) and explicitly defines them on our COM interface which in turn makes them discoverable by Excel.

Thus, an easy way of getting our C# class to define Excel worksheet functions is to simply mark the class with ClassInterfaceType.AutoDual.

However, there is a downside to doing this. All public functions on our class are then available as worksheet functions, including those inherited from System.Object. This isn't ideal. It would be better if we could have more control over what gets exposed to Excel and make sure that only the functions that are intended to be worksheet functions are actually discoverable by Excel. This is the reason I prefer not to use the ClassInterfaceType.AutoDual attribute.

(There is also a problem with COM versioning when using ClassInterfaceType.AutoDual - but we won't cover that here).

Instead, I prefer to define the worksheet functions on a dedicated interface and have my C# class implement that interface.

So, let's define our worksheet function interface. We'll call it IFunctions.

public interface IFunctions {

  object[,] UNIQUEVALUES(Excel.Range TargetRange);

}

Notice that the UNIQUEVALUES function returns a 2-d array of objects. This array will contain the unique values extracted from the specified Excel.Range object.

Next, we define our functions class and have it implement the IFunctions interface. We also use the default attribute to specify that we want the IFunctions interface to be the class's default COM interface.

[ComDefaultInterface(typeof(IFunctions))]   

public class Functions : IFunctions {

  public object[,] UNIQUEVALUES(Excel.Range TargetRange) {

    object[,] values = TargetRange.get_Value(System.Reflection.Missing.Value) as object[,];

    List<object> unqVals = new List<object>();

    foreach (object obj in values) {

      if (!unqVals.Contains(obj))

        unqVals.Add(obj);

      }

    object[,] resVals = new object[unqVals.Count, 1];

    for (int idx = 0; idx < resVals.Length; ++idx)

      resVals[idx, 0] = unqVals[idx];

      return resVals;

    }

}

The algorithm for extracting the unique values is as follows (I confess that this may not be the optimal algorithm).

We extract the values in the Range object into a 2-d array of object. Because the array contains object, we can cope with both textual data and numerical data. Next, we iterate over the array and store each value that hasn't already occurred in a List<object>. We then allocate a new 2-d array of object and copy the unique values from the List<object> into the new array. Finally, we return the new array to Excel.

When we return an array from a UDF Excel handles serialising the array into the worksheet in the appropriate way (as long as the user array-entered the cell formula). This means that the dimensions of the array matter. The first dimension of the array is mapped to columns. So if we return an object[3] then Excel interprets this as "three columns containing one row each" and places the three values adjacent to each other (i.e., each one in a different column). If we return an object[1,3]then Excel interprets this as "one column containing three rows" and places each value beneath each other (i.e., each one in a different row).

So, it is very easy to return an array to Excel. All we have to do is set the correct array dimensions and let Excel handle copying the values from the to the appropriate cells in the worksheet.

We'll talk more about how arrays are used in Excel worksheet functions later in the article.

If we stop coding here, we'll find that Excel still can't use our class. There is one final thing we need to do: set the class's Programmable key in the registry.

The Programmable key indicates that a COM class exposes type information for its default interface. The key is specified under the COM class's HKEY_CLASSES_ROOT\CLSID\{Guid} key (where {Guid} is the guid of the COM class).

There is a convenient way to set this registry key from C#. There exist two attributes called ComRegisterFunctionAttribute and ComUnregisterFunctionAttribute. These attributes are used to mark which functions COM should call during the COM registration/unregistration process. So all we need to do is write a function that inserts the Programmable key during registration and removes it during unregistration.

[ComRegisterFunctionAttribute]

public static void RegisterFunction(Type type) {

  Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));

}

[ComUnregisterFunctionAttribute]

public static void UnregisterFunction(Type type) {

  Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), false);

}

private static string GetSubKeyName(Type type) {

  string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable";

  return s;

}

(Notice that the registration functions are static).

Now we've finished coding the Functions class and the UNIQUEVALUES function. We can now use from an Excel worksheet just like any other custom worksheet function.

Using UNIQUEVALUES in a Worksheet

The first to do is to install our Functions class as an automation addin. This enables Excel to call our custom UDFs.

To install as an automation addin, we bring up the usual Addin Manager via Excel Options->Add-Ins->Manage Excel Add-ins. From this dialogue we click the Automation button and select ExcelExtensions.Functions from the list.

To use the function, we highlight the cells which we want the unique values written into, type the =UNIQUEVALUES( ... ) where ... is the range from which we want to extract the values and then press ctrl-shift-enter.

Note that we must press ctrl-shift-enter to tell Excel that this function returns an array. Excel automatically serialises the values returned from the function into the array of highlighted cells. If the number of values returned from the function is less than the number of cells we highlighted, Excel places #N/A in each of the extra cells.

Consider the following example.

Notice that there are only four unique values in the range D3:D9. However, because we highlighted 7 cells (i.e., E3:E9) Excel has placed #N/A in each of the 3 additional cells.

If we highlight too few cells (say, 2 cells) then Excel will place the first to values returned from the function in those cells.

This is normal array function behaviour in Excel. Notice that we didn't have to write anything in our code to do this; Excel does this for us automatically.

If the data in D3:D9 changes, Excel fires UNIQUEVALUES and our function recalculates the new unique values.

In the following screenshot, we change the first value from Red to Yellow and the unique values automatically update:

The UNIQUEVALUES function can also be used with Excel's native functions that work with arrays. Two examples of such functions are COUNT and INDEX.

Both COUNT and INDEX can accept arrays as parameters. So we can use these functions on the array returned by UNIQUEVALUES.

The formula =COUNT(UNIQUEVALUES(D3:D9)) returns the number of values in the array. The formula =INDEX(UNIQUEVALUES(D3:D9), 1, 1) returns the first value in the array; and =INDEX(UNIQUEVALUES(D3:D9), 2, 1) returns the second; and so on ... (remember that UNIQUEVALUES returns an array one column wide).

Summary

• The functions that we want to expose as custom worksheet functions need to be defined on our class's default interface;
• Defining our custom worksheet functions on a dedicated interface and using ComDefaultInterface to make that interface the default is one way of doing this;
• Our COM class must define the Programmable registry key under its CLSID key;
• We can return multiple values from a custom UDF by returning them in an array;
• Excel handles the serialisation of the array into the worksheet;

Download

I have added the UNIQUEVALUES function into my ExcelExtensions add-in. This can be downloaded from the MSDN Code Gallery by clicking here.

There are two downloads which can be downloaded from the Releases tab: ExcelExtensionsSrc.zip and ExcelExtensions.msi.

ExcelExtensionsSrc.zip contains the complete source code and ExcelExtensions.msi is the set up program.

Over time, I'll be adding additional feature to the addin.

One other area I work in is Business Intelligence. These days BI is an important technology to an increasing number of companies. There are many great BI tools out there, one of which is (of course) Excel.

One of the things that makes Excel such a useful BI tool is its richness of functionality. It has so many existing features that can be used in the BI area - one of which is its statistical analysis functions.

I posted an article over on the Excel team blog illustrating one way we can use Excel's statistical functions to extract useful information from business data.

If you're interested in statistical analysis or BI in Excel, you may find the article interesting. Click here to view the article.

Office Open XML (OOXML), the XML file formats used by Office 2007, today won the ISO vote which paves the way for it becoming an ISO standard.

This is very exciting news indeed for Excel programmability. Coupled with the new Open XML SDK that we announced last week, programming against Excel's native file format has never been as open. I think this opens up quite a lot of exciting programmability scenarios, some of which I'll cover on this blog over time.

More information can be found at the following links:

• Microsoft's press release;
• ISO's press release;
• ECMA's statement;

The new XML file formats in Office 2007 open the door to lots of exciting programmability solutions. For example, the files can be created and consumed without automating Excel.exe. This really helps server-side solutions. It also aids batch processing of Excel files and cross-platform business solutions that exchange Office files.

To enable easy access to the files, we provided the .NET Packaging API.  But this is a low-level API which does not have any application awareness. For example, it doesn't understand Excel ranges or tables; it deals with the raw XML elements and relations.

But we are working on improving this situation.

The Open XML SDK is a .NET library which will provide a higher-level API for the Open XML files. It will have application awareness and will greatly benefit developer productivity.

Version 1.0 will be released in May 2008. This will be an augmentation of last year's CTP. However, there won't really be anything in v1.0 for Excel developers. The Excel support will come later this year.

Here are some links which contain more information on the SDK:

• OpenXMLDeveloper.org
• Download the Open XML SDK

When presented with a table of data in Excel, sometimes it is useful to be able to learn some quick facts about it. One quick fact which is often useful is the distribution of values within a particular column. For example, if we have a column called Region it is useful to  know which regions occur more often than others. Perhaps 50% of the column is North America, 30% Europe and 19% Pacific. Excel has all the tools we need to do this but we just need to tie them together in a way that suits us.

Additionally, it would be nice if we displayed this information graphically; perhaps using some form of simple histogram to compliment the numerical values and provide a tiny bit of visualisation.

In this post, we'll write a C# addin that does such a thing.

Here's a screenshot of the addin we'll build.

Getting Started

We won't cover the basics of getting up-and-running with C# Excel addins in this post. I have already covered that briefly here. Instead, we'll fast forward to the stage where we've already got our basic addin project and now we're ready to add the fun bits.

Note: This addin uses the ExcelUtility class I published previously in this post.

Designing the Custom Ribbon

We'll add a custom tab to the Ribbon called "Excel Extensions". On this tab, we'll place a button titled "Analyse Column" inside a group titled "Table Extensions".

Here's our Ribbon XML:

<?xml version="1.0" encoding="utf-8" ?>

<customUI onLoad="OnRibbonLoad"  xmlns="http://schemas.microsoft.com/office/2006/01/customui">

  <ribbon>

    <tabs>

      <tab id="ExcelExtensionsTab" label="Excel Extensions">                               

        <group id="TableCommandsGroup" label="Table Extensions">

          <button id="ColumnAnalysis" onAction="OnColumnAnalysisClicked" label="Analyse Column" getImage="GetColumnAnalysisButtonImage" getEnabled="GetColumnAnalysisEnabled"

 supertip="Analyse a table column and view statistical details about the values in the column."

 size="large"/>