Create your Own Excel Functions in C#

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 :

26908898_10215005088421194_173605035_o

Call a function in Excel written in C# with Excel DNA.

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.
26972120_10215005088341192_1965049441_o

Project Debug properties.

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.

26937776_10215005088141187_955601267_o

Our first function with some description.

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 :

26909939_10215005088381193_104481825_o

The two final functions in C#.

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 :

26856801_10215005087981183_1815935974_n.png

Call our C# function in Excel in Debug Mode.

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:

26972387_10215005088101186_989382753_o

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 :

26942157_10215005088301191_550204064_o

Call the function in VBA. Note : CStr() is useless here.

Conclusion

Excel-DNA is can be pretty useful in many cases and I think some people might be interested in this open-source project.

Thank you!

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s