Creating Excel XLL addin and user-defined functions
in Visual Studio: VB.NET. C#, C++
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.
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 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:
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.
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.
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.
Add a new public Shared (static in C#) function to the XLLContainer class.
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!
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:
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 adds an 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:
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:
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:
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.
Table 6. Deploying XLL add-in: links to step-by-step instructions
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.