Creating a Visual Basic Office Add-in

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Add-ins provide extended features or services for an application that hosts it. Add-ins can automate certain tasks in its host application, such as a Microsoft Office product. And, add-ins can customize the user interface (UI) of its host application.

Visual Studio Tools for Office

Visual Studio Tools for Office (VSTO) is a Visual Studio add-in that exposes a set of tools, plus a runtime that allows MS Office applications (from 2003 upwards) to host the .NET Framework Common Language Runtime to expose their functionality via .NET. This runtime is known as VSTO Loader. VSTO Loader has no managed dependency and is the only component of the VSTO Runtime engine that is always running.

VSTO Add-ins

By creating a VSTO add-in, you create a managed code assembly that is loaded by a Microsoft Office application. The add-in then can respond to events that are raised in the application. The add-in is also able to make use of any of the classes in the .NET Framework.

VSTO Add-in Components

There are several components that enable Microsoft Office applications discover and load VSTO Add-ins. These are:

Registry Entries

MS Office applications discover the VSTO add-ins by looking for a set of registry entries. These entries are as follows:

All Users

32-bit: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\application name\Addins\add-in ID

64-bit: HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Office\application name\Addins\add-in ID

Current User

32-bit: HKEY_CURRENT_USER\Software\Microsoft\Office\application name\Addins\add-in ID

64-bit: HKEY_CURRENT_USER\Software\Wow6432Node\Microsoft\Office\application name\Addins\add-in ID

Deployment Manifest: Points to the current application manifest.

Application Manifest: Points to the add-in’s assembly as well as specify the entry point to execute from the assembly.

Visual Studio Tools for Office Runtime: End-user computers must have the Visual Studio Tools for Office runtime installed.

Our Project(s)

You will be creating three very basic VSTO add-ins today, one each for MS Word, MS Excel, and MS PowerPoint. Let’s get started.

Creating a Word VSTO Add-in

Start Visual Studio and click File, New Project.

1. Select Visual Basic under Templates.
2. Select Office/SharePoint.
3. Select Word version Add-In.

Figure 1 displays the New Project dialog box, showing some of the available VSTO add-in templates.

Figure 1: VSTO add-in templates

You will notice that your Solution Explorer looks a bit different, as shown in Figure 2. This is obviously because you are creating a whole different type of project. Double-click the ThisAddIn Class and add the following code into it:

Public Class ThisAddIn

   Private Sub ThisAddIn_Startup() Handles Me.Startup

   End Sub

   Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

   End Sub

   Private Sub Application_DocumentBeforeSave(ByVal Doc As Word.Document, _
      ByRef SaveAsUI As Boolean, _
      ByRef Cancel As Boolean) Handles Application.DocumentBeforeSave
         Doc.Paragraphs(1).Range.InsertParagraphAfter()
         Doc.Paragraphs(1).Range.Text = "Entered After Save"
         'Doc.Paragraphs(1).Range.InsertBreak()'

         'Doc.Paragraphs(2).Range.InsertDateTime()'

   End Sub

End Class

Figure 2: Solution Explorer

This is very basic. A new event named Application_DocumentBeforeSave is added. This event fires as soon as a Save method has been invoked inside the host. The preceding code simply inserts a paragraph after the first paragraph and adds a little text. The commented lines demonstrate how easily you can add a separate page break or the automatic date and time into the document that is being saved. Here is more information on the Word.Document.

This will not work yet.

You can run your application and it will open up MS Word where you could test the functionality. To add the add-in to Word, follow these steps:

1. Click File.
2. Click Options.
3. Click Add-Ins.

In the Manage drop-down menu, select COM Add-Ins and click Go. The name of your Add-in should be listed, as shown in Figure 3. Then, you click OK.

Figure 3: COM add-in

Creating an Excel VSTO Add-in

Creating an Excel add-in is also straightforward. You could simply repeat the steps I listed earlier and select an Excel add-in instead. Once loaded, add the following code:

Private Sub ThisAddIn_Startup() Handles Me.Startup

   End Sub

   Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

   End Sub

   Private Sub Application_WorkbookBeforeSave(ByVal Wb As _
      Microsoft.Office.Interop.Excel.Workbook, _
      ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) _
      Handles Application.WorkbookBeforeSave

      Dim ExcelWorksheet As Excel.Worksheet = CType(Application.ActiveSheet, _
         Excel.Worksheet)
      Dim ExcelRow As Excel.Range = ExcelWorksheet.Range("B9")
      ExcelRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown,
         Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow
      )
      Dim InsertedRow As Excel.Range = activeWorksheet.Range("B15")
      InsertedRow.Value2 = "Text"
   End Sub

End Class

This code inserts a row underneath Row 9 and it keeps the formatting; then, it simply adds a little text to Cell B15. Here is more information on the Excel Worksheet object.

Let MS Excel recognize the add-in in the same manner that you added the MS Word add-in.

Creating a PowerPoint VSTO Add-in

Add the following code into your PowerPoint add-in project:

Public Class ThisAddIn

   Private Sub ThisAddIn_Startup() Handles Me.Startup

   End Sub

   Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

   End Sub

   Private Sub Application_PresentationNewSlide(ByVal Sld As _
      PowerPoint.Slide) _
      Handles Application.PresentationNewSlide
      Dim TextBox As PowerPoint.Shape = Sld.Shapes.AddTextbox( _
      Office.MsoTextOrientation. msoTextOrientationDownward, _
         0, 0, 500, 50)
      TextBox.TextFrame.TextRange.InsertAfter("TextBox Text.")
   End Sub
End Class

This code simply adds a Textbox to a new slide when a New slide event has been fired and then adds some text in a downwards direction into the Textbox. Here is more information on the PowerPoint.Slide object.

Conclusion

Knowing how to create Microsoft Office add-ins is quite handy. Although this is just the tip of the iceberg, it is good place to start your adventures with add-ins.