In this tutorial, I’ll show you guys how to create a UDF (User Defined Function). Before I start, why do we need UDFs? Excel already has a lot of functions such as SUM, AVG and UPPER. Can’t we use those? The canned functions are great. They will take you far but they fall short when doing complicated tasks. At that point, you’ll have to create your own. There are different ways to create UDFs by using VBA code. For this tutorial, we’ll go a step further. We’ll use an open source 3rd party library called Excel DNA. I love the sound of DNA, so without further ado, let’s have some fun.
Prerequisites for this project:
- Visual Studio 2017 (You can download the community edition for free)
- Microsoft Excel
Step 1) Launch Visual Studio and create a Class Library Project called HelloUDF. Make sure you select C# as a language.
Step 2) Rename the default class “Class1.cs” created by Visual Studio to HelloWorld.cs
Step 3) Open Package Manager Console
Step 4) Install the package Excel-DNA. Type “Install-Package Excel-DNA” and hit Enter.
Step 5) If the installatoin worked, you should see a couple of files in your solutoins.
5.1) A file called [ProjectName]-AddIn.dna. In my case its HelloUDF. If you selected a different name in step 1, your dna will be different.
5.2) Under references, you should see a file called ExcelDna.Integration.
Step 6) Open HelloWorld.cs and add the following lines of code.
6.1) using ExcelDna.Integration; //Add this at the top next to other using statements
6.2) change public class HelloWorld to public static class HelloWorld
6.3) Inside the class HelloWord, created a method called HelloWorld(). Add the following lines of code
[ExcelFunction(Name = “HeloWorld”)]
public static string HeloWorld()
return “Hello Excel”;
Step 7) Run the solution (Click on the green play button). If everything went right, Excel should automatically open and you will be able to use your newly created UDF.
To use it, type =HelloWorld() and Excel should populate your cell with “Hello Excel”;