Creating Excel XLL add-ins and user-defined functions
(UDF) in Visual Studio .NET - 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 2010 - 2000 in Visual Studio 2005, Visual Studio 2008 and VS 2010 in 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. The source code of the project - both VB.NET and C# versions - can be downloaded here; the download is labeled Add-in Express for Office and .NET sample projects.

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 Excel UDF types: Excel Automation add-ins and Excel XLL add-ins. They differ in several ways described in What Excel UDF type to choose?

Per-user Excel UDFs

An Excel 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.

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 the IDE.

The solution contains an only project, the 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.

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 the XLL add-in in 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.

Now right-click the designer surface and, in the popup menu, choose Add Excel Function Category.

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

Right-click the component and choose Edit Functions in the context menu.

This starts the Excel Function Category editor. You set the CategoryName property in that window.

Then add a function descriptor to the category. Bind the function descriptor to the function created in Step #3 above.

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. 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+)
• 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 Excel 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, see File | Options | Add-ins | Manage "Excel add-ins" | Go

Now you can use your UDF in the Insert Function wizard:

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 the XLL add-in

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

What's next?

Here you can download the project described above, both VB.NET and C# versions; the download link is labeled Add-in Express for Office and .NET sample projects.

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

• Development - typical misunderstandings, useful tips and a must-read section Releasing COM Objects.
• Excel UDFs - many useful articles on developing Excel user-defined functions including What Excel UDF type to choose?

If you develop a combination of Excel extensions, please check HowTo: Create a COM add-in, XLL UDF and RTD server in one assembly.

Back to Add-in Express for Office and .NET homepage