10 Tips for Migrating VBA to Visual Basic Using Visual Studio Tools for Office Second Edition (Part 1 of 2)

Summary: Read 10 tips about how to migrate Microsoft Visual Basic for Applications code to Microsoft Visual Basic code by using Microsoft Visual Studio 2005 Tools Second Edition for the Microsoft Office system. (19 printed pages)

Ken Getz, MCW Technologies, LLC

Jan Fransen, MCW Technologies, LLC

November 2007

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007, Microsoft Office Word 2007, Microsoft Visual Studio 2005 Tools Second Edition for the 2007 Microsoft Office System

Contents

• Migrating to Visual Studio

• Tip #1: Call Managed Code from Command Bar Controls

• Tip #2: Customizing the Office Fluent Ribbon

• Tip #3: Learn How to Create Windows Forms

• Tip#4: Add Custom Task Panes

• Tip #5: Convert Existing VBA Code

Read part two: 10 Tips for Migrating VBA to Visual Basic Using Visual Studio Tools for Office Second Edition (Part 2 of 2).

Migrating to Visual Studio

This technical article is for developers who create solutions by using Microsoft Visual Basic for Applications (VBA) and who want to migrate their code to the managed code environment provided by Microsoft Visual Studio and the Microsoft .NET Framework. Because we (the authors of this paper) are developers who work in both VBA and Visual Studio, we understand the difficulties and challenges that you face.

In this article, we share what we learned when we moved from VBA to Microsoft Visual Studio 2005 Tools Second Edition for the 2007 Microsoft Office system. The tips in this article focus on what you need to understand as you migrate to the new platform. This article presents conversion scenarios, discusses pitfalls, and offers advice to help you successfully migrate your code. Although this article is not an in-depth reference, the tips and suggestions help you get started. Throughout the article, we provide links to other references that help you make the transition from VBA to Visual Basic.

Note
In this article, VBA refers to Visual Basic for Applications and Visual Basic refers to the programming language you use in Visual Studio. When necessary, we explicitly refer to Visual Basic 6.0.

We assume that you can create and debug add-ins by using Visual Studio 2005 Tools for Office Second Edition (SE). For more information about downloading, installing, and using Visual Studio 2005 Tools for Office SE, see How to: Create Visual Studio Tools for Office Products. For more information about the Visual Studio 2005 user interface, see Don’t Freak Out About Visual Studio.

Tip #1: Call Managed Code from Command Bar Controls

In many VBA applications, the user clicks a command bar button to launch your custom code. One of the first things you need to learn is how to provide a user interface element so the user can run your code. In the 2007 Microsoft Office system, custom command bars do not look like they look in Microsoft Office 2003. Instead of appearing as separate toolbars, custom command bars are grouped together on the Add-Ins tab, as shown in Figure 1.

Figure 1. Command bar buttons appear in the Add-Ins tab

Although we recommend that you learn how to customize the Microsoft Office Fluent user interface (Tip #2: Customizing the Office Fluent Ribbon), you can continue to use the CommandBars object model in Visual Studio 2005 Tools for Office SE. To make your custom buttons work, you need to change your existing code so that clicking the button launches managed code instead of a macro. When you use the CommandBars object model, your custom buttons appear in the Add-Ins tab in a 2007 Office system application. Customizing the Office Fluent Ribbon offers you more flexibility because you can control where your buttons or UI elements appear.

Running Managed Code from CommandBar Controls

Because the 2007 Office system still exposes the CommandBar object model, you can dynamically add command bars and their controls when your application loads. You can also assign a variable that points to an existing CommandBar object or CommandBarControl object. However, you cannot use the OnAction property to determine the click action for a button. Instead, you need to write an event handler and attach it to the button’s Click event.

Suppose that you have a VBA add-in and you want to migrate it to Visual Studio 2005 Tools for Office SE but, for now, you want to continue to use the CommandBar object model. The following procedure shows you how to create a simple add-in that demonstrates this capability.

To create a simple add-in

1. In Visual Studio 2005, create a 2007 Add-in project and use the Excel Add-in template. For more information about creating add-ins, see How to: Create Visual Studio Tools for Office Products.

2. In the Solution Explorer, double-click the ThisAddIn.vb class file.

3. Add the following declaration inside the ThisAddIn class, but outside any procedure.

   Private WithEvents sheetInfoButton As Office.CommandBarButton
   

4. Add the following procedure to the ThisAddIn class.

   Private Sub SetupCommandBars()
   
     Dim commandBar As Office.CommandBar = _
      Application.CommandBars. _
      Add("VSTOAddinToolbar", _
      Office.MsoBarPosition.msoBarTop, , True)
     commandBar.Visible = True
   
     ' Add a button with an icon that looks like a report.
     sheetInfoButton = _
      CType(commandBar.Controls.Add( _
      Office.MsoControlType.msoControlButton), _
      Office.CommandBarButton)
   
     sheetInfoButton.Tag = "Display Sheet Info"
     sheetInfoButton.FaceId = 2522 'or 2160, 2522, 2950
     sheetInfoButton.TooltipText = "Display Sheet Info"
     sheetInfoButton.DescriptionText = _
      "List all the sheets in the workbook."
   End Sub
   

   The code first obtains a reference to the top-level CommandBars object and adds a command bar to the set of Office Excel command bars.

   Dim commandBar As Office.CommandBar = _
    Application.CommandBars. _
    Add("VSTOAddinToolbar", _
    Office.MsoBarPosition.msoBarTop, , True)
   commandBar.Visible = True
   

   Visual Studio 2005 Tools for Office SE automatically defines the Application reference for you. The reference always refers to the host application, just as it does within VBA programs.

   Although it looks like the code refers to standard objects from the Microsoft Office type library, it really refers to objects provided by a managed wrapper around the type library. For example, the CommandBars object is part of the Microsoft.Office.Core namespace. To avoid having to type the full name of the class (Microsoft.Office.Core.CommandBars) each time you want to refer to the class, the Visual Studio 2005 Tools for Office SE project templates include project-wide Imports statements. In this case, the project template automatically includes the code Imports Office=Microsoft.Office.Core.

   That way, when you refer to Office.CommandBars, you actually refer to the complete reference. The Imports statement saves time as you write code. You can also add your own Imports statements to the top of any code file. For more information about importing a namespace, see Tip #7: Learn the .NET Framework in 10 Tips for Migrating VBA to Visual Basic Using Visual Studio Tools for Office Second Edition (Part 2 of 2).

   The next block of code creates a CommandBarButton control. The code uses the CType method to convert the return value of the Add method (a CommandBarControl object) to the specific CommandBarButton type. The code can do this because you create a CommandBarButton object.

   sheetInfoButton = _
    CType(commandBar.Controls.Add( _
    Office.MsoControlType.msoControlButton), _
    Office.CommandBarButton)
   

   The code next sets properties of the CommandBarButton control.

   sheetInfoButton.Tag = "Display Sheet Info"
   sheetInfoButton.FaceId = 2522 'or 2160, 2522, 2950
   sheetInfoButton.TooltipText = "Display Sheet Info"
   sheetInfoButton.DescriptionText = _
    "List all the sheets in the workbook."
   

   You still need to provide an event handler for the button’s Click event. In VBA, the code editor creates the event handler for you. Because you specified WithEvents when you declared sheetInfoButton in Step 3, Visual Studio creates the event handler for you as well.

   Note

   If you do not want to use the WithEvents keyword to indicate that the control needs an event handler, you can use the AddHandler statement. This statement requires that you specify a particular event (sheetInfoButton.Click, in this case), along with the address of a procedure that the add-in runs when the event occurs. You use the AddressOf keyword to indicate that what follows is the name of the procedure to run. The procedure you specify must meet stringent requirements: It must have the exact procedure signature that the event expects to find. It is easier to use the WithEvents keyword, but sometimes you need more explicit control over how and when to attach event handlers to controls.

5. At the top of the code window, in the drop-down list on the left, select sheetInfoButton. In the list, select the Click event. Visual Studio inserts the sheetInfoButton_Click procedure, including the correct set of parameters. Modify the procedure so that it looks like this.

   Private Sub sheetInfoButton_Click( _
    ByVal Ctrl As Office.CommandBarButton, _
    ByRef CancelDefault As Boolean) _
    Handles sheetInfoButton.Click
   
     Dim sw As New System.IO.StringWriter
     For Each sheet As Excel.Worksheet In Application.Worksheets
       sw.WriteLine(sheet.Name)
     Next
     MsgBox(sw.ToString, MsgBoxStyle.OkOnly, "Sheet Info")
   End Sub
   

   The code in the sheetInfoButton_Click procedure loops through all the sheets in the workbook and adds the name of each to a memory buffer. The code then uses the MsgBox method to display the contents of the memory buffer. (You can also use the .NET Framework method MessageBox.Show, to accomplish the same goal). The System.IO.StringWriter class provides a convenient mechanism for writing text with multiple lines into a memory buffer—you can use the ToString method of the class to retrieve all the text as a single string, as in this example. When you click the command bar button, the code in the sheetInfoButton_Click event handler runs and displays an alert with the names of all the sheets in the workbook.

6. In the ThisAddIn_Startup procedure, after the existing code, add the following code, to set up the command bars.

   SetupCommandBars()
   

7. Save and run the project.

8. In Office Excel, click the Add-ins tab. You see your button in the Custom Toolbars group. Click the button, and you should see results shown in Figure 2.

   Figure 2. The dialog box that your custom button displays

   

9. Exit Office Excel and return to Visual Studio. Save your add-in because you use it again in the next section.

For a review of the Office CommandBars object model, read How to: Create Office Menus Programmatically, which contains an example of creating a command bar and button by using WithEvents to handle the button’s click event.

Tip #2: Customizing the Office Fluent Ribbon

If you want more control over where your buttons appear in a 2007 Office system application, you need to write code to customize the Office Fluent Ribbon. The Ribbon is a component of the Microsoft Office Fluent user interface, introduced in the 2007 Microsoft Office system. The Office Fluent Ribbon changes the way users interact with the host application’s menu system. Rather than grouping items by functionality into static, layered menus, the Ribbon works with you as you interact with the application. As you modify a table in Word 2007, the Office Fluent Ribbon displays tools that help in the current context. If you select a style in the Ribbon, Word 2007 updates the style of the selection even before you close the gallery of styles, so you see the effect of updating in real time.

You can modify almost any facet of the Office Fluent Ribbon. You can add your own tabs, groups, and controls. You can hide built-in controls or override the behavior for built-in controls. In this article, you learn how to use Visual Studio 2005 Tools for Office Second Edition to provide a platform for customizing the Ribbon. For more information about the Ribbon, see the Office Fluent User Interface Developer Portal.

In general, you can customize the Office Fluent Ribbon in two different ways. You can:

• Add Ribbon markup to Open XML Format files (Microsoft Word 2007, Microsoft Excel 2007, Microsoft PowerPoint 2007) directly, by inserting the XML part into the document. In this case, you generally handle Ribbon interaction and events by using VBA code in the document itself.

• Create a COM add-in that provides the Ribbon markup and event-handling code.

When you customize the Ribbon, you must provide XML markup that defines the content of the customization and you must provide code (either in VBA, or in managed code) that both reacts to Office Fluent Ribbon control events and provides dynamic content.

If you create a COM add-in, you can create a Visual Studio 2005 shared add-in, or you can use Visual Studio 2005 Tools for Office SE to create the add-in. Using Visual Studio 2005 Tools for Office SE is easier and more robust, and is the technique presented in this article.

Because Office Fluent Ribbon customization is a complicated topic, this article focuses on a simple customization: the one that Visual Studio 2005 Tools for Office SE provides when you add Ribbon support to your add-in. The following procedure shows you how to create a simple Ribbon customization

To create a simple Ribbon customization

1. Using the add-in that you created in the previous example, on the Project menu, click Add New Item.

2. In the Add New Item dialog box, click Ribbon Support. Accept the default name (Ribbon1.vb), and click Add. This action adds two new items to your project: Ribbon1.vb and Ribbon1.xml.

3. Open Ribbon1.xml. It contains the following declarative information about the items that appear on the Add-Ins tab on the Ribbon.

   <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnLoad">
     <ribbon>
       <tabs>
         <tab idMso="TabAddIns">
           <group id="MyGroup"
                  label="My Group">
             <toggleButton id="toggleButton1" 
                           size="large"
                           label="My Button"
                           screentip="My Button Screentip"
                           onAction="OnToggleButton1" 
                           imageMso="HappyFace" />
           </group>
         </tab>
       </tabs>
     </ribbon>
   </customUI>
   

   Although you need to study the documentation on Ribbon markup to understand this XML completely, it is easy to understand what the markup does. This XML adds a new group named MyGroup to the Add-Ins tab. The group has a toggle button labeled My Button, and on the button is an image of a happy face. Clicking the button executes a procedure in the add-in named OnToggleButton1.

4. In Solution Explorer, double-click Ribbon1.vb to examine its code.

5. The first portion of the Ribbon1.vb file contains a partial class that is commented out. This code connects the Office Fluent Ribbon named Ribbon1 to the add-in, and indicates to Visual Studio 2005 Tools for Office SE where to find this particular Ribbon customization. Because Visual Studio 2005 Tools for Office SE cannot predict what your add-in does, this code is commented out. You must uncomment the code for the add-in to work. Select the entire partial class named ThisAddIn (but not the comments immediately before it), and click the Uncomment the selected lines toolbar button, as shown in Figure 3.

   Figure 3. Uncomment the entire ThisAddIn partial class within the Ribbon1.vb file

   

6. Scroll down in the Ribbon1.vb file to find the Ribbon1 class, which describes the behavior of your Office Fluent Ribbon customization. Expand the Callbacks section to find the OnToggleButton1 procedure. This code uses the isPressed parameter to display different text when the user presses and releases the toggle button.

7. Expand the Helpers code region to find the GetResourceText procedure, which the add-in uses to load the Office Fluent Ribbon’s XML markup, so that the host application can display the Ribbon customization.

   Note
   For an easier way to load the XML content, see the references that follow for more information and details on customizing the Office Fluent Ribbon.

8. Save and run your add-in. In Office Excel, click the Add-Ins tab to see the new button on the Office Fluent Ribbon. When you click the button, the OnToggleButton1 procedure runs and you see the alert shown in Figure 4.

   Figure 4. Your Ribbon customization in action

   

9. Exit Office Excel and return to Visual Studio.

For more information about working with the Office 2007 Ribbon, see the following resources:

• Customizing the 2007 Office Fluent User Interface Using Visual Studio 2005 Tools for the Office System SE (Part 1 of 2)

• Customizing the 2007 Office Fluent User Interface Using Visual Studio 2005 Tools for the Office System SE (Part 2 of 2)

• Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3)

• Customizing the 2007 Office Fluent Ribbon for Developers (Part 2 of 3)

• Customizing the 2007 Office Fluent Ribbon for Developers (Part 3 of 3)

• Custom Task Panes, the Office Fluent Ribbon, and Reusing VBA Code in the 2007 Office System

• Developer Overview of the User Interface for the 2007 Microsoft Office System.

Tip #3: Learn How to Create Windows Forms

Although much of the code in your VBA modules translates almost directly to Visual Basic, user form code does not. If your VBA application uses forms, you need to recreate the user interface in Visual Basic by using Windows Forms. You can view this downside as an opportunity, though: Windows Forms provides more controls and features than are available in VBA user forms.

Note
When you design your application, be aware that a custom task pane might be a better user interface choice.

Although designing Windows Forms in Visual Studio 2005 Tools for Office SE is a different experience than designing forms in VBA, Windows Forms is far more flexible. It is easy to create and display Windows Forms as part of a Visual Studio 2005 Tools for Office SE add-in. This tip points out some of the differences between user forms and Windows Forms, and shows you how to create your own simple form.

One difference between user forms and Windows Forms is that user forms are modal by default. This means that when a user form is displayed, you cannot access the host application until you hide or close the user form. Windows Forms are modeless by default.

Windows Forms provide most of the controls you find in the VBA user forms toolbox.

Table 1. Comparison of user forms and Windows forms

Each of the Windows Forms controls is slightly different from its user form equivalent. The properties, methods, and events are all different enough that you need to refer to the documentation. In general, however, they are functionally equivalent.

Suppose that you want an Office Excel add-in to display a form that allows users to select a date to insert in the current cell. Although you could use a Microsoft ActiveX control on a user form to accomplish this goal, the task is quite simple using Windows Forms. The following procedure shows you how to modify the example you created in the previous tip to display a Windows Form.

To display a Windows Form

1. Start with the add-in you created earlier, or create an add-in for Excel 2007, and add Office Fluent Ribbon customization support. As discussed previously, make sure you uncomment the ThisAddIn partial class in the Ribbon1.vb code file.

2. In Ribbon1.xml, change the markup so that the toggle button becomes a regular button, and replace the OnAction attribute with a different procedure name. When you are done, the markup for the toggleButton element should look like the following.

   <button id="myButton" 
           size="large"
           label="My Button"
           screentip="My Button Screentip"
           onAction="OnClick" 
           imageMso="HappyFace" />
   

3. On the Project menu, click Add Windows Form. Click Add to create a new Windows Form named Form1.vb in your add-in. Set properties of the form as shown in the following table.

   Table 2. Properties for your form

   Property	Value
   MaximizeBox	False
   MinimizeBox	False
   Text	Select a Date

4. From the Toolbox window, drag a MonthCalendar control onto the new form. (If you do not see the Toolbox window, on the View menu, click Toolbox) Resize the form until it is big enough for the control, as shown in Figure 5.

   Figure 5. A simple Windows form that allows users to select a date

   

5. Double-click the MonthCalendar control. This action creates an event handler for the control’s DateChanged event. Modify the MonthCalendar1_DateChanged procedure, adding the following code.

   Globals.ThisAddIn.Application.ActiveCell.Value = _
    MonthCalendar1.SelectionRange.Start.ToShortDateString
   Me.Close()
   

   The add-in template provides the Globals class. The Globals class allows you to access the ThisAddIn class, which you can use to access the host’s Application object. Using the host’s Application object, the code finds the ActiveCell reference, and sets its Value property to the selected date, converted to a string.

   Note

   If, in the future, you plan to convert this code to Microsoft Visual C#, we advise you to replace all references to the Value property with the similar Value2 property. The Value property accepts a parameter, and Visual C# does not support parameterized properties. Because of this, Visual C# does not recognize the Value property. Using Value2 in your Visual Basic code simplifies conversion to Visual C#.

   After inserting the selected date, the calendar form closes.

6. In the Ribbon1.vb file, add the following procedure within the Ribbon1 class after the OnToggleButton1 procedure.

   Public Sub OnClick(ByVal control As Office.IRibbonControl)
     Using demoForm As New Form1
       demoForm.ShowDialog()
     End Using
   End Sub
   

   This code creates an instance of the Form1 class that you just created, and ensures that the common language runtime destroys the form when you close it—the Using block handles this for you. Inside the Using block, the code calls the ShowDialog method of the form, which displays the form modally, much like a user form.

7. Save and run the solution. In Office Excel, on the Add-ins tab, click the new button. The calendar form appears. Select a date, and the form inserts the selected date into the current cell, and closes the form.

You can do much more with Windows Forms in Visual Studio 2005 Tools for Office SE add-ins, but this simple example helps you get started.

Note
The operating system determines where the form is displayed. If you want to make the form appear within the boundaries of Office Excel, you need more complex code. For more information, see the newsgroup posting How to get IWin32Window for Show/ShowDialog in Excel.

For more information about getting started with Windows Forms, see the following references:

• Windows Forms for Visual Basic 6.0 Users is a good reference if you are familiar with both Visual Basic 6.0 forms and VBA user forms.

• Windows Forms is a portal to articles, how tos, and reference materials related to Windows Forms in Visual Studio 2005.

Tip#4: Add Custom Task Panes

You may find that displaying Windows Forms does not meet your needs. Perhaps you want to display a task pane, docked to the edge of the host application window, and add your content there. Custom task panes make this possible and they are easy to create.

Note
The following 2007 Office system applications support custom task panes: Microsoft Office Access, Office Excel, Microsoft Office InfoPath, Microsoft Office Outlook, Microsoft Office PowerPoint, and Office Word. Some 2007 Office system applications, such as Microsoft Office Visio 2007, do not support them.

When you create a custom task pane, you write code to determine exactly what appears on the task pane at runtime. Creating a custom control is easier, especially if you want to display more than a single control on the task pane—that is, a control that combines many other controls. Then, when it comes time to display the task pane, you can add an instance of your custom control, instead of adding each control individually, in code. The following procedure shows you how to create a simple custom task pane that allows you to insert a date, as in the previous tip, to a docked task pane.

To create a simple custom task pane

1. Start with the add-in you created earlier, or create an add-in for Excel 2007.

2. On the Project menu, click Add User Control. In the Add New Item dialog box, name your user control CalendarTaskPaneControl, and click Add when you are done. Visual Studio displays a designer for your control.

3. Select the control designer, and set its properties as shown in the following table:

   Table 3. Properties for your user control

   Property	Value
   Font	Segoe UI, 10pt
   Size	232, 400

4. From the Toolbox, drag a Label control onto the control designer. Set its properties as shown in the following table:

   Table 4. Properties for your Label control

   Property	Value
   AutoSize	False
   Dock	Top
   Text	Date Selector Task Pane
   Text Align	BottomCenter

5. Drag a MonthCalendar control onto the control designer, and place it below the Label control.

6. Drag a Button control onto the control designer, and place it below the MonthCalendar control. Set the button’s Anchor property to Top, Left, Right. Set its Text property to Insert Selected Date. When you finish the user control looks like Figure 6.

   Figure 6. This user control is the basis for your custom task pane

   

7. Double-click the button you created, and add the following code to the button’s Click event handler.

   Globals.ThisAddIn.Application.ActiveCell.Value = _
    MonthCalendar1.SelectionRange.Start.ToShortDateString()
   

8. Within the ThisAddIn.vb file, in the ThisAddIn_Startup procedure, add the following code at the end of the procedure. This code creates the custom task pane, adds it to the application’s collection of custom task panes, sets its width, and displays it.

   Dim ctp As Microsoft.Office.Tools.CustomTaskPane = _
    Me.CustomTaskPanes.Add( _
    New CalendarTaskPaneControl, "Calendar Task Pane")
   ctp.Width = 232
   ctp.Visible = True
   

9. Save and run the project. When the application starts, you see the custom task pane shown in Figure 7. Select a date, and click the button. The task pane inserts the selected date into the worksheet.

   Figure 7. Your custom task pane inserts a date into the worksheet.

   

Custom task panes have many benefits. Using custom task panes, you can:

• Install as many task panes as you like.

• Display multiple task panes concurrently.

• Show and hide task panes as necessary.

• Dock, move, and resize task panes.

• Customize task panes at run time by using code.

For more information about custom task panes, see the following resources:

• Creating Custom Task Panes Using Visual Studio 2005 Tools for the Office System SE

• Managing Task Panes in Multiple Word and InfoPath Documents

• Creating Custom Task Panes in the 2007 Office System

• Custom Task Panes, the Office Fluent Ribbon, and Reusing VBA Code in the 2007 Office System

Tip #5: Convert Existing VBA Code

As a VBA developer, you already know the object models for the Microsoft Office products you use. You probably also have a large library of VBA code that you use as a resource when you create applications. As you migrate to Visual Studio 2005 Tools for Office SE, you may think that you need to rewrite your code to make it work in Visual Basic. Fortunately, for the most part, you can copy your VBA code into Visual Studio and it works with no or minor changes. (If your VBA code uses classes, it is more likely that it translates directly to Visual Basic.) A common problem when migrating VBA code to Visual Basic is ambiguous references. If the Visual Basic compiler cannot figure out where to find the value for a variable, you need to help the compiler resolve (or disambiguate) the reference. This is discussed in the example in this tip.

For example, suppose you have a VBA function named CreateOutline that you wrote for Microsoft Office Word. The CreateOutline function builds a new document that contains an outline of the current document, and you want to migrate this function to a Visual Studio 2005 Tools for Office SE add-in. The CreateOutline function calls another function named GetLevel. The code for both functions follows.

Public Sub CreateOutline()
    Dim docOutline As Word.Document
    Dim docSource As Word.Document
    Dim rng As Word.Range
    
    Dim astrHeadings As Variant
    Dim strText As String
    Dim intLevel As Integer
    Dim intItem As Integer
        
    Set docSource = ActiveDocument
    Set docOutline = Documents.Add
    
    ' Content returns only the
    ' main body of the document, not
    ' the headers and footer.
    Set rng = docOutline.Content
    astrHeadings = _
     docSource.GetCrossReferenceItems(wdRefTypeHeading)
    
    For intItem = LBound(astrHeadings) To UBound(astrHeadings)
        ' Get the text and the level.
        strText = Trim$(astrHeadings(intItem))
        intLevel = GetLevel(CStr(astrHeadings(intItem)))
        
        ' Add the text to the document.
        rng.InsertAfter strText & vbNewLine
        
        ' Set the style of the selected range and
        ' then collapse the range for the next entry.
        rng.Style = "Heading " & intLevel
        rng.Collapse wdCollapseEnd
    Next intItem
End Sub

Private Function GetLevel(strItem As String) As Integer
    ' Return the heading level of a header from the
    ' array returned by Word.
    
    ' The number of leading spaces indicates the
    ' outline level (2 spaces per level: H1 has
    ' 0 spaces, H2 has 2 spaces, H3 has 4 spaces.
        
    Dim strTemp As String
    Dim strOriginal As String
    Dim intDiff As Integer
    
    ' Get rid of all trailing spaces.
    strOriginal = RTrim$(strItem)
    
    ' Trim leading spaces, and then compare with
    ' the original.
    strTemp = LTrim$(strOriginal)
    
    ' Subtract to find the number of
    ' leading spaces in the original string.
    intDiff = Len(strOriginal) - Len(strTemp)
    GetLevel = (intDiff / 2) + 1
End Function

To test these functions, start Office Word and copy them into a new module in the Visual Basic Editor. Create a test document that has some headings (use styles such as Heading 1 and Heading 2) and some text. With your cursor in the CreateOutline function, press F5. The function creates a document that contains an outline of your test document.

The following procedure shows you how to create a Visual Studio 2005 Tools for Office SE add-in that uses this code, converted to Visual Basic.

To create an add-in

1. Start Visual Studio and create an add-in project using the Word Add-in template.

2. Follow the instructions in Tip #2 and Tip #3 that walk you through creating a simple Office Fluent Ribbon customization. Add a button to the Ribbon, as shown in those tips. (Ensure the OnAction attribute in the XML content contains the text OnClick—that is the name of the button’s Click event handler.)

3. In the Ribbon1.vb file, inside the Ribbon1 class, copy the two VBA procedures CreateOutline and GetLevel. The code has few compiler errors and almost works as is.

   Note
   In Visual Studio, blue underlines indicate compiler errors.

4. Add the following procedure to the Ribbon1 class.

   Public Sub OnClick(ByVal control As Office.IRibbonControl)
     CreateOutline()
   End Sub
   

   At this point, the GetLevel function compiles completely and the CreateOutline method contains only a few places that need modification.

   Note

   The Option Strict setting (which you can apply per file or per project) affects the behavior of the compiler. It is off by default. If you add Option Strict On to the top of the file, or if Option Strict is the default setting in Visual Studio, more lines of code have compiler errors. If your code has many compiler errors, you probably have this setting turned on. To see if this setting is turned on, on the Tools menu click Options, expand Projects and Solutions, and click VB Defaults. For now, add Option Strict Off to the top of the code file. Later, we discuss how to get the code to work with Option Strict turned on.

5. The following line fails because the compiler cannot find the ActiveDocument reference, which is global in VBA code written for Office Word.

   docSource = ActiveDocument
   

   To solve the problem, replace the code with the following.

   docSource = Globals.ThisAddIn.Application.ActiveDocument
   

6. The same issue applies to the next error.

   docOutline = Documents.Add
   

   Replace that code with this fixed version.

   docOutline = Globals.ThisAddIn.Application.Documents.Add
   

   Note
   You might find it simpler to create a Word.Application variable and assign it the value Globals.ThisAddIn.Application. You can then use that variable rather than the full reference in the fixed code.

7. The following fails because the Visual Basic compiler cannot determine which enumeration provides the wdRefTypeHeading value.

   astrHeadings = _
    docSource.GetCrossReferenceItems(wdRefTypeHeading)
   

   Because Office Word uses loosely typed values for its enumerations, you must resolve the reference. In contrast, Office Excel and most of the other 2007 Office system applications create typed enumerations that are easier to resolve. To resolve the reference in Office Word, search for it by its full name, wdRefTypeHeading, in the Object Browser. The Object Browser is available from the View menu, as shown in Figure 8. You can also use the Object Browser in the Visual Basic Editor in Office Word to resolve the reference.

   Figure 8. The Object Browser can help resolve enumeration references

   

   Note
   In general, it is easier to find the full reference for enumerated values in VBA code written for Office Excel and Office PowerPoint. In Visual Studio, press the space bar immediately before the ambiguous value, and Visual Studio can usually find the correct enumeration, allowing you to select from a list of possible values.

8. Based on your findings in the Object Browser, replace the problematic code with the following.

   astrHeadings = _
    docSource.GetCrossReferenceItems( _
    Word.WdReferenceType.wdRefTypeHeading)
   

   Note
   You can shorten the namespace because the add-in template includes an Imports statement that defines Office Word as Microsoft.Office.Interop.Word.

9. The following line of code fails to compile for the same reason.

   rng.Collapse(wdCollapseEnd)
   

10. Search for the enumerated value in the Object Browser, and after you find it, replace the problematic code with the following.

    rng.Collapse(Word.WdCollapseDirection.wdCollapseEnd)
    

11. Save and run the project.

12. To test the conversion, create a blank document in Office Word and add text with headings that use the Heading 1, Heading 2, and Heading 3 styles. On the Add-Ins tab, click the button for your customization. The code should create an outline of your document. Close Office Word when you are finished.

Because Option Strict is off, converting VBA code to Visual Basic is relatively easy. Turning Option Strict on provides much better type safety and allows the compiler to determine code that has late-binding or loosely-typed conversions. In general, your code is safer if you turn Option Strict on. However, when you do, you have more work to do when you convert code.

Although we do not recommend using Option Strict On when you convert VBA code to Visual Basic (although we do recommend using Option Strict On when you write new code), it is instructional to convert the code with Option Strict turned on. The following procedure shows you how to make the code compile with Option Strict on.

To convert the code with Option Strict on

1. Scroll to the top of the Ribbon1.vb file, and add the following as the first line of code in the file.

   Option Strict On
   

2. You now see more compile errors in the code. In this example, the errors are almost all caused by the same problem: The astrHeadings variable is now defined as an Object. Although the variable is originally defined as a Variant, when you copied the code into Visual Basic, the code editor changed the definition. Because the variable is defined as an Object, later code cannot treat it as an array, which the code tries to do. To fix the problem, change the declaration of astrHeadings so that it is an array of strings.

   Dim astrHeadings As String()
   

3. Explicitly declaring the variable fixes some of the compile errors. The code can now retrieve the LBound and UBound values, although in Visual Basic, the LBound value is always 0. Now the call to GetCrossReferenceItems fails. The call fails because this method returns an Object, not an array of strings. To fix the problem, use the CType method to cast the result as the correct type. Change the call to GetCrossReferencesItems to the following.

   astrHeadings = _
    CType(docSource.GetCrossReferenceItems( _
    Word.WdReferenceType.wdRefTypeHeading), String())
   

4. The remaining error occurs in the GetLevel function: the final line of code does not compile. If you rest the pointer on the code, the tip shown in Figure 9 displays, indicating that the code includes an implicit type conversion.

   Figure 9. The Visual Studio editor helps fix compiler errors

   

   You can use the red icon near the error to display the Error Correction Assistant, as shown in Figure 10. Rest the pointer on the red icon, and when the arrow appears, click it. (You can also press SHIFT+ALT+F10 after you click the error.)

   Figure 10. The Error Correction Assistant fixes some errors for you

   

5. Click the link in the Error Correction Assistant, as shown in Figure 11. Visual Studio replaces the code with code that performs an explicit conversion.

   GetLevel = CInt((intDiff / 2) + 1)
   

   Figure 11. Select a link in the Error Correction Assistant to fix errors for you

   

After all of that work, you might feel it is better to leave Option Strict off and, in general, we agree with you. When you convert VBA code to Visual Basic, it might be better to leave Option Strict off. But, when you write new code, turning Option Strict on helps you create better, safer code.

In this tip, we cannot cover all the issues that you need to consider when you copy VBA code to Visual Basic.

Table 5. Common Conversion Issues.

Examine the following references for more in depth coverage of all the differences between VBA and Visual Basic.

• Visual Basic .NET Internals describes many details. Find out when to use VBA constructs, and when to migrate to .NET Framework classes.

• Convert VBA Code to Visual Basic When Migrating to Visual Studio 2005 Tools for Office lists problem areas, such as fully qualifying enumerations, differences in the way dates are handled, parameter passing (ByVal default instead of ByRef), and array bases (the lower bound cannot be changed).

• Converting Code from VBA to Visual Basic .NET

• Life Without On Error Goto Statements

• Custom Task Panes, the Office Fluent Ribbon, and Reusing VBA Code in the 2007 Office System

• Object Library Reference for the 2007 Microsoft Office System summarizes the changes for each version of Microsoft Office since Microsoft Office 97 and also provides conceptual overviews and how-tos.

• Free Book - Introducing Microsoft Visual Basic 2005 for Developers

Read part two: 10 Tips for Migrating VBA to Visual Basic Using Visual Studio Tools for Office Second Edition (Part 2 of 2).