Pieter van der Westhuizen

Building integrated Excel extensions: COM Add-in, RTD Server and XLL in one C# project

Add-in Express for Office and .net makes building Microsoft Excel extensions easy. We provide you with a collection of tools and components to get that world-class Excel add-in out of the door and into the world in record time.

Add-in Express gives you Visual Studio project templates to create Excel COM Add-ins, RTD Servers and XLL add-ins.

Add-in Express project templates to create COM Add-ins, RTD Servers and XLL add-ins

But, did you know you can combine all these features into one single Visual Studio project? In today’s post I’ll take you through the steps of creating a single Visual Studio project that contains an Excel COM add-in, RTD server and a number of XLL UDFs (User-defined functions):

Creating an Excel COM Add-in

As always we start by creating a new COM Add-in project in Visual Studio.

Creating a new COM Add-in project in Visual Studio

Select your language of choice, we choose C#,  and the minimum supported version of Office.

Selecting C# as the programming language and Office 2007 as the minimum supported Office version

Since this article is about Excel extensions, we’ll choose Microsoft Excel as the supported application.

Selecting C# as a programming language and Office 2007 as the minimum supported Office version

After the new Microsoft Office COM Add-in is completed, the AddinModule designer surface will automatically open. From here we can add a number of Office components but for this example we’ll only add our own Ribbon Tab to Excel.

Adding a custom ribbon tab to Excel

Add your own Ribbon tab, by clicking on the ADXRibbonTab button on the AddinModule designer.

Click on the ADXRibbonTab component to add a custom ribbon tab.

Using the built-in designer toolbar you can quickly build complex Ribbon Tab layouts.

Use a visual designer to build your Ribbon Tab layouts.

More about Excel plug-in development:

Adding an XLL add-in

An XLL add-in can contain a number of User-defined functions; UDFs are used to build custom functions in Excel for the end user to use them in formulas.

Add-in Express enables you to build powerful Excel extensions that are not only a COM add-in but can also contain a number of UDFs. This enables you to provide your users with a single setup program to install as well as being able to share application settings across different features.

To add an XLL to your COM add-in, add a new item to you project, by selecting Add New Item… from the Visual Studio Project menu.

Select the XLL Add-in Module project item, which you can find under Add-in Express Items > Excel and click the Add button.

Adding an XLL module to the COM add-in project

This will add a new XLL module class to your project. Once the item is added, switch to its code behind.

You should see a region “Define your UDFs in this section” as well as a region called “Sample function”. Now add the following method underneath the Sample function region:

public static string RandomName(string gender)
{
    string[] males = new[] { "Kenton", "Rich", "Tristan", "Travis",
		"Geoffrey", "Wilburn", "Roland", "Isiah", "Glen", "James" };
    string[] females = new[] { "Maurita", "Christel", "Hilma", "Teisha",
		"Natalya", "Trudi", "Latoya", "Gigi", "Hulda", "Belinda" };
    Random random = new Random();
 
    if (gender.ToLower() == "m")
    {
        return males[random.Next(males.Length)];
    }
    return females[random.Next(females.Length)];
}

Next, switch back to the XLLModule‘s designer surface and right-click on it and select Add Excel Function Category from the context-menu.

Selecting Add Excel Function Category from the context-menu

Select the new category and set its CategoryName property to My Random Data functions.

Selecting the new category and setting its CategoryName property

Next, using the built-in designer, add a new Function descriptor. Select the RandomName function for the FunctionName property and set the Description property to Generates a random male or female first name.

Selecting the new function descriptor and setting its FunctionName and Description properties

Add a parameter to the function descriptor by clicking the Add Parameter button on the built-in designer toolbar and set its Description property to Enter m for a male name or f for a female name and select the gender parameter from the list of options for the ParameterName property.

Build and register your project and you should see the RandomName formula as well as its description and parameter description in Excel.

The RandomName formula, its description and parameter description in Excel

If you want to learn more about XLL add-ins, you can find an end-to-end sample here: Creating Excel XLL add-ins.

Adding a Real-Time Data (RTD) server

Real-time data (RTD) servers gives you a powerful way to build Excel dashboards that update its data based on its own schedule.

To add a new Excel RTD Server to our project, select Add New Item… from the Visual Studio Project menu. Select the RTD Server Module project item and click Add.

Adding an Excel RTD Server module to the project

Once the RTDServerModule class has been added to your project, keep its Interval property at 5000. Next, we need to add an RTD topic, to do this, right-click on the RTDServerModule designer surface and select Add RTD Topic from the context menu.

Adding an RTD topic

Select the RTD Topic component and set its String01 property to * and its String02 property to Genders. Next, create a new event handler for the RefreshData event by double-clicking next to its name in the property grid.

Creating a new event handler for the RefreshData event

Add the following code to the new event handler:

private object adxrtdTopic1_RefreshData(object sender)
{
    string[] males = new[] { "Kenton", "Rich", "Tristan", "Travis",
		"Geoffrey", "Wilburn", "Roland", "Isiah", "Glen", "James" };
    string[] females = new[] { "Maurita", "Christel", "Hilma", "Teisha",
		"Natalya", "Trudi", "Latoya", "Gigi", "Hulda", "Belinda" };
    ADXRTDTopic topic = (ADXRTDTopic)sender;
    Random rand = new Random();
    if (topic.String01.ToLower() == "m")
    {
        return males[rand.Next(males.Length)];
    }
    return females[rand.Next(males.Length)];
}

Build and run your project. To use our RTD server in Excel use the following formula:

=RTD(“IntegratedExcelExt.RTDServerModule”,,A1,”Genders”)

Depending on whether the user enters either an M or F in the A1 cell of Excel, the RTD server will return a random male or female name every 5 seconds. You can find more about RTD servers here: Creating Excel Real-Time Data server.

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample Excel COM Add-in was developed using Add-in Express for Office and .net:

You may also be interested in:

2 Comments

  • http://1.gravatar.com/avatar/303bb783b01e7e9838c1fcc6f95c2fd5?s=32&d=http%3A%2F%2F1.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Henri Pellemans says:

    If you want to use the UDFs of the XLL module in the Addin module, just add the following code to the Addin module:

    [VB.NET]

    Imports MyAddin1.XLLModule1.XLLContainer

    [replace MyAddin1 and XLLModule1 with your own names]

  • http://0.gravatar.com/avatar/ab4ec2858cfdf1e44dadf8c50fae314d?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Dmitry Kostochko (Add-in Express Team) says:

    Hi Henri,

    Thank you for your suggestion. A nice trick!

Post a comment

Have any questions? Ask us right now!