Creating Excel XLL addin and user-defined functions
in Visual Studio: VB.NET. C#, C++

Add-in Express™
for Microsoft® Office and .net

Add-in Express Home > Add-in Express for Office and .NET > Online Guide > Developing Excel XLL add-ins

Developing Excel XLL add-ins

Add-in Express allows developing XLL add-ins for Excel 365, 2021, 2019, 2016, 2013, 2010, 2007 and lower using C#, VB.NET and C++. The sample project below demonstrates how you create an XLL add-in providing a sample user-defined function allocated to a custom function category. You can download the source code of the project as well as plenty more examples on the How-To pages: VB.NET for Excel and C# for Excel.

A bit of theory

Excel user-defined functions (UDFs) are used to build custom functions in Excel for the end user to use them in formulas. This definition underlines the main restriction of an UDF: it should return a result that can be used in a formula - not an object of any given type but a number, a string, or an error value (Booleans and dates are essentially numbers). When used in an array formula, the UDF should return a properly dimensioned array of values of the types above. Excel shows the value returned by the function in the cell where the user calls the function.

There are two UDF types: Automation add-in and Excel XLL addin. They differ in several ways described in What Excel UDF type to choose?

Per-user Excel UDFs

A UDF is a per-user thing that requires registering in HKCU. In other words, a UDF cannot be registered for all users on the machine. Instead, it must be registered for every user separately.

Step 1. Creating an XLL Add-in project

Make sure that you have administrative permissions before running Visual Studio. Run Visual Studio via the Run as Administrator command.

In Visual Studio, open the New Project dialog and navigate to the Extensibility folder.

Choose Add-in Express XLL Add-in and click OK.

This starts the XLL Add-in project wizard.

In the first wizard window, you choose your programming language and specify the oldest Excel version your add-in needs to support:

When in the window below, choose Generate new or specify an existing .snk file and click Next.

If you don't know anything about strong names or don't have a special strong name key file, choose Generate new. If you are in doubt, choose Generate new. If, later on, you need to use a specific strong name key file, you will be able to specify its name on the Signing tab of your project properties; you are required to unregister your add-in project before using another strong name.

The project wizard creates and opens a new solution in Visual Studio.

The solution contains an only project, the Excel XLL add-in project. The project contains the XLLModule.vb (or XLLModule.cs) file discussed in the next step.

Step 2. XLL module

The XLLModule.vb (or XLLModule.cs) file is the core part of the XLL add-in project. The XLL module allows creating and configuring custom user-defined functions (UDF). To review the code, in Solution Explorer, right-click the file and choose View Code in the context menu.

In the code of the module, pay attention to three points:

• the XLLContainer class



That class is the container class for your UDFs; all of them must be declared public static (Public Shared in VB.NET). An UDF must return a string, double or integer. Please see the next step for the use of this class.

• the ExcelApp property



This property was added by the COM add-in project wizard. You use it as an entry point to the Excel object model if this is required in your add-in.

• the CurrentInstace property



This property returns the current instance of the XLL module, a very useful thing when, for example, you need to access a method defined in the module from the code of another class.

Step 3. Creating a user-defined function

Add a new public Shared (static in C#) function to the XLLContainer class.

					Public Shared Function MyFunction(ByVal arg As Object) As Object
						If TypeOf arg Is Double Then
							Dim rnd As System.Random = New System.Random(2000)
							Return rnd.NextDouble()
						Else
							If _Module.IsInFunctionWizard Then
								Return "The parameter must be numeric!"
							Else
								Return AddinExpress.MSO.ADXExcelError.xlErrorNum
							End If
						End If
					 End Function
				 

The method above demonstrates the use of the IsInFunctionWizard property; it returns True if your UDF is called from the Insert Function wizard. In addition, it demonstrates how to return an error such as #NUM!

Step 4. Configuring a user-defined function (UDF)

To integrate your XLL addin in Microsoft Excel, you should supply Excel with a user-friendly add-in name, function names, parameter names, help topics, etc.

In Solution Explorer, right-click XLLModule.vb (or XLLModule.cs) and choose View Designer in the popup menu. Specify the add-in name in the Properties window.

The Excel XLL module designer provides the three areas shown in the screenshot above. They are:

• XLL module designer - (#1 in the screenshot above) it is a usual designer
• In-place designer - (#2 in the screenshot above) if there's a visual designer for the currently selected Add-in Express component, then it is shown in this area
• Help panel – see #3 in the screenshot above

Specify the add-in name in the Properties window. Right-click the XLL module designer and choose Add Excel Function Category in the context menu.

This places a new Excel Function Category component onto the XLL module.

You use the toolbar provided by the in-place designer to select, move, add or remove components. In this XLL add-in, you specify properties of the Excel Function Category component as demonstrated in the screenshot above and add a new function descriptor to the category component as shown below:

In the function descriptor, you set the FunctionName property, which provides a combo box that allows choosing a function from the list of functions defined in the XLLContainer class; select the function you created on Step 3. Other properties are:

• IsHidden allows to hide the function from the UI
• IsThreadSafe - you can mark your function as safe for multi-threaded recalculations (Excel 2007 and higher)
• IsVolatile = True means that your function will be recalculated whenever calculation occurs in any cell(s) of the worksheet; a nonvolatile function is recalculated only when the input variables change
• UnicodeName - allows specifying a language-specific function name (if the Localizable property of the XLL module is set to True).

In the same way, you describe the arguments of the function: add a parameter descriptor and select a parameter in the ParameterName property (see below).

Other properties are described below:

• AcceptAsArray = True means that your code will receive an array of parameters when the user passes a range to your UDF; otherwise, an instance of AddinExpress.MSO.ADXExcelRef will be passed to your code.
• UnicodeName - allows specifying a language-specific name for the argument (if the Localizable property of the XLL module is set to True).

Note. When renaming functions and arguments, you have to reflect these changes in appropriate descriptors. In the opposite case, Excel will not receive the information required.

Step 5. Running the XLL add-in

Choose Register Add-in Express Project in the Build menu, restart Excel, and check if your add-in works. If you use an Express edition of Visual Studio, the Register Add-in Express Project item is located in the context menu of the XLL module's designer surface.

You can find your XLL add-in in the Add-ins dialog:

• in Excel 2000-2003, see Tools | Add-ins
• in Excel 2007, see Office Button | Excel Options | Add-ins | Manage "Excel add-ins" | Go
• in Excel 2010-2013, see File | Options | Add-ins | Manage "Excel add-ins" | Go

Now you can use the XLL function you created in the Excel Insert Function wizard:

Then specify an argument for your user-defined function:

Step 6. Debugging the XLL add-in

In the Project Options window, specify the full path to excel.exe in Start External Program and run the project.

Step 7. Deploying your Excel XLL add-in

The table below provides links to step-by-step instructions for deploying XLL addins. Find background information in Deploying Office extensions.

What's next?

You may want to check the following sections under Add-in Express tips and notes:

• Development - typical misunderstandings and useful tips
• Excel UDFs - many useful articles on developing user-defined functions including Which UDF type to choose?

If you develop an integrated Excel extension, please see How to build a COM Add-in, RTD Server and XLL in one project.