Custom Task Panes

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…

5

How to code a .NET RefEdit Control

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…

12

Upcoming Topics

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;…

2

Busy Times … and a Note about my TechEd Sessions

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…

0

Writing Custom Excel Worksheet Functions in C#

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…

16

Using Statistics in Business Intelligence

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…

1

OOXML wins ISO Vote

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…

0

Open XML SDK Roadmap Announced

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…

1

Analysing Column Data using C# (… with a little Custom Drawing thrown in for fun)

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…

5

Using Custom Functions in Dynamic Ranges

Named ranges are great. They enable us to assign a name to a collection of cells and use that name in code and worksheet functions. However, named ranges tend to be static. That is, they tend to represent a fixed set of cells, such as, A1:E100. But sometimes it would be useful to have the…

2