Visual Studio Tools for the Microsoft Office System

Visual Studio Tools for the Microsoft Office System

Converting Office VBA Help Code Examples to Visual Basic .NET and C#
As you write Visual Basic .NET or C# code to develop solutions that target Microsoft Office 2003, you might have encountered the need for help with the Office objects, collections, properties, methods, and events contained in the Office programmatic object models. Help for these object models is located in the Microsoft Office Visual Basic Help that is included with Office 2003. However, the Visual Basic Help documentation is presented using Visual Basic for Applications (VBA) syntax, not Visual Basic .NET or C# syntax. This topic explains how to convert the code examples in the Office Visual Basic Help content into Visual Basic .NET and C# syntax that will run without syntax errors in your Microsoft Visual Studio Tools for the Microsoft Office System solutions.
Language Differences
Finding and Converting Code in VBA Help
Converted Code Examples
Enabling IntelliSense for Office Objects in Visual Studio .NET
Language Differences
As a Visual Basic .NET or C# developer, you have probably invested a lot of time and effort learning Visual Basic .NET and C# syntax. Visual Basic for Applications (VBA), which is included with Microsoft Office, is a derivative of the Microsoft Visual Basic 6.0 language, the last Visual Basic version that was released prior to Visual Basic .NET. If you are a Visual Basic .NET developer, at first glance, VBA code appears to be somewhat different from Visual Basic .NET code. However, your knowledge of Visual Basic .NET will help you to convert code written in VBA syntax to Visual Basic .NET in a short amount of time.
For instance, here are just a few of the differences between VBA and Visual Basic .NET syntax:
The VBA Variant data type is no longer supported. It has been replaced by the Object data type in Visual Basic .NET.
In VBA, default properties are supported on objects. On a Label control, for example, Caption is the default property. In Visual Basic .NET, however, default properties are only supported if the properties take arguments.
In VBA, you can declare an optional procedure argument without specifying a default value, while in Visual Basic .NET, every optional procedure argument must supply a default value.
The VBA 16-bit signed Integer data type is now referred to as the Short data type in Visual Basic .NET. The VBA 32-bit signed Long data type is now referred to as the Integer data type in Visual Basic .NET. A new 64-bit Long data type now exists in Visual Basic .NET.
In VBA, parentheses are optional in some cases when you call subroutines. In Visual Basic .NET, all subroutine calls must have parentheses.
In VBA, the default argument passing mechanism in procedures is ByRef. The default argument passing mechanism for Visual Basic .NET procedures is ByVal.
In VBA, the default lower bound of every dimension of an array is zero (0). You can change this to one (1) with the Option Base statement. In Visual Basic .NET, the lower bound of every array dimension is zero (0), and you cannot declare it to be otherwise. The VBA Option Base statement is not supported in Visual Basic .NET.
The lower array bounds of most Office collections begin with one (1).
In VBA, you can use the ReDim statement to serve as the initial declaration of a dynamic array. In Visual Basic .NET, you cannot use ReDim as an array declaration. You must declare an array just like any other variable, using Dim or an equivalent statement, before it can appear in a ReDim statement.
The Property Set, Property Let, and Property Get keywords in VBA have been replaced in Visual Basic .NET with a unified Get and Set mechanism.
For more information on these and other differences between VBA and Visual Basic .NET, see Language Changes in Visual Basic.
Finding and Converting Code in VBA Help
This section explores what the experience is like to look up VBA help, what key differences you will encounter as you read VBA help, and how to convert code that uses VBA syntax into code that uses Visual Basic .NET and C# syntax.
To try out VBA help, complete the following exercise:
Create a new Visual Studio Tools for Office project that uses Word and Visual Basic .NET.
Locate and copy some Word VBA code into the project.
Convert the VBA code into Visual Basic .NET code.
Copying the Code
To copy VBA code into a Word project
Start Visual Studio .NET and create a new Visual Basic project using the Word Document project template. For more information, see How to: Create Word and Excel Projects.
Start Microsoft Office Word 2003.
On the Tools menu in Word, point to Macro, and click Visual Basic Editor.
On the View menu, click Object Browser.
In the Project/Library box, select Word.
In the Classes List, click Document.
In the Members of Document list, right-click Range, and then click Help.
In the "Example" section of the Range Method, click As it applies to the Document object.
Copy or type the code from the body of the DocumentRange2 subroutine into the ThisDocument_Open event in the Visual Studio .NET Code Editor. The code looks like this:
Dim r As Range
Set r = ActiveDocument.Range(Start:=0, End:=Selection.End)
r.Case = wdUpperCase
Converting the Code
Now you can convert the code that you just copied so that it runs in the Word Document project in Visual Basic .NET.
To convert the code to Visual Basic .NET
In the first line of code:
Dim r As Range
Visual Basic .NET thinks that the Range object belongs to the System.Data namespace. Since in this case the Range object belongs to the Word namespace, change the Range object to the fully qualified reference of Word.Range to resolve the problem as follows:
Dim r As Word.Range
In the second line of code:
Set r = ActiveDocument.Range(Start:=0, End:=Selection.End)
The ActiveDocument property must be changed to ThisApplication.ActiveDocument to match the Visual Studio Tools for the Microsoft Office System naming convention in the Word Document project. Also, Selection must be changed to ThisApplication.Selection to fully qualify the reference to the Selection property as an accessor property of the Application object. The keyword Set is automatically removed by the editor because it is no longer supported.
In the third line of code:
r.Case = wdUpperCase
Change the wdUpperCase constant to the fully qualified reference to Word.WdCharacterCase.wdUpperCase.
Your converted code should now look like this:
Dim r As Word.Range
r = ThisApplication.ActiveDocument.Range(Start:=0, _
End:=ThisApplication.Selection.End)
r.Case = Word.WdCharacterCase.wdUpperCase
To run this code, you must build the solution. Press F5, type some characters in the Word document associated with this solution, and then save and close the Word document. When you open the Word document again, you see that the first word's characters are converted to upper case.
If you write code in C#, here is the equivalent code converted to C#:
// C#
Word.Range r;
object start = 0;
object end = thisApplication.Selection.End;
r = ThisApplication.ActiveDocument.Range(ref start, ref end);
r.Case = Word.WdCharacterCase.wdUpperCase;
Converted Code Examples
The following code examples were taken from the Word and Excel Visual Basic References and converted to Visual Basic .NET and C#. The VBA sample code is listed first, along with where the code was taken from, followed by the converted Visual Basic .NET equivalent and C# equivalent. Comments have also been added in the converted Visual Basic .NET code to highlight some of the differences between VBA and Visual Basic .NET.
Changing Object Variable Names
The examples in this section change object variable names from ActiveDocument to ThisApplication.ActiveDocument.
The following example is from the Microsoft Office Word VBA "InlineShapes Property" topic. It displays the number of shapes and inline shapes in the active document.
' Visual Basic
Set doc = ActiveDocument
Msgbox "InlineShapes = " & doc.InlineShapes.Count & _
vbCr & "Shapes = " & doc.Shapes.Count

' Visual Basic .NET
' Remove the variable "doc" as it is replaced
' by the ThisApplication.ActiveDocument variable.
' Change references from ActiveDocument to
' ThisApplication.ActiveDocument.
MsgBox("InlineShapes = " & _
ThisApplication.ActiveDocument.InlineShapes.Count & _
vbCr & "Shapes = " & ThisApplication.ActiveDocument.Shapes.Count)

// C#
MessageBox.Show("InlineShapes = " +
ThisApplication.ActiveDocument.InlineShapes.Count +
"\n" + "Shapes = " + ThisApplication.ActiveDocument.Shapes.Count);
The following example is taken from the Word VBA "Exists Method" topic. It determines whether the bookmark named "start" exists in the active document. If the bookmark exists, it is deleted.
' Visual Basic
If ActiveDocument.Bookmarks.Exists("start") = True Then
ActiveDocument.Bookmarks("start").Delete()
End If

' Visual Basic .NET
' Change references from ActiveDocument to
' ThisApplication.ActiveDocument.
If ThisApplication.ActiveDocument.Bookmarks.Exists("start") = True Then
ThisApplication.ActiveDocument.Bookmarks("start").Delete()
End If

// C#
object bkmStart = "start";

if(ThisApplication.ActiveDocument.Bookmarks.Exists("start") == true)
{
ThisApplication.ActiveDocument.Bookmarks.get_Item
(ref bkmStart).Delete();
}
Fully Qualifying Constant Names
The following example is taken from the Word VBA "HeaderFooter Object" topic. It changes the text of both the primary header and the primary footer in the first section of the active document.
' Visual Basic
With ActiveDocument.Sections(1)
.Headers(wdHeaderFooterPrimary).Range.Text = _
"Header text"
.Footers(wdHeaderFooterPrimary).Range.Text = _
"Footer text"
End With

' Visual Basic .NET
' Replace ActiveDocument with ThisApplication.ActiveDocument.
With ThisApplication.ActiveDocument.Sections(1)
' Fully qualify the constant by adding
' Word.WdHeaderFooterIndex.
.Headers(Word.WdHeaderFooterIndex.wdHeaderFooterPrimary).Range.Text _
= "Header text"
.Footers(Word.WdHeaderFooterIndex.wdHeaderFooterPrimary).Range.Text _
= "Footer text"
End With

// C#
ThisApplication.ActiveDocument.Sections[1].Headers
[Word.WdHeaderFooterIndex.wdHeaderFooterPrimary].Range.Text =
"Header text";
ThisApplication.ActiveDocument.Sections[1].Footers
[Word.WdHeaderFooterIndex.wdHeaderFooterPrimary].Range.Text =
"Footer text";
Fully Qualifying Objects and Removing the Set Keyword
The following example is taken from the Word VBA "ActiveWindow Property" topic. It opens a new window for the active window of the active document and then tiles all the windows.
' Visual Basic
Dim wndTileWindow As Window

Set wndTileWindow = ActiveDocument.ActiveWindow.NewWindow
Windows.Arrange ArrangeStyle:=wdTiled

' Visual Basic .NET
' Qualify reference to the Window object by adding Word.
Dim wndTileWindow As Word.Window

' Change ActiveDocument to ThisApplication.ActiveDocument.
' The Set keyword is no longer used.
wndTileWindow = ThisApplication.ActiveDocument.ActiveWindow.NewWindow

' Qualify reference to wdTiled constant by adding
' Word.WdArrangeStyle.
ThisApplication.Windows.Arrange _
(ArrangeStyle:=Word.WdArrangeStyle.wdTiled)

// C#
Word.Window wndTileWindow;
object enumWdTiled = Word.WdArrangeStyle.wdTiled;

wndTileWindow = ThisApplication.ActiveDocument.ActiveWindow.NewWindow();
thisApplication.Windows.Arrange(ref enumWdTiled);
Adding Parentheses Around Subroutine Calls
This example is taken from the Word "ActiveTheme Property" topic. It applies a theme and then displays the name of the active theme plus the theme formatting options for the current document.
' Visual Basic
ActiveDocument.ApplyTheme "artsy 100"
MsgBox ActiveDocument.ActiveTheme

' Visual Basic .NET
' Add parentheses around subroutine calls.
' Change ActiveDocument to ThisApplication.ActiveDocument.
ThisApplication.ActiveDocument.ApplyTheme("artsy 100")
MsgBox(ThisApplication.ActiveDocument.ActiveTheme)

// C#
ThisApplication.ActiveDocument.ApplyTheme("artsy 100");
MessageBox.Show(ThisApplication.ActiveDocument.ActiveTheme);
Declaring Variables Before Using Them
The following example is taken from the Word VBA "Bookmarks Property" topic. It uses the aMarks() array to store the name of each bookmark contained in the active document.
' Visual Basic
If ActiveDocument.Bookmarks.Count >= 1 Then
ReDim aMarks(ActiveDocument.Bookmarks.Count - 1)
i = 0
For Each aBookmark In ActiveDocument.Bookmarks
aMarks(i) = aBookmark.Name
i = i + 1
Next aBookmark
End If

' Visual Basic .NET
' Declare variables first.
Dim aMarks As String()
Dim i As Integer
Dim aBookmark As Word.Bookmark

' Change ActiveDocument to ThisApplication.ActiveDocument.
If ThisApplication.ActiveDocument.Bookmarks.Count >= 1 Then
ReDim aMarks(ThisApplication.ActiveDocument.Bookmarks.Count - 1)
i = 0
For Each aBookmark In ThisApplication.ActiveDocument.Bookmarks
aMarks(i) = aBookmark.Name
i += 1
Next aBookmark
End If

// C#
if(ThisApplication.ActiveDocument.Bookmarks.Count >= 1)
{
Array aMarks = Array.CreateInstance(typeof(string),
ThisApplication.ActiveDocument.Bookmarks.Count - 1);
int i = 0;

foreach(Word.Bookmark aBookmark in
ThisApplication.ActiveDocument.Bookmarks)
{
aMarks.SetValue(aBookmark.Name, i);
i += 1;
}
}
Using the Default Property if Available
This example is taken from the Word VBA "Characters Property" topic.
' Visual Basic
char = Selection.Characters(1).Text
MsgBox "The first character is... " & char

' Visual Basic .NET
' Declare variable first.
Dim ch As String

' Qualify the Selection property by adding ThisApplication.
' Because Item is the Characters collection's
' default property,
' you do not need to write Characters.Item(1).
ch = ThisApplication.Selection.Characters(1).Text

' Add parentheses around the subroutine call.
MsgBox("The first character is... " & ch)

// C#
string ch;

ch = thisApplication.Selection.Characters[1].Text;
MessageBox.Show("The first character is..." + ch);
Excel Example
The general approach that is illustrated in the previous examples also applies for Microsoft Office Excel. The following code example is taken from the Excel VBA "Sheets Property" topic. It creates a new worksheet and then places a list of the active workbook's sheet names in the first column.
' Visual Basic
Set newSheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
newSheet.Cells(i, 1).Value = Sheets(i).Name
Next i

' Visual Basic .NET
' Declare variables before you use them.
' Fully qualify the Worksheet object by adding Excel.
Dim newSheet As Excel.Worksheet
Dim i As Integer

' Qualify the Sheets property by adding ThisWorkbook.
' Qualify the xlWorksheet constant by adding Excel.XlSheetType.
newSheet = _
ThisWorkbook.Sheets.Add _
(Type:=Excel.XlSheetType.xlWorksheet)
For i = 1 To ThisWorkbook.Sheets.Count
newSheet.Cells(i, 1).Value = _
ThisWorkbook.Sheets(i).Name
Next i

// C#
Excel.Worksheet newSheet, thatWorksheet;
object enumWorksheetType = Excel.XlSheetType.xlWorksheet;
object objMissing = System.Reflection.Missing.Value;
Excel.Range thisRange;
Excel.Sheets theseWorksheets;

newSheet =
(Excel.Worksheet)thisWorkbook.Sheets.Add(objMissing,
objMissing, objMissing, enumWorksheetType);
theseWorksheets = thisWorkbook.Sheets;

for(int i = 1; i <= thisWorkbook.Sheets.Count; i++)
{
thisRange = (Excel.Range)newSheet.Cells.get_Item
(i, 1);
thatWorksheet =
(Excel.Worksheet)theseWorksheets.get_Item(i);
thisRange.Value2 = thatWorksheet.Name;
}
Enabling IntelliSense for Office Objects in Visual Studio .NET
Consider the following Visual Basic .NET code:
With ThisWorkbook.Worksheets("Sheet1").Cells.Font
.Name = "Arial"
.Size = 8
End With
If you were to begin writing this code in the Visual Studio .NET Code Editor, you would type:
With ThisWorkbook.Worksheets("Sheet1")
Once you type the code, however, the only member that appears in the IntelliSense list is GetType. This is because the Worksheets collection's default Item method returns a generic Object object, not a Worksheet object. Because the return value is a generic Object object (known as a late-bound object), you must early bind the object by changing the return type to a Worksheet object.
Correcting this problem to show the correct IntelliSense list requires you to type the following code:
Dim wks As Excel.Worksheet
wks = CType(ThisWorkbook.Worksheets("Sheet1"), Excel.Worksheet)

With wks.Cells.Font
.Name = "Arial"
.Size = 8
End With
Now, when you type wks., the correct IntelliSense list appears, displaying the Worksheet object's members. This is because the wks object variable was initialized to an Excel Worksheet object by using the CType function. Consider using this technique if you ever discover that you type a dot and the wrong IntelliSense list appears, or no IntelliSense list appears at all, and you are willing to do just a little additional typing to enable the correct IntelliSense list to appear.
If you write code in C#, here is the equivalent C# code:
// C#
Excel.Worksheet wks;

wks = (Excel.Worksheet)thisWorkbook.Worksheets["Sheet1"];
wks.Cells.Font.Name = "Arial";
wks.Cells.Font.Size = 8;