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.

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
• Adding an XLL add-in
• Adding an RTD server

Creating an Excel COM Add-in

As always we start by 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.

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

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.

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

More about Excel plug-in development:

• Creating COM add-in for Excel 2010 /2007
• Writing Excel COM add-in in C# (for Excel 2003)

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.

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.

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

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.

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.

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.

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.

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.

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:

• Integrated Excel project in C#

You may also be interested in:

• Video: Developing Excel COM add-ins
• Video: Creating managed Excel UDFs – XLL based user-defined functions
• Video: Building an Excel RTD server