User-Defined Functions in Excel 2007 and Web Services

While preparing for an integration project with some of David Heckerman‘s biology-related work, I thought of educating myself on how Excel 2007, .NET, and Web Services could be combined to play together.

A lot of scientists use Excel in their day-to-day activities. So it makes sense to make functionality exposed through Web Services available as custom Excel functions, which can be added through “automation addins”. It is possible to write such addins as .NET classes and exposed as COM servers through COM Interop.

The example below shows an Excel worksheet where the user can define the symbol for two currencies (‘from’ and ‘to’) and the ammount to be converted. The custom function retrieves the exchange rate between the two given currencies from the Web Service. If you change the values in the B1 or B2 cells, the conversion will be recalculated, as you’d expect. The ‘ExchangeRate’ function is the name of the custom function.

Of course, there is the BIG isssue of synchrony. Excel doesn’t know that the call to the function may take a long time. Although there is a way to enable multithreaded evaluation even for custom functions, this is not possible from within .NET (or, at least, I am not aware of how). Here’s the code. Once you build your DLL, you can register it by going to Excel->Options->Addins->(press the “Go” button)->Automation and locate the class. Make sure you say ‘no’ to the question you’ll get about “mscorlib.dll”. If you have any problems, drop me a line.

    //------------------------------
// Copyright (c) Microsoft Corporation.  All rights reserved.
//------------------------------


    namespace Microsoft.TechnicalComputing.Sample
{
    using System;
    using System.Runtime.InteropServices;
    using Microsoft.Win32;

    // Added through "Web References"->Add (http://www.webservicex.net/CurrencyConvertor.asmx?WSDL)

    using Microsoft.TechnicalComputing.Sample.net.webservicex.www;

    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ComVisible(true)]
    [Guid(ExchangeRateSample.TypeGuid)]
    [ProgId("Microsoft.ExchangeRate")]
    publicclassExchangeRateSample
    {
        public ExchangeRateSample() {}

        internalconststring TypeGuid = "13600B26-D84B-4da2-98FE-66FACC5BD2C7";
        staticstring SubKeyName = @"CLSID\{" + ExchangeRateSample.TypeGuid + @"}\Programmable";

        [ComRegisterFunction]
        publicstaticvoid RegisterFunction(Type type)
        {
            Registry.ClassesRoot.CreateSubKey(ExchangeRateSample.SubKeyName);
        }

        [ComUnregisterFunction]
        publicstaticvoid UnRegisterFunction(Type type)
        {
            Registry.ClassesRoot.DeleteSubKey(ExchangeRateSample.SubKeyName);

        }

        publicdouble ExchangeRate(string from, string to)
        {
            CurrencyConvertor svc = newCurrencyConvertor();
            return svc.ConversionRate(
                (Currency)Enum.Parse(typeof(Currency), from), 
                (Currency)Enum.Parse(typeof(Currency), to));
        }
    }
}