Creating Excel XLL add-ins and user-defined functions
(UDF) in Visual Studio .NET - VB.NET. C#, C++

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

Developing Excel XLL add-ins

Add-in Express allows developing XLL add-ins for Excel 2007 - 2000 in Visual Studio 2005 and Visual Studio 2008: Professional, Standard, Express, .NET Framework 2.0 and 3.5) in C#, VB.NET, C++ and Chrome. It also provides some more advanced features for customizing the Excel UI, such as Advanced Excel Task Panes, Excel Automation add-ins etc.

Please note that Add-in Express does not support creating XLL add-ins in Visual Studio 2003 (.NET Framework 1.1).

Step #1 – Creating a new Add-in Express XLL Add-in project

Add-in Express adds the Add-in Express XLL Add-in project template to the Visual Studio IDE. 

When you select the template and click OK, the Add-in Express XLL Add-in project wizard starts. In the wizard window, you choose the programming language (Visual Basic in our XLL project) and setup project options.

This VB.NET sample project shows how to build an  XLL Add-in for Excel with the Add-in Express Loader as a shim.

The Add-in Express Project Wizard creates and opens the XLL Add-in solution in Visual Studio .NET. The solution includes the XLL Add-in project and the setup project.

The XLL Add-in project contains the XLLModule.vb (or XLLModule.cs) file discussed in the next step.

Step #2 – Add-in Express XLL module

The XLLModule.vb (or XLLModule.cs) file is the core part of the XLL Add-in project. The XLL module is a container for Category components. It contains the XLLModule class, a descendant of the ADXXLLModule class that implements the interfaces required by the Excel XLL technology and allows creating and configuring custom used defined functions (UDF). To review its source code, in Solution Explorer, right-click on the XLLModule.vb (or XLLModule.cs) file and choose the View Code popup menu item.

Please note the XLLContainer is a partial class in the code below. We describe its use in the next steps.

Imports System.Runtime.InteropServices
Imports System.ComponentModel

'Add-in Express XLL Add-in Module
 _
Public Class XLLModule
    Inherits AddinExpress.MSO.ADXXLLModule
 
#Region " Component Designer generated code. "
   'Required by designer
   Private components As System.ComponentModel.IContainer

   'Required by designer - do not modify
   'the following method
   Private Sub InitializeComponent()
      '
      'XLLModule
      '
      Me.AddinName = "MyXLLAddin1"

   End Sub

#End Region
 
#Region " Add-in Express automatic code "
 
    'Required by Add-in Express - do not modify
    'the methods within this region
 
    Public Overrides Function GetContainer() As _
           System.ComponentModel.IContainer
        If components Is Nothing Then
            components = New System.ComponentModel.Container
        End If
        GetContainer = components
    End Function
 
     _
    Public Shared Sub RegisterXLL(ByVal t As Type)
        AddinExpress.MSO.ADXXLLModule.RegisterXLLInternal(t)
    End Sub
 
     _
    Public Shared Sub UnregisterXLL(ByVal t As Type)
        AddinExpress.MSO.ADXXLLModule.UnregisterXLLInternal(t)
    End Sub
 
#End Region
 
#Region " Define your UDFs in this section "
 
    Friend Class XLLContainer
 
        Friend Shared ReadOnly Property _Module() As MyXLLAddin1.XLLModule
            Get
                Return CType(AddinExpress.MSO.ADXXLLModule. _
                    CurrentInstance, MyXLLAddin1.XLLModule)
            End Get
        End Property
 
    End Class
 
#End Region
 
    Public Sub New()
        MyBase.New()
 
        'This call is required by the Component Designer
        InitializeComponent()
 
        'Add any initialization after the InitializeComponent() call
 
    End Sub

    Public ReadOnly Property ExcelApp() As Excel._Application
        Get
            Return CType(HostApplication, Excel._Application)
        End Get
    End Property

End Class

Step #3 – Creating a new Excel user-defined function

Just add a new public Shared (static in C#) function to the XLLContainer class. Say, we add the following code to demonstrate all the types that Excel can pass to user-defined functions.

Friend Class XLLContainer
 
...
 
      Public Shared Function AllSupportedExcelTypes(ByVal arg As Object) _
            As String
         If (TypeOf arg Is Double) Then
            Return "Double: " + arg.ToString()
         ElseIf (TypeOf arg Is String) Then
            Return "String: " + arg
         ElseIf (TypeOf arg Is Boolean) Then
            Return "Boolean: " + arg.ToString()
         ElseIf (TypeOf arg Is AddinExpress.MSO.ADXExcelError) Then
            Return "ExcelError: " + arg.ToString()
         ElseIf (TypeOf arg Is Object(,)) Then
            Return String.Format("Array[{0},{1}]", arg.GetLength(0), _
                   arg.GetLength(1))
         ElseIf (TypeOf arg Is System.Reflection.Missing) Then
            Return "Missing"
         ElseIf (arg Is Nothing) Then
            Return "Empty"
         ElseIf (TypeOf arg Is AddinExpress.MSO.ADXExcelRef) Then
            Return "Reference: " + arg.ConvertToA1Style()
         ElseIf (TypeOf arg Is AddinExpress.MSO.ADXExcelRef) Then
            Return String.Format("Reference [{0},{1},{2},{3}]", _
                arg.ColumnFirst, arg.RowFirst, arg.ColumnLast, arg.RowLast)
         Else
            Return "Unknown Type"
         End If
      End Function
 
    End Class

Step #4 – Configuring a user-defined function (UDF)

To integrate the XLL add-in in Excel, you have to supply Excel with a user-friendly add-in name, function names, parameter names, help topics, etc. When using Add-in Express XLL add-ins, you start with categories. For a category, you add function descriptors. For a function descriptor, you add parameter descriptors. Let's go.

In the Solution Explorer window, right-click on the XLL module (XLLModule.vb or XLLModule.cs) and choose View Designer in the popup menu. You can specify the add-in name in the Properties window.

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

For the category, you specify its name. Then, you need to add a function descriptor and bind it to the UDF you created in the XLLContainer class. Just click on the Functions property of the Category component – this runs the visual designer.

For function descriptors, the properties of interest are FunctionName and IsVolatile. The former is a combo box that allows choosing a function from the list of functions defined in the XLLContainer class. As to the latter, it is set to True, and Excel will call the appropriate function whenever it recalculates the worksheet.

In the same way, you describe the arguments of the function: just select the parameter name. The property is a combo box that allows choosing a parameter from the list of parameters of a given function.

Warning! 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 Excel XLL add-in

Build and register your Excel XLL add-in (choose the Register Add-in Express Project item in the Build menu), restart Excel, and check if your newly created XLL add-in works.

Step #6 – Debugging the XLL add-in

To debug your add-in, in the Project Options window, indicate Excel in the Start External Program and run the project.

Step #7 – Deploying the XLL add-in

Just built the setup project, copy all setup files to the target PC and run the setup.exe file to install the add-in. See also Deploying Add-in Express projects.

Back to Add-in Express.NET homepage