N.B.Throughout this post I am using Excel 2010 and Visual Studio 2010.
Writing a UDF in VBA to be exposed to Excel cells is straight forward, just write the function in a VBA module and Bob’s your uncle. However, it is slightly trickier to expose your functions to Excel in a managed language, such as C# or F#.
Essentially there are two ways to achieve this and for every method there are pros and cons:
- Automation Add-Ins Method
- XLL Add-Ins Method
I will demonstrate how to implement each method then I will discuss my verdict. I have created a sample project in each method; you could download it at the end of this post.
Automation Add-Ins Method
Automation Add-ins are COM functions to be called from formulas in Excel worksheets, it is supported since Excel 2002. The idea is that .NET can expose a COM interface that can be consumed from Excel through Automation Add-ins support.
To create your custom functions, you need to create a new C# code library project from Visual Studio, then go to:
Right click Project > Properties > Build > Register for COM Interop and enable it.
Then go to Assembly.cs and set
true. Then you need to create a base class that you will inherit later to create your UDF:
Then your UDF class should inherit
UdfBase as such:
Build your project, then the last step is opening an Excel file, going to: File > Options then selecting Add-Ins. Select “Excel Add-Ins” in the drop down list and then hit “Go…”. Select the “Automation” button and select your component (in this example, the item name to select is
=AutomationSampleAdd(1,2) in a worksheet cell and you should get 3.
Automation Add-Ins Method with a Reference to Excel
The previous method, mentioned above, allows Excel to call .NET, not the other way around. What if you want to have a reference to the Excel application executing your .NET code? Say to colour certain worksheet columns based on some criteria or for asynchronous call back. In this case, you need to implement
To implement this method, you need to reference the assemblies displayed to the right, inherit the
UdfBase abstract class and implement
In my download project, I implemented this class in a standalone project rather than combining it with the existing one. The reason is this approach requires references to specific Excel interop component version. And once you have these references, your deployment project is increased in complexity as now you need to manage more dependencies and make sure that the right referenced version of Excel is installed on the target machine (check NetOffice if you want to avoid that).
To create your UDF methods and to have a reference to the current Excel instance:
Use this project with Excel as mentioned above.
XLL Add-Ins Method
An XLL is an add-in for Excel that you can build with any compiler that supports building native DLLs (dynamic link libraries), it is supported since Excel 97. It is faster than the Automation Add-Ins and has more features, but XLL components are usually built via C/C++.
Luckily for .NET, there is an open source component with permissive licence called Excel DNA that allows .NET to build XLL add-ins effortlessly.
To build an XLL component, create a new project, download Excel DNA and reference
ExcelDna.Integration.dll then write your functions
Build then create a file called
YourDllName.dna, in this case
XllSample.dna with the following content:
Drop it next to your dll, then copy
ExcelDna64.xll next to your dll and rename it to match your dll name, in this case
Build your project, then the last step is opening an Excel file, going to: File > Options then selecting Add-Ins. Select “Excel Add-Ins” in the drop down list and then hit “Go…”. Select the “Browse” button and select your
In an Excel cell, start typing
XllSampleAdd and you will get the rest of the function via Excel’s auto complete.
Here is a comparison table between the two methods:
|Automation Add-Ins||XLL Add-Ins|
|Minimum Supported Version||Excel 2002||Excel 97|
|UDF Auto complete||Not supported||Supported|
|UDF Documentation Tooltip||Not supported||Supported|
|Building in .NET||Easier||Harder (without a 3rd party component)|
Automation Add-Ins support feels more like it is made with VB6 in mind rather than .NET and lacks some important features like auto complete and description when typing in a cell.
XLL Add-Ins per se are complicated from a .NET development point of view, however, Excel DNA did an excellent job in making the interface transparent and abstracted all the nifty details away for the .NET developers.
I have created a Visual Studio 2010 project with all the sample code demonstrated above, feel free to use it and distribute it.