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 2013, 2010, 2007 and lower in Visual Studio 2013 - 2005 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. Also, if you have Windows Vista, Windows 7, or Windows 2008, run Visual Studio via Run as Administrator.

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

Creating an Excel XLL Add-in project

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:

Choosing VB.NET as the programming language

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

Choose to generate a new or specify an existing .snk file

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.

XLL Add-in solution

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 used defined functions (UDF). To review the code, in Solution Explorer, right-click the file and choose View Code in the context menu.

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.

Xll Module - View Designer

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.

Xll add-in designer

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.

Adding Excel function category

This adds an Excel Function Category component onto the XLL module.

Excel Function Category component

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:

Adding a new function descriptor

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, 2010 and 2013)
  • 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).

Adding a parameter descriptor

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

Registering an XLL addin

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

Sample XLL Add-in function

Then specify an argument for your user-defined function:

Specifying an argument for the UDF

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.

Debugging the Excel XLL add-in

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.

Table 6. Deploying XLL add-in: links to step-by-step instructions

How you install the Office extension

Per-user Excel XLL
Installs and registers for the user running the installer

Per-machine Excel XLL
Installs and registers for all users on the PC

A user runs the installer from a CD/DVD, hard disk or local network location. Windows Installer
ClickOnce
ClickTwice :)
N/A
A corporate admin uses Group Policy to install your Office extension for a specific group of users in the corporate network; the installation and registration occurs when a user logs on to the domain. For details, please see the following article on our blog: HowTo: Install a COM add-in automatically using Windows Server Group Policy. Windows Installer N/A
A user runs the installer by navigating to a web location or by clicking a link. ClickOnce
ClickTwice :)
N/A

What's next?

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

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