Pieter van der Westhuizen

Creating integrated Office solutions with Add-in Express

Add-in Express enables you as a developer to quickly and easily create, not just MS Office add-ins but also Excel XLL add-ins, Excel Real-time data (RTD) servers and smart tags.

In this post I’ll demonstrate how you could include all this functionality in one Visual Studio project. Start by creating a new ADX COM Add-in project in Visual Studio.

Creating a new Add-in Express COM Add-in project in Visual Studio

Finish the wizard by selecting Visual C# as the programming language, Microsoft Office 2007 as the minimum supported Office version and Microsoft Excel, Word and Outlook as the supported applications.

COM add-in

Our add-in will share a ribbon tab amongst the three MS Office applications, so add a new ADXRibbonTab component to the AddinModule designer surface and design it to represent the following image:

Creating a shared ribbon tab for Outlook, Excel, Word using the Add-in Express in-place designer

By setting the Ribbons’ property you can choose where your UI elements should show. Set the Ribbon Tab’s Ribbons property to OutlookMailRead;OutlookContact;ExcelWorkbook;WordDocument and choose where the ribbon groups should appear by setting the Ribbons property for each:

  • OutlookRibbonGroup.Ribbons = OutlookMailRead;OutlookContact
  • ExcelRibbonGroup.Ribbons = ExcelWorkbook
  • WordRibbonGroup = WordDocument

Smart tags

We now have our shared functionality, let’s add a Smart Tag to the add-in. Right-click on the project name in the Solution Explorer and select Add > New Item from the context menu. In the Add New Item dialog window select Smart Tag Module.

Adding a Smart Tag Module to the add-in

Add-in Express will add two files to your project, MySmartTag.cs and MySmartTagImpl.cs. You’ll only need to worry about the MySmartTag.cs file for now. Double-click it to open its designer. With the designer open, right-click and select Add Smart Tag from the context menu. Change its properties to:

  • ADXTag – CountriesSmartTag
  • Caption – Countries Smart Tag
  • RecognizedWords – Belarus;Canada;South Africa;United States (Each word on its own line)

Click on the ellipses(…) button next to the Actions property and add two actions.

Adding smart tag actions

Add an event handler for the each action by adding the following code to the modules’ constructor:

populationSmartTagAction.Click += populationSmartTagAction_Click;
wikipediaSmartTagAction.Click += wikipediaSmartTagAction_Click;

Next, add the following code to the Click events of each action:

void wikipediaSmartTagAction_Click(object sender, ADXSmartTagActionEventArgs e)
{
	Process.Start("http://en.wikipedia.org/wiki/" + e.Text.Replace(" ", "_"));
}
 
void populationSmartTagAction_Click(object sender, ADXSmartTagActionEventArgs e)
{
	long populationCount = 0;
	switch (e.Text)
	{
		case "Belarus":
			populationCount= 9648533;
			break;
 
		case "Canada":
			populationCount = 34474000;
			break;
 
		case "South Africa":
			populationCount = 49991300;
			break;
 
		case "United States":
			populationCount = 308745538;
			break;
 
		default:
			break;
	}
	MessageBox.Show(String.Format("Population of {0} is : {1}", e.Text, populationCount));
}

XLL add-in

Next, we’ll include our own custom Excel function that calculates the distance between any two latitude/longitude points. To do this you’ll need to add a new XLL Add-in Module to your project by right-clicking on the project name and selecting Add > New Item from the context menu. Select XLL Add-in Module from the Add New Item window and click Add.

Adding an XLL Add-in Module

Switch to the code behind of the XLLModule and expand the “Define your UDFs in this section” region. Create the following static method within the region:

public static double Distance(double latitudeA, double longitudeA,
	double latitudeB, double longitudeB, string unit)
{
    double R = (unit == "Miles") ? 3960 : 6371;
    double dLat = toRadian(latitudeB - latitudeA);
    double dLon = toRadian(longitudeB - longitudeA);
    double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
    Math.Cos(toRadian(latitudeA)) * Math.Cos(toRadian(latitudeB)) *
    Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
    double c = 2 * Math.Asin(Math.Min(1, Math.Sqrt(a)));
    double d = R * c;
 
    return d;
}
 
private static double toRadian(double val)
{
    return (Math.PI / 180) * val;
}

Once the code for the function was created, switch back to the XLLModule’s designer surface and select Add Excel Function Category from the right-click context menu. Change the CategoryName property to GPS Functions and click the ellipses(…) button next to the FunctionDescriptors property. Add a new Function to the collection and select the function name from the dropdown list and change the Description property to “Calculates the distance between two longitude and latitude values“. With the function selected click the Add Parameter button to add parameter descriptions.

Adding Excel function and parameter descriptions

Adding function and parameter descriptions will ensure that your function is easy to find and to use within Excel. Build and register your add-in and use the following Excel formula to calculate distance between two points:

=Distance(25.7845,30.2587,26.8953,30.8973,”KM”)

RTD server

Finally, we’ll add a Real-Time Data (RTD) server to our project. Do this by adding a new RTD Server Module to your project.

Adding the RTD Server Module to the project

Open the RTDM Module designer and select Add RTD Topic from the context-menu. Set the new topic’s String01 property to *, this is required in order to pass a parameter to the RTD server and the String02 property to RandomValue. Add an event handler for the RefreshData event by double-clicking on the RefreshData event in the list of events in the property window.

Adding an event handler for the RefreshData event

Add the following code to the event handler:

private object adxrtdTopic1_RefreshData(object sender)
{
	object returnValue;
	Random random = new Random();
	int randomNumber = random.Next(0, 100);
 
	AddinExpress.RTD.ADXRTDTopic topic = (AddinExpress.RTD.ADXRTDTopic)sender;
	returnValue = String.Format("{0} : {1}", topic.String01, randomNumber);
	return returnValue;
}

After you’ve build and registered the add-in, you should be able to use the real-time data server with the following Excel formula:

=RTD(“IntegratedSolutionSample.MyRTDServerModule”,,A3,”RandomValue”)

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

Available downloads:

This sample add-in was developed using Add-in Express for Office and .net:
C# sample add-in

You may also be interested in:

Creating an Office COM add-in step-by-step
Building smart tags
Programming Excel Real-Time Data servers
Developing Excel XLL add-ins

2 Comments

  • http://0.gravatar.com/avatar/07e59c9fcf2b8ebe377254eba08a52a2?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Stan says:

    Guys did you ever discover a method to replace smarttags action menus for 2010? it was pretty cool that smarttags allowed to inject arbitrary visible contextual menus into word documents, too bad that they’re deprecated.

  • Hi Stan,

    Smart tags are deprecated in Excel 2010 and Word 2010. However, they still in use: you just need to do a right click on the word. Please read more about this in the Smart Tags article in MSDN. Also you may use the ADXRibbonContextMenu component for showing your actions dynamically (see the Dynamic property of the ADXRibbonMenu control).

Post a comment

Have any questions? Ask us right now!