Programming Office Applications Using Visual C#

  • Article

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Ken Getz, MCW Technologies, LLC

Updated: January 2006

Applies to: Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Office Excel 2003, Microsoft Office Word 2003

Summary: Learn about issues Visual C# developers must be aware of when using Visual Studio 2005 Tools for Office to create Word and Excel applications, including how to work with VBA-focused methods and properties. See examples comparing Visual Basic code with Visual C# code. (17 printed pages)

Contents

  • Overview

  • Passing Parameters to Word

  • Handling Optional Parameters with Excel

  • Handling Optional Parameters with Word

  • Handling Parameterized Properties with Excel

  • Using Accessor Methods in Word

  • Late Binding in Word

  • Conclusion

  • Additional Resources

  • About the Author

Overview

Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio 2005 Tools for Office) makes it possible for you to create rich Microsoft Office 2003–based applications in Microsoft Visual Studio 2005, using the power of Microsoft Office Word 2003 and Microsoft Office Excel 2003. You can take advantage of all the functionality provided by the Word and Excel object models from either Microsoft Visual Basic 2005 or Microsoft Visual C#. Although Visual Basic developers can work comfortably with the objects exposed by the object models, Visual C# developers may experience challenges. Because of the history and the original design of the Office object models, the members of their objects were created with the intent that they would be called by Microsoft Visual Basic for Applications (VBA) code. Therefore, the various properties and methods take advantage of features that are provided by VBA but that do not correspond to Visual C# features. This document discusses issues involved in working with VBA-focused methods and properties for both Word and Excel, and shows examples comparing Visual Basic code and corresponding Visual C# code.

When using Visual Studio 2005 Tools for Office, Visual C# developers must be aware of issues that VBA or Visual Basic developers do not have to consider. In particular, the following list describes the issues discussed in detail in this document:

  • Passing parameters. By default, VBA expects parameters to be passed by reference. By default, Visual Basic and Visual C# both pass parameters by value. VBA and Visual Basic are fuzzy on this distinction, and the Excel primary interop assembly accepts parameters passed by value, even though it originally received all its values from VBA code by reference. The Word primary interop assembly is not so accommodating; its parameters (with few exceptions) must be passed by reference. Visual Basic can handle this without extra work, but Visual C# cannot.

  • Optional parameters. VBA and Visual Basic support optional parameters on method calls, but Visual C# does not. Many Office methods allow 20, 30, or more optional parameters, therefore Visual C# developers must write more code than Visual Basic developers to get the same effect.

  • Parameterized properties. VBA and Visual Basic support properties that accept parameters, acting as read-only functions. Because Visual C# does not, you must use accessor methods for setting and retrieving property values that accept parameters. The same issues apply for Word when using methods that accept Variant parameters.

  • Late binding. VBA can determine properties of objects at run time, effectively providing late binding between your code and the objects themselves. Word takes advantage of this behavior in its Dialog class. Visual Basic can only handle implicit late binding with its Option Strict Off directive. Visual C# developers and Visual Basic developers using Option Strict On cannot take advantage of implicit late binding and must use alternative means to programmatically interact with the Word Dialog class.

To expose the Office object model functionality for Visual Basic and Visual C# developers, Microsoft created a set of primary interop assemblies. Visual Studio 2005 Tools for Office uses individual primary interop assemblies for Word and Excel, and each has its own distinct behaviors for both Visual Basic and Visual C# developers. The rest of this article discusses these issues, showing how the Word and Excel primary interop assemblies handle each case, with code samples demonstrating how Visual C# developers can handle the situations described here.

Passing Parameters to Word

Objects that are provided by the Word object model expect parameters to be passed by reference. Although this is not a problem for Visual Basic developers, as a Visual C# developer, you must take extra care when passing parameters to Word methods. Most methods provided by Word expect each of its methods to be passed using the refkeyword, and you can only pass lvalues(that is, elements that can appear on the left side of an assignment) by reference. You cannot pass literal values to most Word methods from Visual C# code.

As a simple example, the following procedure creates a new window from an existing window and then tiles the two windows. The code fragment demonstrates both Visual Basic and Visual C# versions of the code.

Friend Sub CreateNewWindowAndTile()
    ' Create a new window from the active document.
    Dim wnd As Word.Window = _
        Application.ActiveWindow.NewWindow
    ' Tile the two windows.
    Application.Windows.Arrange( _
        Word.WdArrangeStyle.wdTiled)
End Sub

public void CreateNewWindowAndTile() 
{
    // Create a new window from the active document.
    Word.Window wnd =  Application.ActiveWindow.NewWindow();

    // Tile the two windows.
    Object value = Word.WdArrangeStyle.wdTiled;
    Application.Windows.Arrange(ref value);
}

In this Visual C# example, the code creates an Object variable and assigns it a value. The code then passes this variable, using the refkeyword, to the Arrange method. You must call most methods in Word this same way; many parameters must be passed using the refkeyword, and must pass a variable containing the actual value.

Note

Visual C# developers targeting Excel do not need to take any special steps. The Excel primary interop assembly has been created so that literal values passed by value to the various members are handled correctly.

Handling Optional Parameters with Excel

As in other languages that support optional parameters, optional parameters in VBA must appear at the end of a method's invocation list, and, after a parameter is marked as optional, the remainder of the parameters must also be optional. Many methods that are provided by Excel accept optional parameters; Visual Basic developers can disregard these parameters to accept their default values.

For example, the Workbooks.Add method allows you to specify an optional parameter indicating the template you want to use. You can specify the name of a template file or a member of the XlWBATemplate constants that indicates the type of sheet you want your new workbook to contain, or you can supply no value (creating a workbook with blank worksheets). If you choose to supply no value and accept the default behavior, you can write code like the following.

Dim wb As Excel.Workbook = Application.Workbooks.Add()

Similar code in Visual C# does not compile, however, because of the missing parameter. To solve this problem, pass the System.Type.Missing field for the optional parameter. If you create a project using Visual Studio 2005 Tools for Office, you can take advantage of the predefined variable named missing, which contains the value System.Type.Missing, to save some typing.

Excel.Workbook wb = Application.Workbooks.Add(System.Type.Missing);
// or
Excel.Workbook wb = Application.Workbooks.Add(missing);

System.Type.Missing is useful to Visual Basic developers when using reflection to dynamically create method calls to methods that accept optional parameters. It indicates that you want to use the default value for the specified parameter. You could, of course, read the Excel documentation, find the default value yourself, and pass it explicitly.

If the Excel method requires multiple optional parameters, you may find that writing code in Visual C# requires more effort than writing corresponding Visual Basic code. For example, the following fragments compare code in Visual Basic and Visual C#. The Workbooks.Open method allows you to specify several optional parameters, indicating the behavior of the workbook you are opening.

Dim wb As Excel.Workbook = _
    Application.Workbooks.Open("C:\YourPath\YourWorkbook.xls")

Excel.Workbook wb = Application.Workbooks.Open( 
    @"C:\YourPath\YourWorkbook.xls", 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing);

You can only pass the System.Type.Missing value for parameters that accept reference types. For value-type parameters, you need to determine the actual default value, and pass that instead. The Range.Sort method, for example, accepts a large number of enumerated values as parameters. Being value types, these parameters require you to specify exactly the value you want to pass.

' rng is a variable of type Excel.Range.
rng.Sort(rng, _
    Orientation:=Excel.XlSortOrientation.xlSortColumns)

// rng is a variable of type Excel.Range.
rng.Sort(rng, Excel.XlSortOrder.xlAscending, 
    missing, missing, Excel.XlSortOrder.xlAscending, 
    missing, Excel.XlSortOrder.xlAscending, 
    Excel.XlYesNoGuess.xlNo, missing,missing, 
    Excel.XlSortOrientation.xlSortColumns, 
    Excel.XlSortMethod.xlPinYin, 
    Excel.XlSortDataOption.xlSortNormal, 
    Excel.XlSortDataOption.xlSortNormal, 
    Excel.XlSortDataOption.xlSortNormal);

You may wonder why Visual C# does not include support for optional parameters. Instead, Visual C# and Visual Basic both support method overloading, which allows you to create multiple "versions" of a single procedure, passing in different sets of parameters. The main restriction on overloaded methods is that the parameter signature of each individual method must be different; the compiler must be able to determine which version of the procedure you are calling, based only on the parameters you call it with. This feature is more powerful, and more flexible, than VBA support for optional parameters.

Visual Basic includes optional parameters because Visual Basic and VBA have included this feature for several versions. But, for code that you write now, it is beneficial to use overloading rather than optional parameters. To promote the best code possible, the Visual C# designers used overloading rather than supporting optional parameters. In general coding, you would probably never notice that this feature is missing from Visual C#. The lack of optional parameters in Visual C# is only noticeable when you program against object models (like those provided by Office) in which many members take advantage of optional parameters, and that use a large number of optional parameters.

In some cases, you may find it useful to create wrapper methods around the built-in methods. For example, the Excel WorksheetFunction class exposes a large set of functions, originally meant for use on worksheets, that provide support for statistical, financial, and other types of worksheet-related functionality. Most of the methods of the WorksheetFunction class accept 30 parameters, with one or two required parameters. Calling these methods from Visual C# can be cumbersome, as you can see in the following example, which calls the WorksheetFunction.Min method on a specified Excel Range object.

MessageBox.Show(Application.WorksheetFunction.Min(rng))
MessageBox.Show(Application.WorksheetFunction.Max(rng))

MessageBox.Show(Application.WorksheetFunction.Min(rng, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing).ToString());
MessageBox.Show(Application.WorksheetFunction.Max(rng, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing).ToString());

For cases like these, you might consider creating a wrapper method that can call the existing method, saving you from counting and keeping track of all the optional parameters.

private delegate double WorksheetFunctionDelegate(
    object value1, object value2, object value3,
    object value4, object value5, object value6,
    object value7, object value8, object value9,
    object value10, object value11, object value12,
    object value13, object value14, object value15,
    object value16, object value17, object value18,
    object value19, object value20, object value21,
    object value22, object value23, object value24,
    object value25, object value26, object value27,
    object value28, object value29, object value30);

private double CallWorksheetFunction(
    WorksheetFunctionDelegate func,
    Object value,
    params object[] values)
    {
    // All the values except the first one.
    object[] overflowParameters = new object[29];

    values.CopyTo(overflowParameters, 0);
    for (int i = values.Length;  i < overflowParameters.Length;  i++)
    {
        overflowParameters[i] = missing;
    }

    return func (
        value,
        overflowParameters[0], overflowParameters[1], 
        overflowParameters[2], overflowParameters[3], 
        overflowParameters[4], overflowParameters[5],
        overflowParameters[6], overflowParameters[7], 
        overflowParameters[8], overflowParameters[9], 
        overflowParameters[10], overflowParameters[11],
        overflowParameters[12], overflowParameters[13], 
        overflowParameters[14], overflowParameters[15], 
        overflowParameters[16], overflowParameters[17],
        overflowParameters[18], overflowParameters[19], 
        overflowParameters[20], overflowParameters[21], 
        overflowParameters[22], overflowParameters[23],
        overflowParameters[24], overflowParameters[25], 
        overflowParameters[26], overflowParameters[27], 
        overflowParameters[28]);
    }

Then, to call a worksheet function that accepts a single required parameter, you might write code like the following.

WorksheetFunctionDelegate func = 
    new WorksheetFunctionDelegate(
    Application.WorksheetFunction.Min);
MessageBox.Show(
    CallWorksheetFunction(func, rng, new object[]{missing}).ToString());

Or, to use the same delegate to call the worksheet function with three parameters, you can use code like the following.

MessageBox.Show(CallWorksheetFunction(func, rng1, 
    new object[]{rng2, rng3}).ToString());

You can expand this functionality to support multiple required parameters, and you can also take advantage of the Visual C# paramskeyword.

Tip

The samples included with the documentation for Visual Studio 2005 Tools for Office include helper methods for some commonly used members. Within the samples, look for regions or classes with the word "helper" in the title. You can use these methods as the beginning of your own set of methods that make it easier to call methods provided by Word and Excel.

Handling Optional Parameters with Word

Working with optional parameters in Word requires you to consider the same issues that you considered when working in Excel. You might find yourself writing code like the following fragment, which compares opening a Word document in Visual Basic with the same code in Visual C#.

Application.Documents.Open("C:\Test\MyNewDocument.doc")

Object filename = @"C:\Test\MyNewDocument.doc";

Application.Documents.Open(ref filename, 
    ref missing, ref missing, ref missing, 
    ref missing, ref missing, ref missing, 
    ref missing, ref missing, ref missing, 
    ref missing, ref missing, ref missing, 
    ref missing, ref missing, ref missing);

Calling Word methods from Visual C#, in general, is more complex than calling similar Excel methods, because you must pass most of the parameters by reference. You may find it useful, if you frequently need to call methods like this, to take advantage of the missing variable created by the Visual Studio 2005 Tools for Office project templates.

You often must refer to ranges within Word. In Visual Basic, specifying the Range property without any parameters returns the entire range; you do not need to specify the start and end values if you want to work with the entire contents of the document. This is not true for Visual C# developers, of course, because in Visual C# you must always pass values for all of the optional parameters. In Visual C#, you can pass System.Type.Missing for all optional parameters to assume the default values, as in the following code fragment.

Dim rng As Word.Range = Me.Range()
rng.Select()

Word.Range rng = this.Range(ref missing, ref missing);
rng.Select();

Handling Parameterized Properties with Excel

Many properties in Excel allow you to pass parameters, indicating the specific instance of the property with which you want to interact. For example, you can specify a Range object by passing a named range as a string. You can also specify a range by passing the upper-left cell name and the lower-right cell name. You must specify at least one parameter (a named range), but you can specify two (the two cell names). In each case, the property returns an Excel.Range object corresponding to the specified range.

In Visual Basic, you can write code like the following to specify and retrieve a reference to a Range object.

Dim rng1 As Excel.Range = Application.Range("TotalSales")
' or
Application.Range("A1", "B2").Font.Bold = True

Visual C# does not allow this type of property reference — in Visual C#, property references can only have parameters if the parameter specifies an index. To work around this limitation, the Excel primary interop assembly provides accessor get and set methods for each parameterized property. For instance, to retrieve a reference to an Excel Range object using Visual C#, you can rewrite the previous code, as in the following example.

Excel.Range rng1 = 
    Application.get_Range("TotalSales", missing);
// or
Application.get_Range("A1", "B2").Font.Bold = true;

You must pass two parameters to the get_Range method, because the Application.Range property in Excel requires one parameter with an optional second parameter.

The same issues apply to the Offset property of a Range object; this parameterized property allows you to retrieve a range of a specified number of rows and/or columns from the upper-left corner of the range. The following fragments demonstrate retrieving the property in both Visual Basic and Visual C#.

Private Sub ListRecentFiles()
    Dim i As Integer
    Dim rng As Excel.Range = DirectCast( _
        Application.Range("RecentFiles"). _
        Cells(1, 1), Excel.Range)
    For i = 1 To Application.RecentFiles.Count
        rng.Offset(i - 1, 0).Formula = _
            Application.RecentFiles(i).Name
    Next
End Sub

private void ListRecentFiles()
{
    Excel.Range rng = (Excel.Range)Application.
        get_Range("RecentFiles", missing).Cells[1, 1];

    for (int i = 1; i <= Application.RecentFiles.Count; i++)
    {
        rng.get_Offset(i - 1, 0).Formula = 
            Application.RecentFiles[i].Name;
    } 
}

Tip

TheRange.Cells property looks like it is a parameterized property, but it is not. The Cells property returns an array, and you index into the array by passing values for the row and column numbers.

If you want to work with the various file-handling dialog boxes provided by Excel, you need to again use the get_FileDialog accessor method. Because the Application.FileDialog property has a parameter, it is not directly available from Visual C#. To avoid this limitation, you can call get_FileDialog, as in the following code fragment.

With Application.FileDialog( _
    Office.MsoFileDialogType.msoFileDialogFolderPicker)
    If .Show <> 0 Then
        Application.Range("FolderPickerResults"). _
            Formula = .SelectedItems.Item(1)
    End If
End With

Office.FileDialog dlg = Application.get_FileDialog(
    Office.MsoFileDialogType.msoFileDialogFolderPicker);
if (dlg.Show() != 0)
{
    Application.get_Range("FolderPickerResults", missing).
        Formula = dlg.SelectedItems.Item(1);
}

The Excel Range.Value property also requires special handling. This property accepts a parameter, but Visual C# cannot accept parameters when setting or retrieving property values. In this case, you must use the Value2 property, a variant of the original property that does not expect a parameter. For example, the following code fragments show equivalent Visual Basic and Visual C# code.

Dim rng As Excel.Range = Application.Range("A1")
rng.Value = "Some Value"

Excel.Range rng = Application.get_Range("A1", missing);
rng.Value2 = "Some Value";

This article shows examples of using the Range, Offset, Value, and FileDialog properties; there are many others. If you cannot retrieve or set a property's value directly, check to see if the property is expecting a parameter, and if so, look for the corresponding accessor methods or for an alternative property.

Using Accessor Methods in Word

Unlike Excel, Word does not generally use parameterized properties. There are still cases, however, when you program Word and need to use the get_ and set_ accessor methods. Word handles these methods slightly differently than Excel; however, in this case, the accessor methods are hidden. You do not find them in the list of available methods when you are typing, and you do not find them in the Object Browser window. You must know that they are there, and use them.

For example, the Style property in Word returns and accepts a Variant value, and rather than converting this to an Object type for use in Visual C#, you must instead call the get_Style and set_Style methods to work around this problem. You might write code like the following to set the style for a particular paragraph.

Dim rng As Word.Range = _
    Me.Paragraphs(1).Range
rng.Style = "Normal Indent"

Word.Range rng = this.Paragraphs[1].Range;
Object style = "Normal Indent";
rng.set_Style(ref style);

As with other methods in Word, you must pass the style name (or Style object) by reference, requiring the use of a variable containing the desired value. You can use similar code to retrieve a range's Style property, calling the get_Style method.

The Item method in Word has a similar challenge. Rather than allowing you to pass a Variant parameter to the method, the Word primary interop assembly exposes a get_Item method that accepts an Object parameter by reference. If you want to save a particular document within the current Documents collection, you can write code like the following fragment.

Application.Documents("MyNewDocument.doc").Save()

Object file = "MyNewDocument.doc";
Application.Documents.get_Item(ref file).Save();

The rule is simple: For any method that accepts a Variant parameter, or any parameterized property, you must assume that there is an accessor method (or pair of methods) available.

Late Binding in Word

Because the Word programming model has had a long career, some of its techniques do not fit in an object-oriented, strongly-typed world. One example of this is the Dialog class. This class provides a base set of properties and methods and an array containing all the available dialog boxes that you see from within the Word user interface. You can display any of the dialog boxes, or you can simply use the various properties exposed by the dialog boxes to affect the behavior of Word. For example, you use the Page Setup dialog box to configure page width and height for the current document.

The problem is that the Word object model does not really contain information about each specific dialog box. Although there are many properties associated with the dialog boxes, instead of hard-coding the properties in the type library (and now the primary interop assembly) for Word, the object model creates the properties "on the fly" as you load each dialog box. Basically, each control on each dialog box has a corresponding property, and you can set or retrieve the value of each control programmatically.

In VBA, this was not a problem. Although you would not get Microsoft IntelliSense hints to help you type the code, you could refer to any of the various properties and your code would still compile. In Visual Basic (with Option Strict enabled) or in Visual C#, this is not true. In strongly typed languages, you cannot expect the compiler to disregard the available properties until run time if you intend to use the standard Object.Property syntax when you write the code. For example, the following code works in Visual Basic with Option Strict Off, but the corresponding Visual C# code does not compile.

' with Option Strict Off
Dim dlg As Word.Dialog
dlg = Application.Dialogs( _
    Word.WdWordDialog.wdDialogFilePageSetup)
dlg.PageWidth = 3.3
dlg.PageHeight = 6
dlg.Execute()

// This does not compile.
dlg = Application.Dialogs
    [Word.WdWordDialog.wdDialogFilePageSetup]; dlg.PageWidth = 3.3;
dlg.PageHeight = 6;
dlg.Execute();

There are two options for working with these properties. You can either create a Visual Basic file that includes the Option Strict Off setting at the top and place your code in that file, or you can find a way to perform late binding. Both Visual C# and Visual Basic developers can take advantage of the System.Reflection namespace, which allows running code to determine available members of a specified type and to perform a type of late binding. Given the member information, your code can use the System.Reflection namespace to invoke the property set procedure, for example, to set a property value.

To handle late-bound objects from within Visual Basic or Visual C#, you can add a simple wrapper around the System.Reflection namespace's ability to set a property of an object whose capabilities are not known until run time.

Private Sub InvokeHelper(ByVal dlg As Word.Dialog, _
 ByVal member As String, ByVal dlgValue As Object)
  Dim dlgType As Type = GetType(Word.Dialog)
    dlgType.InvokeMember(member, _
     BindingFlags.SetProperty Or _
     BindingFlags.Public Or BindingFlags.Instance, _
     Nothing, dlg, New Object() {dlgValue})
End Sub

private void invokeHelper(Word.Dialog dlg, string member, Object dlgValue) 
{ 
  // Assumes a using statement in the file:
  // using System.Reflection;
  Type dlgType = typeof(Word.Dialog); 
  dlgType.InvokeMember(member, 
    BindingFlags.SetProperty | 
    BindingFlags.Public | 
    BindingFlags.Instance, 
    null, dlg, new object[] {dlgValue.ToString()}); 
}

To take advantage of the InvokeHelper method, you pass in the Dialog object, the property to set, and the value for the property.

Public Sub HiddenPageSetupDialog()
    Dim dlg As Word.Dialog
    dlg = Application.Dialogs( _
        Word.WdWordDialog.wdDialogFilePageSetup)

    InvokeHelper(dlg, "PageWidth", 3.3)
    InvokeHelper(dlg, "PageHeight", 6)
    dlg.Execute()
End Sub

public void HiddenPageSetupDialog()
{
    Word.Dialog dlg;
    dlg = Application.Dialogs[
        Word.WdWordDialog.wdDialogFilePageSetup];
    invokeHelper(dlg, "PageWidth", 3.3);
    invokeHelper(dlg, "PageHeight", 6);
    dlg.Execute();
}

Conclusion

Word and Excel expose rich programming models, but those object models were originally written to be consumed by VBA clients. Visual Basic, even with Option Strict On, can handle most of the requirements of the Office object models. Consuming those object models with Visual C# requires more care. When writing applications using Visual Studio 2005 Tools for Office and Visual C#, remember that you often need to make concessions to the differences between the languages, watching for optional parameters, parameterized properties, Variant parameters, and late binding. After you understand the types of issues you may find, writing Visual C# code that interacts with Office is no more difficult than writing Visual Basic code.

Additional Resources

Visual Studio 2005 Tools for Office

Office Developer Center

Code Security

About the Author

Ken Getz is a developer, writer, and trainer, working as a senior consultant with MCW Technologies, LLC, a Microsoft Solution Provider. He has co-authored several technical books for developers, including the best-selling ASP.NET Developer's Jumpstart, the Access Developer's Handbook series, and the VBA Developer's Handbook series. Ken co-authored AppDev's C#, ASP.NET, Visual Basic .NET and ADO.NET courseware. Ken is a technical editor for Advisor Publications' VB.NET Technical Journal, and is a columnist for both MSDN Magazine and CoDe magazine. Ken speaks regularly at industry events, including Advisor Media's Advisor Live events, FTP's VSLive, DevConnection's VS Connection and ASP Connection, and Microsoft Tech-Ed.