Pieter van der Westhuizen

Creating an Office COM add-in: getting started for VB developers

In today’s article I’ll take a break from my usual C# and return to my roots as a VB developer. We’ll take a whirlwind trip through some of the features of Add-in Express and how to use it using Visual Studio 2010 and Visual Basic.Net

Getting Started

Let’s start by creating a new ADX COM add-in, which is available under Installed Templates> Other Project Types> Extensibility.

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

Click OK and you’ll be presented with the New Microsoft Office COM Add-in wizard. On the first screen you can select your programming language, which in our case will be Visual Basic. You should also choose which version of Office will be the minimum version your add-in supports. Bear in mind that certain features are only available in newer versions of Office, so choose carefully (for more info, please see Supporting several Office versions in an add-in. Interop assemblies and late binding.). In our scenario we’ll choose Microsoft Office 2007.

Selecting Visual Basic as a programming language and Office 2007 as the minimum supported version

Click Next. Add-in Express allows you to target multiple Microsoft Office applications with one add-in. We’ll select Microsoft Excel, Word and Outlook as the supported applications.

Selecting Microsoft Excel, Word and Outlook as supported applications

Click Next. You can choose to generate a new strong name file or use an existing one. Choose Generate new for now. If you need to use another string name file later on, you can select it in your projects’ properties Signing tab.

Choosing to generate a new strong name file

Click Finish to complete the wizard, the Add-in Module Designer should then open automatically, if not, open it by double-clicking on AddinModule.vb in the Solution Explorer.

Need Help and the latest version?

Add-in Express added a Help panel at the bottom of the AddinModule designer surface to help you quickly access the wealth of help and resources on their website. You can also use it to quickly check which version of Add-in Express you’re using.

Help panel

Adding UI elements

Add a ribbon tab by clicking on the ADXRibbonTab button Ribbon tab button on the designer toolbar. Select the ribbon tab component and expand the designer by clicking on the two up arrows. Using the designer toolbar, add three ribbon groups (ADXRibbonGroup) and also add a button to each group.

Adding three ribbon groups

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

By setting the Ribbons’ property you can choose where your UI elements should show. In the above example the Outlook group will only show in Outlook when an e-mail is read and when the user opens a Contact item.

Using the Office objects

Now that we have all the necessary UI elements, let’s have a look at the code behind the scenes. Switch to the Add-in Module’s code by clicking on the View Code button on the Solution Explorer toolbar. You’ll see the three important properties we need in order to gain access to the MS Office objects:

Public ReadOnly Property ExcelApp() As Excel._Application
            Return CType(HostApplication, Excel._Application)
        End Get
    End Property
    Public ReadOnly Property WordApp() As Word._Application
            Return CType(HostApplication, Word._Application)
        End Get
    End Property
    Public ReadOnly Property OutlookApp() As Outlook._Application
            Return CType(HostApplication, Outlook._Application)
        End Get
    End Property

Each of these properties exposes the running MS Office application and allows you to gain access to it. Add an event handler for the OutlookRibbonButton‘s OnClick event by selecting it in the code-behind object dropdown, and selecting the OnClick event in the event dropdown.

Adding an event handler for the OutlookRibbonButton's OnClick event

This will automatically generate the event handler code for you. Add the following code to it:

Private Sub OutlookRibbonButton_OnClick _
		(sender As Object, control As AddinExpress.MSO.IRibbonControl, pressed As Boolean) _
		Handles OutlookRibbonButton.OnClick
        Dim currentInspector As Outlook.Inspector = TryCast(OutlookApp.ActiveInspector, _
        If currentInspector IsNot Nothing Then
            Dim item as Object = currentInspector.CurrentItem
            If TypeOf (item) Is Outlook.MailItem Then
                Dim currentMailItem As Outlook.MailItem = TryCast(item, Outlook.MailItem)
                MessageBox.Show("The e-mail subject is : " & currentMailItem.Subject)
            ElseIf TypeOf (item) Is Outlook.ContactItem Then
                Dim currentContactItem As Outlook.ContactItem = TryCast(item, _
                MessageBox.Show("The contact is : " & currentContactItem.FullName)
            End If
        End If
    End Sub

The code will check the type of the current Inspector item, and show a messagebox specific to the item type. Next, add an event handler for the Excel button in a similar fashion as above and add the following code:

Private Sub ExcelRibbonButton_OnClick _
        (sender As Object, control As AddinExpress.MSO.IRibbonControl, pressed As Boolean) _
        Handles ExcelRibbonButton.OnClick
        Dim currentSheet As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, _
        currentSheet.Cells(1, 1).Value = "Process Id"
        currentSheet.Cells(1, 2).Value = "Process Name"
        currentSheet.Range("A1", "B1").Font.Bold = True
        Dim rowCount As Integer = 2
        Dim processList() As Diagnostics.Process
        processList = Diagnostics.Process.GetProcesses()
        For Each process As Diagnostics.Process In processList
            currentSheet.Cells(rowCount, 1).Value = process.Id
            currentSheet.Cells(rowCount, 2).Value = process.ProcessName
            rowCount += 1
    End Sub

This code will loop through a list of running processes on your system and populate the active worksheet in the Excel workbook. Finally add an event handler for the Word ribbon button, add the following code to it:

Private Sub WordRibbonButton_OnClick _
        (sender As Object, control As AddinExpress.MSO.IRibbonControl, pressed As Boolean) _
        Handles WordRibbonButton.OnClick
        Dim currentDoc As Word.Document = TryCast(WordApp.ActiveDocument, Word.Document)
        Dim myTable As Word.Table = currentDoc.Tables.Add(currentDoc.Range, 1, 2, _
			Nothing, Nothing)
        myTable.Cell(1, 1).Range.Text = "Process Id"
        myTable.Cell(1, 1).Range.Font.Bold = True
        myTable.Cell(1, 2).Range.Text = "Process Name"
        myTable.Cell(1, 2).Range.Font.Bold = True
        Dim rowCount As Integer = 2
        Dim processList() As Diagnostics.Process
        processList = Diagnostics.Process.GetProcesses()
        For Each process As Diagnostics.Process In processList
            myTable.Cell(rowCount, 1).Range.Text = process.Id
            myTable.Cell(rowCount, 1).Range.Font.Bold = False
            myTable.Cell(rowCount, 2).Range.Text = process.ProcessName
            myTable.Cell(rowCount, 2).Range.Font.Bold = False
            rowCount += 1
    End Sub

The above code will perform a similar task as the Excel button, but this time it will add the list of running processes to a MS Word table.

Testing your add-in

Once you’re satisfied with your UI and code logic, build your project by selecting Build Solution from the Visual Studio Build menu. After the project has been built successfully, select Register ADX Project from the same Build menu, this will register your Add-in for use by MS Office. After the project has been registered, run your project by pressing F5. If you want to change the MS Office application you wish to test with, go to your projects’ property pages and on the Debug tab set the Start external program value to the desired MS Office application.

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

Available downloads:

This sample add-in was developed using Add-in Express 2010 for Microsoft Office and .net
VB.NET sample Office add-in

Post a comment

Have any questions? Ask us right now!