Automating Microsoft Office Applications - VSTO Traps and Technicalities

This article is the third page of the Chapter 13 lesson of the About Visual Basic "Complete Course" for VB.NET. Sign up for the entire course at ... this signup page

VSTO presents technical traps for the unwary right from the initial installation. You must install Visual Studio.NET 2003 first because VSTO requires some internal components of VS.NET. Then, you should install Office 2003 next so the VSTO install can configure itself using those systems. Finally, you install VSTO last. If you do it in the wrong order, VSTO very likely won't work.

Once you get VSTO working, you discover more technical traps. Although VSTO gives you full access to the Word and Excel object models and IntelliSense statement completion for those models, the IntelliSense suggestions for VSTO might not work with the IntelliSense suggestions for VB.NET and this can lead to some confusion. For example, in the example "Hello About Visual Basic" program below, you might try assigning the color Red to the text in the Excel spreadsheet with a statement like this. (I did.)

myRange.Font.Color = System.Drawing.Color.Red

Intellisense suggests methods and properties for both the left and right sides of the statement. No syntax errors are created and when you run the code, no errors are shown. But it doesn't work. The problem is that the left side is the Excel object model and the right side is the VB.NET object model. And when a run time error is generated by VB.NET, Excel COM (which is actually in control and calls the VB.NET Assembly) just ignores the error.

It's like being at a party with both English and Americans. If someone says, "I've got rocks in my boot and I can't raise my bonnet!" You still don't know for sure whether the subject is automobiles or wearing apparel. Furthermore, both sides of the conversation are very polite and nobody speaks up and says, "Hey! I'm not sure I understand that."

Here's a very simple "Hello About Visual Basic" application built with VSTO and Excel just to give you a flavor of how VSTO works. When you run it, you get the result shown when you click Here. Click the "Back" button in your browser to return.

This example, by the way, is an adaptation of an example for VSTO provided by Microsoft MSDN but spiced up a little to show the manipulation of properties and Range selections. Notice also that the VSTO code is enclosed in a Try ... Catch block. That's the only way you can detect an error with VSTO - even in Debug mode. Although this code is left justified here, copy and paste it into Visual Studio .NET and it will be formatted correctly.

Imports System.Windows.Forms
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports MSForms = Microsoft.Vbe.Interop.Forms
' Office integration attribute.
' Identifies the startup class for the workbook.
' Do not modify.

<Assembly: System.ComponentModel.DescriptionAttribute( _
"OfficeStartupClass,Version=1.0,Class=ExcelProject1.OfficeCodeBehind")>

Public Class OfficeCodeBehind
Friend WithEvents ThisWorkbook As Excel.Workbook
Friend WithEvents ThisApplication As Excel.Application

#Region "Generated initialization code"

' Called when the workbook is opened.
Private Sub ThisWorkbook_Open() _
Handles ThisWorkbook.Open
' Visual Basic
Dim str As String
str = "About Visual Basic"
Dim mySheet As Excel.Worksheet
Dim myRange As Excel.Range
Try mySheet = _
CType(Me.ThisApplication.Sheets.Item(1), _
Excel.Worksheet)
CType(mySheet.Cells.Item(2, 2), Excel.Range).Value _
= "Hello From"
CType(mySheet.Cells.Item(3, 2), Excel.Range).Value _
= "About Visual Basic"
myRange = mySheet.Range("B2:B3")
myRange.Font.ColorIndex = 3
myRange.Font.Bold = True
myRange.Font.Size = 18
Catch ex As Exception
MsgBox("something didn't work: " & ex.Message)
End Try
End Sub
End Class

After all this, you might think that VSTO should be avoided at all costs. Not at all. Like nearly every new Microsoft technology, the rough edges in version 1 will get smoothed off in later versions until it shines like a pearl. And it does work today! Now is the time to start your transition if you have existing VBA code, or to start learning the technology. This one is a keeper.