Chapter 12: Migrating VBA Solutions to VSTO
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.
We always overestimate the change that will occur in the next two years and underestimate the change that will occur in the next ten.
—BILL GATES
Visual Basic for Applications (VBA) has been around for a long time, and many people have existing VBA projects. Still, using Microsoft Visual Studio Tools for Office (VSTO) and managed code offers advantages for creating customizations for Microsoft Office Word, Excel, and Outlook:
Code that is managed by the .NET Framework and includes automatic garbage collection
Access to thousands of classes in the .NET Framework class library
Use of an object-oriented programming language
Improved security model
More manageable update and rollback model
Better development, environment, and debugging tools
Enhanced object models that enable data binding and expose events on native Office objects
Data caching and offline capabilities
Ability to create document-specific Word and Excel smart tags
Simplified coding for creating customized Word and Excel task panes
Stable loading of managed add-ins
You have seen the power that VSTO and the .NET Framework bring to Office development. If you’ve decided to make the move to Visual Basic 2005 and VSTO, you should consider the discussion in this chapter before migrating your existing code.
Contents
Migration Strategies
Simple Migration of a Word VBA Project
Advanced Migration of a Word VBA Project
VBA and VSTO Interoperability
Summary
Review Questions
Additional Resources
Migration Strategies
You should consider a number of factors when determining how to move your VBA applications to VSTO. First, it’s a good idea to take an inventory of your VBA applications to judge their complexity and their dependencies. Does a given VBA application consist mainly of recorded macro code, or is it all high-end, handwritten VBA that uses features like UserForms, Web services, and native Windows API calls? The answer to this question will assist you in determining your migration strategy.
Next, inventory your skills. Do you have advanced VBA and Visual Basic knowledge, and do you understand the business problem that the VBA application is trying to solve? Part of the migration process is migrating your programming skills.
The easiest way to convert an application from VBA to VSTO is to convert the VBA syntax to Visual Basic syntax. This is the approach that you will take in the next section (refer to Chapter 4 to learn about the language differences between VBA and Visual Basic 2005). Converting syntax is easy for simple to moderately complex VBA projects, even if you have little knowledge of VBA or VSTO and little domain knowledge. Although this approach will get your VBA application converted, it will not take advantage of the features of VSTO. If you start with a poorly written VBA application, you will end with a poorly written VSTO application.
The next choice is to take a balanced approach. You convert much of your VBA code line by line, exploiting VSTO features where applicable. For example, in Advanced Migration of a Word VBA Project later in this chapter you will use an actions pane instead of a simple Windows Forms dialog box to collect user information. You could use XML nodes instead of bookmarks, and data caching instead of hidden worksheets. It requires more VSTO and domain knowledge to redesign the relevant parts of an application.
The most advanced way to migrate your VBA project is to completely redesign the entire solution. This approach requires the most VBA and VSTO knowledge and the most domain knowledge. The advantage is that you can focus on end-user goals, and you are not bound to the requirements that were specified when the application was originally written. User requirements change frequently, and redesigning the application gives you the opportunity to address many user needs. However, this approach is the most costly and time-consuming. Depending on the size or complexity of the application, you may want to purchase a third-party conversion tool to automate part of the conversion. You might also want to use interop to convert parts of the application in stages. This method allows you to incrementally add VSTO functionality to an existing VBA application.
Another advanced approach is to use VBA-to-VSTO interoperability, calling VSTO functions from VBA and calling your VBA functions from VSTO. In this way, you can migrate your applications to VSTO in phases. For example, if you need to call a Web service, you can do it very easily in VSTO. But if your application uses a complex VBA macro, it might be easier to reuse it than to rewrite it.
Whichever approach you take, the benefits of moving to VSTO and managed code will pay off in the long term with increased developer productivity, quicker time to market, and advanced user features. However, if your VBA solution is working and meets your business requirements, you are not required to migrate the solution. VBA will be supported by Microsoft for the foreseeable future, giving you time to carefully plan your migration when it makes sense for your business.
Simple Migration of a Word VBA Project
In this section, you will migrate a sample Word VBA application to VSTO. As shown in Figure 12.1, the application is a simple memo template that prompts users for information when they create a new document. After users enter information into the fields of the VBA UserForm and click OK, the bookmarks on the document are populated with the information.
The New event fires when a new document is created from a template. It is here that you will write the code to show the UserForm, because the form is displayed only when the document is created. You also set other properties of the document, such as the caption. The frmMain.Show code in Listing 12.1 displays the UserForm in Figure 12.1.
Listing 12.1. Displaying a UserForm when a new document is created
Private Sub Document_New()
With ActiveDocument
.SpellingChecked = True
.GrammarChecked = True
.ActiveWindow.Caption = _
.ActiveWindow.Caption & " - Memo"
.ActiveWindow.View.ShowFieldCodes = False
End With
' Show the form.
frmMain.Show
End Sub
Figure 12.1. Word VBA application with input form
.jpg "Word VBA application with input form")
Figure 12.2 shows the UserForm in the design view of the VBA Editor.
As shown in Listing 12.2, the VBA code handled by the Click event of the OK button sets the text box values to the correct bookmark and then closes the form.
Figure 12.2. Input form in the VBA Editor
.jpg "Input form in the VBA Editor")
Listing 12.2. Populating bookmarks on a UserForm Click event
Private Sub btnOK_Click()
' Insert the fields into the bookmarks.
With ActiveDocument
.Bookmarks("To").Range = txtTo
.Bookmarks("From").Range = txtFrom
.Bookmarks("CC").Range = txtCC
.Bookmarks("Re").Range = txtRe
.Bookmarks("Subject").Range = txtSubject
End With
Unload Me
End Sub
Figure 12.3 shows the expected results of your VBA project. The bookmarks have been populated with the correct information from your UserForm.
Figure 12.3. Completed Word VBA application
.jpg "Completed Word VBA application")
Advanced Migration of a Word VBA Project
You will now convert the VBA memo project that you created in the preceding section. You will convert the project to VSTO using the easiest conversion path without changing any semantics of the original VBA program.
Copying the Word Template
Copy the existing Word template file so that you have a template to use for your VSTO solution. Name the new template Professional Memo VSTO.dot.
Removing the VBA Code and the UserForm from the Template
Open the template in Word by clicking Open on the File menu, and then press ALT+F11 to open the VBA Code Editor.
In the Forms folder, delete the frmMain form.
In the Microsoft Word Objects folder, open the ThisDocument code. Delete all the code by selecting the code with the mouse and pressing the DELETE key.
Save the file as Professional Memo VSTO.dot.
Now that you have removed all the VBA code, you are ready to use the template to create your VSTO project.
Create a new VSTO Word project.
Start VSTO and create a new Word Template project. Name the new project Professional Memo VSTO and then click OK to start the VSTO Project Wizard.
Select Copy An Existing Document, and type the path to Professional Memo VSTO.dot, as shown in Figure 12.4. Click OK to create the VSTO project.
Figure 12.4. Using an existing document template
.jpg "Using an existing document template")
Creating a Windows Form
VSTO does not use the same forms system used by VBA, so you need to re-create your VBA form using Windows Forms. Add a new Windows Form to the VSTO project by right-clicking the project, pointing to Add, and then clicking Windows Form. In the Add New Item dialog box, name the form frmMain.vb. A new Windows form is added to your project, and the designer is opened.
You can now drag and drop controls onto your document to create a form that looks like your VBA UserForm. You can see in Figure 12.5 that the Windows Form is nearly identical.
Figure 12.5. Creating a Windows Form
.jpg "Creating a Windows Form")
You are now ready to populate the bookmarks when the user selects OK. Double-click the OK button on frmMain to create a Click event handler for the OK button. Type the code in Listing 12.3. You can see that it is almost identical to the VBA code. One difference is that you get a reference to the Document object in the With statement. In this case, because you are in a Windows Form you use the Globals object to reference the ThisDocument object. In addition, Visual Basic 2005 does not support default properties so you must specify the text property in all cases. You close the Windows Form using its Close method.
Listing 12.3. Populating bookmarks on an OK button Click event
Public Class frmMain
Private Sub btnOK_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnOK.Click
' Insert the fields into the bookmarks.
With Globals.ThisDocument
.Bookmarks("To").Range.Text = txtTo.Text
.Bookmarks("From").Range.Text = txtFrom.Text
.Bookmarks("CC").Range.Text = txtCC.Text
.Bookmarks("Re").Range.Text = txtRe.Text
.Bookmarks("Subject").Range.Text = txtSubject.Text
End With
' Close the form.
Me.Close()
End Sub
End Class
Starting the Form
Now that you have created your form, you are ready to launch it when the user creates a new document from the template. The New event of the document fires when a new document instance is created. Right-click ThisDocument.vb in Solution Explorer, and click View Code. In the Class Name drop-down box (left), select ThisDocument Events. The Method Name drop-down box (right) now displays all the events of the Document object.
Select New in the right drop-down to create an event handler to handle the New event. Type the code in Listing 12.4 in the New event handler. This code is very similar to the VBA code. The first thing you change is the way you reference the Document object. Instead of using Active-Document, you use the Me keyword. Also, the way you launch the Windows Form is a little different. Because Visual Basic 2005 is an object oriented language, you create a new instance of the frmMain object.
Listing 12.4. Displaying a Windows Form when a new document is created
Public Class ThisDocument
Private Sub ThisDocument_New() Handles Me.New
Me.SpellingChecked = True
Me.GrammarChecked = True
Me.ActiveWindow.Caption = _
Me.ActiveWindow.Caption & " - Memo"
Me.ActiveWindow.View.ShowFieldCodes = False
' Show the UserForm.
Dim frmMain As New frmMain
frmMain.Show()
End Sub
Private Sub ThisDocument_Startup(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Startup
End Sub
Private Sub ThisDocument_Shutdown(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Shutdown
End Sub
End Class
Running the Solution
You have completed migrating your VBA code, and you’re ready to run your new VSTO solution. Press F5 to run the solution. You can see in Figure 12.6 that the VSTO solution works the same as the VBA application did.
Figure 12.6. VSTO application with Windows Form
.jpg "VSTO application with Windows Form")
Converting your VBA to VSTO is not difficult, but this solution doesn’t take advantage of the VSTO features. In the next section, you will change some things in your solution to make it more like .NET and to use more features of VSTO.
Redesigning the Solution
Now let’s go beyond a simple conversion to use more of the power of VSTO and the .NET Framework. You will upgrade your project to use the Bookmark view control to program against the Word bookmarks as first-class .NET objects. You will then convert your Windows Form to use the Document Actions task pane.
Bookmark View Controls
As you learned in Chapter 6, when you create a bookmark in Word while in the Visual Studio IDE, a wrapper object is created called a view control. The Bookmark view control shows up in IntelliSense as a programmable object. And unlike regular Bookmark objects, Bookmark view controls are not late bound; thus, you do not need to refer to the bookmark using its string name from the Bookmarks collection.
Using late-bound objects can cause problems because typographical errors in the name are not found until run time. The names of the Bookmark view controls will be the same names as the bookmarks they wrap. To make the code more readable, you will append the type name to the names of all the view controls. For example, To becomes ToBookmark. The code also updates the Windows Form field names so that instead of using the Hungarian notation of VBA, you use a more .NET-style naming convention, as shown in Listing 12.5. The sample is not only more resilient to document changes but also easier to read and program.
Listing 12.5. Populating Bookmark view controls on an OK button Click event
Public Class MainForm
Private Sub btnOK_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles OkButton.Click
' Insert the fields into the bookmarks.
With Globals.ThisDocument
.ToBookmark.Text = ToTextBox.Text
.FromBookmark.Text = FromTextBox.Text
.CCBookmark.Text = CCTextBox.Text
.REBookmark.Text = ReTextBox.Text
.SubjectBookmark.Text = SubjectTextBox.Text
End With
' Close the form.
Me.Close()
End Sub
End Class
Using the Actions Pane
You will now convert your sample from showing a modal dialog box to showing an actions pane that will enable the user to update the bookmarks. As described in Chapter 5, the actions pane is docked and does not obstruct users’ view of the document as a Windows Form does. This helps the users understand the context of the fields they are adding.
Converting a Windows Form to a User Control
It is super easy to make this conversion. A user control can be placed in other controls or forms. In the example, you will put your user control onto the actions pane. You need to change the type that your form derives from. It’s now System.Windows.Forms.Form, and you need to change it to System.Windows.Forms.UserControl.
To do this, click on the Show All Files button at the top of Solution Explorer. Expand the MainForm.vb node in Solution Explorer to see the code-behind files. Open the MainForm.Designer.vb class, and change the type that the form inherits from, as shown in Listing 12.6. Save and close the MainForm.Designer.vb class, and click the Show All Files icon again to hide the code-behind files. Open the MainForm.vb class, and notice that it is now a user control and does not have a title bar.
Listing 12.6. Change the Windows Form to a user control type
<Global.Microsoft.VisualBasic.CompilerServices. _
DesignerGenerated()> _
Partial Class MainForm
Inherits System.Windows.Forms.UserControl
Let’s look at how to show the MainForm UserControl inside of the ActionsPane when the document is started.
Right-click the ThisDocument.vb file in Solution Explorer, and click View Code.
Change the New event handler to the code in Listing 12.7. The only change is the way you open the form. You are creating a new instance and adding it to the actions pane’s Controls collection in one line, shown in Listing 12.7 in bold.
Listing 12.7. Displaying a user control in the actions pane when a new document is created
Public Class ThisDocument Private Sub ThisDocument_New() Handles Me.New Me.SpellingChecked = True Me.GrammarChecked = True Me.ActiveWindow.Caption = _ Me.ActiveWindow.Caption & " - Memo" Me.ActiveWindow.View.ShowFieldCodes = False ' Show the user form. ActionsPane.Controls.Add(New MainForm()) End Sub Private Sub ThisDocument_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup End Sub Private Sub ThisDocument_Shutdown(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Shutdown End Sub End ClassChange the MainForm control code to the code in Listing 12.8. The only line that changes is the line Me.Visible = False. This code hides the form after the fields are inserted into the document.
Listing 12.8. Populating bookmark view controls on an OK button Click event
Public Class MainForm Private Sub btnOK_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles OkButton.Click ' Insert the fields into the bookmarks. With Globals.ThisDocument .ToBookmark.Text = ToTextBox.Text .FromBookmark.Text = FromTextBox.Text .CCBookmark.Text = CCTextBox.Text .REBookmark.Text = ReTextBox.Text .SubjectBookmark.Text = SubjectTextBox.Text End With 'Hide the form. Me.Visible = False End Sub End ClassRun the solution by pressing F5. You now have an Actions pane docked to the right side of the document that contains the MainForm user control, as shown in Figure 12.7.
Figure 12.7. VSTO application with actions pane
.jpg "VSTO application with actions pane")
VBA and VSTO Interoperability
VBA and VSTO interop is not a built-in feature of VSTO or VBA, so you must manually connect the two programming environments. Connecting VBA and VSTO allows you to call user defined functions (UDFs), which can be written only in VBA. A UDF is a VBA function that can be used in Excel formulas and other places. In Chapter 7, you learned how to create a simple UDF. In this section you will create a UDF to calculate the distance between two points.
Creating an Interop Excel Application
To create the sample Excel application, which calculates the distance between two points in two-dimensional space, you will use a user defined function written in VBA.
Open a new Excel document, and save it as CalculateDistance.xls to a location of your choice.
Create the spreadsheet shown in Figure 12.8.
Figure 12.8. Calculating the distance between two points
.jpg "Calculating the distance between two points")
Select cell B5 and enter the formula to call the UDF that you will create in the next section.
=VBA_CalculateDistance2D(B2,C2,B3,C3)
Select cell B6 and enter the formula to call the UDF that you will create in the next section.
=VSTO_CalculateDistance3D(B2,C2,D2,B3,C3,D3)
Creating the User Defined Functions in VBA
Because Excel supports UDFs only in VBA (and automation DLLs, a topic outside the scope of this book), you must create your UDFs in a VBA module. You will also use this module to call your VSTO functions.
Press ALT+F11 to open the VBA Editor.
Right-click the Modules node under VBA Projects (CalculateDistance.xls), and choose Insert | Module from the context menu. This action creates a new module with the default name Module1.
Add the code in Listing 12.9 to Module1.
Listing 12.9. Creating UDFs and implementing VSTO interop in VBA
Public Class MainForm Dim VSTOFunctions As Object ' VSTO will call this function to connect to VBA. Public Sub RegisterVSTOFunctions(VSTOFunctionsReference _ As Object) Set VSTOFunctions = VSTOFunctionsReference End Sub Public Function VBA_CalculateDistance2D( _ X1 As Integer, Y1 As Integer, _ X2 As Integer, Y2 As Integer) As Double Dim Distance2D As Double Distance2D = Sqr((X2 - X1) ^ 2 + (Y2 - Y1) ^ 2) VBA_CalculateDistance2D = Distance2D End Function ' Calculate the 3D distance in VSTO. Public Function VSTO_CalculateDistance3D( _ X1 As Integer, Y1 As Integer, Z1 As Integer, _ X2 As Integer, Y2 As Integer, Z2 As Integer) _ As Double Dim Distance3D As Double Distance3D = VSTOFunctions.CalculateDistance3D( _ X1, Y1, Z1, X2, Y2, Z2) VSTO_CalculateDistance3D = Distance3D End Function End Class
The first thing we add to the module is a variable called VSTOFunctions, which will hold a reference to the VSTO object. It is passed when VSTO calls the RegisterVSTOFunctions subroutine during the document’s Open event. When you have a reference to this object, you can call public methods on this object from VBA. Note that VBA uses late binding to call the methods, so you do not see IntelliSense for this object.
In this example, you have created two UDFs. The first of these, VBA_CalculateDistance2D, is implemented in VBA. The other UDF, VSTO_CalculateDistance3D, is a pass-through to the VSTO function CalculateDistance3D. These functions complete the VBA side of the interop.
Note that the VBA functions are wrappers around the VSTO functions, allowing you to create managed UDFs. Now let’s look at the VSTO side.
Creating a VSTO Excel Project
You now need to create a VSTO project that uses the CalculateDistance.xls file that you built earlier. Create a new Excel Workbook project. In the Create New Project Wizard, select Copy An Existing Document. Browse to and select CalculateDistance.xls.
Creating the VSTOFunctions Class
You will now create a class called VSTOFunctions. This class contains the functions that will be called by VBA.
On the Project menu, click Add Class. Name the class VSTOFunctions.vb.
Add the code in Listing 12.10.
Listing 12.10. Creating the VSTOFunctions class
<System.Runtime.InteropServices.ComVisible(True)> _
Public Class VSTOFunctions
' Called by VBA.
Public Function CalculateDistance3D( _
ByVal X1 As Integer, ByVal Y1 As Integer, _
ByVal Z1 As Integer, ByVal X2 As Integer, _
ByVal Y2 As Integer, ByVal Z2 As Integer) _
As Double
Dim Distance3D As Double
If Z1 + Z2 = 0 Then
'This is 2D so call CalculateDistance2D (in VBA).
Return CalculateDistance2D(X1, Y1, X2, Y2)
End If
' Calculate the 3D distance.
Distance3D = Math.Sqrt((X2 - X1) ^ 2 + (Y2 - Y1) ^ 2 + _
(Z2 - Z1) ^ 2)
Return Distance3D
End Function
' Call the VBA function.
Public Function CalculateDistance2D( _
ByVal X1 As Integer, ByVal Y1 As Integer, _
ByVal X2 As Integer, ByVal Y2 As Integer) As Double
' Use the existing CalculateDistance2D written in VBA.
Return Globals.ThisWorkbook.Application.Run( _
"VBA_CalculateDistance2D", X1, Y1, X2, Y2)
End Function
End Class
The first thing you add to the class is an attribute called ComVisible, whose value you set to True. This attribute allows you to pass this class to VBA. Next, you create two functions. The CalculateDistance3D function is implemented in Visual Basic 2005 and is called from the VBA VSTO_CalculateDistance3D function you created earlier. The CalculateDistance2D function calls VBA_CalculateDistance2D in VBA. The CalculateDistance2D function is called by the CalculateDistance3D function if the values of Z1 and Z2 are zero.
This example demonstrates calling VSTO from VBA, and calling VBA from VSTO. The final step is to connect VSTO and VBA when the workbook opens.
Connecting VBA and VSTO When the Workbook Opens
When the workbook opens, you call the RegisterVSTOFunctions subroutine in VBA, passing a reference to an instance of the VSTOFunctions class you created earlier.
Add the code in Listing 12.11 to the ThisWorkbook.vb class.
Listing 12.11. Calling the RegisterVSTOFunctions subroutine in VBA when the workbook opens
Public Class ThisWorkbook Private Sub ThisWorkbook_Open() Handles Me.Open ' Connect VSTO and VBA. Me.Application.Run("RegisterVSTOFunctions", _ New VSTOFunctions) End Sub Private Sub ThisWorkbook_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup End Sub Private Sub ThisWorkbook_Shutdown(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Shutdown End Sub End ClassPress F5 to run the VSTO solution.
When the workbook opens, type some values for the two points. You might need to press CTRL+ALT+F9 to get Excel to recalculate the formulas.
As you have seen, implementing VBA and VSTO interoperability is not very complicated. This technique can be a powerful part of your migration strategy because it lets you migrate individual functions to VSTO one at a time.
Summary
We started this chapter by discussing VBA migration and explained what is involved in migrating to VSTO. You then looked at various strategies for this migration. You saw how to migrate Word and Excel VBA projects to VSTO, and you learned about some of the issues that are involved in converting VBA forms to Windows Forms. Finally, you saw how to implement interoperability between VSTO and VBA.
Review Questions
What are some of the reasons to migrate VBA solutions to Visual Basic 2005 and VSTO?
What are some of the strategies for migrating VBA solutions to VSTO?
How can the actions pane be used to replace dialog boxes?
What types of forms are used in VBA and VSTO?
Name one advantage of using the actions pane instead of a Windows Form.
Additional Resources
For more information, see the following resources: