Hello World Excel – Your First User Defined Function (UDF) in Microsoft Excel

 

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:

Step 1) Launch Visual Studio and create a Class Library Project called HelloUDF. Make sure you select C# as a language.

Picture of how to create a visual studio project add-in for Microsoft Excel

Create a visual studio project

Step 2) Rename the default class “Class1.cs” created by Visual Studio to HelloWorld.cs

Change the class name

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”;

 

Leave a Comment