Today we will see how to quickly write your own library of Excel function in C#.
But, what is the incentive?
There are many possible reasons, some of them could be:
- You are much more comfortable with C# than VBA.
- You want to benefit from the various C# libraries.
- You prefer Visual Studio than the VBA editor…
- C# Performance, LINQ etc
Here is an example, I call a function wrote in C# in my Excel. The inputs are an option prices data file path, an interest rate and the parameters for the SABR model and it gives me back my volatility surface. The plot is the 3D plot from Excel :
Let’s dive into Excel DNA
We will use the free and open-source independent project Excel DNA.
Open your Visual Studio, create a new project as a Class Library.
The class already created will contain the function accessible in your final Excel Library (.dll).
- Open the NuGet Package Manager in Visual Studio, find “Excel_DNA” and download/install it.
- Add “using ExcelDna.Integration” at the top of your code.
- When you launch your project in Visual Studio (debug), it should open Excel so that you can test and debug your code.
- If not, open the project property, debug, and check the path of the Excel.exe.
Create your first function
In the class created by Visual Studio, you can write your first function that will be called in Excel. Let’s write an easy one that takes a string name as input and returns “Hello ” + name.
You can add some documentation to your function with the attributes ExcelFunction, ExcelArgument and ExcelCommand.
How can I return more than one variable?
Arrays. Let’s try to implement a function that takes two arguments in input and return them squared. The return type in the function signature is a 2D array of double in our case :
Let’s try them, build the project and launch in mode debug. It will open Excel, open a new Spreadsheet, select a cell and write “=MyFunction(“Alex”)”, it will return “Hello Alex”.
For the second function, select 4 cells and write “=MyFunction2(1,2)” and press Ctrl+Shft+Enter. The items of the array appear in the cells :
Release your Excel Library (.dll)
Once your project is finalized, you might want to release the .dll library and import it into your Excel. On Visual Studio, switch from debug to release and launch:
If things go well, your .dll library (a 32bit and a 64bit version) is in the bin/debug project folder.
To use it in Excel, open your Spreadsheet’s Option, select Add-Ins or References and search your .dll to finally import it. Once it’s done, you can freely call your functions!
I want to call them in VBA, is that possible?
Yes, it is. In VBA you can set a cell’s formula as follows :
Excel-DNA is can be pretty useful in many cases and I think some people might be interested in this open-source project.