Welcome to my blog of Excel DNA using Visual Studio and C#. In this post we will see how to
create Excel User Defined Functions ( UDFs ) using Visual Studio and C# by using Excel-DNA.
Introduction:
As per https://excel-dna.net/, Excel-DNA is an independent project to provide high-performance user-defined functions ( UDFs ), create custom ribbon bar and more to your Excel sheet. It allows to develop native .xll add-ins for Excel using C#, VB.NET, F#. It helps to integrate .NET into Excel.
Prerequisites:
Since we are integrating .NET into Excel we need Visual Studio. We can get Visual Studio 2015 Community Edition for free at https://beta.visualstudio.com/downloads/.
Once you install Visual Studio you are ready to go. So let's dig in and see step-by-step guide of creating a user-defined function ( UDF ) and execute that in Excel.
Create Project:
Launch Visual Studio. You need to create a Class Library Project. You can create by going to
File -> New -> Project and select Visual C# and then select Class Library project and give it a name like MyExcelDNALibrary. Please see below
This creates a new solution for you like below
Next step you need to do is add Excel-DNA add-in to your class library project. This can be done using NuGet Package Manager ( this is included in most of the recent visual studio versions).
In your Visual Studio, go to View menu at the top and the Other Windows and select Package Manager Console. This will open Package Manager Console pane. Please see below
Note : In Package Manager Console make sure you select the correct project under default project dropdown. In above image the red circled one.
Give the following command next to PM> in Package Manager Console and press Enter
Install-Package Excel-DNA.
You will get a bunch of text successful and complete messages and Excel-DNA add-in will
be added to your project.
Now your Solution Explorer must look like below
This step is optional. Open Solution Explorer and right click on Class1.cs and then select Rename to rename class to UDFHelper.
Now double click above class to open it and give using ExcelDna.Integration; at the top. Your class must look like below
To make things simple, let's add a function that gets current date and time like below
[ExcelFunction(Name = "CURRENTDATETIME")]
public static DateTime CurrentDateTime()
{
return DateTime.Now;
}
Lets also add a function that adds 2 numbers and returns result
[ExcelFunction(Name = "ADDTWONUMBERS")]
public static int Add(int a , int b)
{
return a + b;
}
Now your class must look like below
ExcelFunction tells that the function must be exposed in Excel as work sheet function.
[ExcelFunction(Name = "CURRENTDATETIME")] tells that .NET C# CurrentDateTime function must be exposed in Excel as CURRENTDATETIME.
[ExcelFunction(Name = "ADDTWONUMBERS")] tells that .NET C# Add function must be exposed in Excel as ADDTWONUMBERS.
In Solution Explorer right-click on your Excel-DNA Class Library project and select Properties. This will open properties tab. Go to Debug and make sure you have Excel.exe location
for Start external program. In my case it was C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
If you have 32-bit Excel then you can skip below steps and go directly to testing. If you have 64-bit Excel please follow bekow steps.
64-bit Excel:
In Solution Explorer right-click on your Excel-DNA Class Library project and select Properties. This will open properties tab. Go to Debug.
In Command line arguments : add 64 before .xll for example "MyExcelDNALibrary-AddIn64.xll" like below. This will tell that 64-bit version XLL will be loaded in Excel when you debug the application else you will get error
Test:
We are now ready to test. In Visual Studio, in command bar make sure you select Debug and then click on the green triangle to run the project or else press F5 on keyboard to run application.
If you donot have any compile errors, your solution will be compiled and Excel-DNA creates few xll files. Excel should be launched and depending on your security settings it will ask whether
to enable the add-in. Just select Enable button. This will open your Excel session.
Select a cell in excel and give =CURRENTDATETIME() like below. ( If everything goes fine Excel intellisense will show the method like below).
Select method like below to see result.
In order to format cell, select cell and right-click and follow below
Now you must see sensible date like below
To use Add follow below
Conclusion :
In this article we saw how we can create user defined functions (UDFs) and use them in Excel using Excel-DNA add-in. We created functions using .NET and C#. In future post I will show you how to create custom ribbon bar in Excel and how we can add buttons to it and perform some functionality like open a web-browser and launch a sample page. Till then I hope you find this post useful.
I hope you enjoyed reading this post as much as I did while writing it. Please feel free to let me know your valuable and constructive comments and suggestions which can help me to make this article better and improve my technical and written skills. Last but not the least please excuse me for my grammar and typos.
Thanks and have a nice and wonderful day.
