Migrating Word VBA Solutions to Visual Studio Tools for Office

Article
12/18/2006

June 2004

Applies to:
    Microsoft Visual Basic for Applications
    Microsoft Visual Basic .NET 2003
    Microsoft Visual Studio Tools for the Microsoft Office System
    Microsoft Office 2003 Editions
    Microsoft Office Word
    Microsoft Office Excel

Summary: Read a case study of a migration from VBA to Visual Studio Tools for Office that highlights the migration process, best practices, and useful resources, and provides sample code to illustrate the concepts that are presented. (20 printed pages)

Download odc_VSTVBAtoVSTO_SAMPLE.msi.

Introduction
Migration Case Study
Migrating the VBA Solution
Conclusion
Resources

Introduction

For years, developers have used Microsoft Visual Basic for Applications (VBA) to build Microsoft Office solutions. VBA has proven to be a reliable and capable development environment for individuals and in corporate teams. In recent times, many developers have noticed that demands placed on their solutions have exceeded the abilities of VBA and so we are beginning to see the transition from VBA to the professional development environment of the Microsoft .NET Framework, Microsoft Visual Studio 2005, Microsoft Visual Studio Tools for the Microsoft Office System, and Microsoft Office 2003.

How are organizations benefiting from these new technologies? First, the .NET Framework provides an extensive class library of scalable, reliable, and secure components that can be used for developing Web applications, Web services, Windows applications, and component libraries. Second, developers know that millions of people use Microsoft Office daily and that it is an ideal environment to reach users with enterprise solutions. Add Visual Studio Tools for Office to the mix, and you have the tools to successfully build an enterprise-ready .NET solution with a Microsoft Office Word or a Microsoft Office Excel document-based solution.

Once an organization has made the strategic decision to standardize on the .NET Framework and Microsoft Office 2003, questions are often raised about the effort to migrate existing VBA solutions to Visual Studio Tools for Office. For example, is it possible to migrate a VBA solution to Visual Studio Tools for Office? How much effort will it require? What issues are encountered during migration? What techniques exist to minimize migration effort?

This article addresses these questions and explores the process and techniques needed to migrate an existing Microsoft Office Word VBA solution to a Visual Studio Tools for Office solution. This article also provides guidance on best practices and includes pointers to useful migration-related developer resources.

Please note that although this article presents a Microsoft Office Word-based example, many of the ideas and techniques presented in this article also hold true for issues faced when migrating an Microsoft Office Excel VBA solution.

Migration Case Study

To discuss migration, it is helpful to have a sample project as a basis for this discussion. For that reason, I will profile the fictitious company Trey Research. Trey Research is a wholesale supplier and distributor of products normally sold in retail pharmacies. Their legal department has over 50 Microsoft Word 2002 and Microsoft Excel 2002 templates that they use regularly for corporate communications and contract agreements with vendors, suppliers, and retailers.

VBA automation is used to simplify the document creation and maintenance of Trey Research templates. These template solutions often collect information from a user, connect to a database, and provide reusable content based on document context.

This article discusses the migration of a template that reflects a common set of functions used by most of the legal department's templates. It is an ideal candidate for understanding the migration challenges for other templates. The template is the Supply and Service Agreement.

The VBA version of the Supply and Service Agreement works in Microsoft Office XP and Microsoft Office 2003.

To simplify the terminology in this paper, I use the term "VBA solution" for the original template being migrated and "Visual Studio Tools for Office solution" for the new version of the migrated template.

Using the Original Supply and Service Agreement Template

The Supply and Service Agreement helps the legal department in creating contracts for new retailers who want to sell Trey Research products. To follow along in using this template, download the sample code accompanying this article and extract the sample files to your local hard drive. Open the SupplyContractVBA.dot template in the VBA Solution directory included in the sample files. When the SupplyContractVBA.dot template opens, the form shown in Figure 1 is presented.

Figure 1. Form as completed by the user

This form simplifies collecting reseller and agreement information. The reseller's address and contact information is retrieved from the legal department's reseller tracking database using ADO for data access. When the user completes the form, the information in the form is pushed into document bookmarks anddocument variables. Bookmarks are a common way to tag data displayed in a Word document. Document variables, on the other hand, are not visible to the user and are ideal for storing information in a document that should only be changed by the business logic code of the solution. Figure 2 shows Page 1 of the new document with the customer information inserted into the document.

Figure 2. New document

Now the document is ready for normal editing. The template user modifies the legal text of this document. The editing task is simplified with the aid of a custom toolbar (see Figure 3) that is a part of the template. By using the toolbar, you can:

Insert boilerplate text into the document by selecting a fragment from the drop-down list and then clicking the Insert button.
Edit the company and contract information using the form shown in Figure 1.
Finalize the document.

Figure 3. Custom toolbar

The boilerplate text contains frequently used contract text that can be easily inserted into the document. The boilerplate text is stored in individual Word documents stored on a network share. When the user selects contract text from the toolbar drop-down list and clicks the Insert button, the document fragment stored on the network share is inserted into the current cursor location of the active document.

When the user has completed editing the agreement, the Finalize button of the toolbar is clicked. The Finalize****step marks the document as complete and records the location and document tracking number in the reseller tracking database. The custom toolbar is now removed and the document is protected so that it cannot be edited.

Migration Goals

Now that you understand the business model of Trey Research and the Supply and Service Agreement template, let's move on to migrating the solution. First, it's important to have the goal for this migration clearly in mind. There are two approaches you can take:

Minimal Effort

In this approach, you focus on minimizing the work to get the VBA solution working in Visual Studio Tools for Office. The goal of the minimal effort approach is to focus on the language differences of VBA and Visual Basic .NET with the intent of not changing any code unless absolutely necessary.
Rearchitect Solution

Another approach is to rearchitect the solution to take advantage of new features of Office 2003 and the .NET Framework. This requires more planning, testing, and writing of new code. This approach, rearchitecting presents an opportunity to rethink a solution and add extra business value through new functionality and better integration with other enterprise systems, as well as improve code maintainability.

The following table summarizes the advantages and disadvantages of each approach.

Table 1. Migration Approach Comparison

Migration Approach	Advantage	Disadvantage
Minimal Effort	Requires minimal planning Uses existing work from VBA solution Reduced testing requirements of business logic Net result: minimum effort and cost to get solution running quickly	Does not use new functionality of Office 2003 (for example, attached XML Schema, Smart Document navigation, document protection at a node level) Does not use .NET Framework (for example, ADO.NET and other class libraries, object oriented programming) Dependent on old external libraries (for example, ADO, File System objects, Win32 API calls)
Rearchitect Solution	Benefits from new features in Office 2003 and .NET Framework Allows tight integration with enterprise servers Improves maintainability using .NET design best practices over VBA techniques	Requires increased planning Requires increased testing for new code and business logic Requires more effort and cost

Minimal Effort

Requires minimal planning

Uses existing work from VBA solution

Reduced testing requirements of business logic

Net result: minimum effort and cost to get solution running quickly

Does not use new functionality of Office 2003 (for example, attached XML Schema, Smart Document navigation, document protection at a node level)

Does not use .NET Framework (for example, ADO.NET and other class libraries, object oriented programming)

Dependent on old external libraries (for example, ADO, File System objects, Win32 API calls)

Rearchitect Solution

Benefits from new features in Office 2003 and .NET Framework

Allows tight integration with enterprise servers

Improves maintainability using .NET design best practices over VBA techniques

Requires increased planning

Requires increased testing for new code and business logic

Requires more effort and cost

Looking back at the business scenario, you find that Trey Research has decided to move all their VBA solutions to Visual Studio Tools for Office to gain the benefits of the .NET Framework. In the future, Trey Research plans to rearchitect the entire solution to take advantage of the new abilities of the .NET Framework and the release of Visual Studio Tools for Office 2005. A future article will discuss the rearchitecture of this template. For now, let's get the original VBA solution working within the .NET Framework.

Trey Research has chosen the minimal effort approach. You are tasked to migrate the VBA solution and to only do the work absolutely necessary to get the template working within the .NET Framework.

Assessing the Scope of Work

Before migrating the code, it is important to have an idea of the structure of the existing VBA solution and all its dependencies. Start by inventorying the components that make up a VBA solution. The following table summarizes the components of the VBA solution and its dependencies. If you want to follow along in the sample code, open the SupplyContractVBA.dot file in Word and open the Visual Basic Editor.

Table 2. VBA Solution Structure

Component	Description	Dependencies
SupplyContractVBA.dot	The template document	Microsoft Word
ThisDocument	Module for capturing Document_New and Document_Open events	ThisDocument and the remaining components are embedded into SupplyContractVBA.dot
basUtils	Module with general purpose utility functions used by solution	Win 32 API call
clsDal	Class containing Data Access Layer	ActiveX Data Objects 2.5
Form: frmMain	Form used for capturing information (see Figure 1)	VBA Form Engine
basFormData	Module that contains general-purpose functions used in conjunction with frmMain
basToolbar	Module with code to create Custom Toolbar and contains the action code for the buttons	CommandBars File System Object

After inventorying the components of the VBA solution, consider the following questions:

Are all the components still needed?
- Some components might no longer be valid. There is no need to migrate these components.
- Some components may provide functionality now available in Office 2003. In this case, these components are no longer needed.
In what order should required components be migrated?
- Is a component independent of other components? If so, it might be good to migrate independent components first. You will find them easier to understand and test, improving your grasp of the VBA solution.
- Does a component provide shared general-purpose functionality used by other components? Migrating shared components makes it easier to migrate dependent components.
- Does a group of components have dependency on one another? If so, these should be migrated last, as they are likely to be the most challenging part of the migration and any completed work so far might ease migrating these more complex components.

After evaluating the answers to these questions, it becomes clear in which order the components should be migrated. You will also have a clear idea about the effort involved and which components will need the most attention.

Migrating the VBA Solution

Now that you have your migration goals clearly in mind and have assessed the scope of the work, you can begin the physical migration process. For the rest of this section, you will look at the steps I took to migrate the VBA solution to a Visual Studio Tools for Office solution. To follow along as I migrate the original VBA solution, in Word, open the SupplyContractVSTO.dot template in the VSTO Solution directory included in the sample files. Also open the Visual Studio solution file SupplyContract.sln in the VSTO Solution\SupplyContractdirectory.

Preparing the Document

First, the original template document must be prepared for migration. I made a copy of the SupplyContractVBA.dot and named it SupplyContractVSTO.dot. As it is likely you will have the original template and new template open in Word during the migration process, having a unique name for the Visual Studio Tools for Office solution helps avoid confusion about which document is being edited.

After copying the old template, I stripped out the old VBA solution code from SupplyContractVSTO.dot. This involved opening the new SupplyContractVSTO.dot document in Word and then, using the Visual Basic Editor, deleting all the modules, forms, and classes embedded in the document. The end result is a Word template with no embedded VBA.

Create a Template Project with the Visual Studio Tools for Office Wizard

Next, I created a Visual Studio Tools for Office project for my solution using the Microsoft Office Project Wizard found in Visual Studio. The Microsoft Office Project Wizard supports three solution types: Excel Workbook, Word Document, and Word Template. I selected Word Template, as it simplifies the process of creating an environment designed for Word templates.

Note It is important to specify the Word Template project type when migrating a template, as it contains the proper events in ThisDocument.cs. If a Word Document project type is selected, it does not include the ThisDocument_New event handler that you need to detect when a new instance of the template is created.

The Microsoft Office Project Wizard can produce source code in the Visual Basic and C# languages. Because VBA and Visual Basic are similar in syntax and lessen code changes, I selected the Visual Basic .NET version of the wizard.

The wizard asks if you want to create a blank template or use an existing template. For this solution, I selected Use existing document and specified the SupplyContractVSTO.dot file (see Figure 4).

Figure 4. The Microsoft Office Project Wizard

After clicking the Finishbutton****in the wizard, it creates a new Visual Studio project that contains:

A solution file for the project.
A link to the base template (SupplyContractVSTO.dot).
References to required assemblies that are needed for a Word Template solution (Microsoft.Office.Core, MSForms, VBIDE, Word).

It also creates a ThisDocument.cs class file that mimics the ThisDocument class in a VBA project.

Configuring Global Settings

Now that I have a new project to work with, I want to configure the project with various global settings. First, because the VBA solution relies on ADO version 2.5 for data access, I need to add a reference to this library.

To add the ADO 2.5 Library in Visual Studio .NET

In the Solution Explorer, right click the References node and select Add Reference.

The Add Reference dialog box appears.
Select the COM tab to see the list of installed COM libraries.
From the list of COM libraries, select Microsoft ActiveX Data Objects 2.5 Library and click Select.
Click OK.

A new reference named ADODB is listed under the References node in the Solution Explorer.

Next, there are a number of frequently used libraries referenced in the project. Usually you use the Imports statement with the referenced libraries' namespace at the top of each code file to specify which class libraries are used. This project's source files use several common references and require the Imports statement to be used in each source file. To save time, Visual Basic .NET allows you to define project-level Imports used by every source file in the project.

To Add Project Level Imports in Visual Studio .NET

Select the Projects menu and then click Properties.

The project's Property Pages dialog box appears.
Under Common Properties, click Imports.
Next, add each namespace. See Figure 5 for a list of the namespaces defined in this Visual Studio Tools for Office solution.
Click OK.

Figure 5. The Property Pages dialog box

This little technique is amazingly helpful for future migration steps when I bring in each VBA component. It saves me from having to figure out which namespaces the VBA component is dependent on as well as from having to defining the Imports statement at the top of the new code modules.

ThisDocument

The Visual Studio Tools for Office solution contains a module named ThisDocument.vb. ThisDocument.vb corresponds to the VBA ThisDocument module. In VBA, ThisDocument allows you to capture the following events:

Document_New

This event fires when a new document is created based on the template.
Document_Open

This event fires when an existing document based on the template is opened.
Document_Close

This event fires when the document based on the template is being closed.

The ThisDocument.vb module in the Visual Studio Tools for Office solution plays a similar role and causes ThisDocument_New and ThisDocument_Open to correspond with the VBA Document_New, and Document_Open events.

The code from the VBA ThisDocument class is copied into ThisDocument.vb. A few other changes are also made to ThisDocument.vb. For example, the toggleActiveXControls variable is changed from False to True, signaling that the solution has ActiveX controls in the document. Also, a new function is added called HookGlobalObjects. HookGlobalObjects is used to set up several global objects needed by the solution. These global objects are discussed in the next section.

Note When you migrate a template-based solution, it is a good idea to create a new document based on the template and to save it to disk for testing during the migration process. Because the ThisDocument_Open event only fires when an existing document is opened, it's convenient to have an existing document available for testing the ThisDocument_Open event.

Setting up Global Objects

When developing in the Word VBA environment, you are provided access to several global objects without having to declare them for use. For example:

The ActiveDocument object represents the document that has focus in Word.
The Selection object represents the current text selection in the active document.
ActiveX Controls in the document are also globally addressable through ActiveDocument (for example ActiveDocument.optMail, where optMail is an ActiveX control option button on the document).

For convenience, VBA developers often use these global objects. However, these global objects are not available directly in Visual Studio Tools for Office solutions unless they are declared. Any VBA code in your solution dependent on these global objects fails to compile until the global objects are explicitly declared in the Visual Studio Tools for Office solution. For this Visual Studio Tools for Office solution, I created a module in Visual Studio .NET called basDocumentGlobals.vb. In the following code, I define the global objects needed by the solution.

Public CurrentDocument As Document
Public WithEvents optMail As Microsoft.Vbe.Interop.Forms.OptionButton
Public WithEvents optEmail As Microsoft.Vbe.Interop.Forms.OptionButton
Public WithEvents optFax As Microsoft.Vbe.Interop.Forms.OptionButton
Public WithEvents optCustomerPickup As _
    Microsoft.Vbe.Interop.Forms.OptionButton

The global objects in basDocumentGlobals.vb need to be set when a document is opened; this is done from the events in the ThisDocument.vb module using the ThisDocument_New and the ThisDocument_Open events. The HookGlobalObjects method of the ThisDocument module sets the Global objects:

Private Sub HookGlobalObjects()
    CurrentDocument = ThisDocument
    optMail = FindControl("optMail")
    optFax = FindControl("optFax")
    optEmail = FindControl("optEmail")
    optCustomerPickup = FindControl("optCustomerPickup")
End Sub

In basDocumentGlobals.vb, I declare a variable called CurrentDocument that holds a pointer to the document to substitute where the original VBA code referred to the ActiveDocument object. I could have made a variable called ActiveDocument instead of CurrentDocument and all the code dependent on ActiveDocument would not need to be changed. I decided that it would be good to have a new name for the object, to avoid confusion with the ActiveDocument object provided in the Word object model. With CurrentDocument defined, it is simple to use the Visual Studio Find and Replace feature to replace all instances of "ActiveDocument" with "CurrentDocument".

I also created global objects for each ActiveX control used in the document in basDocumentGlobals.vb. These ActiveX controls are declared with the WithEvents keyword so you can capture the click events of these controls. This is different from VBA, where controls are automatically hooked to their events for you. Because the ActiveX controls need WithEvents to capture the events, I moved the original code behind the ActiveX controls from the VBA ThisDocument module into my new Visual Studio Tools for Office basDocumentGlobals.vb module.

Setting a reference to ActiveX controls embedded into a document can be tricky. The Visual Studio Tools for Office solution created by the Microsoft Office Project Wizard includes a utility function called FindControl(), which, when given a control name as a parameter, does all the work of binding a variable to the ActiveX control.

Now basDocumentGlobals.vb centralizes all the global object logic into one module, and these global objects become available to all modules in the project.

Shared Components

Next, migrate the shared components used in the template. The two shared components are basUtils and clsDAL. Let's first look at basUtils.

To migrate a component, you first have to create a new module in the Visual Studio Tools for Office solution and then copy and paste the original VBA code into the new module. This task is required for each component in your solution.

Add a New Item from Visual Studio .NET

Select the Projects menu and click Add Module.

The Add New Item dialog box appears with a module item selected.
Type in the name of the new module in the Name field. For this module, it is basUtils.
Click Open.

A new module is added to the solution.

Now that you have a new module, open the basUtils module in the VBA project and select all the code. Then copy the code from basUtils and paste it into the new module that was created in Visual Studio.

As you start copying and pasting code from VBA to the Visual Studio Tools for Office solution, it is nice to discover that the VBA code and the Visual Basic .NET code are fairly compatible. For the most part, the pasted VBA code often requires little, if any, change. This is not to say that migrating code is as simple as copy and paste. It's not. But if you are aware of a number of the subtle differences between VBA and Visual Basic .NET, the copied code is easy to fix.

Where you can run into migration challenges is where the VBA code calls out to external libraries. For example, the VBA basUtils module makes WIN 32 API call to GetUserName. GetUserName is not a VBA function, it is a function call provided by Microsoft Windows to get the name of the user currently logged into the computer. The syntax for making WIN 32 API calls is different in Visual Basic .NET. For example, in VBA you declare the WIN 32 API call using the following code:

Declare Function GetUserName& Lib "advapi32.dll" Alias "GetUserNameA" _
    (ByVal lpBuffer As String, nSize As Long)

In Visual Basic .NET, the API call is declared using the DLLImport function attribute:

<DllImport("advapi32.dll")> _
Public Function GetUserName(ByVal lpBuffer As StringBuilder, _
    ByRef nSize As Integer) As Integer
End Function

For more information on converting WIN 32 API calls, see the Visual Basic Language Reference subject Walkthrough: Calling Windows APIs in the Microsoft MSDN Library.

One of the great things about the .NET Framework is that it provides a lot more Windows functionality for free than VBA did. For example in this case you could have used System.Environment.UserName instead of calling the WIN 32 API. However, there may still be cases where you need to call external libraries in .NET, and doing the exercise of trying it here is a great way to see how to do it.

Data Access Layer

Our VBA solution includes a class module called clsDAL. The shared component clsDAL manages access to the remote reseller database used by Trey Research. Just as you did with basUtils, you need to create a new class module in your Visual Studio Tools for Office solution and name it clsDAL. Then copy and paste the VBA code from clsDAL to the new clsDAL module in the Visual Studio Tools for Office solution.

One thing I like about copying VBA code and pasting it into the Visual Basic .NET Editor, is that Visual Basic .NET is smart enough to update some of my VBA code to the new syntax of Visual Basic .NET. For example, the VBA solution uses the Set keyword often when creating objects. Consider the following VBA code:

Set mConn = New ADODB.Connection

The Visual Basic .NET editor automatically updates the code to:

mConn = New ADODB.Connection

This added convenience saves time during migration and cleans up some of the code to use the new Visual Basic .NET syntax.

To retrieve and insert data into the reseller database, clsDAL uses ADO 2.5. The good news is that all the ADO code works without any change (earlier, you set a reference to the ADO 2.5 COM library).

What do have to be changed are the VBA methods Class_Initialize and Class_Terminate. Class_Initalize is first called when a VBA class is created and is now replaced with the New() method in Visual Basic .NET. Class_Terminate is called when the VBA class is shutting down and is now Finalize() in Visual Basic .NET. All I had to do was copy the code from Class_Initialize to New() and the code from Class_Terminate to Finalize().

Note You may have noticed that I like to use the same VBA component names in my migrated Visual Studio Tools for Office solution. For example the old data access layer class was named clsDAL and the new Visual Studio Tools for Office class module is also named clsDAL. Even though the VBA code uses the old-style Hungarian notation for object and variable names, a convention not encouraged when writing .NET code, I prefer to keep the object and variable names the same. It helps reduce confusion as I switch back and forth between the old VBA and new Visual Studio Tools for Office code. Again, the goal is to minimize effort and not rearchitect the solution.

Converting the Form

The most challenging aspect of migrating the VBA solution to Visual Studio Tools for Office is the VBA UserForm (shown in Figure 1). The VBA UserForm engine is not compatible with .NET Windows Forms and so the form has to be manually rebuilt in Visual Studio .NET using the WinForm designer. Recreating a form is a time-consuming process, as each control and its settings have to be duplicated.

On the up side, because you had to convert the form manually, it gave you an opportunity to take advantage of the great controls that are included in the .NET Framework. For example, the .NET Framework includes a DateTimePicker control. This control provides a drop-down calendar so the user can easily browse a calendar for a date. The DateTimePicker also provides date and time validation, eliminating the date validation code of the old VBA solution. Not only did this save some time, but it reduced the amount of code you have to maintain. I was able to get rid of three VBA functions in the UserForm: FormatAsDate, txtAgreementDate_AfterUpdate, and txtPrepDate_AfterUpdate.

The VBA form has a method called UserForm_Initialize that is called when the form is first opened. UserForm_Initialize is not supported in the .NET Windows Form, therefore the UserForm_Initialize code has to be copied into the frmMain_Load event of the new Windows Form.

The form has a combo box control that is used for selecting a customer. The list of customers in the combo box is initialized in the UserForm_Initialize method. In VBA, the combo box control makes this easy to do through data binding an ADO Recordset to the control. The following code shows how this is done:

Dim rsCustomers As Recordset
Set rsCustomers = mData.GetActiveCustomerList
cboSelectClient.Column = rsCustomers.GetRows

Unfortunately, the Windows Form controls do not support binding to ADORecordsets. At first I thought I'd have to write new code to walk through the Recordset and write the records to the combo box. Then I discovered that it is possible to convert a Recordset to an ADO.NET DataSet object and the DataSet can be bound to the Windows Form combo box control.

Dim rsCustomers As Recordset
rsCustomers = mData.GetActiveCustomerList
Dim da As New System.Data.OleDb.OleDbDataAdapter
Dim ds As New DataSet
da.Fill(ds, rsCustomers, "ActiveClients")
cboSelectClient.DataSource = ds.Tables("ActiveClients")
cboSelectClient.DisplayMember = "CompanyName"
cboSelectClient.ValueMember = "ClientID"

The code creates the ADO Recordset and stores it in rsCustomers. Then you create a DataSet object and push the rsCustomers Recordset into the DataSet with the DataAdapter.Fill method. From there, you can bind the DataSet to the Windows Form combo box. This technique requires a few more lines of code than the original VBA, but the approach still requires less code than manually walking each record in the ADO Recordset and populating the combo box. For more information on converting ADO Recordsets to ADO.NET DataSets, see the resource section at the end of this article.

Another difference between VBA UserForms and Visual Studio Tools for Office WinForms is in the properties and methods supported by the Windows Form controls. For example, if a user has not selected a customer when they try to complete the form in the VBA form, it does not close and opens the Select Customer combo box with the following code:

cboSelectClient.SetFocus
cboSelectClient.DropDown

The .NET combo box does not support these methods and had to be converted:

cboSelectClient.Focus()
cboSelectClient.DroppedDown = True

As you can see, the code is fairly similar, but the old VBA code won't compile and has to be changed. This was not an issue though, as the .NET controls in all cases were able to match the functionality of VBA controls, and in most cases the .NET controls were more powerful. In just a few minutes of using the Visual Studio editor's IntelliSense I was able to locate the .NET combo box methods and properties I needed. After the UserForm was converted to a Windows Form, it looks almost identical to the original. Figure 6 shows the converted Windows Form with the DateTimePicker control in action.

Figure 6. Converted Windows Form showing the DateTimePicker control

To wrap up the work on the form, there is a supporting module named basFormData that needs to be migrated. basFormData takes the information gathered in the form and populates it into the document bookmarks and variables. I created a new module named basFormData.vb and copied and pasted the original VBA code into the new module.

I then compiled the Visual Studio Tools for Office solution, and there were no errors. I even ran the project and it looked like everything was working. However, this module was a good reminder that it is important to thoroughly test migrated code, even if it compiles without an error. It wasn't immediately visible, but while testing the migrated solution, I spotted a subtle error in the code: the VBA Solution used the VBA Format() function to format the date fields inserted into the document.

Format(<<Date Value Here>>, "mmmm dd, yyyy")

I discovered that the Visual Basic .NET version of the format function required using uppercase MMMM for the month formatting to display correctly, as shown in the next line of code:

Format(<<Date Value Here>>, "MMMM dd, yyyy")

After making this small change, the Visual Basic .NET format function worked as expected. Again, this is a good reminder to verify the results of the migrated code even if it compiles.

CommandBars

The final component to migrate is the basToolbar module. The basToolbar contains the code that creates the template's CommandBar (see Figure 3). Once again, I created a new module and named it basToolbar.vb. I then copied and pasted the original code from the VBA solution into my new module.

I was able to reuse almost all of the code from the VBA solution, although a few changes were needed. First, I had to add the following module level CommandBarButton variables to basToolbar.vb:

Private WithEvents cbFormEdit As CommandBarButton
Private WithEvents cbInsertButton As CommandBarButton
Private WithEvents cbFinalizeButton As CommandBarButton

The CommandBarButton objects are declared using the WithEvents keyword so the button events can be captured. This is a key difference in how VBA and the .NET Framework handle CommandBars. In VBA, when creating a CommandBar, you specify the public function that is to be called when the user clicks the button through the OnAction property. For example, the VBA solution contains the following code:

Set cbFormEdit = cbar.Controls.Add(msoControlButton)
With cbFormEdit
    .BeginGroup = True
    .Tag = "WizardEdit"
    .OnAction = "RunWizardAgain"
    .FaceId = 1099
    .Caption = "Edit Info"
    .TooltipText = "Edit the contract information"
    .Style = msoButtonIconAndCaptionBelow
End With

I changed this code to:

cbFormEdit = cbar.Controls.Add(MsoControlType.msoControlButton)
With cbFormEdit
    .BeginGroup = True
    .Tag = "WizardEdit"
    .FaceId = 1099
    .Caption = "Edit Info"
    .TooltipText = "Edit the contract information"
    .Style = MsoButtonStyle.msoButtonIconAndCaptionBelow
End With

There are two key differences. First, the VBA code creates a CommandBarButton and assigns it to the variable cbFormEdit. Notice that the VBA code has the OnAction property set to "RunWizardAgain". When a user clicks this cbFormEdit button, Word searches the underlying VBA for a public function named RunWizardAgain. In the Visual Studio Tools for Office solution, you use the .NET approach to capturing control events using WithEvents and specifying which function handles the click event of the button using the Handles keyword. I removed the OnAction property assignment from the migrated code and changed the original VBA function RunFromWizard() to:

Private Sub cbFormEdit_Click(ByVal Ctrl As CommandBarButton,_
    ByRef CancelDefault As Boolean) Handles cbFormEdit.Click

When the user clicks the Edit Info button on the toolbar, .NET calls this function. I had to make the same changes to the other two buttons used in this solution's CommandBar.

The second difference in the CommandBarButton code is in how I set the style property to msoButtonIconAndCaptionBelow. The enumeration msoButtonIconAndCaptionBelow is built into the Office CommandBar type library that defines the way a button should appear. This enumeration is also available in the Visual Studio Tools for Office solution; however the full namespace path to the enumeration must be included. With this button, the full namespace path is MsoButtonStyle.msoButtonIconAndCaptionBelow.

Most of the modules migrated up to this point have included enumeration to referenced type libraries and often require that the full namespace path be used. In your own migration effort, keep in mind that if a property or method set with an enumeration fails to compile, it is likely that you do not have its namespace path fully qualified. If you don't know the full namespace path of an enumeration that won't compile, open the Visual Studio Object Browser and search on the name of the failing enumeration. The Object Browser returns all constant names and their full namespace path that match your search.

General Migration Issues

During the migration, I encountered several common issues. Just about every component that was copied and pasted into a new module required attention to these issues. Here are some to keep an eye out for.

Remove Option Explicit

All the components in the VBA solutions used Option Explicit. When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time. If the Option Explicit statement is not used, all undeclared variables are of Variant type unless the default type is otherwise specified

By default, Visual Basic .NET is configured to use Option Explicit at a project level. Therefore the Option Explicit statement is not needed in the migrated code.

Default Properties

VBA objects and controls support the notion of a default property. If you try to get or set the value of an object or control without specifying the property you are trying to access, VBA assumes you are trying to access the default property. Visual Basic .NET, on the other hand, requires that each property you access be explicitly defined. For example, in the VBA solution, the value in the text box on the form (see Figure 1) was set using the following line of code:

Form.txtAgreementDate = "4/4/2004"

This line of code won't compile in Visual Basic .NET and has to be changed to explicitly state that the Value property should be accessed:

Form.txtAgreementDate.Value = "4/4/2004"

As a developer, I prefer the Visual Basic .NET approach. The Visual Basic .NET convention makes the code self-documenting in that it is clear about what I was changing in the object. Even so, VBA makes it easy to use the default property and you are likely to find a number of VBA objects and controls that must be changed to specify the property being accessed.

Error Handling

Error handling was one area I expected problems, as the VBA solution uses the old Visual Basic error handling syntax. This includes On Error Goto LABEL, On Error Goto 0 and Error Resume Next. To my surprise, the Visual Basic .NET language supports this old style of error handling and compiled just fine.

One thing I discovered during this migration is that you cannot mix the old On Error Goto Visual Basic syntax with the new Visual Basic .NET Try/Catch Error handling syntax in the same function. Your code will not compile if you try to combine these two forms of error handling. If you find that you need to modify the error handling in a routine, it is probably worth the effort to migrate the code to the Try/Catch handling syntax.

Unsupported VBA Commands

In the migration of this solution, the SendKeys command was the only VBA function not supported by Visual Basic .NET. SendKeys sends one or more keystrokes to the active window as if typed at the keyboard.

The VBA solution opens the drop-down list of Contract Text in the solutions CommandBar. Unfortunately the combo box control does not provide a method to open the list. The VBA solution mimics this functionality by selecting the control and then using SendKeys to press the DOWN ARROW key, which opens the list box:

cbCombo.SetFocus
SendKeys "{Down}"

I discovered that some of the old functions available in Word are still exposed through the WordBasic object. WordBasic returns an Automation object (Word.Basic) that includes methods for all the WordBasic statements and functions available in Word version 6.0 and Word for Windows 95. Is this a great solution? No. But it works and allows me to reach my goal of minimizing the effort required to get the solution working. I resolved this problem with the following line of code:

CurrentDocument.Application.WordBasic.SendKeys("{Down}")

Conclusion

The solution is now complete. All that remains is compiling the final project and running the solution through quality assurance. As the goal was to minimize the migration effort, the way the migrated solution is used by the user has not changed. The user continues to use the solution in the same way they did with the original VBA version.

Migrating a VBA solution to Visual Studio Tools for Office can be simplified with good planning, an understanding of the original VBA solution, and a familiarity with the language differences between VBA and Visual Basic .NET.

Happy programming!

Resources

Before migrating a VBA solution, it is a good idea to familiarize yourself with the following reference material:

Converting Code from VBA to Visual Basic .NET. This is an excellent article that summarizes the most common individual language issues for converting VBA code to Visual Basic .NET. This article covers late binding, declaring variables, default properties, ByVal, ByRef parameters, enumerations, non-zero bound arrays, use of parentheses with method calls, set keyword, data access, and conversion of UserForms to Windows Forms.
Converting Microsoft Office VBA Macros to Visual Basic .NET and C#. This article shows you how to migrate an Office VBA macro to the .NET Framework, specifically Visual Basic .NET and C#, and then how to transform that solution into a Visual Basic .NET managed COM add-in.
Revisiting the Use of ADO in .NET Applications. This article explores the technique of using ADO Recordsets with ADO.NET DataSets.

About the Author

Chris Kunicki is a longtime enthusiast of Office development and has been evangelizing Office as an important platform for building solutions for many years. As the founder of OfficeZealot.com, a leading Web site on Office solutions development, Chris builds enterprise solutions, designs tools for developers, delivers presentations, and writes extensively on the topic of Microsoft Office, with the goal of helping developers take control of the world's most powerful software. Find out more about Chris and Office at www.OfficeZealot.com or e-mail him at chris@officezealot.com.