How to create Excel add-in: C# and VB.NET
Excel is one of the Microsoft Office packages where probably 90% of its users know only 40% about its power and potential, Excel can be an incredibly versatile tool both for us as developers and for our customers. Personally, I’ve used Excel as a report builder for my end-users and as a tool to generate and populate test data for databases.
So, it is also about time I write a few posts about writing Excel COM add-ins, so in today’s article we’ll take a look at how to create a superb world-class Excel add-in using Add-in Express for Office and .net.
Let us get started by creating a new COM Add-in using Visual Studio 2010.
Create a version neutral Excel plug-in
If you’re a long time user of Add-in Express or reader of this blog, you probably know by now that Add-in Express makes creating a version neutral Office extension a piece of cake. The same goes for creating Excel extensions. In this example we would like our C# Excel Addin to work in Excel 2003, 2007 and 2010.
All we need to accomplish this is select Microsoft Office 2003 as the minimum supported Office version when prompted. That’s it! Add-in Express will take care of and add the correct interop assemblies to our project.
Adding Excel 2003 UI elements
Since Office 2007 moved away from tool and menu bars and replaced it with the Ribbon UI, we first need to design the Excel 2003 specific UI elements. Fortunately, Add-in Express provides us with a range of visual designers for both Office Excel 2003 and Excel 2007-2010 UI elements.
Creating a custom command bar for Excel 2003
To add your own custom toolbar, click on the ADXCommandBar button on the AddinModule designs’ toolbar.
With the command bar component selected you can use the built in Add-in Express visual designer to design your own custom command bar. I’ve designed a simple Excel command bar that contains a button, dropdown list and a command bar popup control.
Customize an existing Excel command bar
Add-in Express makes it very easy to add your own controls to one of the built-in command bars in Excel. All the command bars in Excel are identified by their names. To add your own controls, you need to add an ADXCommandBar component to the AddinModule designer and change its CommandBarName property to the name of the built-in command bar.
To get the name of the built-in controls, Add-in Express has a very useful and free tool called the Built-in Controls Scanner. As you can see in the image below the command bar name is Picture.
In order to add your own button to the Picture toolbar in Excel, you need to set your ADXCommandBar components’ CommandBar property to Picture, design your UI components and register and run the project.
You will then see a new button on the built-in Excel Picture toolbar.
Connect to a built-in Excel control
In the above example we’ve added our own button the built-in Excel 2003 Picture toolbar. But, what if you would like to prompt the user from where he/she would like to insert a picture when they click the existing Insert Picture From File button?
Once again, Add-in Express makes this an easy task. Using the Built-in Controls Scanner, we can get the built-in buttons’ Office ID, which in this case is 2619. Armed with this information, we can add an ADXBuiltInControl to the AddinModule designer surface.
Set its Id property to 2619 and add an event handler for its ActionEx event:
private void adxBuiltInControl1_ActionEx(object sender, ADXCancelEventArgs e) { e.Cancel = true; MessageBox.Show("Where would you like to insert the Image from?"); }
Customizing Excel 2003 main menu
For most Excel add-ins, you need to add their own menu to the Excel main menu bar. Add-in Express provides the ADXMainMenu component to help you visually add and design your custom main menu. Once you’ve added a new ADXMainMenu component to the AddinModule designer surface, set its SupportedApps property to Excel and CommandBarName property to Worksheet Menu Bar. Next, add a command bar popup control to the menu and set its BeforeID property to 30010. 30010 is the id of the Help menu item, ideally we want our custom menu item to appear just before the built-in Help menu and setting the BeforeID property accomplishes this. If you do not specify either the AfterID or BeforeID property your menu item will be added as the last item on the Excel menu bar.
And as easy as that you can start to build your Excel 2003 add-in. In my next post, we’ll take a look at customizing the Excel 2007 and 2010 UI as well as taking a closer look at some of the events exposed by the Microsoft Excel Object model.
Thank you for reading. Until next time, keep coding!
Available downloads:
This sample Excel COM Addin was developed using Add-in Express for Office and .net: