Converting Code from VBA to Visual Basic .NET

Kathleen McGrath
Microsoft Corporation

June 2003

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Word 2003
    Microsoft Office Excel 2003
    Microsoft Visual Basic® for Applications (VBA)
    Microsoft Visual Studio® .NET 2003
    Microsoft Visual Basic .NET

Summary: Presents issues to consider when converting your Microsoft Office solutions from Visual Basic for Applications (VBA) to Visual Basic .NET. Provides a general overview of their differences, and then uses examples from Word and Excel to describe how you can use Visual Studio Tools for the Microsoft Office System to convert your code. (20 printed pages)

Contents

Introduction
Should I Convert my VBA Code to Visual Basic .NET?
Differences Between VBA and Visual Basic .NET
   Language Differences
   Project Management Differences
   Security Differences
   Deployment Differences
Converting VBA Code
   VBA Code Example in Word
   VBA Code Example in Excel
Conclusion
Appendix
Related Articles

Introduction

This article addresses issues you might encounter as you begin to convert your Microsoft® Visual Basic® for Applications (VBA) code to Microsoft Visual Basic .NET. It also serves to help you learn what changes are necessary when you move from VBA (unmanaged code) to code managed by the Microsoft .NET Framework (managed code). First, I will discuss the reasons for converting—and not converting—your code to Visual Basic .NET. Then I will introduce many of the changes you will notice as you convert your code, including differences in the integrated development environment (IDE), language, UserForms, security, and deployment. Finally, I will show you how to take VBA code from existing Word and Excel code modules and convert it to Visual Basic .NET, using Microsoft Visual Studio Tools for the Microsoft Office System.

Should I Convert My VBA Code to Visual Basic .NET?

Before you begin to convert your VBA code to Visual Basic .NET, you might want to look at some of the advantages and disadvantages. Visual Studio Tools for the Microsoft Office System enables you to write managed code behind Microsoft Office Word 2003 documents and Microsoft Office Excel 2003 workbooks. A document or workbook with custom properties that link it to a managed code assembly is said to have managed code extensions. Using managed code extensions, you can take advantage of the resources available in Microsoft Visual Studio® .NET and the Microsoft .NET Framework, including security, deployment, full support for XML Web services, structured exception handling, and Microsoft Windows® Forms. However, there might be situations where you would benefit from leaving your code in VBA.

When thinking about whether to convert your VBA code to Visual Basic .NET, consider the following:

Use VBA...

• When the code needs to be tightly bound to the document; for example, when the code needs to travel with the document.
• When there are deployment constraints within your organization, for example, if the .NET Framework will not be available to the end user.
• When you want to use Excel custom functions.
• When you want to create methods that intercept the native commands of Word.
• When you are customizing Microsoft Office applications other than Microsoft Office Word 2003 or Microsoft Office Excel 2003. These other applications are not supported by Visual Studio Tools for the Microsoft Office System.

Use Visual Basic .NET...

• When you want to take advantage of the Visual Studio .NET environment, including Server Explorer and enhanced IntelliSense functionality.
• When your solutions can benefit from the full resources of the .NET Framework, for example, built in Web services support, easier XML parsing, and access to all of the classes available in the .NET Framework.
• When you want easier code maintenance. The code in the assembly is separate from the document, and multiple documents can reference the same assembly.
• When you want flexible deployment options. The assembly can be deployed to a network share; then, each time a user opens an Excel or Word document while connected to the network, updated code in the assembly is immediately available.
• When you would like to implement a more flexible security model.

Differences between VBA and Visual Basic .NET

Understanding the differences between VBA and Visual Basic .NET can help you make a more informed decision about converting your code. This section examines differences in the following areas:

• Language
• Project management
• Security
• Deployment

Language Differences

Because Visual Basic .NET was designed to take advantage of the .NET Framework, it contains many changes and areas where compatibility with previous versions of the language has not been preserved. The following is a partial list of changes to the Visual Basic language in Visual Basic .NET. For more information, see Introduction to Visual Basic .NET for Visual Basic Veterans.

• Late Binding.   VBA and Visual Basic .NET support late binding; however, using early-bound objects makes your code easier to read and maintain and enables IntelliSense. Visual Basic .NET introduces the Option Strict On statement, which enforces early binding and prevents implicit conversion where data might be lost. The compiler default is Option Strict Off. One reason this is important is that many of the methods and properties of Office objects return the type Object, and you must explicitly convert the object to the correct type, as shown in the following example:

  ' Using Ctype to convert the Object.returned by Sheet1 to a Worksheet.
  MsgBox(CType(ThisWorkbook.Worksheets("Sheet1"), Excel.Worksheet).Name)
  

• **Declaring Variables.   **In VBA, you can use the Option Explicit statement to enforce explicit variable declaration. You can also set this automatically by selecting the Require Variable Declaration check box in the VBA IDE options, which by default is not selected. All implicitly declared variables are of Variant type.

  The Visual Basic .NET compiler enforces explicit declaration, requiring that every variable be declared. You can override this by using the statement Option Explicit Off. All implicitly declared variables are of Object type. You should consider this when copying and pasting code from VBA to Visual Basic .NET, because the Variant data type is no longer supported and will automatically be converted to the Object data type. You should explicitly type all variables declared in your project.

• Default Properties.   In Visual Basic .NET, default properties are only supported if the properties take arguments. In VBA, you can use shortcuts when typing code by eliminating the default properties. For example:

  ActiveDocument.Tables(1).Cell(1, 1).Range = "Name"
  

  When converting this code to Visual Basic .NET, you must type out the default property of the Range object, which is Text:

  ThisApplication.ActiveDocument.Tables(1).Cell(1, 1).Range.Text = "Name"
  

  Note that the default property for the Tables object, Item, is not required because it takes an index parameter. However, your code will be more readable if you include all of the default properties:

  ThisApplication.ActiveDocment.Tables.Item(1).Cell(1, 1).Range.Text _
      = "Name"
  

• **ByVal, ByRef Parameters.   **In VBA, parameters are passed by reference by default. In Visual Basic .NET, parameters are passed by value by default. When preparing your code for conversion to Visual Basic .NET, you might want to check that all methods explicitly define whether the parameters should be passed by reference or by value. When you paste code into the Visual Studio .NET IDE with parameters that are not defined, ByVal is automatically added to each parameter in the list.

• **Enumerations.   **Enumeration constants must be fully qualified in Visual Basic. NET. When converting your VBA code, you must add the fully qualified enumeration name to both Word and Excel constants. For example, when performing a search in Word VBA, you specify the FindWrap value of the Selection or Range using a wd constant. Three options are given: wdFindStop, wdFindAsk, and wdFindContinue. In VBA, you can simply assign the constant because the enumeration is global to your project:

  Selection.Find.Wrap = wdFindContinue
  

  In Visual Basic .NET, you must fully qualify the constant with the enumeration name:

  ThisApplication.Selection.Find.Wrap = Word.WdFindWrap.wdFindContinue
  

  This may seem like a lot of extra typing, but if you use the IntelliSense feature of Visual Studio .NET, locating and typing the qualified constants is relatively easy, and it makes your code more readable. If you are already familiar with the constant names, you will find that the enumeration name often closely matches the constant name. In the case above, they both contain wdFind. To explore the available enumerations, type Word.Wd and scroll through the items available in the IntelliSense drop-down list (type Excel.XL to scroll through the list of available enumerations for Excel).

• **Non-Zero Bound Arrays.   **In VBA, the default lower bound of an array dimension is 0 (zero). Using Option Base, you can change this to 1. In Visual Basic .NET, the Option Base statement is not supported, and the lower bound of every array dimension must be 0. Additionally, you cannot use ReDim as an array declaration. One thing to keep in mind when working with Office collections from Visual Basic .NET is that the lower array bounds of most Office collections begin with 1.

• Use of Parentheses with Method Calls.   In VBA, parentheses are optional in some cases when you call subroutines, and it is sometimes difficult to remember when they are required. In Visual Basic .NET, parentheses are required when passing parameters in a method call.

• Set Keyword.   In VBA, the Set keyword is necessary to distinguish between assignment of an object and assignment of the default property of the object. Since default properties are not supported in Visual Basic .NET, the Set keyword is not needed and is no longer supported. This change is illustrated in the following examples:

  ' VBA
  Dim mySelection as Selection
  Dim myOtherSelection as String
  Set mySelection = Selection
  myOtherSelection = Selection
  
  ' Visual Basic .NET
  Dim mySelection As Word.Selection
  Dim myOtherSelection As String
  mySelection = ThisApplication.Selection
  myOtherSelection = ThisApplication.Selection.Text
  

• **Data Access. **   Data binding to a Data Access Object (DAO) or Remote Data Object (RDO) data source is not supported in Microsoft Visual Basic .NET. ActiveX® Data Objects (ADO) data binding is supported for backward compatibility; however, you may want to consider converting to ADO.NET. For more information, see Comparison of ADO.NET and ADO.

• **Conversion of UserForms to Windows Forms.   **VBA UserForms cannot be copied or imported into Visual Studio .NET. In most cases, you will need to recreate your forms as Windows Forms. The use of drag-and-drop controls is consistent with creating forms in VBA, but event handlers for Windows Form controls are handled differently. Many new features are available to make creating your forms easier than before, for example:

  • Control anchoring is now possible, so that when a user resizes your form, the controls automatically resize and reposition properly.
  • Setting tab order is much easier with Windows Forms. Enable tab ordering by clicking Tab Order on the View menu. Then simply click each control in the preferred order.
  • Creating menus in-line is an improvement over menu creation in VBA.
  • In VBA, you can show a form as vbModal or vbModeless. In Visual Basic .NET, the ShowDialog method is used to display a form modally; the Show method is used to display a form non-modally. Note, however, that the form will display non-modally, but when you click in the Word or Excel document, the form moves to the background, which can be confusing to your users.
  • Many new form controls are also available in Visual Basic .NET, such as data-entry validators, common dialog boxes, hyperlinked labels, system tray icons, panels, numeric-up/downs, on-the-fly designable tree views, Help file linkers, ToolTip extenders, and more.

Project Management Differences

When you start using Visual Basic .NET to create your Office solutions, one difference you will notice is the location of the code in your project. When you use VBA to create an Office solution, the code resides in modules, UserForms, and class modules within a Word template, Word document, or Excel workbook. When you use Visual Studio Tools for the Microsoft Office System, the code resides in classes and Windows Forms, which are compiled into an assembly, and is referenced by the Word or Excel document.

The Visual Studio .NET IDE has enhanced functionality, but it is similar to the VBA IDE in many ways. Each has an explorer for viewing projects, modules, forms, and references. Each provides a properties window, toolbox, object browser, and debugging capabilities. Table 1 lists some differences you might notice when managing your projects.

Table 1. Differences in the VBA and Visual Studio .NET IDE

Another difference that you will find is in the use of ActiveX controls. In VBA, ActiveX controls are top-level objects and have IntelliSense support. In Visual Basic .NET, you must define variables for the controls, use the FindControl method in Visual Studio Tools for the Microsoft Office System, and convert them to a strong type in order to access IntelliSense.

Security Differences

The Microsoft .NET Framework provides security features that you cannot take advantage of in VBA. In VBA, there are three basic security options:

• Set the security settings to high on user machines and digitally sign your code.
• Let the user decide whether or not to trust the code when presented with the macro virus warning.
• Set the security to low to allow all code to run (including malicious code). Note that this third option should never be used.

Word and Excel documents with managed code extensions that are created using Visual Basic .NET do not use Office macro security, which relies on the Office certificate store. They incorporate the standard security features available in the Microsoft .NET Framework 1.1, for example:

• Code signing is no longer necessary, as there are several types of evidence that are available for security in .NET Framework, including Application Directory, Strong Name, URL, and more.
• Administrators can use standard tools to set security policies. The security policy must grant full trust to an assembly or the code cannot execute.
• The end user cannot change security options within Word or Excel to permit untrusted code to run. If the end user opens a document with untrusted code, the code will not run.

For more information on setting security in the .NET Framework, read the Visual Studio Tools for the Microsoft Office System help topic, "Security in Office Solutions That Use Managed Code Extensions," or see An Overview of Security in the .NET Framework.

Deployment Differences

Deploying Visual Basic .NET applications is quite different from deploying VBA applications. Visual Studio Tools for the Microsoft Office System projects usually consist of two files: the assembly, which contains the compiled code, and the document (Excel worksheet or Word document or template), which contains custom properties that point to the assembly. The document and assembly are deployed separately. Unlike many VBA projects where the code is embedded in the document, in Visual Studio Tools for the Microsoft Office System projects, the assembly can be stored in a shared network location, or it can be copied to each end user's computer. The advantage in deploying an assembly to a network location is that it is easier to update the code because you will have only one copy of the assembly on the network share. Users can modify and customize their copy of the document and will have access to the updated assembly every time the document is opened. This happens automatically, with no user intervention.

Converting VBA Code

In this section, I will show you how to convert VBA code to Visual Basic .NET using Visual Studio Tools for the Microsoft Office System. I have included an example from Word and one from Excel. The complete Visual Basic .NET code for both examples is available in the Appendix.

VBA Code Example in Word

The following Word macro creates a new style if it does not already exist, and then applies the style to all paragraphs that are left aligned in the active document.

' VBA
Private Sub Document_Open()
    Dim currentParagraph As Paragraph

    ' Call CreateStyle, passing the name and attributes.
    CreateStyle "MyNewStyle", "Arial", 9.5, True, False, 0.5

    ' Apply Style to each paragraph that is left aligned.
    For Each currentParagraph In ActiveDocument.Paragraphs
        If currentParagraph.Alignment = wdAlignParagraphLeft Then
            currentParagraph.Style = "MyNewStyle"
        End If
    Next currentParagraph
End Sub
Private Function StyleExists(styleName As String) As Boolean
    Dim currentStyle As Style
    Dim stylePresent As Boolean
    stylePresent = False

    ' Check for existence of style in active document.
    For Each currentStyle In ActiveDocument.Styles
        If currentStyle.NameLocal = styleName Then
            stylePresent = True
            Exit For
        End If
    Next currentStyle

    ' Return.
    StyleExists = stylePresent
End Function

Private Sub CreateStyle(styleName As String, styleFontName As String, _
    styleFontSize As Single, styleBold As Boolean, _
    styleItalic As Boolean, Optional styleFirstLineIndent As Single, _
    Optional styleSpaceBefore As Single)

    ' Check if the style already exists.
    If Not StyleExists(styleName) Then

        ' Create the style with attributes passed.
        ActiveDocument.Styles.Add styleName
        With ActiveDocument.Styles(styleName)
            .Font.Name = styleFontName
            .Font.Size = styleFontSize
            .Font.Bold = styleBold
            .Font.Italic = styleItalic
            .ParagraphFormat.FirstLineIndent = _ 
                InchesToPoints(styleFirstLineIndent)
            .ParagraphFormat.SpaceBefore = _
                InchesToPoints(styleSpaceBefore)
            .ParagraphFormat.Alignment = wdAlignParagraphLeft
        End With
    End If
End Sub

Creating a Visual Basic .NET Project

To convert your VBA code, you need to create a Word document project in Visual Studio .NET using Visual Studio Tools for the Microsoft Office System.

To create a new Word Document project

1. From the File menu, point to New, and then click Project.
2. In the New Project dialog box, expand Microsoft Office 2003 Projects, and then select Visual Basic Projects.
3. In the Templates pane, select Word Document.
4. Name the project Create Word Style, and then click OK.
5. When the Microsoft Office Project Wizard appears, be sure that Create new document is selected, and click Finish.

Next, add text to your Word document.

To add text to the document

1. Open the Create Word Style.doc document in Microsoft Office Word 2003.

   A warning might appear stating that the assembly name or Assembly Link Location property is corrupted, because you have not compiled the assembly yet. Click OK to close the warning.

2. Add a few paragraphs of text, setting varying paragraph alignments. For example, center a title, left align a few paragraphs, and right align a few paragraphs.

3. Save and close the Word document.

Copying the Code

Next, you need to copy and paste the VBA code into your new Word Document project in Visual Studio .NET.

To copy and paste VBA code into the Word project

1. In your Visual Basic .NET project, locate the OfficeCodeBehind class.

2. Copy the VBA code from the Document_Open subroutine (do not include the Sub and End Sub keywords) and paste it in the ThisDocument_Open method.

3. Copy the complete StyleExists function and the CreateStyle subroutine and paste them into the OfficeCodeBehind class below the ThisDocument_Close method (not within this method).

   Notice that some of objects (Style, ActiveDocument) have blue squiggles, or wavy lines, under them, indicating that they are problematic in the code. Also notice that when you pasted the code, ByVal was automatically added to the parameter list for the function StyleExists, but not for the CreateStyle subroutine. This is because the CreateStyle subroutine has optional parameters that need default values, described later in this article.

   **Tip   **You may have noticed that the Visual Studio .NET Tools menu contains an Upgrade Visual Basic 6 Code menu item. This tool will convert Visual Basic 6 language differences; however, Visual Studio Tools for the Microsoft Office System uses Microsoft Office Word 2003 primary interop assemblies (PIAs) as a way to access the Word object model (which is the way managed code can interoperate with COM), and this cannot be resolved using the upgrade tool. Instead, issues and warnings in the form of comments will appear throughout your code. You can get the same information when you paste your VBA code directly in the Visual Studio .NET IDE, through the IntelliSense features available when hovering your mouse pointer over text that contains a blue squiggle. For more information on Office primary interop assemblies, see Working with the Office XP Primary Interop Assemblies.

Converting the Code

After you have copied the VBA code into your new project, you can begin to convert the code.

To convert the code in Visual Basic .NET

1. In the declaration statement of the StyleExists function:

   Dim currentStyle as Style
   Dim stylePresent As Boolean
   stylePresent = False
   

   The Style object belongs to the Word namespace, so you need to change it to the qualified reference of Word.Style. You can also combine the declaration statement of stylePresent with the assignment of False:

   Dim currentStyle as Word.Style
   Dim stylePresent As Boolean = False
   

2. In the first line of the For Each statement:

   For Each currentStyle in ActiveDocument.Styles
   

   In VBA, ActiveDocument is a member of the Application object. Because you write your code directly in the application, the reference to the Application object is given to you automatically. In VBA, you can, but do not need to, write Application.ActiveDocument.

   When you create your Word Document project using Visual Studio Tools for the Microsoft Office System, declarations for the Word.Document (ThisDocument) and Word.Application (ThisApplication) are created for you. ActiveDocument must be changed to ThisApplication.ActiveDocument.

   **Tip   **You can run a global find-and-replace operation to quickly change this throughout your code.

   For Each currentStyle in ThisApplication.ActiveDocument.Styles
   

   ****Tip   ThisDocument can also refer to the active document, provided you are working with only one document. ThisDocument actually refers to the document that is associated with the assembly (the document that you used when creating your Visual Studio Tools for the Microsoft Office System project).

3. In the last line of the function:

   StyleExists = stylePresent
   

   You can return the variable using the keyword Return. Change StyleExists = to Return:

   Return stylePresent
   

4. In the CreateStyle subroutine:

   Private Sub CreateStyle(styleName As String, styleFontName As String, _
       styleFontSize As Single, styleBold As Boolean, _
       styleItalic As Boolean, Optional styleFirstLineIndent As Single, _
       Optional styleSpaceBefore As Single )
   

   The blue squiggle that appears under the comma after the optional parameter indicates that a problem exists in the code. Hold your mouse pointer over the squiggle to display the ToolTip, "Optional Parameters must specify a default value."

   Add a default value of 0 for each optional parameter, as shown in the following example.

   Optional ByVal styleFirstLineIndent as Single = 0, _
   Optional ByVal styleSpaceBefore As Single = 0)
   

   Notice that ByVal is automatically added to each parameter once the optional parameters are resolved.

   **Tip   **In Visual Basic .NET, the preferred method of working with optional parameters is to create overloaded methods. For more information see Overloaded Properties and Methods.

5. In the line where the FirstLineIndent is being set:

   ParagraphFormat.FirstLineIndent = _
       InchesToPoints(styleFirstLineIndent)
   

   The InchesToPoints method is a member of the Application object. Change the method to ThisApplication.InchesToPoints.

   ParagraphFormat.FirstLineIndent = _
       ThisApplication.InchesToPoints(styleFirstLineIndent)
   

6. Similarly, in the next line, change the InchesToPoints method to ThisApplication.InchesToPoints.

   ParagraphFormat.SpaceBefore = _
       ThisApplication.InchesToPoints(styleSpaceBefore)
   

7. In the next line:

   ParagraphFormat.Alignment = wdAlignParagraphLeft
   

   You need to fully qualify the wdAlignParagraphLeft constant with the enumeration name:

   ParagraphFormat.Alignment = _
       Word.WdParagraphAlignment.wdAlignParagraphLeft
   

8. In the variable declaration statement of the ThisDocument_Open method:

   Dim currentParagraph As Paragraph
   

   The Paragraph object belongs to the Word namespace, so you need to change it to the qualified reference of Word.Paragraph as follows:

   Dim currentParagraph As Word.Paragraph
   

9. In the For Each statement, add the ThisApplication qualifier to ActiveDocument (this may have already been changed if you ran a global find-and-replace operation earlier):

   For Each currentParagraph In _
       ThisApplication.ActiveDocument.Paragraphs
   

10. In the next line:

    If currentParagraph.Alignment = wdAlignParagraphLeft Then
    

    You need to fully qualify the wdAlignParagraphLeft constant with the enumeration name:

    If currentParagraph.Alignment = _
        Word.WdParagraphAlignment.wdAlignParagraphLeft Then
    

11. Build the solution by clicking Build and choosing Build Solution.

When you open the Word document again, you will see that all paragraphs that were left aligned have the NewStyle style applied.

VBA Code Example in Excel

The following Excel VBA macro creates a new style if it does not already exist, and then applies the style to a range of cells (A1-C2).

Private Sub Workbook_Open()
    Dim cellRange As Range
    Dim styleName As String
    Dim cellFormat As String
    cellFormat = "_($* #,##0.00_)"
    styleName = "NewStyle"
    
    ' Set a range for cells A1 through C2.
    Set cellRange = Range("A1:C2")    

    ' Create and format the style if it doesn't already exist.
    If Not StyleExists(styleName) Then
        FormatStyle styleName, "Times New Roman", 9, cellFormat
    End If
    
    ' Apply the style to the range.
    cellRange.Style = styleName
End Sub

Function StyleExists(styleName As String) As Boolean
    On Error GoTo StyleExists_Err
    Dim blnStyleExists As Boolean

    ' Assume style doesn't exist when you begin.
    blnStyleExists = False
    ActiveWorkbook.Styles.Add (styleName)

StyleExists_End:
    StyleExists = blnStyleExists
    Exit Function

StyleExists_Err:
    Select Case Err.Number
        Case 1004
            ' Error Number 1004 occurred, thus the style exists.
            blnStyleExists = True
        Case Else
            ' Handle unforseen circumstances.
    End Select
    Resume StyleExists_End
End Function

Sub FormatStyle(styleName As String, styleFont As String, _
        styleFontSize As Single, styleFormat As String)
        
        ' Format the style.
        With ActiveWorkbook.Styles(styleName)
            .Font.Name = styleFont
            .Font.Size = styleFontSize
            .NumberFormat = styleFormat
        End With
End Sub

Creating the Project

To convert your VBA code, you need to create an Excel Workbook project in Visual Studio .NET using Visual Studio Tools for the Microsoft Office System.

To create a new Excel Workbook project

1. From the File menu, point to New, and then click Project.
2. In the New Project dialog box, expand Microsoft Office 2003 Projects, and then select Visual Basic Projects.
3. In the Templates pane, select Excel Workbook.
4. Name the project Create Excel Style, and then click OK.
5. When the Microsoft Office Project Wizard appears, be sure that Create new document is selected, and click Finish.

Next, you need to add text to your Excel workbook.

To add text to the workbook

1. Open the Create Excel Style.xls workbook in Microsoft Office Excel 2003.

   A warning might appear stating that the assembly name or Assembly Link Location property is corrupted, because you have not compiled the assembly yet. Click OK to close the warning.

2. Add numbers to range A1-C2 of your Excel worksheet. You may add additional text to other cells as a means of testing that the code affects the A1-C2 range only.

3. Save and close the Excel workbook.

Copying the Code

Next, you need to copy your VBA code into your new Excel Workbook project. You will copy all of the code except the StyleExists method.

To copy and paste VBA code into the Excel project

1. In your Visual Basic .NET project, locate the OfficeCodeBehind class.

2. Copy the FormatStyle subroutine from the VBA code and paste it into the OfficeCodeBehind class below the ThisWorkbook_BeforeClose method (not within this method).

   Notice that ByVal has been added automatically as the default for parameters.

3. Copy the VBA code from the Workbook_Open subroutine into the ThisWorkbook_Open method of the Visual Basic .NET project (do not include the Sub and End Sub keywords).

Converting the Code

After you have copied the VBA code into your new project, you can begin to convert the code.

To convert the code in Visual Basic .NET

1. In the first line of the FormatStyle subroutine:

   With ActiveWorkbook.Styles(styleName)
   

   The ActiveWorkbook object belongs to the Application object, therefore you must add the ThisApplication qualifier.

   With ThisApplication.ActiveWorkbook.Styles(styleName)
   

   Alternatively, you can use the ThisWorkbook variable in place of ThisApplication.ActiveWorkbook. Keep in mind that ThisWorkbook may not always refer to the active workbook; it is actually referencing the workbook associated with the project.

2. In the first line of the VBA Workbook_Open subroutine:

   Dim cellRange as Range
   

   The Range object is part of the Excel namespace; therefore, you need to qualify the object with Excel.Range.

   A little further down, notice the following line:

   cellRange = Range("A1:C2")
   

   Note that the Set keyword is not supported and has been removed automatically from the code. The Range property is a member of the Worksheet object. To use the Worksheet object, initialize an object variable using the Ctype function. You need to qualify the Range property with ThisApplication.Range. You can also combine the declaration of variables with the assignment of their values:

   Dim ThisWorksheet As Excel.Worksheet = _
       CType(ThisWorkbook.Worksheets.Item("Sheet1"), Excel.Worksheet)
   Dim cellRange As Excel.Range = ThisWorksheet.Range("A1:C2")
   

3. Change the other declaration to include variable assignment:

   Dim styleName As String = "NewStyle"
   Dim cellFormat As String = "_($* #,##0.00_)"
   

4. Instead of calling the StyleExists function from the VBA example, you can add structured exception handling directly in the ThisWorkbook_Open method to test for the existence of the style. Remove the If statement and type the following code:

   ' Create the style if it doesn't already exist and format it.
   Try
       ThisApplication.ActiveWorkbook.Styles.Add(styleName)
   Finally
       FormatStyle(styleName, "Times New Roman", 9, cellFormat)
   End Try
   

5. Build the solution by clicking Build and choosing Build Solution.

   When you open the Excel workbook again, you will see that the range of cells A1-C2 have the NewStyle style applied.

Conclusion

When deciding whether to convert your VBA code to Visual Basic .NET, you should consider how they differ with regard to security, deployment, and code maintenance, and whether your project will benefit from the features available in the Microsoft .NET Framework. You will need to adjust your code for the language changes in Visual Basic .NET, recreate VBA UserForms as Windows Forms, and ensure that end-user computers have the .NET Framework and Office 2003 primary interop assemblies installed. You may need to make changes to your code when accessing the Word and Excel object models through the primary interop assemblies; but the object models are the same, and some portions of your code will not need conversion. Using Visual Studio Tools for the Microsoft Office System and the information contained in this article, you can begin converting your Word and Excel VBA code to Visual Basic .NET.

Appendix

This section contains the complete Visual Basic .NET code for both the Word VBA and Excel VBA examples.

Word Example

' Visual Basic .NET
' Called when the document is opened.
Private Sub ThisDocument_Open() Handles ThisDocument.Open
    Dim currentParagraph As Word.Paragraph

    ' Call CreateStyle, passing the name and attributes.
    CreateStyle("MyNewStyle", "Arial", 9.5, True, False, 0.5)

    ' Apply Style to each paragraph that is left aligned.
    For Each currentParagraph In ThisApplication.ActiveDocument.Paragraphs
        If currentParagraph.Alignment = _
            Word.WdParagraphAlignment.wdAlignParagraphLeft Then
            currentParagraph.Style = "MyNewStyle"
        End If
    Next currentParagraph
End Sub

Private Function StyleExists(ByVal styleName As String) As Boolean
    Dim currentStyle As Word.Style
    Dim stylePresent As Boolean = False

    ' Check for existence of style in active document.
    For Each currentStyle In ThisApplication.ActiveDocument.Styles
        If currentStyle.NameLocal = styleName Then
            stylePresent = True
            Exit For
        End If
    Next currentStyle

    ' Return
    Return stylePresent
End Function

Private Sub CreateStyle(ByVal styleName As String, ByVal _
    styleFontName As String, ByVal styleFontSize As Single, _
    ByVal styleBold As Boolean, ByVal styleItalic As Boolean, _
    Optional ByVal styleFirstLineIndent As Single = 0, _
    Optional ByVal styleSpaceBefore As Single = 0)

    ' Check if the style already exists.
    If Not StyleExists(styleName) Then

        ' Create the style with attributes passed.
        ThisApplication.ActiveDocument.Styles.Add(styleName)
        With ThisApplication.ActiveDocument.Styles(styleName)
            .Font.Name = styleFontName
            .Font.Size = styleFontSize
            .Font.Bold = styleBold
            .Font.Italic = styleItalic
            .ParagraphFormat.FirstLineIndent = _
                ThisApplication.InchesToPoints(styleFirstLineIndent)
.            ParagraphFormat.SpaceBefore = _
                ThisApplication.InchesToPoints(styleSpaceBefore)
.            ParagraphFormat.Alignment = _
                Word.WdParagraphAlignment.wdAlignParagraphLeft
        End With
    End If
End Sub

Excel Example

' Visual Basic .NET
' Called when the workbook is opened.
Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open

    Dim ThisWorksheet As Excel.Worksheet = _
        CType(ThisWorkbook.Worksheets.Item("Sheet1"), Excel.Worksheet)
    Dim cellRange As Excel.Range = ThisWorksheet.Range("A1:C2")

    Dim styleName As String = "NewStyle"
    Dim cellFormat As String = "_($* #,##0.00_)"

    ' Set a range for cells A1 through C2.
    cellRange = ThisApplication.Range("A1:C2")

    ' Create the style if it doesn't already exist and format it.
Try
    ThisApplication.ActiveWorkbook.Styles.Add(styleName)
Finally
    FormatStyle(styleName, "Times New Roman", 9, cellFormat)
End Try

    ' Apply the style to the range.
    cellRange.Style = styleName
End Sub

Sub FormatStyle(ByVal styleName As String, ByVal styleFont As String, _
    ByVal styleFontSize As Single, ByVal styleFormat As String)

    ' Format the style.
    With ThisApplication.ActiveWorkbook.Styles(styleName)
        .Font.Name = styleFont
        .Font.Size = styleFontSize
        .NumberFormat = styleFormat
    End With
End Sub

Related Articles

Visual Studio Tools for the Microsoft Office System
Introduction to Visual Basic .NET for Visual Basic Veterans
Comparison of ADO.NET and ADO
An Overview of Security in the .NET Framework
Working with the Office XP Primary Interop Assemblies
Understanding the Excel Object Model from a .NET Developer's Perspective
Understanding the Word Object Model from a .NET Developer's Perspective