Excel UDF library in C# from scratch

Introduction

In this post, I’ll show you how to create a UDF library for Excel in C# from scratch. By "from scratch", I mean without using Visual Studio, only low-level tools: a simple text-editor like Notepad and the C# compiler. This is of course not the way you’ll do it usually but it will hopefully help you to better understand how things work under the hood.

The pretext for this sample is a set of functions that provide financial data like the last bid and ask prices for a stock. It uses the Yahoo finance REST API which is rich and simple and that you could use as a base for developing more advanced tools.

The latest version of the article is available here. (I try my best to keep them in sync. :))

The C# UDF Library

So the first thing we need is the C# code of our amazing library:

using System.Net; // WebClient
using System.Runtime.InteropServices; // Guid, ClassInterface, ClassInterfaceType
using System.Globalization; // CultureInfo
namespace Finance
{
    [Guid("828d27b7-389d-4d41-bcfc-656abd11136e")]
    public interface IFinancialFunctions
    {
        double Bid(string symbol);
        double Ask(string symbol);
        double[,] BidnAsk(string symbol, string direction = null);
    }
    [Guid("bb7c9ec8-ecfc-4258-97d8-f9bcd3cdb714")]
    [ClassInterface(ClassInterfaceType.None)]
    public class FinancialFunctions : IFinancialFunctions
    {
        private static readonly WebClient webClient = new WebClient();
        private const string UrlTemplate = "http://finance.yahoo.com/d/quotes.csv?s={0}&f={1}";
        
        private static double GetDoubleDataFromYahoo(string symbol, string field)
        {
            string request = string.Format(UrlTemplate, symbol, field);
            
            string rawData = webClient.DownloadString(request);
        
            return double.Parse(rawData.Trim(), CultureInfo.InvariantCulture);
        }
        public double Bid(string symbol)
        {
            return GetDoubleDataFromYahoo(symbol, "b3");
        }
        
        public double Ask(string symbol)
        {
            return GetDoubleDataFromYahoo(symbol, "b2");
        }
        
        public double[,] BidnAsk(string symbol, string direction = null)
        {
            double bid = GetDoubleDataFromYahoo(symbol, "b3");
            double ask = GetDoubleDataFromYahoo(symbol, "b2");
            
            return direction == "v" ? new[,]{ { bid }, { ask } } : new[,]{ { bid, ask } };
        }
    }
} 

Well, it’s not rocket science but it’s a simple and useful use-case for an Excel UDF library.

Note the “BidnAsk” function: while the “Bid” and “Ask” functions return only one value, the “BidnAsk” function returns a matrix which allows this function to be used with array formulas in Excel.

So you can use it across a range of two cells and validate it with “SHIFT-CTRL-ENTER“.

By default, the values are arranged horizontally (this is how Excel works by default too), but if you want to insert them in a vertical range, you can use the optional parameter “direction” with the value “v”.

Apart from the classic C# stuff, you might have spotted the three highlighted lines which are all attributes (in .NET and especially C# attributes represent metadata that will be consumed by other components or tools): Guid and ClassInterface.

In this context, these metadata are aimed at being consumed by the COM infrastructure.

In the COM world, each type is identified by a unique GUID that clearly distinguishes it from any other type.
There are a lot of ways to generate a new GUID so if you do not know how to proceed, check this other article and use the one you find the handiest.

Next step is compilation to generate the .NET library that will be used by Excel.
We use the C# compiler, CSC, which should be bundled with your .NET Framework installation.
To compile, simply open a CMD command-prompt and navigate (with the cd command) to the folder containing your source code and run the CSC compiler:

>\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe /target:library FinancialFunctions.cs
Microsoft (R) Visual C# Compiler version 4.0.30319.17929
for Microsoft (R) .NET Framework 4.5
Copyright (C) Microsoft Corporation. All rights reserved.

You should now have a DLL file called FinancialFunctions.dll in the same folder.

We're halfway there: we now need to make Excel aware of our new functions.

COM Registration

This is a two-step process: first, we need to register our library into the system, more specifically into the Windows registry, to make them available for any application able to use COM interop.

For .NET assemblies, this is done using the RegAsm tool: RegAsm will read the COM metadata (remember the Guid attributes?) we have specified in our C# code and that are now stored into our .NET DLL assembly and write them into the registry.

Note that for a quick-and-dirty registration, you need to have administrator rights (there is a way to do that for the current user only but this is a little more convoluted and may be the subject of a future article…).

So you should run your CMD command prompt as administrator:

Run CMD as Administrator

Then navigate to the folder containing the DLL and simply call regasm:

>\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe /codebase FinancialFunctions.dll
Microsoft .NET Framework Assembly Registration Utility version 4.0.30319.17929
for Microsoft .NET Framework version 4.0.30319.17929
Copyright (C) Microsoft Corporation.  All rights reserved.
RegAsm : warning RA0000 : Registering an unsigned assembly with /codebase 
can cause your assembly to interfere with other applications that may be installed 
on the same computer. The /codebase switch is intended to be used only with signed assemblies. 
Please give your assembly a strong name and re-register it.
Types registered successfully

So far so good…

COM registration could have been that simple…but in order to make our library usable from Excel, we need to achieve a second step by adding a little flag, a "Programmable" empty key, into the registry information for our type.
To do so, use the RegEdit tool.
For my installation (a 32bits Office 2010 on a 64bits Windows 7), I added the key there:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\
{BB7C9EC8-ECFC-4258-97D8-F9BCD3CDB714}\Programmable

If you have a 64 bits version of Office (very uncommon but possible) 
  then you’ll need to use the 64 bits version or RegAsm, 
  located in "\Windows\Microsoft.NET\Framework64\v4.0.30319\", to register your DLL:
>\Windows\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe /codebase FinancialFunctions.dll
Microsoft .NET Framework Assembly Registration Utility version 4.0.30319.17929
for Microsoft .NET Framework version 4.0.30319.17929
Copyright (C) Microsoft Corporation.  All rights reserved.
RegAsm : warning RA0000 : Registering an unsigned assembly with /codebase can 
  cause your assembly to interfere with other applications that may be installed on the same computer.
The /codebase switch is intended to be used only with signed assemblies.
Please give your assembly a strong name and re-register it.
Types registered successfully

Moreover the 64 bits RegAsm uses another tree in the registry, so you ‘ll have to add the “Programmable” key under:

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{BB7C9EC8-ECFC-4258-97D8-F9BCD3CDB714}

(thanks Marc for having raised the issue)

We’re done with the plumbing and can now reference and use our library from Excel.

Using the Library in Excel

First, you need to have access to the Developer tab, if this is not the case showing it is really easy as demonstrated in this 45s video:

• http://www.youtube.com/embed/SdxNopUuoS0?feature=oembed

Then, in this Developer tab, click the "Add-Ins" button and from here the "Automation" button and look for our type, select it and click "OK" :

Automation Servers

Say "No" if you have a warning concerning "mscoree.dll" and ensure our component is correctly checked.

We can now use our functions from our Excel sheets.
Here are the formulas:

Bid Ask Formulas

And the results (first call can be a little long):

Conclusion

I hope this article has helped you understand all the processes of extending Excel with C# UDF.

But before you use this technology in your production environment, you should be aware of its limitations:

• Poor integration in Excel, no auto-completion and no user-documentation: you cannot help the user of your functions by providing additional metadata (there are some workarounds, but you don’t want to use such a complicated stuff when there is an alternatives)
• As you’ve seen, you have to mark the type as "Programmable" in the registry and thought it can be automated it remains quite cumbersome
• No support for multi-threaded recalculation introduced with Excel 2007
• Poor performance compared to XLL

So to write UDF libraries for Excel in C#, I advise you to use XLL instead, e.g. by using ExcelDNA.

Whatever the technology you choose for your future UDF projects, the procedures described in this post are very general and can be reused for any COM component written in C# particularly for those interacting with Excel.

So take the time to understand all the COM plumbing: GUIDs, ClassInterface and RegAsm as you will see them in other Office interop scenarios like shared-addins (this should be the subject of a future article).

If you have any feedback, question or issue, please leave a comment.