Excel UDF library in C# from scratch

Posted on by

Introduction

In this article 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 provides 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 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 who 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 is 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-steps 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

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 :

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

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

Bid Ask Formulas

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

bidask_values

Conclusion

I hope this article have helped you understand all the process 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 is some workarounds but you don’t want to use such a complicated stuff when there is 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 article 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 let a comment.

From here you can go a step further by extending Excel at the VBA level, this will open you to a new world of possibilities: Extend your VBA code with C#, VB.Net or C++/CLI

And if you want to use another language like Python there is also plenty of alternatives, especially PyXLL.

28 thoughts on “Excel UDF library in C# from scratch”

    • Hi Rajeev,

      yes you can return arrays.
      The only thing you must know is that by default Excel consider them as horizontal => double[] represents a line.
      So if you want to insert vertically you must specialize your function and return a double[,] matrix formatted accordingly: {{bid}, {ask}}.
      I’ll complete the article with additional functions to illustrate this.

      Stay tuned!

      Reply ↓

    • Hi Rajeev,

      I’ve updated the article with a matrix function, BidnAsk, that can be used in array formulas.

      Enjoy! 🙂

      Reply ↓

  2. Thanks for the tutorial. I followed it exactly, but have a strange problem though, the list of Automation Servers does not contain “Finance.FinancialFunctions” and when I Browse for the DLL, it gives me the error: “The file you selected does not contain a new Automation Server, or you do not have sufficient privileges to register the Automation Server.”
    (Win 7/64 bit, Excel 2010, installed VS 2012)

    Reply ↓

    • Hi Marc, have you correctly edited the registry to add the Programmable key? Because this would explain why you don’t see it in the list.

      Reply ↓

      • Ah, found the problem. Has to do with me running 64 bit Office. I had to: 1) Uncheck the “Register for COM Interop”, 2) Rebuild, 3) run regasm from a different location: \Windows\Microsoft.NET\Framework64\v4.0.30319\

        That did the trick.

        Reply ↓

      • Ah, whoops, that solution was for another simple project that i was having a similar problem with, but it was being done in VS. But it is a Office 64 bit problem here as well. Basically I needed to run the 64 bit version of Regasm and then insert the ‘Programmable’ Key.

        Reply ↓

        • Glad you fixed it. 🙂

          Just out of curiosity: why are you using the Office 64bits version?

          I’ve never seen this kind of setup, the 32bits version has always been preferred.

          Thanks.

          Reply ↓

        • I’ve updated the article, adding a dedicated section for the 64 bits Office.

          Thanks for the feedback. 🙂

          Reply ↓

          • No problem. I bought Office stand-alone a while ago and opted for the 64 bit version figuring it would be faster. I didn’t realize it was an uncommon setup.

  3. Hi,

    I would like to know if you have any idea about implementing an addin excel that makes an UDF update multiple cells ( as it’s forbiden by the native excel feature)

    Best regards
    Xyloto

    Reply ↓

  4. Hi Pragmateek,

    I am glad for this blog. I have to write custom functions in VB.Net and return results in excel. My company has 64bit Office and Visual Studio 2013, because of which have spent almost one day in Google but finally you saved me. ThankYou.

    I am trying to write a function which retrieve values from a database and put it in a cell. I have built the code and registered the library. But when I trying to add this ‘Addin’ it says
    ‘AB_CD.Functions’ is not a valid Add-in. Could you suggest where I am going wrong.

    Reply ↓

    • Hi Kumar,
      hum, considering your setup you may have the same issue as Marc…
      Have you tried the same steps and still have the issue?

      Reply ↓

      • Hi Pragmateek,

        Thank-you for the response. Yes I figured it out, and its working now.

        Now I am facing up new issue. I have update the function in the base file, but its not reflecting in excel.
        Suppose, initially I had a function called Fun(a,b) which returns a+b. Now I update it to return a*b and while running fun(a,b) in excel, it still giving me a+b.

        Note: After updating the function, I have compiled again and registered the dll. But still function is not updated.

        Could you tell what am I missing?
        Do I need to un-register the previous dll, and re-register new dll? (I am using same GUID for both new and updated functions)

        Reply ↓

        • Hum strange, should work out of the box.

          Have you tried with a new worksheet to be sure the DLL is indeed correctly generated?

          If it works with the new spreadsheet try to unreference and then reference again the addin.

          Reply ↓

  5. Hi,

    When ever I am updating the function, I have to go to registry, delete that old GUID key and again create a new key with ‘Programmable’ sub-key. Then only the updated function is working.

    I have a small issue which I am working on, which is: Do you know how to give optional parameter in custom function?

    For Eg: Lets say a Func(a,b). Now I have to introduce new parameter ‘c’ as ‘optional’. Do you know how to go about it? {like Func(a as int, b as int, c as optional) }

    Reply ↓

    • You should not have to go to registry but sometimes you may have to unreference and reference again the addin from Excel.

      As for optional parameters I’ve used one in the BidnAsk method.

      Here is another example:
      – in the COM interface:

      string GetValueOrDefault(string value = "DEFAULT");

      – in the implementing class:

      public string GetValueOrDefault(string value = "DEFAULT")
{
    return value;
}
      Reply ↓

      • Hi,

        Hope you doing good.

        I have a small query. I would like the custom function to return range of values. { like I would like to show result from this query: “Select Column1 from Table1”}

        If this query return 10 rows, I would like to display these 10values.

        Thank You

        Reply ↓

          • Hi,

            Exactly. But that result is with in one cell. I would like to return result like… if the select statement throws up 10 rows, then in excel:
            A1 should be row 1, A2 should be row-2 value and so on…

            Do you have any idea?

          • You first have to select the destination range and validate the formula with SHIFT-CTRL-ENTER.
            If you’d like to avoid preselecting then I fear a simple UDF library might not do the job.

  6. Hi,

    I am trying to implement same function in VB (asI have hands on experience in VB compared to C#). I followed the same steps and the respective dll file is reflected in ‘Automation’ window.
    But after selecting the dll and going to ‘Ok’, it says ‘.DLL file is not a valid Add-inn’. Could you help me out.

    Here is my code:

    Imports System.Globalization
    Imports System.Net
    Imports System.Text

    Public Interface Inv
    Function investments(v1 As Double, v2 As Double) As Double
    End Interface

    Public Class UDF : Implements Inv

    Public Function investments(v1 As Double, v2 As Double) As Double Implements Inv.investments
    Return v1 + v2
    End Function

    End Class

    Reply ↓

    • Whether you use C# or VB won’t change anything, all the code is compiled to .Net byte-code.

      The only issue I see is the lack of the [Guid] attributes on the class and interface definitions.

      You can check this other article to learn how to generate some Guids: 4 ways to generate a GUID.

      Reply ↓

      • Hi,

        Thanks for the reply. Yes I have moved code to VB and not working fine. Now, I am facing a new problem. So, I am trying to return array of values with out pre-selecting the range by running a for loop. Here is the sample code:

        Dim xCell as Excel.Range
        xCell = CType(ExclAP.ActiveCell, Excel.Range)

        For i = 0 to 10
        xCell.Value = i
        xcell=xcell.offset(1,0)
        Next

        I am getting an error at this line ‘xcell.value’ and the error is
        “Exception from HRESULT: 0x800A03EC”.

        Do you have any idea?

        Note: I am running this loop within a function.

        Reply ↓

        • Not 100% sure but AFAIK you can’t access cells when you are in edit mode, i.e. when the user is entering a formula.
          Excel can only update the range the user has selected with the value returned by the function.
          Some addins like Bloomberg have such features but I think they do not use a basic UDF library but a full featured COM addin.

          Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove me you\'re human :) *