Excel 2013 add-ins in Visual Studio 2012: Getting started for VSTO developers
So we’ve taken a look at how to get started with developing Office add-ins using Add-in Express when you have a Visual Studio Tool for Office (VSTO) background and How to create Outlook 2013 add-in in VS 2012.
In this article we’ll have a look at how you, as a VSTO developer, can get started developing add-ins for Excel using Add-in Express for Office and .net and Visual Studio 2012 (C#, VB.NET, C++ .NET are supported). The article covers:
- Creating the Excel add-in project
- Accessing Excel application objects and events
- Excel ribbons, command bars and context menu UI designers
- XLL add-ins, user defined functions (UDF) and Real-time data servers (RTD)
Creating the Excel add-in project in VS 2012
As with all Add-in Express projects, you start your Excel add-in project by creating a new ADX COM Add-in in Visual Studio.
A wizard will then guide you through some important steps. The first is to (a) choose which programming language you would like to write your add-in with – C#. VB.NET and C++/NET are supported; and (b) what the minimum version of Office is that it should support. For example, if you choose Microsoft Office 2003, your add-in will work with all versions of Excel from 2003 up to Excel 2013.
The second step is to choose the supported Microsoft Office application. This is unlike VSTO where your add-in can only support one Microsoft Office application at a time. Add-in Express allows you to support multiple applications from one project.
Since you only need to support Excel, you only select Excel from the list of supported applications.
Accessing Excel application objects and events
When using VSTO you can access the Excel application object via the Application object, e.g.:
this.Application
Add-in Express provides a specific component to make handling and creating an Excel event handler very easy. In order to respond to Excel events, you first need to add a new Microsoft Excel Events component to the AddinModule designer surface.
This component exposes all the available events for Microsoft Excel. To add an event handler for a specific Excel event, select the Excel events component and double-click next to the event name in the list of events in the properties window.
This will then automatically generate the event handler code inside the AddinModule.cs class for you e.g.:
public Excel._Application ExcelApp { get { return (HostApplication as Excel._Application); } }
Excel ribbons, command bars and context menu UI designers
The Add-in Express toolset is built around a collection of visual designers. With these designers you can visually design the UI for your Excel add-in.
Creating a custom Excel ribbon
VSTO does provide a basic visual ribbon designer for simple ribbon designs as well as an XML based ribbon designer for complex ribbon layouts. With Add-in Express you do not need to use XML at all, you can design all your Excel ribbon tabs using the visual designer no matter what the level of complexity is.
To create a custom ribbon tab, you first need to add its component to the AddinModule’s designer surface.
Using the visual designer you can add a variety of ribbon controls to your own custom Excel ribbon tab. Add-in Express provides access to the following Ribbon controls:
- Ribbon Group
- Ribbon Box
- Ribbon Button Group
- Ribbon Button
- Ribbon Split Button
- Ribbon Label
- Ribbon Check box
- Ribbon Edit box
- Ribbon Combo box
- Ribbon Dropdown
- Ribbon Gallery
- Ribbon Menu
- Ribbon Separator
- Ribbon Menu Separator
- Ribbon Dialog box Launcher
All the ribbon controls are accessible via their own toolbar inside the Add-in Express visual designer:
Creating a custom Excel toolbar
If you want to create toolbars for Excel 2003 and earlier, Add-in Express provides visual designers for that too. First add a Command Bar component to the AddinModule designer surface.
By setting the SupportedApps property of the Command Bar component to Excel, you tell Add-in Express to only show this particular custom command bar in Excel. You can then design it in a similar fashion as the Ribbon tab.
Add-in Express provides access to the following command bar controls:
- Command bar button
- Command bar combo box
- Command bar dropdown list
- Command bar edit box
- Command bar pop-up
Creating a custom Excel context menu
Custom context-menus can provide powerful additional functionality for Excel users. The add-in Express Context Menu component allows you to create your own context-menus for Excel. In order to use this, first add the component to the AddinModule designer.
Then design the context-menu using the built-in visual designer as well as choose which Excel context-menu your menu should be added to. Add-in Express provides a long list of all the built-in Excel context-menus which you can specify in the CommandBarName property.
XLL add-ins, user defined functions (UDF) and Real-time data servers (RTD)
Creating user defined functions and RTD servers with VSTO is simply not possible. Luckily, Add-in Express provides project and item types to easily create your own RTD Server or XLL add-in.
Creating an XLL add-in
To create an XLL add-in, create a new ADX XLL Add-in project in Visual Studio.
The XLL Add-in project also provides a visual designer surface called XLLModule. To create your own user defined functions, start by adding an Excel Function Category.
The next step would be to add functions by selecting the category and using the visual designer.
The function name is automatically assigned based up what you’ve called your method inside the XLLModule class, e.g.:
public static string AllSupportedExcelTypes(object arg) { if (arg is double) return "Double: " + (double)arg; else if (arg is string) return "String: " + (string)arg; else if (arg is bool) return "Boolean: " + (bool)arg; else if (arg is AddinExpress.MSO.ADXExcelError) return "ExcelError: " + arg.ToString(); else if (arg is object[,]) return string.Format("Array[{0},{1}]", ((object[,])arg).GetLength(0), ((object[,])arg).GetLength(1)); else if (arg is System.Reflection.Missing) return "Missing"; else if (arg == null) return "Empty"; else if (arg is AddinExpress.MSO.ADXExcelRef) { AddinExpress.MSO.ADXExcelRef reference = arg as AddinExpress.MSO.ADXExcelRef; return string.Format("Reference [{0},{1},{2},{3}]", reference.ColumnFirst, reference.RowFirst, reference.ColumnLast, reference.RowLast); } else if (arg is short) return "Short: " + (short)arg; else return "Unknown Type"; }
See How to create an Excel XLL add-in for full details.
Creating an Excel RTD server add-in
To create an RTD server add-in, you need to create a new ADX RTD Server project in Visual Studio.
The RTD Server project follows the same pattern and provides a RTDServerModule designer surface. You need to add a new RTD Topic to the designer surface by right-clicking on it and selecting "Add RTD Topic" from the context-menu.
From there on onwards, create a new event handler for the RTD topic’s RefreshData event and add your own logic.
private object adxrtdTopic1_RefreshData(object sender) { Random rand = new Random(); return rand.Next(1,1000); }
See How to create an Excel Real-Time Data server for step-by-step instructions.
So, when you need to kick things up a notch when developing Excel add-ins, you need to fire up Add-in Express and get going.
Thank you for reading. Until next time, keep coding!
Office 2013 add-ins and VS 2012: Getting started for VSTO developers
- Part 1: Office 2013 addin in VS 2012: project types and solution architecture
- Part 2: Outlook 2013 add-in development in Visual Studio 2012