Converting Microsoft Office VBA Macros to Visual Basic .NET and C#
Paul Cornell
Microsoft Corporation
November 7, 2002
Download Office11072002.exe.
Summary: Paul Cornell shows you how to migrate an Office VBA macro to .NET, specifically Visual Basic .NET and C#, and then how to transform that solution into a Visual Basic .NET managed COM add-in. (32 printed pages)
I've noticed that a lot of Microsoft Office developers out there, both in newsgroups and at conferences, are interested in learning more about how to code solutions using the Microsoft .NET development platform.
In this month's column, I'll show you to take an Office VBA macro and convert it to .NET, both in Visual Basic .NET and C#. As an added bonus, I'll show you in the appendix at the end of this month's column how to make a Visual Basic .NET managed COM add-in out of the solution.
About a month ago, I received some Microsoft Word documents from a technical writer outside of Microsoft that I wanted to publish on the MSDN Web site. He attached his own Word document template and styled his text in a specific way. I needed a solution that changed his styles to match the styles in the MSDN Web site's document template in Word. To automate this process, I wanted to put the style mappings in a Microsoft Excel spreadsheet. Armed with the Word macro recorder, a coworker and I came up with a workable solution, and I further modified the code in the Office Visual Basic Editor to refine the solution.
Let's examine how I created the solution in VBA, and then from there we'll look at how I coded the same solution in Microsoft Visual Basic® .NET and C#.
Using Visual Basic for Applications to Create the Solution
Most Office developers know how to create VBA solutions, so I won't go into too much detail here. Besides, you're probably reading this column for the .NET stuff anyway, right? However, we need some baseline code to work with and compare to .NET; here's how I did it.
First, I created a VBA UserForm and attached the form module to my Normal.dot document template in Word. You can import the frmMain.frm from the VBAStyleChange folder of the sample download to your VBA project, or you can perform the following steps to create the solution yourself. The UserForm looks like Figure 1.
.gif)
Figure 1. Sample VBA UserForm
I set the UserForm's properties as follows:
| Item | Property | Value |
|---|---|---|
| UserForm | Name | frmMain |
| Caption | Change Styles | |
| CommandButton | Name | cmdWord |
| Caption | Select Word Document | |
| CommandButton | Name | cmdExcel |
| Caption | Select Excel Style Change File | |
| CommandButton | Name | cmdGo |
| Caption | Change Styles | |
| Label | Name | lblWord |
| Caption | (blank) | |
| Label | Name | lblExcel |
| Caption | (blank) | |
| Label | Name | lblStatus |
| Caption | (blank) |
Next, I set references to the Microsoft Word 10.0, Microsoft Excel 10.0, and Microsoft Office 10.0 object libraries.
Then I added the following code to the UserForm. In the Initialize event of the UserForm , I disabled the Change Styles button so that users wouldn't be able to click it unless they provided the Word and Excel file paths first.
Private Sub UserForm_Initialize()
' User must specify Word and Excel files first.
Me.cmdGo.Enabled = False
End Sub
The ReplaceStyles subroutine is the heart of the solution. The Excel portion of the subroutine basically walks down the worksheet, one row at a time, reading the old style in column A and the new style in column B into memory. The Word portion of the code uses the Selection, Find, and Replacement objects to swap the styles from one template to another.
Private Sub ReplaceStyles(ByVal strWordPath As String, _
ByVal strExcelPath As String)
' Purpose: Replaced old Word styles with new Word
' styles as specified in an Excel style change file.
' Accepts:
' strWordPath: The path to the Word document.
' strExcelPath: The path to the Excel style change file.
' Note: This code uses the Office 10.0 object library
' and will only work with Word 2002 and Excel 2002.
' You must set references to the Office 10.0,
' Word 10.0, and Excel 10.0 object libraries.
Dim wdApp As Word.Application
Dim objDoc As Word.Document
Dim xlApp As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRngOld As Excel.Range
Dim objRngNew As Excel.Range
On Error GoTo ReplaceStyles_Err
' Open the Word document.
Set wdApp = New Word.Application
Set objDoc = wdApp.Documents.Open(FileName:=strWordPath)
' Open the first worksheet in the Excel workbook
' and start with cell A1.
Set xlApp = New Excel.Application
Set objWB = xlApp.Workbooks.Open(FileName:=strExcelPath)
Set objWS = objWB.Worksheets.Item(Index:=1)
Set objRngOld = objWS.Range(Cell1:="A1")
' If the cell is empty, we're done.
Do While Not objRngOld.Value = ""
' Replace with style in column B.
Set objRngNew = objRngOld.Offset(ColumnOffset:=1)
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Style = _
ActiveDocument.Styles(Index:=objRngOld.Value)
frmMain.lblStatus.Caption = "Attempting to replace " & _
objRngOld.Value & " style..."
DoEvents
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Style = _
ActiveDocument.Styles(Index:=objRngNew.Value)
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
' Go to column A in the next row.
Set objRngOld = _
objRngNew.Offset(RowOffset:=1, ColumnOffset:=-1)
Loop
objDoc.Save
frmMain.lblStatus.Caption = "Operation complete."
ReplaceStyles_Exit:
' Clean up.
wdApp.Quit
xlApp.Quit
Set objRngOld = Nothing
Set objRngNew = Nothing
Set objWS = Nothing
Set objWB = Nothing
Set xlApp = Nothing
Set objDoc = Nothing
Set wdApp = Nothing
Exit Sub
ReplaceStyles_Err:
Select Case Err.Number
Case 5941 ' Unknown style.
frmMain.lblStatus.Caption = "Could not change " & _
" from style " & objRngOld & " to " & _
objRngNew.Value
Debug.Print "Could not change from style " & _
objRngOld & " to " & _
objRngNew.Value
Resume Next
Case 62
Resume Next
Case Else ' Unknown error.
MsgBox Prompt:="Error " & Err.Number & _
" in ReplaceStyles macro: " & Err.Description
End Select
GoTo ReplaceStyles_Exit
End Sub
The cmdExcel_Click and cmdWord_Click events display the Office File Open dialog box to allow the user to specify the path to the target Word document and Excel style change list file.
Private Sub cmdExcel_Click()
' User specifies the Excel document to use.
Dim objFileDialog As Office.FileDialog
Set objFileDialog = Application.FileDialog _
(FileDialogType:=msoFileDialogOpen)
With objFileDialog
.AllowMultiSelect = False
.Title = "Select Excel Style Change File"
.Filters.Clear
.Filters.Add _
Description:="Style Change Files", _
Extensions:="*.xls"
' "-1" means the user clicked the "Open" button
' in the file dialog box.
If .Show = -1 Then
Me.lblExcel.Caption = .SelectedItems(1)
End If
End With
Call CheckGoStatus
End Sub
Private Sub cmdWord_Click()
' User specifies the Word document to use.
Dim objFileDialog As Office.FileDialog
Set objFileDialog = Application.FileDialog _
(FileDialogType:=msoFileDialogOpen)
With objFileDialog
.AllowMultiSelect = False
.Title = "Select Word Document"
.Filters.Clear
.Filters.Add _
Description:="Word Documents", _
Extensions:="*.doc"
' "-1" means the user clicked the "Open" button
' in the file dialog box.
If .Show = -1 Then
Me.lblWord.Caption = .SelectedItems(1)
End If
End With
Call CheckGoStatus
Set objFileDialog = Nothing
End Sub
The CheckGoStatus subroutine enables the user to click the Change Styles button only if they have first specified the target Word document and Excel style change list file.
Private Sub CheckGoStatus()
' Should the "Change Styles" button be enabled?
If Me.lblWord.Caption <> "" And _
Me.lblExcel.Caption <> "" Then
Me.cmdGo.Enabled = True
End If
End Sub
The Change Styles button, when clicked, calls the ReplaceStyles subroutine described earlier.
Private Sub cmdGo_Click()
Call ReplaceStyles(strWordPath:=Me.lblWord.Caption, _
strExcelPath:=Me.lblExcel.Caption)
End Sub
Next, I created an Excel spreadsheet that contains the existing (old) Word styles in column A and the desired (new) styles in column B. Here's an example, taken from the SampleStyleChangeFile.xls file in the download's root folder:
.gif)
Figure 2. A sample Excel style change file
Finally, with the UserForm designer visible, I pressed F5, and ran the solution.
To distribute the solution, I can export the form module to a .frm file, and another user imports the .frm file into his or her Normal.dot document template and runs the UserForm.
Now, let's examine the .NET version of this VBA solution.
Using Visual Studio .NET to Create the Solution
Many Office developers know about .NET in theory, but they may not have used Visual Studio® .NET to create Office solutions before. Although I demonstrated how to use Visual Studio .NET over a year ago in my column Introducing .NET to Office Developers, now is a good time for a refresher. So, here's how to code the previous solution in Visual Studio .NET, starting from scratch.
First, download and install the Office XP Primary Interop Assemblies (PIAs) on your development computer. The Office XP PIAs make developing Office XP solutions easier with Visual Studio .NET. For more information on the Office XP PIAs, see Working with the Office XP Primary Interop Assemblies and Office XP Primary Interop Assemblies Known Issues.
Next, you can open the StyleChange.sln file in the StyleChange folder of the sample download, or you can perform the following steps to create the solution yourself:
Start by creating a Visual Basic .NET Windows Application.
Start Visual Studio .NET.
On the File menu, point to New, and click Project.
In the Project Types pane, open the Visual Basic Projects folder.
In the Templates pane, click the Windows Application icon.
In the Name box, type StyleChange.
In the Location box, type or browse to a folder on your local hard drive.
Click OK.
Next, customize the Windows Form.
In the Solution Explorer (if it's not visible, on the View menu, click Solution Explorer), expand the StyleChange project node, right-click the Form1.vb file, click Rename, and rename the file to frmMain.vb.
Right-click the StyleChange project node, click Properties, open the Common Properties folder, click General, click frmMain in the Startup object list, and click OK.
Use the Toolbox (if it's not visible, on the View menu, click Toolbox) and the Properties Window (if it's not visible, on the View menu, click Properties Window) to customize the Windows Form. See the previous section for the names and properties to use for the Windows Form and form controls.
**Note **The VBA Caption property is equivalent to the .NET Text property. Also, Visual Studio .NET uses the term Button, while VBA uses the term CommandButton.
The finished Windows Form should look like Figure 3 below.
Figure 3. A sample Windows Form
Next, set references to the Word 2002 PIA and the Excel 2002 PIA.
On the Project menu, click Add Reference.
Click the COM tab.
Double-click the Microsoft Word 10.0 Object Library and the Microsoft Excel 10.0 Object Library.
**Note **You don't have to double-click the Microsoft Office 10.0 Object Library because not only is it added by default, you'll be using two .NET OpenFileDialog controls to accomplish the same thing.
Add two OpenFileDialog controls to the form. Rename them to objWordFileDialog and objExcelFileDialog, respectively.
Next, add the code.
On the View menu, click Code.
Here's the code, with some explanations interspersed for those of you new to Visual Basic .NET programming.
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Word.WdUnits
Imports Microsoft.Office.Interop.Word.WdFindWrap
Imports Microsoft.Office.Interop.Word.WdReplace
Imports System.Windows.Forms.Application
Imports System.Runtime.InteropServices.Marshal
The Imports statement allows you to reduce and simplify your Visual Basic .NET code. In VBA, when you set a reference to a COM type library or object library, you have the option of omitting the type library or object library name prior to typing an object name or member name, assuming that the name is unique across all of the references in the project. In .NET, you must precede all class, member, and type names with their corresponding namespace (a namespace is a logical unit of organization inside of an assembly; an assembly is similar to a type library or object library). For example, in Word VBA, you have the wdStory constant, but in .NET you have the Microsoft.Office.Interop.Word.WdUnits.wdStory constant. In VBA you can use wdStory in code, but in .NET you have to use Microsoft.Office.Interop.Word.WdUnits.wdStory every time in the code if you do not use the Visual Basic .NET Imports statement Imports Microsoft.Office.Interop.Word.wdUnits.
Public Class frmMain
Inherits System.Windows.Forms.Form
In .NET, you can put all of your form module, code module, and class module code for a project in one code file. In Visual Basic .NET, the code file has the extension .vb, unlike the separate .frm, .bas, and .cls file extensions in VBA. In a .vb file, to denote code belonging to form modules and class modules, you use the Class…End Class statements. To denote code belonging to code modules, you use the Module…End Module statements.
In .NET, you don't create a form, you "embrace and extend" the functionality of a generic Windows Form by using the Visual Basic .NET Inherits keyword. In this case, the System.Windows.Forms.Form class has all the functionality that a generic Windows Form needs, like a title bar with minimize, maximize/restore, and close buttons, a frame, and so on.
' Don't modify any code in this collapsed code region.
' + Windows Form Designer generated code
I won't bore you with the code in the collapsed code region. In VBA, you are shielded from all of the code that the compiler uses to create a UserForm. In Visual Basic .NET, all of this code is available to you if you want to tweak it. However, there's never really any need to modify this code directly; use the graphical Windows Form designer and the Properties window instead.
Private Sub frmMain_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
' User must specify Word and Excel files first.
Me.cmdGo.Enabled = False
End Sub
Events are coded slightly differently in Visual Basic .NET than in VBA. In .NET, you create a subroutine that handles an event, also known as an event handler. In this case, the frmMain_Load event handler code runs when the MyBase.Load event occurs; in other words, when the Windows Form loads. The sender and e arguments can be used for greater programmatic control, such as which object caused the event handler to run.
Private Sub ReplaceStyles(ByVal strWordPath As String, _
ByVal strExcelPath As String)
' Purpose: Replaced old Word styles with new Word
' styles as specified in an Excel style change file.
' Accepts:
' strWordPath: The path to the Word document.
' strExcelPath: The path to the Excel style change file.
' Note: This code will only work with Word 2002 and Excel 2002.
' You must set references to the Word 10.0 and Excel 10.0
' object libraries.
Try
The Try keyword, used for error handling, is new in Visual Basic .NET. While you can still use the VBA and Visual Basic On Error constructs if you want, the Visual Basic .NET Try...Catch...Finally statements allow you to run different error-handling code based on the type of error instead of using Select Case Err.Number code. To use Try…Catch…Finally, enclose the code you want to test for errors in a Try…End Try code block. To catch an error, enclose the error handling code for a particular error case in a Catch block. You finish by enclosing "clean up" or "exit" code in a Finally block.
' Open the Word document.
Dim wdApp As New Word.ApplicationClass()
Dim objDoc As Word.Document = _
wdApp.Documents.Open(FileName:=strWordPath)
In the preceding code, the wdApp object variable is of type Word.ApplicationClass and not Word.Application. This is due to a current limitation of the Office XP PIAs. .NET cannot resolve the difference between overloaded statements across member types, such as the Word.Application.Quit event and the Word.Application.Quit method, so special …Class classes were created in the Office XP PIAs to resolve these differences at compile time.
Two other things to note are the absence of the Set keyword and the use of the equals symbol in a Dim keyword. The Set keyword doesn't exist in Visual Basic .NET; if you want to set a variable equal to an object or value, you just do it with the equals symbol. Also, in most cases you can now set a variable equal to an object or value at the same time you declare the variable, cutting down your keystrokes considerably with no performance penalty.
' Open the first worksheet in the Excel workbook
' and start with cell A1.
Dim xlApp As New Excel.ApplicationClass()
Dim objWB As Excel.Workbook = _
xlApp.Workbooks.Open(FileName:=strExcelPath)
Dim objWS As Excel.Worksheet = _
objWB.Worksheets.Item(Index:=1)
Dim objRngOld As Excel.Range = _
objWS.Range(Cell1:="A1")
Dim objRngNew As Excel.Range
Dim objSelection As Word.Selection = wdApp.Selection
' If the cell is empty, we're done.
Do While Not objRngOld.Value = ""
' Replace with style in column B.
objRngNew = objRngOld.Offset(ColumnOffset:=1)
With objSelection
.HomeKey(Unit:=wdStory)
.Find.ClearFormatting()
.Find.Style = objDoc.Styles(Index:=objRngOld.Value)
End With
Me.lblStatus.Text = "Attempting to replace " & _
objRngOld.Value & " style..."
DoEvents()
In .NET, all method calls must have parenthesis after them, even if the method doesn't take any arguments. That's why we can code DoEvents in VBA, but we must code DoEvents() in .NET.
With objSelection.Find
.Replacement.ClearFormatting()
.Replacement.Style = _
objDoc.Styles(Index:=objRngNew.Value)
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute(Replace:=wdReplaceAll)
End With
' Go to column A in the next row.
objRngOld = objRngNew.Offset(RowOffset:=1, _
ColumnOffset:=-1)
Loop
objDoc.Save()
Me.lblStatus.Text = "Operation complete."
wdApp.Quit()
xlApp.Quit()
Catch e As System.Exception
MsgBox(Prompt:="Error of type " & _
e.GetType.ToString & vbCrLf & _
"in " & e.TargetSite.ToString & vbCrLf & _
"of " & e.Source & ":" & vbCrLf & _
e.Message)
End Try
End Sub
The rest of the preceding code is almost identical to its VBA counterpart, so I won't go into any more detail about it here.
Private Sub cmdWord_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles cmdWord.Click
' User specified the Word document to use.
With Me.objWordFileDialog
.Multiselect = False
.Title = "Select Word Document"
.Filter = "Word Documents (*.doc)|*.doc"
The Windows Forms OpenFileDialog control (defined in the System. Windows.Forms namespace) mimics the Office FileDialog object programmatically in many ways. The OpenFileDialog.Filter property differs from the Office FileDialog.Filters collection in that you can specify a pipe-delimited string of file types and file extensions instead of doing the equivalent in Office by calling the FileDialog.Filters.Add method repeatedly.
If .ShowDialog = DialogResult.OK Then
To specify that the user clicked the Open button in the Office File Open dialog box, you check to see if the FileDialog.Show method returns the value of -1. In Windows Forms, you check to see if the OpenFileDialog.ShowDialog method returns the value DialogResult.OK.
Me.lblWord.Text = .FileName
Similarly, to retrieve the selected file name from the Office File Open dialog box, you retrive the value of the FileDialog.SelectedItems.Item(1) method. In Windows Forms, you retrieve the value of the OpenFileDialog.FileName property.
End If
End With
Call CheckGoStatus()
End Sub
Private Sub cmdExcel_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles cmdExcel.Click
' User specified the Excel document to use.
With Me.objExcelFileDialog
.Multiselect = False
.Title = "Select Excel Style Change File"
.Filter = "Style Change Files (*.xls)|*.xls"
If .ShowDialog = DialogResult.OK Then
Me.lblExcel.Text = .FileName
End If
End With
Call CheckGoStatus()
End Sub
Private Sub CheckGoStatus()
' Should the "Change Styles" button be enabled?
If Me.lblWord.Text <> "" And Me.lblExcel.Text <> "" Then
Me.cmdGo.Enabled = True
End If
End Sub
Private Sub cmdGo_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles cmdGo.Click
Call ReplaceStyles(strWordPath:=Me.lblWord.Text, _
strExcelPath:=Me.lblExcel.Text)
End Sub
End Class
The rest of the preceding code has already been discussed or looks similar to its VBA counterpart, so I won't go into any more detail about it here.
You can run the solution by clicking Start on the Debug menu.
To distribute the solution, you can copy the StyleChange.exe file in your <Project Name>\StyleChange\bin folder to a publicly-accessible file share, notify users where the file share is located, and then user copy the StyleChange.exe file to a single folder anywhere on their local computer and then run the StyleChange.exe file. You also need to make sure that your users have the .NET Framework Redistributable and the Office XP PIAs properly installed on their computers. Otherwise, you have to create an .msi file and customize it to include these potentially missing components. Be aware that adding these components to your solution will add a few dozen megabytes of code to what was at first a very small solution in terms of file size.
Let's finish this month's column by examining the C# version of the Visual Basic .NET solution.
Using C# to Create the Solution
Some Office developers who know about .NET are beginning to investigate C# as an alternative programming language to Visual Basic .NET for Office solutions. I wrote an article about Developing Microsoft Office Solutions Using C#. Along with the article Programming Microsoft Word 2002 and Excel 2002 with Microsoft Visual C#, you should find that these are two good introductory references to programming Office solutions using C#.
After you've downloaded and installed the Office XP PIAs on your development computer, you can open the CSharpStyleChange.sln file in the CSharpStyleChange folder of the sample download, or you can perform the following steps to create the solution yourself:
- Start Visual Studio .NET.
- On the File menu, point to New, and click Project.
- In the Project Types pane, open the Visual C# folder.
- In the Templates pane, click the Windows Application icon.
- In the Name box, type CSharpStyleChange.
- In the Location box, type or browse to a folder on your local hard drive.
- Click OK.
Next, customize the C# version of the Windows Form similar to the way you did for the Visual Basic .NET solution earlier.
- In the Solution Explorer (if it's not visible, on the View menu, click Solution Explorer), expand the CSharpStyleChange project node, right-click the Form1.cs file, click Rename, and rename the file to frmMain.cs.
- Right-click the CSharpStyleChange project node, click Properties, open the Common Properties folder, click General, click CSharpStyleChange.frmMain in the Startup Object list, and click OK.
Customize the Windows Form, set references to the Word 2002 PIA and the Excel 2002 PIA, and add two OpenFileDialog controls to the form, just like you did for the Visual Basic .NET solution.
Next, you add the code. On the View menu, click Code and add the code below. I've interspersed some explanations in the code about how C# syntax varies from Visual Basic .NET syntax. You can compare this code to the Visual Basic .NET code earlier in this article to compare functionality line by line.
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using Word = Microsoft.Office.Interop.Word;
using Excel = Microsoft.Office.Interop.Excel;
The C# using keyword is similar to the Visual Basic .NET Imports keyword, but has one key difference. Visual Basic .NET allows you to use Imports with a class name, but you can't do this in C#. For instance, in Visual Basic .NET, Imports Microsoft.Office.Interop.Word.WdUnits allows you to use wdStory in code, but in C#, using Microsoft.Office.Interop.Word.WdUnits only allows you to use WdUnits.wdStory in code (coding wdStory by itself in C# in this case generates a compiler error).
Two other important things to note. First, in C#, capitalization matters. Typing Using instead of using will cause a compile-time error. Second, in C#, statements are terminated by semicolons (;) and code blocks begin with an opening curly brace ({) and end with a closing curly brace (})—C# does not use carriage returns or line-continuation characters to terminate or continue code statements or code blocks like Visual Basic .NET does.
namespace CSharpStyleChange
In C#, you use the namespace keyword to declare a namespace. When you create a C# project, a default namespace is created with the same name as your project. You can use the Namespace keyword in Visual Basic .NET to declare a namespace. The default namespace does not appear by default in Visual Basic .NET; it can be found by right-clicking the project icon in the Solution Explorer window, clicking Properties, opening the Common Properties folder, clicking General, and looking at the Assembly name box. You can also have nested namespaces; in the case of the Microsoft.Office.Interop.Word namespace, the Word namespace is nested in the Interop namespace, which is nested in the Office namespace, which is nested in the Microsoft namespace.
{
public class frmMain : System.Windows.Forms.Form
{
private System.Windows.Forms.Button cmdWord;
private System.Windows.Forms.Button cmdExcel;
private System.Windows.Forms.Button cmdGo;
private System.Windows.Forms.Label lblWord;
private System.Windows.Forms.Label lblExcel;
private System.Windows.Forms.Label lblStatus;
private System.Windows.Forms.OpenFileDialog objWordFileDialog;
private System.Windows.Forms.OpenFileDialog objExcelFileDialog;
private System.ComponentModel.Container components = null;
The C# public, private, and class keywords are exactly the same as the Public, Private, and Class keywords in Visual Basic .NET. The colon in C# is the same as the Inherits keyword in Visual Basic .NET. In this case, the frmMain class inherits the base characteristics of a Windows Form.
In C#, you declare a variable's data type before the name of the variable. So in Visual Basic .NET you would code Private objExcelFileDialog As System.Windows.Forms.OpenFileDialog, but in C# you would code private System.Windows.Forms.OpenFileDialog objExcelFileDialog;.
In C#, all of your code must be inside of a class, unlike Visual Basic .NET. In this code, a single frmMain class contains a Dispose method, a Main method, a frmMain_Load event handler, and so on.
public frmMain()
{
InitializeComponent();
// User must specify Word and Excel files first.
this.cmdGo.Enabled = false;
}
This frmMain code block is known in .NET as a constructor, and is similar to the VBA Initialize event. A constructor shares the same name as its containing class (frmMain in this case).
The InitializeComponent method, tucked away in the collapsed code region below, prepares the Windows Form for later use in the Main method.
The this keyword in this example represents the Windows Form, and is similar to the Me keyword in Visual Basic .NET.
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
In C#, you declare a method's return type before the method name, unlike Visual Basic .NET. An equivalent Visual Basic .NET statement here would be Overrides Sub Dispose(Disposing As Boolean). The void type is equivalent to the Nothing type in Visual Basic .NET.
The protected keyword in C# is the same as the Protected keyword in Visual Basic .NET; in this example it means the Dispose method can be called from within the class itself or any class that derives from this class.
The override keyword in C# is the same as the Overrides keyword in Visual Basic .NET. Overriding in .NET "embraces and extends" class members just as inheritence "embraces and extends" classes.
// Do not change any of the code in this code region.
// #region Windows Form Designer generated code
Similar to Visual Basic .NET, C# hides the Windows Form underlying code in a collapsed code region. You can edit the code if you want to, but there's never really any reason for it.
In C#, comments begin with the double-slash (//) symbols and continue for the rest of the line. You can also begin a multiline comment with the slash-asterisk combination (/*) and end a multiline comment with the asterisk-slash combination (*/).
[STAThread]
static void Main()
{
Application.Run(new frmMain());
}
Attributes in .NET allow you to "adorn" your code with special instructions. In C# you use the opening square bracket ([) and closing square bracket (]) to denote attributes. In Visual Basic .NET you use the opening angle bracket (<) and closing angle bracket (>) to do the same thing. (In this case, the STAThread attribute indicated that the threading model for this application is single-threaded apartment.)
The static keyword in C# is the same as the Shared keyword in Visual Basic .NET. This means that you can call the Main method directly here; you don't have to declare an object of type frmMain first to call frmMain.Main.
The Application.Run method simply loads the Windows Form frmMain. Code execution continues with the frmMain_Load event handler below.
private void frmMain_Load(object sender, System.EventArgs e)
{
// User must specify Word and Excel files first.
cmdGo.Enabled = false;
}
private void cmdGo_Click(object sender, System.EventArgs e)
{
ReplaceStyles(lblWord.Text, lblExcel.Text);
}
private void CheckGoStatus()
{
// Should the "Change Styles" button be enabled?
if((lblWord.Text != "") && (lblExcel.Text != ""))
{
cmdGo.Enabled = true;
}
}
To code the Visual Basic .NET If…Then equivalent in C#, you simply use the if statement followed by parentheses. If the expression in the parentheses is true, then the statements in the associated code block will execute.
The C# != operator is equivalent to the <> operator in Visual Basic .NET. Also, the && operator in C# is equivalent to the And keyword in Visual Basic .NET.
private void cmdWord_Click(object sender, System.EventArgs e)
{
// User specified the Word document to use.
objWordFileDialog.Multiselect = false;
objWordFileDialog.Filter = "Word Documents (*.doc)|*.doc";
if(objWordFileDialog.ShowDialog() == DialogResult.OK)
{
this.lblWord.Text = objWordFileDialog.FileName;
}
In C#, two equals symbols (==) signify equality, while one equals symbol (=) denotes assignment. This is why in C# you can't code objWordFileDialog.ShowDialog() = DialogResult.OK (only one equals symbol) because the ShowDialog method returns a dialog result. You can't assign the DialogResult.OK value to the ShowDialog method in this example, you can only check for equality.
CheckGoStatus();
}
private void cmdExcel_Click(object sender, System.EventArgs e)
{
// User specified the Excel document to use.
objExcelFileDialog.Multiselect = false;
objExcelFileDialog.Title =
"Select Excel Style Change File";
objExcelFileDialog.Filter =
"Style Change Files (*.xls)|*.xls";
if(objExcelFileDialog.ShowDialog() == DialogResult.OK)
{
lblExcel.Text = objExcelFileDialog.FileName;
}
CheckGoStatus();
}
private void ReplaceStyles(string strWordPath,
string strExcelPath)
{
// Purpose: Replaced old Word styles with new Word
// styles as specified in an Excel style change file.
// Accepts:
// strWordPath: The path to the Word document.
// strExcelPath: The path to the Excel style change file.
// Note: This code will only work with Word 2002
// and Excel 2002.
// You must set references to the Word 10.0 and Excel 10.0
// object libraries.
object objMissing = System.Reflection.Missing.Value;
try
{
// Open the Word document.
Word.ApplicationClass wdApp = new
Word.ApplicationClass();
object ostrWordPath = strWordPath;
Word.Document objDoc =
wdApp.Documents.Open(ref ostrWordPath,
ref objMissing, ref objMissing, ref objMissing,
ref objMissing, ref objMissing, ref objMissing,
ref objMissing, ref objMissing, ref objMissing,
ref objMissing, ref objMissing, ref objMissing,
ref objMissing, ref objMissing);
In C#, you must provide all arguments that are defined in a class member, even arguments that are marked as [Optional] in C# or Optional in Visual Basic .NET.
In C#, the Word Documents class's Open method signatures is defined as Open(ref object, ref object, ref object, ref object, ref object, ref object, ref object, ref object, ref object, ref object, ref object, ref object, ref object, ref object, ref object). What these means is that in C# the Open method takes 15 required arguments, and each argument must be preceded with the ref keyword and each argument must be of type object. Since the first argument is a file name, normally a String value in Visual Basic .NET, we must declare a variable of type object that holds the C# string value, hence the code object ostrWordPath = strWordPath;. Although we only need to use the first argument in the Open method, remember that C# does not allow optional arguments, so we provide the final 14 arguments as variables of type object that hold values of System.Reflection.Missing.Value (object objMissing = System.Reflection.Missing.Value; above). These values instruct the compiler that we won't be providing values for these arguments.
// Open the first worksheet in the Excel workbook
// and start with cell A1.
Excel.ApplicationClass xlApp =
new Excel.ApplicationClass();
Excel.Workbook objWB = xlApp.Workbooks.Open(strExcelPath,
objMissing, objMissing, objMissing,
objMissing, objMissing, objMissing,
objMissing, objMissing, objMissing,
objMissing, objMissing, objMissing,
objMissing, objMissing);
Excel.Sheets objSheets = objWB.Worksheets;
Excel.Worksheet objWS =
(Excel.Worksheet)objSheets.get_Item(1);
In Visual Basic .NET, you use the CType method to convert a variable from one type to another. In C#, you use parentheses before the variable and put the target type inside the parenthesis (a technique called casting). Because the Excel Worksheets collection's get_Item method return an object of type object, it must be cast to type Excel.Worksheet or you will get a compile-time error. That's why the code (Excel.Worksheet) precedes the code objSheets.get_Item(1). You're probably wondering why the C# get_Item method is used here (which you won't find in Visual Basic .NET) instead of the Item method. If you look at the C# Object Browser, you'll see an Item property but you don't see a get_Item method. In IntelliSense, you don't see an Item property but you'll see a get_Item method. In C#, to call an accessor (a member that accesses an object), you must use the accessor's get_ method (if one exists) to get an object variable and you must use the accessor's set_ method (if one exists) to set an object variable.
Excel.Range objRngOld = objWS.get_Range("A1", objMissing);
Excel.Range objRngNew;
Word.Selection objSelection = wdApp.Selection;
// If the cell is empty, we're done.
while((string)objRngOld.get_Value(objMissing) != "")
while loops in C# are similar to Visual Basic .NET Do While…Loop loops. The do keyword also exists in C# and behaves like its Visual Basic .NET counterpart.
{
// Replace with style in column B.
objRngNew = objRngOld.get_Offset(objMissing, 1);
object owdStory = Word.WdUnits.wdStory;
objSelection.HomeKey(ref owdStory, ref objMissing);
Word.Find objFind = objSelection.Find;
objFind.ClearFormatting();
foreach(Word.Style objStyle in objDoc.Styles)
The foreach keyword in C# works just like the For Each…Next statements in Visual Basic .NET. The foreach statement has one advantage in that you can declare the iterator object in the foreach statement instead of declaring it prior to the foreach statement.
{
if (objStyle.NameLocal ==
(string)objRngNew.get_Value(objMissing))
{
lblStatus.Text = "Attempting to replace " +
objRngOld.get_Value(objMissing) +
"style...";
In C#, you use the plus operator (+) to concatenate strings just as you use the ampersand operator (&) in Visual Basic .NET for string concatenation.
Application.DoEvents();
objFind.Replacement.ClearFormatting();
Word.Replacement objReplace =
objFind.Replacement;
object oValue =
objRngNew.get_Value(objMissing);
objReplace.set_Style(ref oValue);
objFind.Text = "";
objFind.Replacement.Text = "";
objFind.Forward = true;
objFind.Wrap = Word.WdFindWrap.wdFindContinue;
objFind.Format = true;
objFind.MatchCase = false;
objFind.MatchWholeWord = false;
objFind.MatchWildcards = false;
objFind.MatchSoundsLike = false;
objFind.MatchAllWordForms = false;
object owdReplaceAll =
Word.WdReplace.wdReplaceAll;
objFind.Execute(ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref owdReplaceAll,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing);
}
}
// Go to column A in the next row.
objRngOld = objRngNew.get_Offset(1, -1);
objDoc.Save();
lblStatus.Text = "Operation complete.";
wdApp.Quit(ref objMissing, ref objMissing,
ref objMissing);
xlApp.Quit();
}
}
catch(System.Exception e)
{
MessageBox.Show("Error of type " +
e.GetType().ToString() +
"\n" + "in " + e.TargetSite.ToString() + "\n" +
"of " +
e.Source + ":" + "\n" + e.Message);
}
}
}
}
The rest of the preceding code has been explained in either the Visual Basic.NET section or earlier in this section. Running and distributing the C# solution is identical to the Visual Basic .NET solution previously described.
Appendix
For the adventurous Office solution developers out there, here's how to create the Visual Basic .NET version of the macro as a COM add-in (also known as a managed add-in) with Visual Studio .NET.
Using Visual Basic .NET to Create a Managed Add-In
I wrote about how to create a managed add-in earlier this year in my column Creating Office Managed COM Add-Ins with Visual Studio .NET. Without stepping through all of the code, let me show you how to create a managed COM add-in out of the code I developed in the Visual Basic .NET section.
First, this approach assumes that you have the Office XP PIAs installed. See the previous section for information on how to install the Office XP PIAs.
Next, you can open the StyleChangeAddIn.sln file in the StyleChangeAddIn folder of the sample solution, or you can perform the following steps to create the solution yourself:
Create a Visual Studio .NET Shared Add-In project.
Start Visual Studio .NET.
On the File menu, point to New, and click Project.
In the Project Types pane, open the Other Projects folder, and then open the Extensibility Projects folder.
In the Templates pane, click Shared Add-In.
In the Name box, type StyleChangeAddIn.
In the Location box, type a folder path or click Browse and select a folder path, and then click OK. The Extensibility Wizard appears.
Click Next. The Select a Programming Language page appears.
Click Create an Add-in using Visual Basic, then click Next. The Select An Application Host page appears.
Clear all of the check boxes except for Microsoft Word.
Click Next. The Enter a Name and Description page appears.
In the What is the name of your Add-In box, type Change Styles.
In the What is the description of your Add-In box, type Changes Word styles based on information in an Excel style change file.
Click Next. The Choose Add-In Options page appears.
Select only the I would like my Add-in to load when the host application loads check box.
**Note **Selecting the My Add-in should be available to all users of the computer it was installed on, not just the person who installed it check box has an interesting affect. It causes the add-in to not display in the COM Add-Ins dialog box in Word. This is because Office applications display only per user add-ins in the COM Add-ins dialog box. When you select this check box in the wizard, this makes your add-in per machine rather than per user. For more information on this issue, see PRB: Visual Studio .NET Shared Add-in Is Not Displayed in Office COM Add-ins Dialog Box (Q316723).
Click Next. The Summary page appears.
Click Finish, and wait while the Shared Add-In project is built.
Next, set references to the Word 2002 PIA and the Excel 2002 PIA, following the steps in the previous project. You will also need to set a reference to the System.Windows.Forms.dll and System.Drawing.dll assemblies, as you will be adding a Windows Form to this project. These assemblies can be found on the .NET tab of the Add Reference dialog box.
Next, add the Windows Form that you created for the previous project.
In the Solution Explorer, right-click the StyleChangeAddIn project node, point to Add, and click Add Existing Item.
Locate and click the frmMain.vb file that you created in the previous project, and then click Open.
Next, customize the code in the Connect.vb file to display the frmMain.vb file when the user runs the add-in. To do so, in the Solution Explorer, double-click the Connect.vb file and add the following code:
Imports Microsoft.Office.Core imports Extensibility Imports System.Runtime.InteropServices ' *** BEGIN CUSTOM CODE. *** Imports Microsoft.Office.Core.MsoControlType Imports Microsoft.Office.Core.MsoButtonStyle ' Do not modify any of the code in this code region. '+ Read me for Add-in installation and setup information. <GuidAttribute("67A4095A-F5EA-4321-BE51-C24BEF97534F"), _ ProgIdAttribute("StyleChangeAddIn.Connect")> _ Public Class Connect Implements Extensibility.IDTExtensibility2 Dim applicationObject As Object Dim addInInstance As Object ' *** BEGIN CUSTOM CODE. *** Dim WithEvents objCommandBarButton As CommandBarButton ' *** END CUSTOM CODE. *** Public Sub OnBeginShutdown(ByRef custom As System.Array) _ Implements Extensibility.IDTExtensibility2.OnBeginShutdown ' *** BEGIN CUSTOM CODE. *** objCommandBarButton.Delete() ' *** END CUSTOM CODE. *** End Sub Public Sub OnAddInsUpdate(ByRef custom As System.Array) _ Implements Extensibility.IDTExtensibility2.OnAddInsUpdate ' *** BEGIN CUSTOM CODE. *** ' No code needed in this method. ' *** END CUSTOM CODE. *** End Sub Public Sub OnStartupComplete(ByRef custom As System.Array) _ Implements _ Extensibility.IDTExtensibility2.OnStartupComplete ' *** BEGIN CUSTOM CODE. *** Dim objCommandBars As CommandBars Dim objCommandBar As CommandBar Dim objCommandBarControl As CommandBarControl ' Create a menu command on the "Format" menu. objCommandBars = applicationObject.CommandBars objCommandBar = objCommandBars.Item("Format") ' Make sure menu command doesn't already exist. For Each objCommandBarControl In objCommandBar.Controls If objCommandBarControl.Caption = "Change Styles..." Then objCommandBar.Controls.Item _ ("Change Styles...").Delete() End If Next objCommandBarControl objCommandBarButton = _ objCommandBar.Controls.Add(msoControlButton) With objCommandBarButton .Caption = "Change Styles..." .Style = msoButtonCaption .Tag = "Change Styles..." .OnAction = "!<StyleChangeAddIn.Connect>" .Visible = True End With ' *** END CUSTOM CODE. *** End Sub Public Sub OnDisconnection _ (ByVal RemoveMode As Extensibility.ext_DisconnectMode, _ ByRef custom As System.Array) _ Implements Extensibility.IDTExtensibility2.OnDisconnection ' *** BEGIN CUSTOM CODE. *** On Error Resume Next ' Disconnect the shared add-in, ' no matter how the host application ' was shut down. If RemoveMode <> _ Extensibility.ext_DisconnectMode.ext_dm_HostShutdown Then _ Call OnBeginShutdown(custom) applicationObject = Nothing ' *** END CUSTOM CODE. *** End Sub Public Sub OnConnection(ByVal application As Object, _ ByVal connectMode As Extensibility.ext_ConnectMode, _ ByVal addInInst As Object, ByRef custom As System.Array) _ Implements Extensibility.IDTExtensibility2.OnConnection applicationObject = application addInInstance = addInInst ' *** BEGIN CUSTOM CODE. *** ' No matter how the host application started, ' connect the shared add-in. If (connectMode <> _ Extensibility.ext_ConnectMode.ext_cm_Startup) Then _ Call OnStartupComplete(custom) ' *** END CUSTOM CODE. *** End Sub ' *** BEGIN CUSTOM CODE. *** Private Sub objCommandBarButton_Click _ (ByVal Ctrl As CommandBarButton, _ ByRef CancelDefault As Boolean) _ Handles objCommandBarButton.Click ' Display the Windows Form. Dim frmMain As New frmMain() frmMain.Show() End Sub ' *** END CUSTOM CODE. *** End ClassNext, specify that Word should start when you test the project.
In the Solution Explorer, right-click the StyleChangeAddIn project node, and then click Properties.
Open the Configuration Properties folder, click Debugging, click the Start external program option, type the path to WINWORD.EXE on your local computer, and click OK.
Next, run the solution by closing any running instances of Microsoft Word and then clicking Start on the Debug menu.
To deploy this solution, you'll need to customize the Setup project that was generated by the Extensibility Wizard. Also, to respect a computer's Office security settings, you'll need to create and add a piece of proxy code called a shim to the solution. For more information on how to do this, see Using the COM Add-in Shim Solution to Deploy Managed COM Add-ins in Office XP.
Paul Cornell works for the MSDN Online Office Developer Center and the Office developer documentation team. Paul also contributes to the Office Power User Corner column for the Office Assistance Center. He spends his free time with his wife and two daughters.