Chapter 4: Moving from VBA to VSTO and Visual Basic

This article is an excerpt from VSTO for Mere Mortals™: A VBA Developer's Guide to Microsoft Office Development Using Visual Studio 2005 Tools for Office by Kathleen McGrath and Paul Stubbs from Addison-Wesley Professional (ISBN 0-321-42671-1, copyright Pearson Education, Inc. 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

The great thing in this world is not so much where you stand, as in what direction you are moving.

               —OLIVER WENDELL HOLMES

Microsoft Visual Studio 2005 Tools for Office (VSTO 2005) supports two programming languages: Visual C# and Visual Basic 2005. If you’re planning to move from Visual Basic for Applications (VBA) to managed code, it might not make a difference which language you choose, and it might even be in your best interest to learn both. However, developers who are familiar with C++ would probably find it easier to learn C#, because there are many similarities between the languages. For example, C++ and Visual C# share a similar syntax. Similarly, VBA developers might find it easier to move to Visual Basic 2005.

It’s challenging enough to learn to use a new integrated development environment, debugging tools, the .NET Framework class libraries, the .NET Framework security model, and object-oriented programming concepts without having to learn a completely different programming language. Additionally, if you migrate your existing VBA solutions to VSTO and plan to use any migration tools, such as the Visual Basic Migration Wizard, these tools would most likely convert the VBA code directly to Visual Basic 2005. You would have to take additional steps to then convert the code into Visual C#. For more information about migrating VBA solutions to VSTO, see Chapter 12.

For these reasons, we believe it makes sense to make the move from VBA to Visual Basic 2005 rather than to Visual C#. Once you are more familiar with programming Office applications using VSTO, you can transfer those skills if you decide to program with C#.

Although there are many similarities between VBA and Visual Basic 2005, there are several differences. These differences range from language changes such as data types and array bounds to new features of Visual Basic 2005, such as the My objects and IntelliSense code snippets. You also must learn about the differences between ActiveX controls and Windows Forms controls. In this chapter we describe these differences and introduce you to some of the new features in Visual Basic 2005.

Contents

• New Features of Visual Basic 2005

• Language Differences of VBA and Visual Basic 2005

• UserForms Versus Windows Forms

• Summary

• Review Questions

• Additional Resources

New Features of Visual Basic 2005

Many new features in Visual Basic 2005 help increase your productivity. Some of these features are enhancements to the IDE, such as improved IntelliSense capabilities, the ability to use preinstalled code snippets, and debugger enhancements such as Edit and Continue, the Exception Assistant, and Just My Code. (These new IDE features are described in Chapter 2.) Other new features include the .NET Framework enhancements that are supported by Visual Basic 2005 (such as partial classes and generics) and enhancements to Visual Basic 2005 (such as the new My objects and the ability to create IntelliSense code snippets).

The My Objects

The new My objects give you easy access to application, system, and user information without having to search through the .NET Framework class libraries to locate equivalent functionality. Instead, if you type My followed by a period, IntelliSense displays all the members of the My objects that are available to VSTO solutions. Table 4.1 describes these objects as well as those that are not available.

Table 4.1 The My Objects in Visual Basic 2005

My Object	Description
My.Application	Enables you to access information about the application. For example, you can change the culture of your application to English (US) by using My.Application.ChangeCulture(“en-US”).
My.Computer	Enables you to set properties for computer components such as the file system and the Clipboard. For example, you can clear the Clipboard by using My.Computer.Clipboard.Clear(). You can also retrieve information about the computer, such as its name or operating system.
My.Resources	Provides read-only access to resources in your application. For example, you can retrieve a string resource by referencing the name of the string. If you have a string resource named ControlName, using My.Resources.ControlName retrieves the value of the string.
My.Settings	Provides access to application settings. For example, you can reset the application settings to their default values by using My.Settings.Reset().
My.User	Gives you access to information about the current user. For example, you can check the name of the current user by using the My.User.Name property.
My.WebServices	Provides an instance of every Web service that is currently referenced in the project. You can call a function within a Web service by using My.WebServices.
My.Forms	Not available in VSTO projects.
My.Log	Not available in VSTO projects.
My.Request	Not available in VSTO projects.
My.Response	Not available in VSTO projects.

One example of using a My object is to access the name of the current user. Note, however, that VSTO does not set My.User by default. You must first call InitializeWithWindowsUser. The code in Listing 4.1 displays the current user in a message box.

Listing 4.1 Displaying the user’s login name in a message box

My.User.InitializeWithWindowsUser()
MsgBox(CStr(My.User.Name))

In this section you will create a simple Word application that demonstrates some of the My object features.

1. On the File menu, point to New and then click Project.

2. Select a Word document in the Templates pane, leaving all the default settings in the New Project dialog box. Click OK to create a new document project.

3. In the Visual Studio Tools for Office Project Wizard, select Create a New Document, and then click OK.

4. Drag a combo box and list box from the Toolbox to the document, and resize the controls so that it looks similar to the document shown in Figure 4.1.

   Figure 4.1. Document with combo box and list box controls

   

5. In Solution Explorer, right-click ThisDocument.vb and select View Code. The Code Editor opens and displays two default event handlers: Startup and Shutdown.

6. Replace the code in the Startup event handler with the code in Listing 4.2. This code adds entries to the combo box when the document first opens.

   Listing 4.2. Adding entries to the combo box

   Private Sub ThisDocument_Startup(ByVal sender As Object, _
     ByVal e As System.EventArgs) Handles Me.Startup
   
     With Me.ComboBox1.Items
       .Add("Ports")
       .Add("Drives")
       .Add("Loaded Assemblies")
     End With
   End Sub
   

7. Add the code in Listing 4.3 to the ThisDocument class. This code uses the My objects to populate the list box with information about the system drives and ports, along with the assemblies that are loaded when the application is running.

   Listing 4.3. Using the My objects to obtain system information

   Private Sub ComboBox1_SelectedValueChanged(ByVal sender _ 
     As Object, ByVal e As System.EventArgs) Handles _ 
     ComboBox1.SelectedValueChanged
   
       Select Case ComboBox1.SelectedItem
         Case "Loaded Assemblies"
           Dim i As Integer = 0
           Dim TotalAssemblies = My.Application.Info. _
             LoadedAssemblies.Count
           Me.ListBox1.Items.Clear()
           For i = 0 To TotalAssemblies - 1
             Me.ListBox1.Items.Add(CStr(My.Application.Info. _
               LoadedAssemblies(i).FullName))
           Next
         Case "Drives"
           Dim i As Integer = 0
           Dim TotalDrives = My.Computer.FileSystem.Drives.Count
           Me.ListBox1.Items.Clear()
   
           For i = 0 To TotalDrives – 1
             Me.ListBox1.Items.Add(CStr(My.Computer. _
               FileSystem.Drives(i).Name))
           Next
         Case "Ports"
             Dim i As Integer = 0
             Dim TotalPorts = My.Computer.Ports. _
               SerialPortNames.Count
             Me.ListBox1.Items.Clear()
               For i = 0 To TotalPorts - 1
                 Me.ListBox1.Items.Add(CStr(My.Computer. _
                   Ports.SerialPortNames(i)))
               Next
       End Select
   End Sub
   

8. Run the code by pressing F5.

The code is built and the Word application opens. When you select Ports in the combo box, the list box displays the name of the ports on your system. When you select Drives, the list box displays all the available drives. The Loaded Assemblies option displays all the assemblies that the application has loaded on your system, as shown in Figure 4.2.

Figure 4.2. Displaying the assemblies loaded by the application

IntelliSense Code Snippets

IntelliSense code snippets are a new .NET Framework feature supported by Visual Basic 2005. The IntelliSense code snippets are saved in XML files and become available in the Visual Studio IDE when you right-click the Code Editor and select Insert Snippet.

Visual Studio comes with a number of preinstalled snippets. When you select a code snippet to insert, an entire code block is inserted into the Code Editor. You can then customize the code for your application. For example, you can change the highlighted variable names to names you have defined in your application. Chapter 2 describes the default code snippets and the additional snippets you can download from www.microsoft.com.

You can insert an IntelliSense code snippet into the Code Editor in a number of ways. You can right-click the Code Editor, select Insert Snippet, and then navigate through the folders to the desired snippet. Or you can type the snippet’s shortcut name and press the TAB key.

Another technique is to type the first few letters of the shortcut name, followed by a question mark (?), and then press the TAB key. A list of all the shortcuts that begin with the letters you typed appears in a dropdown list, and you can select the appropriate snippet. In addition to numerous preinstalled Visual Basic 2005 and VSTO code snippets that ship with Visual Studio, you can search for additional code snippets on the Internet, and you can create your own code snippets.

You create your own IntelliSense code snippets by creating an XML file that has a .snippet extension. You might find it useful to create these snippets to store frequently used functions as a type of function library for your projects. Instead of having to manually type the code or use the Toolbox as temporary storage for code snippets, you can create IntelliSense code snippets and then quickly add complete procedures to your code. It’s similar to storing autotext entries as you do in Word, but here, you insert them into the Code Editor. If you’re interested in creating your own XML code snippets, you can find a description of how to manually create a simple code snippet in Appendix A.

Statements

Two new statements in Visual Basic 2005 make coding tasks easier: the Continue statement and the Using statement.

Continue Statement

You can use the Continue statement in a Do loop, a For loop, or a While loop to transfer execution to the next iteration within the loop. Listing 4.4 shows how to use a Continue statement to identify the number of styles in use in a Word document.

Listing 4.4. Using a Continue statement in a For loop

Sub CountStyles()
    Dim styleCount As Integer = 0

    For Each currentStyle As Word.Style In Me.Styles
        If currentStyle.InUse = False Then Continue For
        styleCount += 1
    Next
    MsgBox("Total styles: " & Me.Styles.Count & vbCrLf & _
                "Styles available: " & styleCount)
End Sub

In this code example, you use the += operator to add a value to a numeric variable and then assign the result to the variable. Using VBA, you type this:

styleCount = styleCount + 1

Using Visual Basic 2005, you can type the following to get identical functionality:

styleCount += 1

Using Statement

You can use a Using statement to ensure that unmanaged resources are disposed of properly. Recall from Chapter 3 that you do not need to track and manage the memory resources for your application because the garbage collector reclaims any memory that is no longer being used. However, if you want to ensure that unmanaged resources are properly disposed of, or if you are using a managed resource that uses a lot of memory, you can use a Using block to ensure that the resource is disposed of whenever the block is exited.

In the Using block, you specify the resources that you want to manage. You use the End Using statement to indicate that you are no longer using the resource and that the system can dispose of any resources that were controlled by the Using block. Listing 4.5 shows how to use the Using statement with a SQL database. You should always close a SQLConnection by calling Close or Dispose, but when you declare the connection within a Using statement, it ensures that the Dispose method is called for you. The example assumes that the string Connection is a valid connection string.

Listing 4.5. A Using block manages a resource.

Sub SampleCode(ByVal Connection As String)
    Using sqlConnection As New System.Data.SqlClient. _
        SqlConnection(Connection)

        'Add code to open connection and work with data.
    End Using
End Sub

Operators

Visual Basic 2005 introduces a new IsNot operator. Also, there are two logical operators introduced in an earlier version of Visual Basic .NET that you might not be familiar with: the AndAlso and OrElse operators.

IsNot Operator

The IsNot operator lets you compare two object references. This operator returns False if both objects refer to the same object; otherwise, it returns True. This is equivalent to using Not with the Is operator, but it makes your code easier to read. In Listing 4.6, both If statements behave in the same way, but the If statement that uses the IsNot operator is much easier to read.

Listing 4.6. Comparing the Is and IsNot operators

Dim explorer As Outlook.Explorer = Me.ActiveExplorer()

If Not explorer Is Nothing Then
    'Add code to customize the active explorer.
End If

If explorer IsNot Nothing Then
    'Add code to customize the active explorer.
End If

AndAlso Operator

The AndAlso operator is used to evaluate two expressions but evaluates the second expression only if the first expression is True. For example, if you want to access a property of a Bookmark object only if the bookmark exists (thus avoiding an error), you can use the AndAlso statement, as shown in Listing 4.7. This technique is often referred to as short circuiting.

Listing 4.7. Using the AndAlso operator

If Bookmark1 IsNot Nothing AndAlso Bookmark1.Bold Then
    MsgBox("The bookmark text is bold")
End If

OrElse Operator

The OrElse operator is used to evaluate two expressions but evaluates the second expression only if the first expression is False. The code in Listing 4.8 checks whether the text in a bookmark named Bookmark1 has italic formatting. If it doesn’t, it then checks whether the bookmark has bold formatting. If either of these expressions is true, a message box is displayed.

Listing 4.8. Using the AndAlso operator

If Bookmark1.Italic OrElse Bookmark1.Bold Then
    MsgBox("The bookmark text has formatting")
End If

Language Differences of VBA and Visual Basic 2005

Visual Basic 2005 is an object-oriented programming language that supports encapsulation, inheritance, interfaces, and overloading. Additionally, many language differences exist between VBA and Visual Basic 2005.

Data Types

To comply with the common language specifications, data types in languages supported by the .NET Framework must map to the types in the System namespace. For example, an Integer maps to System.Int32, and a Long maps to System.Int64. Because of these requirements, some data types in Visual Basic 2005 differ from those in VBA. When you write your code, you can use either the Visual Basic aliases for the data type (Integer, Long, etc.) or the .NET Framework data types (System.Int32, System.Int64, etc.). However, the aliases provided for Visual Basic are much easier to read and remember.

Data Type Differences

In VBA an integer is 16 bits, which is equivalent to a Short in Visual Basic 2005. A Long is 32 bits in VBA, which is equivalent to an Integer in Visual Basic 2005.

The Currency data type is not supported in Visual Basic 2005. Instead, you can use the Decimal data type. Decimal is more accurate than the Currency data type. Decimal is a 128-bit fixed-point data type that can hold up to 28 digits to the right of the decimal point. You can force the data type of a literal value to Decimal by adding a D to the end of the literal value. For example, 23445937D is forced to be interpreted as a Decimal. Without the D, the literal value is interpreted as a Long.

The Variant data type is not supported in Visual Basic 2005. When you copy VBA code into the Visual Basic 2005 Code Editor, if that code either declares a variable as a Variant or does not specify the data type, it is automatically converted to the Object type. Instead of declaring a variable as an Object, you should specify its data type to avoid late binding of the object. Late binding means that the type of the object is not known until run time. Early binding gives you the added bonus of having access to all the IntelliSense features of Visual Studio at design time.

A Date in Visual Basic 2005 maps to a System.DateTime type in the .NET Framework. In VBA, the Date data type is stored as a floating-point number. To convert between a Double and a Date data type in Visual Basic 2005, you should use the Date’sToOADate and FromOADate methods. Listing 4.9 demonstrates the conversion of a date stored in an Excel range to a Date. The example assumes that cell A1 contains a valid date.

Listing 4.9. Using the FromOADate method

Sub GetDate()
    Dim myDate As Date = Date.FromOADate( _
        Globals.Sheet1.Range("A1").Value2
    MsgBox(CStr(myDate))
End Sub

When the GetDate method runs, the message box displays the date in cell A1. If you rewrite the code to declare myDate as a Double and assign it the value of cell A1, the message box displays the value as a double rather than in date format.

Table 4.2 shows the mapping between data types in Visual Basic 2005, VBA, and the .NET Framework.

Table 4.2 Data Types in VBA, Visual Basic 2005, and the NET Framework

VBA	Visual Basic 2005	.NET Framework
Integer	Short	System.Int16
Long	Integer	System.Int32
Currency	Decimal	System.Decimal
Variant	Object	System.Object
Date (stored as double)	Date	System.DateTime

Strings Are Objects

In Visual Basic 2005, a String is an object that has its own members, which you can use to manipulate the String. For example, if you start typing the code in Listing 4.10, when you type the period after AuthorName, IntelliSense displays all the methods and properties available for a String, as shown in Figure 4.3.

Listing 4.10. Methods for a String

Dim AuthorName As String = "Kathleen McGrath"
Dim Names() As String = AuthorName.Split(" ")
MsgBox("First Name: " & Names(0) & vbCrLf & _
        "Last Name: " & Names(1))

Figure 4.3. Calling the Split method on a String

You can convert data types to strings using the CStr function, but many types can be converted to a String by using the ToString() method. For example, if you want to display an integer in a message box and you have Option Strict turned on, you must first convert the Integer to a String. Replace the code in the ThisDocument class of a Word document solution with the code in Listing 4.11, and then run the code.

Listing 4.11. Displaying an Integer in a message box

Option Strict On
Public Class ThisDocument

  Private Sub ThisDocument_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

    Dim ParagraphCount As Integer = Me.Paragraphs.Count
    MsgBox(ParagraphCount)
  End Sub
  Private Sub ThisDocument_Shutdown(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Shutdown
  End Sub
End Class

When you run the code in Listing 4.11, a build error occurs indicating that Option Strict On disallows implicit conversions from Integer to String. You can do one of two things. The first is to turn off Option Strict. Keep in mind that in addition to ensuring that you explicitly convert numeric types and strings, Option Strict restricts data type conversion that could result in data loss and generates errors when objects are late bound. Turning Option Strict on can help reduce errors in your code.

The second option is to explicitly convert the Integer to a String. You do this by using the CStr function or by calling the ToString() method on the integer. Change the line of code that reads MsgBox (ParagraphCount) to MsgBox (ParagraphCount.ToString()), and press F5. This time the message box displays the total number of paragraphs in the document.

String Functions

It is important to note that the string handling functions for bytes and double-bytes in VBA are not supported in Visual Basic 2005. Visual Basic 2005 strings are in Unicode, and conversion of Strings to double-byte character sets is no longer needed. Table 4.3 lists the VBA functions that are no longer supported and their equivalent Visual Basic 2005 functions (found in the Microsoft.VisualBasic namespace).

Table 4.3. Unsupported VBA Functions and the Equivalent Visual Basic 2005 Functions

VBA	Visual Basic 2005 Equivalent
AscB	Asc
ChrB, ChrB$	Chr, ChrW
InstrB	Instr
LeftB, LeftB$	Left
LenB	Len
MidB, MidB$	Mid
RightB, RightB$	Right

Fixed-Length Strings

Fixed-length strings have a specific length that is specified when the variable is declared. When you assign text that is longer than the specified length, the text gets truncated. Although it is possible to declare fixed-length strings in VBA, they are not supported in Visual Basic 2005.

Arrays Are Zero-Based

Visual Basic 2005 arrays cannot have a lower bound other than zero (0). You cannot set the lower bound to 1, as you can when you use VBA. If you want to initialize the array at the time you declare it, you can place curly brackets ({ }) around all the elements you want to add. You’ll notice later in Listing 4.12 that to display the first element of the array (apples), you must reference the element 0.

Even though arrays and collections are zero-based in managed code, keep in mind that when you write code against the Office object models, many of the collections are 1-based instead of 0-based. For example, if you tried to access the first bookmark in the Bookmarks collection using the following code example, a run-time error would occur:

MsgBox(CStr(Me.Bookmarks(0).Name))

Methods

There are a few differences between methods that are created in VBA and methods that are created in Visual Basic 2005. For example, you must use parentheses in method calls when you pass parameters to a method in Visual Basic 2005. This differs from the behavior in VBA, where parentheses are sometimes, but not always, required.

Parameters

By default, parameters are passed by reference in VBA. In Visual Basic 2005, they are passed by value. If you create a method in Visual Basic 2005 that takes parameters and if you do not specify whether the parameter must be passed by reference or by value, Visual Basic 2005 automatically inserts the ByVal keyword into the Code Editor.

Method Overloading

Optional parameters are still supported in Visual Basic 2005. You will use them often when you call methods on the Office object models, because many of the methods in Word and Excel accept optional parameters. However, there is another way to provide methods that can take a variety of parameters in Visual Basic 2005. This feature is known as method overloading.

In Visual Basic 2005, you can create multiple methods that have the same name but differ only in the type or number of arguments. Each additional method defined is an overload of the method. For example, the code example in Figure 4.4 shows a Print method with three overloads. The first overload prints the document to a file; therefore, it contains some arguments specific to printing to a file, such as Append and OutputFileName. These arguments don’t make sense in the second overload, which does not allow a document to be printed to a file. The third overload doesn’t accept any arguments and instead prints the document with all the default arguments.

Figure 4.4. Providing overloaded methods

Variable Declaration and Scope

When you declare a variable within a Visual Basic 2005 class, the variable is available only within that instance of the class and cannot be accessed by another instance. If you declare the variable as Shared, it is available to all instances of the class. In VBA, variables declared within a code block, such as a loop, are available locally to the entire procedure, but variables in Visual Basic 2005 are available only within the code block itself. If you declare a variable within a looping structure, the variable is not available outside the loop.

As in VBA, you declare a variable in Visual Basic 2005 using the Dim statement and specify a data type for the variable. If you do not specify a data type in VBA, the variable is automatically created as the type Variant. The Variant data type is no longer supported in Visual Basic 2005. Instead, an unspecified variable is created as an Object.

As a best practice, you should always explicitly type any variables that you declare. This is to avoid late binding to variables. Although late binding is supported in Visual Basic 2005, early-bound variables aid in code readability, and enable IntelliSense on the object. Figure 4.5 shows the differences in IntelliSense capabilities when a variable’s data type is not specified versus when it is specified.

Figure 4.5. Late binding to variables (top) versus early binding

In VBA, if you declare more than one variable within a statement, you must specify the type for each variable or else it defaults to the Variant type. In Visual Basic 2005, you can declare multiple variables as the same type. Table 4.4 shows the difference in multiple variable declarations between VBA and Visual Basic 2005.

Table 4.4 Variable Declaration in VBA and Visual Basic 2005

Declaration	VBA	Visual Basic 2005
Dim iCount, iTotal As Integer	iCount is Variant, iTotal is Integer.	Both iCount and iTotal are Integers.
Dim iCount	iCount is a Variant data type.	iCount is an Object data type.

Array variables should not be declared using the New keyword (As New). In VBA, you can automatically initialize array members by declaring the array with the New keyword. This keyword is not supported for arrays in Visual Basic 2005. Instead, you must explicitly initialize the members of an array, as shown in Listing 4.12.

Listing 4.12. Initializing an array

Dim Fruit() As String = {"apples", "oranges", "bananas", "pears"}
MsgBox(Fruit(0))

Default Properties

Default properties are supported in Visual Basic 2005 only if the properties take arguments. For example, the Label control in VBA has a property called Caption that is the default property; this means that you can assign a value to this control directly, without specifying the property name. In Visual Basic 2005, you must specify the property when assigning values to properties. For example, you would have to type Label1.Text = “OK” to assign the OK string to the label. Typing Label1 = “OK” would cause an error.

An example of a default property that takes an argument is the Item property. You do not need to explicitly specify the Item property because it takes an index as a parameter. For example, you can reference a bookmark using Me.Bookmarks(1) instead of specifying the Item property using Me.Bookmarks.Item(1).

Because of this change, you no longer need to use the Set keyword when assigning an object to a variable. The Set keyword was used to differentiate between the assignment of an object and the assignment of a value to a default property of the object. Removing the support for default properties removes the ambiguity of assignment.

Enumerations

You must fully qualify enumeration constants in Visual Basic 2005. You might be accustomed to using a constant, such as xlUnderlineStyleSingle, when you assign an underline style to an Excel worksheet cell. In Visual Basic 2005, you must fully qualify the enumeration by typing Excel.XlUnderlineStyle.xlUnderlineStyleSingle, as shown in Listing 4.13. Note that Excel is an alias for the Microsoft.Office.Interop.Excel namespace.

Listing 4.13. Fully qualifying enumeration constants

' VBA
Dim myRange As Excel.Range
myRange = Range("A1")
myRange.Font.Underline = xlUnderlineStyleSingle

' Visual Basic 2005
Dim myRange As Excel.Range
MyRange.Font.Underline = Excel.XlUnderlineStyle _
    .xlUnderlineStyleSingle

Exception Handling

Using VBA, you typically handle errors by using an On Error statement that specifies the location of the error handling code. When an error is encountered, execution moves to the previous On Error statement in the subroutine, which then moves execution to the location specified. The On Error statement can specify three things:

• The code execution should move to a particular line or label (On Error GoTo xErrorHandler).

• The code execution should move to the next statement in the code where the error occurred (On Error Resume Next).

• The error handling should be disabled within the procedure (On Error GoTo 0).

Visual Basic 2005 supports the On Error statement, but you should consider using structured error handling. Structured error handling in Visual Basic 2005 translates to the Try Catch Finally statements (see Listing 4.14). These statements enable you to catch errors that are thrown at run time and handle the errors gracefully so that the application doesn’t just crash unexpectedly. You can write code to handle specific exceptions that you know might occur in your code, or you can catch unspecified exceptions in a general Catch block.

Listing 4.14. Try Catch statement

Try
    ' Code that might cause an exception.
Catch ex As Exception
    ' Code to handle the exception.
Finally
    ' Additional code to run whether or not exception occurs.
End Try

Visual Basic 2005 has the Try Catch statement available as a code snippet. Three statements are available, and each snippet has a corresponding shortcut that you can expand by typing the shortcut name and then pressing the TAB key.

• Try Catch EndTry, which has the shortcut TryC

• Try Catch Finally EndTry, which has the shortcut TryCF

• Try Finally EndTry, which has the shortcut TryF

To add a Try Catch statement using a code snippet shortcut, type TryCF in the Code Editor, and then press the TAB key. Notice that the variable ApplicationException in the Catch statement is automatically highlighted. You should change this variable to the type of exception that you expect. For example, you can change it to a NullReferenceException if it is possible that a variable has not yet been assigned a value, as shown in Listing 4.15. You can have more than one Catch block to handle different types of exceptions.

You add the code that might cause an error to the Try block. If an error occurs, execution moves to the Catch block, which should contain the code that handles the error. Code execution then moves to the first statement after the End Try statement. If the optional Finally statement is present before the End Try statement, the code execution always moves to the Finally block. Code within a Finally block runs after the errors are handled and runs even if an error is not encountered. Often, developers add cleanup code, such as closing a database connection, to the Finally block.

Listing 4.15. Catching a NullReferenceException

Dim myString As Object = Nothing
Try
    MsgBox(myString.ToString())
        Catch ex As NullReferenceException
        MsgBox(ex.Message)
End Try

If code in a method encounters an error and if a Catch block for the type of exception thrown cannot be located, the exception is passed up to the calling method. This continues until the top of the call stack is reached. If a Catch block is still not found, the default exception handler runs. The Message property of the exception contains information about the cause of the error, and you can display this information to end users in a message box. You can even provide a link to a Help topic that contains additional information.

If you plan to upgrade existing VBA code to Visual Basic 2005, keep in mind that you cannot combine the VBA-style error handling with structured error handling within the same methods. Doing so will cause a compiler error, as shown in Figure 4.6.

Figure 4.6. Error when using a Try statement with an On Error statement

UserForms Versus Windows Forms

When you use VBA to create an Office solution, you can design a user interface to display a dialog box. To do so, you add a UserForm to your project and then add controls to the UserForm. These controls are referred to as ActiveX controls. Whenever the UserForm has focus, the Toolbox, which contains all the default controls, becomes visible. Figure 4.7 shows a UserForm that contains all the default ActiveX controls.

Figure 4.7. A UserForm in VBA that contains all the default ActiveX controls

When you use VSTO to create an Office solution, you can design the user interface by designing a Windows Form that can be displayed as a window, as a dialog box, or on a user control that appears in the document or Document Actions task pane. The Toolbox in Visual Studio displays all the Windows Forms controls that are available for a Windows Form. Figure 4.8 shows a Windows Form with all the controls that are displayed on the UserForm in Figure 4.7. The Toolbox in Figure 4.8 also shows some of the controls that can be added to the Windows Form.

Notice that some of the controls that are available for a UserForm are not available on a Windows Form—for example, the Toggle control, the Spin control, and the MultiPage control. Many additional Windows Forms controls that appear on the Toolbox are not displayed in Figure 4.8. These controls are described in Chapter 8.

Figure 4.8. A Windows Form in VSTO with Toolbox displaying a portion of the available controls

Comparison of ActiveX Controls to Windows Forms Controls

ActiveX controls differ from the corresponding Windows Forms controls in many ways. For example, the names of some of the controls and their properties, methods, and events are different. The left column in Table 4.5 shows the names of the ActiveX controls, and the right column shows the names of the corresponding Windows Forms controls.

Table 4.6 shows the changes in the names of the properties, methods, and events that are common to many of the controls listed in Table 4.5.

Table 4.5 ActiveX Controls in VBA and the Corresponding Windows Forms Controls in Visual Basic 2005

ActiveX Controls (VBA)	Windows Forms Controls (Visual Basic 2005 Controls)
TextBox	TextBox
Label	Label
ComboBox	ComboBox
ListBox	ListBox
CheckBox	CheckBox
OptionButton	RadioButton
ToggleButton	N/A (can use a CheckBox control and set its Appearance property to Button)
CommandButton	Button
TabStrip	TabControl
MultiPage	TabControl
ScrollBar	VScrollBar, HScrollBar
SpinButton	NumericUpDown
ImageControl	N/A (can use a PictureBox control instead)

Table 4.6 Changes to Common Members of the Windows Forms Controls

Member	VBA	Visual Basic 2005
Property	Caption	Text
Property	Container	Parent
Property	Height	Height, Size
Property	HWnd	Handle
Property	MousePointer	Cursor
Property	Parent	FindForm method
Property	Picture	Image
Property	SelLength	SelectionLength
Property	SelStart	SelectionStart
Property	SelText	SelectedText
Property	ToolTipText	ToolTip component
Property	Width	Width, Size
Method	Move	SetBounds
Method	SetFocus	Focus
Method	ZOrder	BringToFront, SendToBack
Event	DblClick	DoubleClick
Event	GotFocus	Enter
Event	LostFocus	Leave
Event	Validate	Validating

In addition to the differences in the properties, methods, and events, there are differences in the members that are unique to each control. Table 4.7 lists these differences.

Table 4.7 Members of the ActiveX Controls Compared with Windows Forms Controls

Control	VBA	Visual Basic 2005
TextBox	Alignment	TextAlign
TextBox	Locked	ReadOnly
TextBox	Change	TextChanged
Label	Alignment	TextAlign
Label	BackStyle	BackColor set as transparent
Label	WordWrap	(Automatic)
ComboBox	List	Items
ComboBox	ListCount	Count
ComboBox	ListIndex	SelectedIndex
ComboBox	Locked	DropDownStyle = DropDownList
ComboBox	Style	DropdownStyle
ComboBox	AddItem	Add, AddRange, Insert
ComboBox	RemoveItem	Items.Remove
ComboBox	Change	TextChanged
ComboBox	Click	SelectedIndexChanged
ListBox	Columns	MultiColumn, ColumnWidth
ListBox	List	Items
ListBox	ListColumn	Count
ListBox	ListIndex	SelectedIndex
ListBox	MultiSelect	SelectionMode
ListBox	SelCount	Count
ListBox	Selected	GetSelected, SetSelected
ListBox	AddItem	Add, AddRange, Insert
ListBox	RemoveItem	Remove
ListBox	ItemCheck	N/A (available only on a CheckedListBox)
CheckBox	Alignment	CheckAlign
CheckBox	Style	Appearance
CheckBox	Value	CheckState
CheckBox	Click	CheckStateChanged
OptionButton	Alignment	TextAlign
OptionButton	Appearance	FlatStyle
OptionButton	Value	Checked
OptionButton	Click	CheckedChanged
Frame	Appearance	FlatStyle (GroupBox0
Frame	Click	Click (Panel)
CommandButton	Cancel	N/A (use the CancelButton of the Form instead)
CommandButton	Default	N/A (use the AcceptButton of the Form instead)
CommandButton	Value	N/A
ScrollBar	Max	Maximum
ScrollBar	Min	Minimum
ScrollBar	TabIndex	TabIndexChanged
ScrollBar	TabStop	TabStopChanged
ScrollBar	Value	ValueChanged

The following section describes some of the differences between an ActiveX control and the corresponding Windows Forms control.

TextBox versus TextBox

The ActiveX control TextBox provides a way to collect user input. The default property is Value, and the default event is Change. The equivalent Windows Forms control is also named TextBox. The property used to display text in the TextBox control is the Text property, and the default event is TextChanged.

Label versus Label

The ActiveX control Label is used to display information on a Windows Form. The default property is Caption, and the default event is Click. The equivalent Windows Forms control is also named Label. The property used to display text in the Label control is the Text property, and the default event is Click.

In VBA, the WordWrap property is used to determine whether a label wraps. Text wrapping in Visual Basic 2005 is automatic for a Label. The BackStyle property is no longer available for a Label. Instead, you can set the BackColor property to transparent.

ComboBox versus ComboBox

The ActiveX control ComboBox combines the features of a ListBox and a TextBox. The default property is Value, and the default event is Change. You can change the Style property to make the ComboBox appear as a drop-down list. The equivalent Windows Forms control is also named ComboBox. The property used to display text in the ComboBox control is the Text property, and the default event is SelectedIndexChanged.

ListBox versus ListBox

The ActiveX control ListBox displays a list of values that users can select. The default property is Value, and the default event is Click. You can change the ListStyle property to change the appearance of the ListBox so that it displays option buttons or check boxes within the list. The equivalent Windows Forms control is also named ListBox. To add items to a ListBox, you use the Add method. The default event is SelectedIndexChanged. You cannot change the appearance of a ListBox control to display check boxes; instead, you should use the CheckedListBox control.

CheckBox versus CheckBox

The ActiveX control CheckBox enables users to choose between two options (true/false, on/off, yes/no). The default property is Value, and the default event is Click. The equivalent Windows Forms control is also named CheckBox. The property used to display text in the CheckBox control is the Text property. The default event is CheckedChanged, which occurs when the value of the check box changes.

OptionButton versus RadioButton

The ActiveX control OptionButton enables users to choose between mutually exclusive options. The default property is Value, and the default event is Click. The equivalent Windows Forms control is RadioButton. The property used to display text in the RadioButton control is the Text property. The default event is CheckedChanged, which occurs when the value of the radio button changes.

ToggleButton versus CheckBox

The ActiveX control ToggleButton shows whether an item is selected. The default property is Value, and the default event is Click. To create an equivalent Windows Forms control, you can add a CheckBox control and then set the Appearance property of the CheckBox to Button.

Frame versus GroupBox and Panel

The ActiveX control Frame is used to group controls. For example, any OptionButton controls added to a frame are mutually exclusive. The default event is Click.

There are two Windows Forms controls that are similar to the Frame control. The first is the GroupBox control, and the second is the Panel control. The property used to display text in the GroupBox control is the Text property, and the default event is Enter. The default event for the Panel control is Paint. Note that GroupBox and Panel cannot be added directly to a Word document or Excel worksheet at design time. Instead, you can add these controls to a user control and then add the user control to the document or worksheet.

CommandButton versus Button

The ActiveX control CommandButton is used to trigger an event, such as starting or stopping an action. The default property is Value, and the default event is Click. The equivalent Windows Forms control is the Button control. The property used to display text in the Button is the Text property, and the default event is Click.

The Windows Forms Button control does not have a Default or Cancel property. Instead, you can pass the Button control to the CancelButton or AcceptButton property of the Windows Form. The equivalent of setting a CommandButton’sValue to True is calling the BeforeClick method of a Button.

TabStrip versus TabControl

The ActiveX control TabStrip is used to group related controls, and it contains a Tabs collection. The default property is SelectedItem, and the default event is Change. The equivalent Windows Forms control is the TabControl, which contains Tab pages. This control is like a combination of a TabStrip and a MultiPage control. The default event is Click.

MultiPage versus TabControl

The ActiveX control MultiPage enables you to combine related information, and it contains a Pages collection. The default property is Value. The default event is Change. There is no equivalent Windows Forms control; however, a TabControl is like a combination of a TabStrip and a MultiPage control.

ScrollBar versus VScrollBar and HScrollBar

The ActiveX control ScrollBar enables you to provide scrolling capabilities to another control. The default property is Value, and the default event is Change. You can create a horizontal or vertical scroll bar by dragging the sizing handles on the UserForm.

There are two Windows Forms controls that are equivalent to the ScrollBar: the VScrollBar (for vertical scrolling) and the HScrollBar (for horizontal scrolling). Most controls have their own scrolling capabilities, but these ScrollBar controls enable you to provide scrolling capabilities for other controls, such as the PictureBox. The default event is Scroll.

SpinButton versus NumericUpDown

The ActiveX control SpinButton enables you to change and update the value of another control. The default property is Value. The default event is Change. There is no equivalent Windows Forms control; however, there is a NumericUpDown control, which can be used to spin through a series of numbers.

ImageControl versus PictureBox

The ActiveX control ImageControl enables you to display pictures. The default event is Click. You can use a PictureBox control to display pictures on a Windows Form. The default event for a PictureBox control is SelectedIndexChanged.

Changes to Control Functionality

In addition to changes in the names of the properties, methods, and events, the functionality of the controls might also differ. For example, Windows Forms controls have a different implementation for handling data access and fonts.

Fonts

Using VBA, you can set the font of a control by setting the font properties directly. In Visual Basic 2005, you must create an instance of a System.Drawing.Font object whenever you want to programmatically set the font property of a control. Listing 4.16 shows how to change the text and font used on a CommandButton in VBA and a Button in Visual Basic 2005. When you create a Font object in Visual Basic 2005, you must choose one of ten overloaded constructors. In Listing 4.16, we pass the font size and style when we instantiate the Font object and then assign it to the Font property of the Button.

Listing 4.16. Setting the font on a control

' VBA
Private Sub CommandButton1_Click()
    Me.CommandButton1.Caption = "Sample"
    With Me.CommandButton1.Font
        .Name = "Courier New"
        .Size = 8
        .Italic = True
    End With
End Sub

' Visual Basic 2005
Private Sub Button1_Click (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

    Me.Button1.Text = "Sample"
    Dim myFont as new System.Drawing.Font("Courier New", _
        8, FontStyle.Italic)
    Me.Button1.Font = myFont
End Sub

Colors

In VBA, colors are of type Long, and there are eight constants that can be used: vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, and vbWhite. In Visual Studio 2005, colors are of type Color, and there are more than 100 choices. Why set text to blue when you can choose DarkSlateBlue or DeepSkyBlue, as shown in Figure 4.9?

Figure 4.9. Setting the ForeColor of a Button to DeepSkyBlue

Controls inherit the colors of their parents (the form) in Visual Basic 2005. Listing 4.17 shows that to set the ForeColor of a CommandButton on a UserForm in VBA, you must set the ForeColor of the control; setting the ForeColor of the UserForm does not affect the color of any of its controls. However, in Visual Basic 2005, if you set the ForeColor property of the Windows Form, all the controls on the form automatically inherit the color setting.

Listing 4.17. Inheriting ForeColor property settings

' VBA
Private Sub CommandButton1_Click()
    Me.ForeColor = vbBlue
End Sub

' Visual Basic 2005
Private Sub Button1_Click (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
    Me.ForeColor = Color.Blue
End Sub

Using VBA, you can also set the color using the RGB function, where you pass an integer value between 0 and 255 for each color component: red (R), green (G), and blue (B). For example, to set the ForeColor of a control to blue, you can set the property to RGB(0, 0, 255). In Visual Basic 2005, you can assign only a value of type System.Drawing.Color.

You can use the ColorTranslator class to convert an RGB value to a Color structure. Listing 4.18 shows how to set the ForeColor of a CommandButton control using an RGB function in VBA. It also shows how to translate the RGB value to a Color structure in Visual Basic 2005 so that it can be set to the ForeColor property of a Button control.

Listing 4.18. Using RGB in Visual Basic 2005

' VBA
Private Sub CommandButton1_Click()
    Me.CommandButton1.ForeColor = RGB(0, 0, 255)
End Sub

' Visual Basic 2005
Private Sub Button1_Click (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

    Dim colorValue as Integer = RGB(0, 0, 255)
    Me.Button1.ForeColor = System.Drawing.ColorTranslator _
        .FromOle(colorValue)
End Sub

You can also use the FromArgb method of a Color structure:

Me.Button1.ForeColor = System.Drawing.Color.FromArgb(0, 0, 255)

Displaying a Form

To display a form in VBA, you call the Show method of the form. You can indicate whether to display the form as modal or modeless by passing vbModal or vbModeless. If you do not pass either parameter, the form shows as modal by default.

In Visual Basic 2005, forms are classes, and you must instantiate the form before you can display it. You call the Show method to display a modeless form, and you call the ShowDialog method to show the form as a modal dialog box. Listing 4.19 demonstrates how to show a modal UserForm using VBA, and a modal Windows Form using Visual Basic 2005. The code is called from the Click event handler of one form, which causes the second form to be displayed.

Listing 4.19. Displaying a form in VBA versus Visual Basic 2005

' VBA
Private Sub CommandButton1_Click()
    UserForm2.Show (vbModal)
End Sub

' Visual Basic 2005
Private Sub Button1_Click (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

    Dim myForm As New Form2
    myForm.ShowDialog()
End Sub

Size and Location

In VBA, you adjust the size of a control by using the Height and Width properties. The unit of measurement for these properties is twips. In Visual Basic 2005, the Height and Width properties are combined into a single Size property, which is measured in pixels.

When you display a form, you often want to control exactly where it will appear on the screen. In VBA, you set the StartUpPosition property. For example, you can set the StartUpPosition property to Manual and then set the Top property to 0 and the Left property to 0, displaying the form in the upper-left corner of the application.

To manually control the position of a Windows Forms control using Visual Basic 2005, you use the StartPosition and Location properties. The location is calculated (in pixels) in relation to the upper-left portion of the display area. You can instead specify the location by using the form’s Left and Top properties directly. Listing 4.20 shows how to display a UserForm and a Windows Form in the upper-left corner of the screen.

Listing 4.20. Setting the location of a UserForm and a Windows Form

' VBA
Private Sub UserForm_Initialize()
    Me.StartUpPosition = Manual
    Me.Left = 0
    Me.Top = 0
End Sub

' Visual Basic 2005
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e _
    As System.EventArgs) Handles MyBase.Load

    Me.StartPosition = FormStartPosition.Manual
    Me.Location = New Point(0, 0)
End Sub

You can move the form to a new location by using these properties; or you can use the Move method in VBA, or the SetBounds method in Visual Basic 2005. Both of these techniques require that you indicate the location and size of the form. You can pass the Form’s current height and width, as shown in Listing 4.21.

Listing 4.21. Changing the location of a UserForm and a Windows Form

' VBA
Private Sub CommandButton1_Click()
    Dim UserFormHeight As Integer
    Dim UserFormWidth As Integer
    UserFormHeight = Me.Height
    UserFormWidth = Me.Width
    Me.Move 0, 0, UserFormWidth, UserFormHeight
End Sub

' Visual Basic 2005
Private Sub Button1_Click (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
    Dim formHeight As Integer = Me.Height
    Dim formWidth As Integer = Me.Width
    Me.SetBounds(0, 0, formWidth, formHeight)
End Sub

Control Arrays

Control arrays are not supported in Visual Basic 2005; however, you can implement the same functionality by extending an event handler to handle multiple controls. Notice in Listing 4.21 that the Button1 Click event handler has a Handles clause at the end of the statement (Handles Button1.Click). To implement this event handler for multiple controls, you can add references to the controls in the Handles clause. For example, you can indicate that the Click event handler of Button1 should also handle the Click event of Button2, as shown in Listing 4.22.

Listing 4.22. Handling the events of multiple controls

' Visual Basic 2005
Private Sub Button1_Click (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click, _
    Button2.Click

    Dim formHeight As Integer = Me.Height
    Dim formWidth As Integer = Me.Width
    Me.SetBounds(0, 0, formWidth, formHeight)

End Sub

Dynamic Controls

In addition to adding controls to a document or worksheet by dragging them from Toolbox, you can add Windows Forms controls programmatically. The way you add these controls to a document differs from the way you would add them to a Windows Form; VSTO provides a number of helper methods that eases the task of adding these controls to your documents. You will learn more about adding controls to a document in Chapter 8.

Data Binding

You can fill ActiveX controls with data in VBA by using an ActiveX Data Object (ADO). For example, you can fill a ComboBox with data by reading the ADO recordset, adding each row of the recordset to an array and then assigning the array to the List property of the control. Using Visual Basic 2005, you can bind a control directly to a data source using the control’s DataBindings property. You can bind to a data source such as a column in a database, or to any structure that contains data, including XML. You can also bind any property of the control to a data source.

Windows Forms controls support either simple data binding or complex data binding. Simple data binding enables you to bind one element of data to a control. Complex data binding enables you to bind a control to more than one data element, and it is typically supported in controls that display lists of information.

Programmatically, you can data-bind a control using the Add method of the control’s DataBindings property, passing three parameters: the property you want to bind the data to, the data source, and the data member. For example, to bind data to a text box, you could use code similar to this:

TextBox1.Databindings.Add("Text", ds, data.member)

It is much easier to bind data to controls at design time using the Data Sources window and dragging data-bound controls to the document or to a Windows Form. Data binding is covered in more detail in Chapters 6 and 7.

Summary

We started this chapter with a discussion of why you might consider a move from VBA to Visual Basic 2005 and VSTO. Next, we looked at the new features of Visual Basic 2005, and you learned how to use the new My objects and how to use IntelliSense code snippets. We then took a closer look at the language changes between VBA and Visual Basic 2005, including changes to data types, variable declarations, variable scope, and structured error handling. Finally, you learned about the differences between ActiveX controls and the corresponding Windows Forms controls available in Visual Basic 2005.

Review Questions

1. Name two new features of Visual Basic 2005.

2. What is the file format of IntelliSense code snippets?

3. What is the difference between an option button and a radio button?

4. What is the main purpose of a Using statement?

5. Name two data types that are no longer supported in Visual Basic 2005. Which types can be used instead?

6. What is the preferred alternative to declaring optional parameters on a method?

7. What are two advantages of specifying data types for variable declarations?

Additional Resources

For more information, see the following resources:

• Chapter 12: Migrating VBA Solutions to VSTO

• Visual Studio Developer Center

• Office Developer Center

• Office Development with Visual Studio Developer Portal