Share via


Understanding the Excel Object Model from a Visual Studio 2005 Developer's Perspective

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

December 2005

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

Summary: Learn how you can use the Application, Workbook, Worksheet, and Range objects in Microsoft Office Excel 2003 to create managed code solutions with Microsoft Visual Studio 2005 Tools for the Microsoft Office System. Microsoft Visual Basic and Microsoft Visual C# examples demonstrate properties, methods, and events of each object. (91 printed pages)

Note

In the MSDN Library, you can read Understanding the Excel Object Model from a .NET Developer's Perspective, which applies to Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003, Microsoft Office Excel 2003, and Microsoft Visual Studio .NET 2003.

Download OfficeVSTOExcelOM.msi.

Contents

  • Overview

  • The Application Object

  • The Workbook Class

  • The Worksheet Class

  • The Range Object

  • Conclusion

  • Additional Resources

  • About the Author

Overview

Microsoft Office System and those wishing to simply use COM Automation to control Microsoft Office Excel 2003 applications must be able to interact with the objects provided by the Excel object model. Excel provides hundreds of objects with which you might want to interact, but you can get a good start on the object model by focusing on a very small subset of the available objects. These include:

  • Application

  • Workbook

  • Worksheet

  • Range

Although it is impossible to quantify concretely, a large percentage of the work you do with Excel centers around these four classes and their members. In this document, you learn how to take advantage of each of these classes, and are introduced to some of the properties, methods, and events of each. You also see examples you can try, demonstrating some of the features of each object.

Tip

In general, developers who use Microsoft Visual Basic 2005 have an easier time working with objects in the Microsoft Office System than developers who use C# for one important reason: Microsoft Visual Basic for Applications (VBA) methods often include optional parameters, and Visual Basic 2005 supports optional parameters. C# developers find that they must supply a value for each and every optional method parameter, whereas Visual Basic developers can simply use named parameters to supply only the values they need. In addition, C# does not support properties with parameters other than indexers, yet many Excel properties accept parameters. Properties such as the Application.Range property, available in VBA and Visual Basic 2005, require separate accessor methods for C# developers (the get_Range method replaces the Range property). Watch for differences between the languages like these throughout this document.

For the most part, the Excel object model directly emulates its user interface. It is not too hard to guess that the Application object provides a wrapper around the entire application, and each Workbook object contains a collection of Worksheet objects. From there, the major abstraction representing cells is the Range object, which allows you to work with individual cells or groups of cells.

Each of the following sections describes one of the major Excel objects, picking specific members of the object for demonstration. With hundreds of objects to explore, it is impossible to dig into all of them here. However, you do get enough of the flavor of the object models to get started and to use Excel VBA Help for more details.

Tip

Throughout this article are many uses of the Visual Basic CType operator. The reason for this is that the sample project has its Option Strict setting on— this means that Visual Basic requires strict type conversions. Many Excel methods and properties return Object types or rely on late binding: For example, the Application.ActiveSheet property returns an Object, as opposed to a Worksheet, as you might expect. Therefore, to be as rigorous about conversions as possible, the sample has enabled Option Strict, and handles each type conversion explicitly. (Without using Option Strict in Visual Basic, it is possible that you your code compiles fine, but fails at run time. That is the point of Option Strict—it makes it much less likely that an invalid conversion at run time causes an exception.) If you are a C# developer, you can appreciate this decision.

To make things easier for developers using Visual Studio 2005 Tools for Office, the Excel project templates provide easy access to the Excel Application object, and to each of the individual sheets within the current workbook. Developers can use the Application reference to work with the Application object. In addition, the Globals class provides references to each of the individual worksheets within the workbook.

This article references the sample project, ExcelObjectModelVB.sln or ExcelObjectModelCS.sln. This project contains an Excel workbook and associated Visual Basic or C# code. Not every sample shown in this paper appears in the example project, but any that involves more than a line or two of code is placed into the workbook, with a hyperlink within the project set up to call the code.

Throughout this document, are references to named ranges treated as objects: Because of the addition of host controls in Visual Studio 2005 Tools for Office, it is possible to treat named ranges created at design time as if they are standard controls. For example, the following code clears the contents of an existing named range (the named range in this case is WorkbookPath):

WorkbookPath.ClearContents()
WorkbookPath.ClearContents();

This article discusses other ways to refer to named ranges, but if a named range is created at design time in the sample project, the demonstration code refers to the named range using the new, and very convenient, functionality.

Tip

In an article of this limited size, it is not possible to document each and every object or member. It is not even possible to mention a small fraction of the classes. Your best tool for investigating any large object model is the Object Browser window, where you can find a list of each of the classes, along with the members of that class. Many of the members discussed in this document apply to many different classes: for example, the PrintOut method discussed in the context of the Sheets collection applies just as well to the Chart, Worksheet, Range, and other objects. The point of this document is to give you an idea of what is available, and leave the rest to your inquisitive nature.

The Application Object

The Excel Application object represents the Excel application itself. That may sound obvious, but the Application object exposes a great deal of information about the running application, the options applied to that instance, and the current user objects open within the instance. The Application object provides many members, many of which you do not need to investigate, and others of which are crucial to the correct behavior of your application. You can break these members down into the following categories:

  • Members that control the state and display in Excel

  • Members that return objects

  • Members that execute actions

  • Members that handle file manipulation

  • Other members

The following sections introduce each of these groups, along with code examples demonstrating of some of the members.

Members That Control the State and Display in Excel

The Application object provides a large set of properties that control the general state of Excel. Table 1 lists a subset of the state-related Application object properties.

Table 1. A Subset of the Application Properties that Control the State of Excel

Property

Type

Description

Cursor

XlMousePointer (xlDefault, xlIBeam, xlNorthwestArrow, xlWait)

Gets or sets the appearance of the mouse pointer.

EditDirectlyInCell

Boolean

Gets or sets the ability to edit cells directly in place. If False, you can only edit cells in the formula bar.

FixedDecimal

Boolean

If True, all numeric values use the FixedDecimalPlaces property to determine the number of decimal places; otherwise, FixedDecimalPlaces property is ignored (the default value is False).

FixedDecimalPlaces

Long

Determines the number of decimal places to be used for numeric data if the FixedDecimal property is True.

Interactive

Boolean

Gets or sets the ability of the user to interact with Excel via the keyboard and mouse; if you set this property to False, make absolutely sure you set it back to True in your exception handler. Excel does not reset it for you.

MoveAfterReturn

Boolean

If True, the selection moves to the next cell when you press Enter; the default value is True.

MoveAfterReturnDirection

xlDirection (xlDown, xlToLeft, xlToRight, xlUp)

Indicates the direction to move after pressing Enter, if the MoveAfterReturn property is True. The default value is xlDown.

ScreenUpdating

Boolean

If True, Excel updates its screen after each method call. To save time, and to make your application look more professional, you can turn off the display while your code is running. Make sure you reset this property to True again once you are finished. Excel does not reset it for you.

SheetsInNewWorkbook

Long

Gets or sets the number of sheets Excel automatically places in new workbooks

StandardFont

String

Gets or sets the name of the default font in Excel; does not take effect until you restart Excel.

StandardFontSize

Long

Gets or sets the size of the default font in Excel; does not take effect until you restart Excel.

StartupPath (read-only)

String

Returns the complete path of the folder containing the Excel startup add-ins.

TemplatesPath (read-only)

String

Returns the complete path of the folder containing templates; this value represents one of the Windows special folders.

Of all the properties listed in Table 1, the one you are most likely to use is the ScreenUpdating property. By taking advantage of this property, you can not only make your Excel applications look more professional, you can make them run faster—updating the display after each modification can exact a huge toll on your code, especially when programmatically filling in a large range. It is important, however, that you always set this property when you are finished with your work, because Excel does not reset it for you. Therefore, you do need to always use code similar to the following fragment when using the ScreenUpdating property, taking advantage of .NET exception handling to ensure that screen updating resumes:

Try
    Application.ScreenUpdating = False
    ' Do your work that updates the screen.

Finally
    Application.ScreenUpdating = True
End Try
try
{
    Application.ScreenUpdating = false;
    // Do your work that updates the screen.
}

finally
{
    Application.ScreenUpdating = true;
}

The Application object also provides a group of properties that control the display in Excel. You can modify any of these to change what users see on the screen. Table 2 lists a subset of the available display options.

Table 2. A Subset of the Application Properties that Control the Appearance of Excel

Property

Type

Description

DisplayAlerts

Boolean

If True (the default value), Excel displays warning messages while your code runs, as necessary—when deleting a sheet, for example. Set to False to bypass warnings. Excel acts as if you selected the default value for each alert.

DisplayFormulaBar

Boolean

If True (the default value), Excel displays the standard formula bar for editing cells; set to False to hide the editing bar.

DisplayFullScreen

Boolean

If True, Excel runs in full-screen mode (which has a different effect from simply maximizing the Excel window); the default value is False.

Tip

Just as with the ScreenUpdating property, it is important to reset the DisplayAlerts property. Excel does not reset this property for you. Further, with it set to False, Excel does not prompt you to save workbooks before you close them. Therefore, not carefully resetting the DisplayAlerts property can cause you to lose data if you are not careful.

Members That Return Objects

Many of the Application object's properties return other objects. Because the standard Microsoft Office project template supplied by Visual Studio 2005 Tools for Office provides a global reference to the Application object, you generally need to take advantage of the object members of the Application class to reference the other objects provided by Excel. You can use these members to retrieve a reference to a particular child object using a property like ActiveWindow, or to a collection of available objects using a property like Charts. Table 3 lists a subset of the object-returning properties of the Application object.

Visual Studio 2005 Tools for Office creates a class named Globals that provides references to all the top-level objects within your project (ThisWorkbook, and each of the individual sheets within the workbook). If you need to refer to these items, use the Globals class to retrieve the reference.

Table 3. A Subset of the Available Object-Returning Properties of the Application Object

Property

Type

Description

ActiveCell

Range

Returns a reference to the currently active cell in the active window (the window that is on top). If there is no active window, this property raises an error.

ActiveChart

Chart

Returns a reference to the currently active chart. An embedded chart is only considered active when it is selected or activated.

ActiveSheet

Object

Returns a reference to the active sheet in the active workbook.

ActiveWindow

Window

Returns a reference to the active window (the window that is on top); returns Nothing if there are no active windows.

Charts

Sheets

Returns a collection of Sheet objects (the parent for both Chart and Worksheet objects) containing references to each of the charts in the active workbook.

Selection

Object

Returns the selected object within the application. Might be a Range, a Worksheet, or any other object—also applies to the Window class, in which case the selection is generally a Range object. If no object is currently selected, returns Nothing.

Sheets

Sheets

Returns a collection of Sheet objects containing references to each of the sheets in the active workbook.

Workbooks

Workbooks

Returns a collection of Workbook objects containing references to all the open workbooks.

You most often interact with the Workbooks property of the Application class. This property allows you to iterate through the open workbooks, open or create a new workbook. The following section describes the behavior of this property.

The Workbooks Collection

The Workbooks collection makes it possible to work with all the open workbooks, create a new workbook, and import data into a new workbook. The following list works through some of the main uses you find for the Workbooks collection:

  • Create a new workbook using code like the following (you can also specify the name of a workbook template as a parameter to the Add method):

    Dim wb As Excel.Workbook = Application.Workbooks.Add()
    
    Excel.Workbook wb = Application.Workbooks.Add(missing);
    
  • Close all open workbooks. Unlike most collections, this one allows you to close all its members at once. The following method call closes all open workbooks:

    Application.Workbooks.Close()
    
    Application.Workbooks.Close();
    
  • Open an existing workbook, using the Open method of the Workbooks collection. In its simplest form, you use the Open method like you see in the following fragment. The Open method supplies a large number of optional parameters that affect its behavior in specific circumstances, but you generally do not need to use the optional parameters:

    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);
    

    Tip

    If you are a C# developer, get used to seeing references to the missing value in method calls. Because the Excel object model is written with VBA in mind, many of its methods accept optional parameters—sometimes up to 30 optional parameters. You need to use many instances of the missing value or supply the specific default value for each individual parameter.

  • Open a text file, database, or XML file as a workbook using the OpenText, OpenDatabase or OpenXml method. These methods provide a great deal of flexibility, and even simple coverage takes more space than can be allotted here. Your knowledge of the existence of these methods is enough for now—if you need to load any of these items into Excel, you can investigate these items in more detail. You can use code like the following to load a text file as a workbook, using commas as delimiters, starting on the third row in the text file:

    Application.Workbooks.OpenText("C:\Test.txt", StartRow:=3, _
     DataType:=Excel.XlTextParsingType.xlDelimited, Comma:=True)
    
    Application.Workbooks.OpenText("C:\\Test.txt", 
        missing, 3, Excel.XlTextParsingType.xlDelimited, 
        Excel.XlTextQualifier.xlTextQualifierDoubleQuote, 
        missing, missing, missing, True, 
        missing, missing, missing, 
        missing, missing, missing, 
        missing, missing, missing);
    
  • Refer to individual workbooks. You can index into the Workbooks collection using either integers (indicating position within the collection) or workbook names. If you want to refer to a workbook by name, however, you must be careful how you refer to it: you must use the name you see in the title bar, which does not include the .xls extension until you have saved the file:

    Dim wb As Excel.Workbook = Application.Workbooks(1)
    ' Before Book1 is saved:
    wb = Application.Workbooks("Book1")
    ' After Book1 is saved:
    wb = Application.Workbooks("Book1.xls")
    
    Excel.Workbook wb = Application.Workbooks[1];
    // Before Book1 is saved:
    wb = Application.Workbooks["Book1"];
    // After Book1 is saved:
    wb = Application.Workbooks["Book1.xls"];
    

    Tip

    When you refer to a particular workbook, you are taking advantage of the default indexer, the Item property. In addition to the Item property, the Workbooks collection, like all collections provided by Microsoft Office, includes a Count property that returns the number of items, (Workbooks in this case) in the collection.

Members That Execute Actions

The Application object provides a number of methods that allow you to execute actions from recalculating the current data to undoing changes to data. The following list enumerates some of the Application object's methods and describes each with a small example. The samples for this section appear on the Application Object sheet of the sample workbook:

  • Calculate: Forces a recalculation of all open workbooks, a specific workbook, or a specific range:

    Application.Calculate
    ' Or...
    Globals.Sheet1.Calculate
    ' Or...
    SomeNamedRange.Calculate
    
    Application.Calculate();
    // Or...
    Globals.Sheet1.Calculate();
    // Or...
    SomeNamedRange.Calculate();
    

    Note

    As shown in the example code, the Range and Worksheet objects also supply a Calculate method. Use the method of the object that limits the calculation range to the smallest number of cells that you want to recalculate. The recalculation engine in Excel is very fast, but if you can limit the number of cells involved, you can optimize the operation. Use Application.Calculate only when you want to recalculate every pending change in every open workbook.

    Tip

    Visual Basic and C# do not handle Excel members exactly the same. For example, the Range property in Excel, VBA, and Visual Basic can only be accessed in C# using the get_Range method. You can see several examples of this, and other, accessor members throughout this document. It is generally easiest to refer to named ranges that you create at design time using the NamedRange host control that is created for you by Visual Studio 2005 Tools for Office. For example, in the previous code snippets, SomeNamedRange is a NamedRange host control.

  • CheckSpelling: Returns a Boolean value indicating whether the supplied parameter is spelled correctly. You can optionally supply the name of a custom dictionary and a Boolean value indicating whether you want to ignore case. The following fragment checks the spelling of a value you supply and indicates the results on the sheet:

    Friend Sub TestSpelling()
        ' Expects ranges named CheckSpellingParam 
        ' and CheckSpellingResults:
        Dim strOut As String
    
        If Application.CheckSpelling( _
          Me.CheckSpellingParam.Text.ToString()) Then
            strOut = "Spelled correctly"
        Else
            strOut = "Spelled incorrectly"
        End If
        Me.CheckSpellingResults.Formula = strOut
    End Sub
    
    internal void TestSpelling()
    {
        // Expects ranges named CheckSpellingParam 
        // and CheckSpellingResults:
        this.CheckSpellingResults.Formula =
        Application.CheckSpelling(
        this.CheckSpellingParam.Text.ToString(),
        missing, missing)
        ? "Spelled correctly"
        : "Spelled incorrectly";
    }
    
  • Evaluate: Converts an Excel name into an actual reference or value. This method allows you to create a reference as a string, and then convert it as needed into an actual object reference, or to evaluate the value of the expression. The following example allows you to enter a cell address into the sample sheet, and the code places text into the cell whose address you specify:

    Friend Sub TestEvaluate()
        ' Expects range named EvaluateParam:
        Try
            Dim rngNew As Excel.Range = _
              CType(Application.Evaluate( _
              Me.EvaluateParam.Text), Excel.Range)
            rngNew.Formula = "Hello, World!"
        Catch ex As Exception
            MessageBox.Show(ex.Message, Application.Name)
        End Try
    End Sub
    
    internal void TestEvaluate()
    {
        try
        {
            // Expects a range named EvaluateParam:
            Excel.Range rngNew =
                (Excel.Range)Application.Evaluate(
                this.EvaluateParam.Text);
            rngNew.Formula = "Hello, World!";
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, Application.Name);
        }
    }
    
  • MailSystem, MailSession, MailLogoff, MailLogon, SendMail: These members allow you to log on, send the current workbook as an attachment, and log off from your installed e-mail system. The MailSystem property indicates the installed e-mail system and the MailSession property returns a reference to the current e-mail session (you do not need to log on if there is an active session). The following example sends the sample workbook as an attachment to a simple e-mail message:

    Friend Sub TestEmail()
        ' Expects a range named SendMailParam:
        If Application.MailSystem = Excel.XlMailSystem.xlMAPI Then
            If Application.MailSession Is Nothing Then
                Dim frm As New SendMailForm
                If frm.ShowDialog = DialogResult.OK Then
                    Application.MailLogon( _
                     frm.EmailName, frm.EmailPassword, _
                     frm.DownloadNewMail)
                End If
            End If
            Dim strEmail As String = Me.SendMailParam.Text.ToString()
            Globals.ThisWorkbook.SendMail( _
             strEmail, "Sample Excel Email")
            Application.MailLogoff()
        Else
            MessageBox.Show( _
             "This demonstration works only if MAPI is installed.")
        End If
    End Sub
    
    internal void TestEmail()
    {
        if (Application.MailSystem ==
            Excel.XlMailSystem.xlMAPI)
        {
            if (Application.MailSession == null)
            {
                SendMailForm frm = new SendMailForm();
                if (frm.ShowDialog() == DialogResult.OK)
                {
                    Application.MailLogon(frm.EmailName,
                        frm.EmailPassword, frm.DownloadNewMail);
                }
            }
            // Expects a range named SendMailParam:
            string strEmail = this.SendMailParam.Text.ToString();
            Globals.ThisWorkbook.SendMail(strEmail,
                "Sample Excel Email", missing);
            Application.MailLogoff();
        }
        else
        {
            MessageBox.Show("This demonstration works only if " +
                "MAPI is installed.");
        }
    }
    

    Note

    The Workbook class provides the SendMail method; this makes sense, because the most granular object you can email is the workbook itself. The SendMail method does not supply any means of attaching text to the message you send, nor is there much flexibility in the addressing. Clearly, these members are supplied only to make it simple to send a workbook via email. If you want more full-featured support, you have to investigate other means of interacting with e-mail. In addition, if you are not currently online and connected to your e-mail system, the previous sample code fails. You can work around this failure and not attempt to send the mail, if the MailSession property returns Nothing.

  • Quit: Allowsyou to quit Excel programmatically. If you have set the DisplayAlerts property to False, you are not prompted to save any unsaved data. In addition, if you set the Saved property of a Workbook to True, Excel does not ask you to save it whether or not you have made changes:

    Application.Quit
    
    Application.Quit();
    
  • Undo: Cancels the last action taken by the user within the user interface. This method has no effect on actions taken by code, and can only undo a single action. It is not terribly functional, but it does allow you to undo the last action the user took before executing your code:

    Application.Undo
    
    Application.Undo();
    

Members That Handle File Manipulation

The Application object provides several members that allow you to interact with the file system within the Excel application's context. The following sections describe some of the members you are likely to use. (The longer samples described in this section are on the Application File Handling sheet of the sample workbook.)

DefaultFilePath Property

This simple property gets or sets the path Excel uses for loading and saving files:

' When the workbook opens:
DefaultFilePath.Formula = Application.DefaultFilePath

' When you save the DefaultFilePath property:
Application.DefaultFilePath = DefaultFilePath.Text.ToString
// When the workbook opens:
DefaultFilePath.Formula = Application.DefaultFilePath;

// When you save the DefaultFilePath property:
Application.DefaultFilePath = 
    DefaultFilePath.Text.ToString();

DefaultSaveFormat Property

This property gets or sets the default format for saving workbooks. Excel provides a large number of options for this property, all of which are members of the XlFileFormat enumeration. The sample workbook allows you to select from the available items, as shown in Figure 1. The following code fragment demonstrates how the sample loads and saves the property's value.

In the example, column E in the sample sheet contains a list of the names of all the possible values for the XlFileFormat enumeration (in a range named XlFileFormat) and column F contains the corresponding integer values. Figure 2 shows a subset of these two columns. The DefaultSaveFormat named range (in Figure 1) contains a reference to the XlFileFormat range, allowing you to select from a list. Once you make a choice and elect to save the value, the code must find the string you have selected using the Range.Find method, and then uses the Range.Offset method to return a value at the specified offset from the value you found. For more information on the Range.Find method, see "Searching within Ranges" later in this article. Finally, the code stores the integer value (converted to the appropriate enumeration type) back into the DefaultSaveFormat property.

Retrieving the current value of the DefaultSaveFormat is easy. The following code converts the value into text, and displays it in the correct Range on the sample sheet:

' When the workbook opens, convert the enumerated value 
' into a string:
DefaultSaveFormat.Formula = _
    Application.DefaultSaveFormat.ToString
// When the workbook opens, convert the enumerated value 
// into a string:
DefaultSaveFormat.Formula = 
    Application.DefaultSaveFormat.ToString();

Assigning the selected value back is a bit more difficult. This involves three steps. The code must handle the following tasks:

Retrieve the name of the selected save format from the DefaultSaveFormat range on the sheet:

' Retrieve the name of the new save format, as a string:
Dim strSaveFormat As String = DefaultSaveFormat.Text.ToString()
// Retrieve the name of the new save format,
// as a string:
string strSaveFormat = DefaultSaveFormat.Text.ToString();

Look up the matching integer value, in the column adjacent to the XlFileFormat range on the sheet, calling the Find method of the Range class. Then, the code uses the Range.Offset property to retrieve the value one column to the right:

Dim intSaveFormat As Integer = _
    CType(xlFileFormat.Find(strSaveFormat). _
    Offset(0, 1).Value, Integer)
Excel.Range rngFind = xlFileFormat.Find(strSaveFormat,
    missing, missing, missing, missing,
    Excel.XlSearchDirection.xlNext, missing, missing,
    missing);

// In C#, use the get_Offset method instead of the Offset property:
int intSaveFormat =
    Convert.ToInt32(rngFind.get_Offset(0, 1).Value2);

Tip

The previous fragment uses the Offset method of a Range object, neither of which you may have encountered yet. Both are discussed in the section discussing the Range object later in this article. The use of the Range class is simple to understand: A Range object represents a cell or group of cells. The Offset property returns a Range object at the specified number of rows and columns from the upper-left corner of the associated Range, and allows you to work with cells relative to a known location.

Assign the integer value back into the DefaultSaveFormat property:

Application.DefaultSaveFormat = _
    CType(intSaveFormat, Excel.XlFileFormat)
Application.DefaultSaveFormat = 
    (Excel.XlFileFormat) intSaveFormat;

Figure 1. Select a file format from the list of available types

Figure 2. A subset of the XlFileFormat range on the sample worksheet

RecentFiles Property

The RecentFiles property returns a collection of strings containing the names of all the files that appear within the File menu's list of recently used files. The length of the list varies depending on the number of files the user has selected to retain. The sample workbook calls this procedure as it opens, copying the list of recent files to a range named RecentFiles on the sample worksheet:

Friend Sub ShowRecentFiles()
    ' Expects a range named RecentFiles:
    For i As Integer = 1 To Application.RecentFiles.Count
        CType(RecentFiles.Cells(i, 1), Excel.Range).Formula = _
          Application.RecentFiles(i).Name
    Next
End Sub
internal void ShowRecentFiles()
{
    // Expects a range named RecentFiles:
    for (int i = 1; i <= Application.RecentFiles.Count; i++)
    {
        ((Excel.Range) RecentFiles.Cells[i, 1]).Formula = 
            Application.RecentFiles[i].Name;
    }
}

FileDialog Property

The FileDialog property returns a FileDialog object, which handles four types of file manipulation. This FileDialog object returned by the property allows you to:

  • Select a file and open it.

  • Select a file location and save the current workbook.

  • Select a folder.

  • Select a file name.

Using this dialog box, you can take advantage of all the file handling capabilities provided by Microsoft Office. The FileDialog property requires that you select a particular use of the dialog box by passing it one of the msoFileDialogType enumerated values, msoFileDialogFilePicker, msoFileDialogFolderPicker, msoFileDialogOpen, or msoFileDialogSaveAs. You can then interact with the FileDialog object returned by the property.

The FileDialog object, like many others, is provided by the Microsoft.Office.Core namespace. The Visual Studio 2005 Tools for Office project templates import this namespace automatically, so you do not need to do any extra work to use members of the namespace. In other words, your projects automatically include a statement like the following:

Imports Office = Microsoft.Office.Core
using Office = Microsoft.Office.Core;

The FileDialog object's Show method displays the dialog box; this method returns -1 if you press OK, and 0 if you press Cancel. If you have used the msoFileDialogOpen or msoFileDialogSaveAs enumerated values, you can use the Execute method of the class to actually open or save the files. The SelectedItems property contains a collection of strings, each representing one of the selected file names.

For example, the following code from the sample workbook prompts you to open a new workbook. This fragment allows multi-selection, clears the list of available filters, adds two new filters, and then displays the dialog box, as shown in Figure 3. If you select a file or files, the code then calls the Execute method of the FileDialog object to open the requested file(s):

With Application.FileDialog( _
  Office.MsoFileDialogType.msoFileDialogOpen)
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls;*.xlw"
    .Filters.Add "All Files", "*.*"
    If .Show <> 0 Then
        .Execute
    End If
End With
// Declared previously:
Office.FileDialog dlg;

// In the code:
dlg = Application.get_FileDialog(
    Office.MsoFileDialogType.msoFileDialogOpen);
dlg.Filters.Clear();
dlg.Filters.Add("Excel Files", "*.xls;*.xlw", missing);
dlg.Filters.Add("All Files", "*.*", missing);
if(dlg.Show() != 0)
    dlg.Execute();

Figure 3. Using the FileDialog class displays the standard File Open dialog box

The following fragment from the sample demonstrates how you might use the dialog box to select a folder:

With Application.FileDialog( _
  Office.MsoFileDialogType.msoFileDialogFolderPicker)
    If .Show <> 0 Then
        FolderPickerResults.Formula = .SelectedItems.Item(1)
    End If
End With
dlg = Application.get_FileDialog(
    Office.MsoFileDialogType.msoFileDialogFolderPicker);
if (dlg.Show() != 0)
{
    FolderPickerResults.Formula = 
     dlg.SelectedItems.Item(1);
}

Note

The Application object also provides the GetOpenFileName and GetSaveAsFileName methods, which allow you to select a file name for opening. Although you can use these, the corresponding OpenFileDialog and SaveFileDialog controls provided by the Microsoft .NET Framework are richer and easier to use.

Other Useful Members

The Application object provides several members that do not fit other categories, such as the WorksheetFunction property, the Names collection, and the Windows collection. The following sections describe these members.

The WorksheetFunction Class

The Application object includes a property, WorksheetFunction, which returns an instance of the WorksheetFunction class. This class provides a number of shared/static methods, each of which wraps up an Excel worksheet function. Each of these methods exposes one of the many Excel spreadsheet calculation functions that are not otherwise provided by VBA. Some of the members are duplicated by Visual Basic and C# operators and methods, so you are unlikely to use those (for example, the And method).

What you find buried in the methods of the WorksheetFunction class are a large number of interesting and useful functions, summarized in the following list:

  • Mathematical functions such as Acos, Acosh, Asin, Asinh, Cosh, Degrees, Ln, Log, Median, Max, Min, Mode, Radians, and more.

  • Domain functions that allow you to perform calculations on ranges, such as DAverage, DCount, DCountA, DGet, DMax, DMin, DProduct, DSum, and more.

  • Logical functions such as IsErr, IsError, IsLogical, IsNA, IsNonText, IsNumber, IsText.

  • Statistical functions such as BetaDist, BinomDist, ChiTest, ChiInv, LogNormDist, NegBinomDist, Pearson, SumProduct, SumSq, TDist, TTest, Var, VarP, and more.

  • Spreadsheet functions that you are unlikely to take advantage of from the .NET Framework, such as And, Or, Choose, and more.

  • Thai-related functions: There are an unexplained raft of functions that manipulate Thai numbers, calendar, and currency, such as BahtText, IsThaiDigit, ThaiDayOfWeek, ThaiDigit, ThaiMonthOfYear, ThaiNumSound, ThaiNumString, ThaiStringLength, ThaiYear, RoundBahtDown, and RoundBahtUp.

From a Visual Studio 2005 Tools for Office project, it is easy to take advantage of the WorksheetFunction class. Because the project template provides you with the Application object, you can simply refer to the WorksheetFunction property of that object. The sample application contains a sheet named Other Application Members, shown in Figure 4, which tests just a few members of the class.

Note

The WorksheetFunction class and its members provides a good example of why working with Excel objects from Visual Basic is far easier than the equivalent code in C#. Many of the WorksheetFunction class methods require C# developers to pass 30 parameters, most of which are empty. It is certainly possible to ease this load by writing wrappers around the different groups of methods (those that have one required parameter, those that have two required parameters, and so on). For the purposes of this document, the code calls the methods with no wrapper methods. The C# code is quite ugly, that is for sure.

Clicking the Demonstrate WorksheetFunction link runs the following code. For more information on the Sort method, see "Sorting Data within a Range" later in this article.

Friend Sub TestWorksheetFunction()
    ' Expects ranges named RandomNumbers, 
    ' Min, Max, Median, Average, and StDev:
    Dim rng As Excel.Range = Me.RandomNumbers.InnerObject
    Dim rnd As New System.Random

    For i As Integer = 1 To 20
        rng.Cells(i, 1) = rnd.Next(100)
    Next i
    rng.Sort(rng, _
      Orientation:=Excel.XlSortOrientation.xlSortColumns)

    With Application.WorksheetFunction
        Min.Formula = .Min(RandomNumbers)
        Max.Formula = .Max(rng)
        Median.Formula = .Median(rng)
        Average.Formula = .Average(rng)
        StDev.Formula = .StDev(rng)
    End With
End Sub
internal void TestWorksheetFunction()
{
    // Expects ranges named RandomNumbers, 
    // Min, Max, Median, Average, and StDev:
    Excel.Range rng = this.RandomNumbers.InnerObject;
    System.Random rnd = new System.Random();

    for (int i = 1; i <= 20; i++)
        rng.Cells[i, 1] = rnd.Next(100);

    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);

    Excel.WorksheetFunction wsf = Application.WorksheetFunction;
    Min.Formula = wsf.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);
    Max.Formula = wsf.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);
    Median.Formula = wsf.Median(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);
    Average.Formula = wsf.Average(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);
    StDev.Formula = wsf.StDev(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);
}

Figure 4. Select the WorksheetFunction sheet to test the WorksheetFunction class and its useful methods

As you can see in the sample code, you can pass a Range object as a parameter to the WorksheetFunction methods. In addition, you can pass a single value, or a list of values as parameters. The methods generally accept up to 32 parameters, so if you want to calculate the average of a fixed list of numbers, you can use code like the following:

Dim dblAverage As Double = Application.WorksheetFunction.Average( _
 12, 14, 13, 19, 21)
// Note the number of missing values--the method accepts
// 30 parameters.
Double dblAverage = Application.WorksheetFunction.Average(
    12, 14, 13, 19, 21, 
    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);

The Window Class and Windows Collection

As you might expect, it is the Application object that provides control over the windows displayed within the Excel application, and you can use the Windows property of the Application object to open, close, and arrange Excel object windows.

The Windows property returns a collection of Window objects, and you can call the Arrange method to arrange all the open windows (or just the visible windows). Specify one of the XlArrangeStyle enumerated values to indicate how you want the windows arranged, and optionally, information on whether you want only visible windows arranged and how you want to synchronize the window scrolling. For example, to tile the windows within the Excel workspace, you can use code like the following:

Application.Windows.Arrange( _
  Excel.XlArrangeStyle.xlArrangeStyleTiled)
Application.Windows.Arrange( 
  Excel.XlArrangeStyle.xlArrangeStyleTiled, 
  missing, missing, missing);

If you want to programmatically create a new window, you can call the NewWindow method of the workbook, like this:

ThisWorkbook.NewWindow()
ThisWorkbook.NewWindow();

Because the NewWindow method returns a Window object, you can also write code like the following, which sets the caption for the new window and then activates it:

With ThisWorkbook.NewWindow()
    .Caption = "New Window"
    .Activate()
End With
Excel.Window wnd = ThisWorkbook.NewWindow();
wnd.Caption = "New Window";
wnd.Activate();

The Windows class provides properties and methods that control the appearance and behavior of the associated window, including colors, caption, visibility of window features, and scrolling behavior. You can write code like the following to work with a particular window's properties:

With Application.Windows(3)
    .GridlineColor = ColorTranslator.ToOle(Color.Red)
    .Caption = "A New Window"
    .DisplayHeadings = False
    .DisplayFormulas = False
    .DisplayWorkbookTabs = False
    .SplitColumn = 1
End With
wnd = Application.Windows[3];
wnd.GridlineColor = ColorTranslator.ToOle(Color.Red);
wnd.Caption = "A New Window";
wnd.DisplayHeadings = false;
wnd.DisplayFormulas = false;
wnd.DisplayWorkbookTabs = false;
wnd.SplitColumn = 1;

Tip

Although VBA and .NET work with colors using a similar paradigm—each uses a triplet of bytes containing red, green, and blue components of a color, encoded as the lower three bytes of a 32-bit integer—they handle the colors differently. You can use the System.Drawing.ColorTranslator.ToOle method to convert from a .NET color to an OLE color that is required by VBA.

Clicking Work with Windows on the Other Application Members sheet runs the sample procedure TestWindows, which contains all the code provided in small chunks throughout this section. Clicking Reset Windows in the same sheet runs the following procedure, which closes all but the first window and then maximizes that window:

Friend Sub DoResetWindows()
    For i As Integer = Application.Windows.Count To 2 Step -1
        Application.Windows(i).Close()
    Next
    Application.Windows(1).WindowState = _
     Excel.XlWindowState.xlMaximized
End Sub
internal void DoResetWindows()
{
    for (int i = Application.Windows.Count; i >= 2; i--)
        Application.Windows[i].Close(
          false, missing, missing);
    Application.Windows[1].WindowState =
      Excel.XlWindowState.xlMaximized;
}

The Name Class and Names Collection

The Application object supplies its Names property, which returns a collection of Name objects. Each Name object corresponds to a named range in the Excel application. There are a number of ways to retrieve a reference to a named range: you can use the Names property of a Workbook or of a Worksheet object, also.

To create a new named range, use the Add method of the Names collection, as in the following fragment. The Add method accepts a number of optional parameters, in addition to the two required parameters:

Dim nm As Excel.Name = Application.Names.Add( _
 "NewName", Me.Cells(6, 1))
Excel.Name nm = Application.Names.Add(
  "NewName", this.Cells[6, 1],
  missing, missing, missing,
  missing, missing, missing,
  missing, missing, missing);

Specify the name and location (along with other optional parameters), and you can then refer to the range in your code:

Application.Range("NewName").Formula = "Hello, World!"
Application.get_Range(
  "NewName", missing).Formula = "Hello, World!";

To retrieve information about a named range, you can use the various properties of the Name class. The following list describes a few of the most commonly used members:

  • Name returns the name assigned to the named range.

  • RefersTo returns a string containing the actual target address, in standard format ("=SheetName!$B$25").

  • RefersToR1C1 returns the target address, in "R1C1" format ("=SheetName!R25C2").

  • Value returns a reference to the named range that resolves to the contents of the range.

Clicking the Work with Names link in the sample runs the following code, filling a region on the sheet with information about all the named ranges:

' Expects a range named NamesInfo
Dim nm As Excel.Name
Dim rng As Excel.Range = Me.NamesInfo.InnerObject

For i As Integer = 0 To Application.Names.Count - 1
    nm = Application.Names.Item(i + 1)
    rng.Offset(i, 0).Value = nm.Name
    ' Without the leading "'", these references
    ' get evaluated, rather than displayed directly.
    rng.Offset(i, 1).Value = "'" & nm.RefersTo.ToString
    rng.Offset(i, 2).Value = "'" & nm.RefersToR1C1.ToString
    rng.Offset(i, 3).Value = nm.Value
Next i
// Expects a range named NamesInfo
Excel.Range rng = this.NamesInfo.InnerObject;
for (int i = 0; i <= Application.Names.Count - 1; i++)
{
    nm = Application.Names.Item(i + 1, missing, missing);
    rng.get_Offset(i, 0).Value2 = nm.Name;
    // Without the leading "'", these references
    // get evaluated, rather than displayed directly.
    rng.get_Offset(i, 1).Value2 = "'" + nm.RefersTo.ToString();
    rng.get_Offset(i, 2).Value2 = "'" + nm.RefersToR1C1.ToString();
    rng.get_Offset(i, 3).Value2 = nm.Value;
}

Application Events

In addition to all the other methods provided by the Application class, a large group of events is available. Although it is impossible to demonstrate them all in any coherent sort of way, their use is relatively clear from the names alone. The following sections describe a subset of the events, arguably the events you are most likely going to handle in your own applications.

Tip

The parameters passed to event handlers in an Office application may feel different than parameters used in native .NET events. Normally, .NET event handlers always receive an Object variable referring to the object that raised the event, and a second parameter that inherits from the EventArgs base class containing extra information about the event. There is no such well-defined event design pattern for Office applications, so each event handler accepts an arbitrary number of parameters, defined by the original developer.

Sheet Behavior

The Application object provides a range of events associated with sheets (both charts and worksheets). The following list contains information about many of those events:

  • SheetActivate occurs when any sheet is activated. Excel passes the event handler an Object variable containing a reference to the activated sheet.

    Tip

    As in any situation in Excel in which you are passed an Object that refers to a sheet, you need to cast the reference to a variable of the correct type (Worksheet or Chart, depending on the circumstances) before you can use the reference. If you have disabled the Option Strict setting in Visual Basic, however, you can take advantage of late binding. You still cannot take advantage of IntelliSense as you type, making writing the code more difficult. All of the examples in this document that use an item within the Sheets collection cast the result as the particular type of sheets that is required, either a Worksheet or Chart, explicitly.

  • SheetBeforeDoubleClick occurs when any sheet is double-clicked, before Excel provides the default double-click handling. Excel passes the event handler an Object variable containing a reference to the sheet, a Range object containing the cell nearest the location of the double-click, and a Boolean value (False by default) that allows you to cancel the default event handling. (This event does not occur on chart sheets.)

    Tip

    All of the events that include the word "Before" in their name allow you to cancel the default event handling. The parameter passed to your event handler is normally named Cancel, and has a default value of False. Set this parameter to True, and Excel does not execute its default handling for the event.

  • SheetBeforeRightClick occurs when any sheet is right-clicked, before Excel provides the default right-click handling. Excel passes the event handler an Object variable containing a reference to the sheet, a Range object containing the cell nearest the location of the right-click, and a Boolean value (False by default) that allows you to cancel the default event handling. (This event does not occur on chart sheets.)

  • SheetCalculate occurs when any sheet is recalculated. Excel passes the event handler an Object variable containing a reference to the sheet that is recalculated.

  • SheetChange occurs when cells in any worksheet are changed, either by the user or by running code. Excel passes the event handler an Object variable containing a reference to the sheet and a Range variable referring to the changed range.

  • SheetDeactivate occurs when any sheet is deactivated (that is, when it no longer has the focus). This event handler only runs when the focus shifts to another sheet within the same workbook. Excel passes the event handler an Object variable containing a reference to the sheet that is deactivated.

  • SheetFollowHyperlink occurs when you click any hyperlink within any workbook. Excel passes the event handler an Object variable referring to the sheet containing the link, and a Hyperlink object containing a reference to the link you clicked. (The sample project makes use of this event, providing the navigation within the sample.)

  • SheetSelectionChange occurs when the selection changes on a worksheet (the event does not occur on chart sheets). Excel passes the event handler an Object variable referring to the sheet on which the selection changed and a Range variable that refers to the new selection. (Excel does not pass information about the original selection before it changed.)

    Note

    Each of the events in this section is also available as an event provided by the Workbook class. When the event is provided by the Application object, it is raised for any sheet currently open within Excel. When it is provided by the Workbook object, the event only occurs when it affects a sheet within that particular workbook. In addition, the same events are provided by the Worksheet class. In that case, the event name does not include the word "Sheet" (for example, you find FollowHyperlink instead of SheetFollowHyperlink, and so on), and the event handlers are not passed a reference to a sheet—that information is implied by the object that received the event. Otherwise, the events, their usage, and their parameters are identical to the events you see here.

Window Behavior

The Application object (and correspondingly, the Workbook object) provides a range of events that handle behavior of Window objects. The following list describes these events:

  • WindowActivate occurs when any window is activated. Excel passes the event handler a Workbook object referring to the workbook that supplied the window, and a Window object that refers to the selected window. Like other activation events, this event only fires when the focus moves within Excel. Switching to another application and then back to Excel does not raise this event.

  • WindowDeactivate occurs when any window is deactivated.

  • WindowResize occurs when any workbook window is resized. Excel passes the event handler a Workbook object referring to the workbook that supplied the window and a Window object that refers to the resized window.

    Note

    In the events provided by the Workbook class, the event handlers do not receive a reference to a Workbook—that information is implied by the object that raised the event.

Workbook Management

The Application object provides a range of events that occur when you interact with any Workbook object. Each of these event procedures receives a Workbook variable that indicates the particular workbook involved in the event. The following list describes a subset of the available events:

  • NewWorkbook occurs when a new workbook is created. Excel passes the event handler a Workbook variable that refers to the new workbook. (This event is only supplied by the Application class.)

  • WorkbookActivate occurs when any workbook is activated. Excel passes the event handler a Workbook variable that refers to the workbook that is activated. (As with other "activation" events, this event only occurs when you switch from one workbook to another.)

  • WorkbookBeforeClose occurs when an open workbook closes, just before the default event handling. Excel passes the event handler a Workbook variable that refers to the workbook that is about to close, along with a Boolean value (False by default) that allows the event handler to cancel the default event handling (that is, keeping the workbook open).

    Note

    If you summarily set the Cancel parameter to True, without taking any conditions into consideration, no workbook can ever close.

  • WorkbookBeforePrint occurs when printing starts within a workbook, just before the default event handling. Excel passes the event handler a Workbook variable that refers to the workbook containing printed content, along with a Boolean value (False by default) that allows the event handler to cancel the default event handling (that is, skipping the requested printing).

  • WorkbookBeforeSave occurs when a workbook is saved, just before the default event handling. Excel passes the event handler a Workbook variable that refers to the workbook you are saving, along with a Boolean value (False by default) that allows the event handler to cancel the default event handling (that is, canceling the save).

  • WorkbookDeactivate occurs when any workbook is deactivated. Excel passes the event handler a Workbook variable that refers to the workbook that is deactivated. (As with other "activation" events, this event only occurs when you switch from one workbook to another.)

  • WorkbookNewSheet occurs when a new sheet is added to a workbook. Excel passes the event handler a Workbook variable that refers to the workbook, and an Object variable that refers to the new sheet.

  • WorkbookOpen occurs when a workbook is opened. Excel passes the event handler a Workbook variable that refers to the newly opened workbook.

    Note

    The Workbook class provides its own set of events, very similar to the events you have seen here. All the events that begin with "Workbook" appear in the list of events in the Workbook without that designation ("Activate" instead of "WorkbookActivate", and so on). The Workbook class event handlers do not receive a Workbook variable as a parameter; that information is implied by the object raising the event. In addition, the Workbook class provides mirrors of the other Application object events, but traps them only for a single workbook, as opposed to trapping for all workbooks. The rest of this document does not discuss events, as you have already seen the ones you are most likely to use.

The Workbook Class

As you might imagine, the Workbook class represents a single workbook within the Excel application. In this section, you investigate some of the members of this class, including the most-often-used properties and methods.

Tip

Many members of the Application class show up as members of the Workbook class as well. In this case, the properties apply to a specific workbook, as opposed to applying to the active workbook. This section discusses far fewer members than the previous section, mostly because you have already seen many of the items in question.

Properties of the Workbook Class

The Workbook class provides a huge number of properties (around 90 or so), and many deal with specific cases that most developers never think about; for example, the AutoUpdateFrequency property returns the number of minutes between automatic updates for a shared workbook, the Date1904 property returns True if the workbook uses the 1904 date system (a date serialization scheme which uses Jan 2, 1904 as the date corresponding to the value 1, common on Macintosh computers), the PasswordEncryptionAlgorithm property allows you to set the exact algorithm used for encrypting passwords, and so on.

Rather than making any attempt to be comprehensive in covering the many properties of the Workbook object, this section merely introduces the ones you are most likely to use. The general rule of thumb is this: If you need some behavior of a workbook, someone else probably already requested it, and there is most likely a property that allows the behavior, and normally a method that provides the behavior. Check the documentation carefully before adding your own code to a workbook.

The following list describes some of the most commonly used Workbook properties:

  • Name, FullName, Path (String, read-only): Each of these properties returns a different version of the workbook's name. FullName returns the full path, including the workbook file name. Name returns just the name portion and Path returns just the path portion. Clicking the Name Information link in the sample workbook runs the following code, and returns information as shown in Figure 5:

    WorkbookName.Formula = Globals.ThisWorkbook.Name
    WorkbookPath.Formula = Globals.ThisWorkbook.Path
    WorkbookFullName.Formula = Globals.ThisWorkbook.FullName
    
    WorkbookName.Formula = Globals.ThisWorkbook.Name;
    WorkbookPath.Formula = Globals.ThisWorkbook.Path;
    WorkbookFullName.Formula = Globals.ThisWorkbook.FullName;
    

Figure 5. Use Workbook properties to retrieve information about the name

  • Password (String): Gets or sets the password associated with the workbook. If you have specified a non-empty password, the HasPassword property of the workbook returns True, as well. You can retrieve the Password property, but its value is always "********". Clicking the Set Password link on the sample workbook runs the following code, which either sets or clears the workbook's password, depending on whether you have supplied text or an empty string. This example uses the form named Password from the sample project, which provides a single text box and a Password property:

    Friend Sub DoSetPassword()
        Using frm As New PasswordForm
            If frm.ShowDialog = DialogResult.OK Then
                Globals.ThisWorkbook.Password = frm.Password
            End If
        End Using
    End Sub
    
    private void DoSetPassword()
    {
        using (PasswordForm frm = new PasswordForm())
        {
            if (frm.ShowDialog() == DialogResult.OK)
                Globals.ThisWorkbook.Password = frm.Password;
        }
    }
    
  • PrecisionAsDisplayed (Boolean): If True, Excel performs calculations using the number of decimals displayed. If False (the default value), calculations are performed using all available decimals, even if they are not all displayed. Figure 6 shows the sample workbook, with the property set to True. Each of the values in column C is a copy of a value in column B, but the numeric formatting is set to display only two decimal places in column C. With the PrecisionAsDisplayed property set to True, the sums are different, because rounding caused the actual values to be different. If you click the PrecisionAsDisplayed = False link, the sums are the same. Clicking calls the following procedure, passing in True or False (depending on the link you click):

    Private Sub TestPrecisionAsDisplayed( _
      ByVal IsPrecisionAsDisplayedOn As Boolean)
        Globals.ThisWorkbook. _
         PrecisionAsDisplayed = IsPrecisionAsDisplayedOn
    End Sub
    
    private void TestPrecisionAsDisplayed(
        bool IsPrecisionAsDisplayedOn)
    {
        Globals.ThisWorkbook.PrecisionAsDisplayed = 
            IsPrecisionAsDisplayedOn;
    }
    

Figure 6. With the PrecisionAsDisplayed property set to True, Excel uses only visible decimals to perform calculations

  • ReadOnly (Boolean, read-only): Returns True if the workbook opened as read-only. You may want to take different actions in your application if you are unable to save data to the workbook.

  • Saved (Boolean): Gets or sets the saved state of the workbook. If the user made modifications to the workbook's contents or structure, the Saved property is True. Attempting to close the workbook or quit Excel causes an alert to appear, prompting you to save the workbook (unless you have set the Application.DisplayAlerts property to False). If you set the value of the Saved property value to False in your code, Excel treats your workbook as if it is already saved, and does not prompt you to save it again.

Working with Document Properties

Just like the other Office applications, Excel allows you to store document properties along with the workbook. Excel provides a number of built-in properties, and you can add your own, as well. Selecting File|Properties displays the dialog box shown in Figure 7, and you can also select the Custom tab to create and modify custom properties.

Figure 7. Use this dialog box to set document properties

Use the Workbook class BuiltInDocumentProperties property to work with built-in properties, and the CustomDocumentProperties property to work with custom properties. Each of these properties returns a DocumentProperties object, which is a collection of DocumentProperty objects. You can use the Item property of the collection to retrieve a particular property, either by name, or by index within the collection. The full list of property names is available in the Excel documentation, but there is an easy way to retrieve the list: the following procedure runs when you click the Document Properties link on the sample workbook (see Figure 8). This procedure calls the DumpPropertyCollection method to list all the built-in properties and their current values, and then repeats the process for the custom properties. In addition, the procedure modifies the Revision Number property individually, and creates a new custom property:

Friend Sub DisplayDocumentProperties()
    Dim prp As Office.DocumentProperty
    Dim prps As Office.DocumentProperties

    ' Expects a range named DocumentProperties:
    Dim rng As Excel.Range = Me.DocumentProperties.InnerObject
    Dim i As Integer

    Try
        Application.ScreenUpdating = False

        Try
            prps = CType( _
             Globals.ThisWorkbook.BuiltinDocumentProperties, _
             Office.DocumentProperties)

            ' Set the Revision Number property:
            prp = prps.Item("Revision Number")
            prp.Value = CType(prp.Value, Integer) + 1

            ' Dump contents of the collection:
            DumpPropertyCollection(prps, rng, i)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        ' Work with custom properties:
        Try
            prps = CType( _
             Globals.ThisWorkbook.CustomDocumentProperties, _
             Office.DocumentProperties)
            DumpPropertyCollection(prps, rng, i)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        ' Add a custom property:
        Try
            ' Delete the property, if it exists.
            prp = prps.Item("Project Name")
            prp.Delete()
        Catch
            ' Do nothing if you get an exception.
        End Try

        Try
            ' Add a new property.
            prp = prps.Add("Project Name", False, _
             Office.MsoDocProperties.msoPropertyTypeString, _
             "White Papers")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    Finally
        Application.ScreenUpdating = True
    End Try
End Sub

Private Sub DumpPropertyCollection( _
 ByVal prps As Office.DocumentProperties, _
 ByVal rng As Excel.Range, ByRef i As Integer)
    Dim prp As Office.DocumentProperty

    For Each prp In prps
        rng.Offset(i, 0).Value = prp.Name
        Try
            If Not prp.Value Is Nothing Then
                rng.Offset(i, 1).Value = _
                 prp.Value.ToString
            End If
        Catch
            ' Do nothing at all.
        End Try
        i += 1
    Next
End Sub
internal void DisplayDocumentProperties()
{
    Office.DocumentProperty prp = null;
    Office.DocumentProperties prps =
      (Office.DocumentProperties)
      Globals.ThisWorkbook.BuiltinDocumentProperties;

    // Expects a range named DocumentProperties:
    Excel.Range rng = DocumentProperties.InnerObject;
    int i = 0;

    try
    {
        Application.ScreenUpdating = false;

        try
        {
            // Set the Revision Number property:
            prp = prps["Revision Number"];
            prp.Value = Convert.ToInt32(prp.Value) + 1;

            // Dump contents of the collection:
            i = DumpPropertyCollection(prps, rng, i);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, Application.Name);
        }

        // Work with custom properties:
        try
        {
            prps = (Office.DocumentProperties)
             Globals.ThisWorkbook.CustomDocumentProperties;
            DumpPropertyCollection(prps, rng, i);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, Application.Name);
        }

        // Add a custom property:
        try
        {
            // Delete the property, if it exists.
            prp = prps["Project Name"];
            prp.Delete();
        }
        catch
        {
            // Do nothing if you get an exception.
        }
        try
        {
            // Add a new property.
            prp = prps.Add("Project Name", false,
              Office.MsoDocProperties.msoPropertyTypeString,
              "White Papers", missing);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, Application.Name);
        }
    }
    finally
    {
        Application.ScreenUpdating = true;
    }
}

private int DumpPropertyCollection(
 Office.DocumentProperties prps, Excel.Range rng, int i)
{
    foreach (Office.DocumentProperty prp in prps)
    {
        rng.get_Offset(i, 0).Formula = prp.Name;
        try
        {
            if (prp.Value != null)
            {
                rng.get_Offset(i, 1).Formula =
                  prp.Value.ToString();
            }
        }
        catch
        {
            // Do nothing at all.
        }
        i += 1;
    }
    return i;
}

Figure 8. Built-in document properties

Note

Although you are working with Excel and its objects here, it is the Microsoft Office application that supplies the list of available built-in document properties, and Excel does not necessarily implement all the properties—attempting to access the Value property for undefined properties triggers an exception. The sample procedure includes simple exception handling to deal with this situation, if it occurs.

Working with Styles

Much like a Microsoft Office Word document, Excel workbooks allow you to apply named styles to regions within the workbook, and Excel supplies a number of predefined (although not terribly interesting) styles. You can use the Format|Styles menu item to display a dialog box that allows you to interactively modify styles, as shown in Figure 9.

Figure 9. Modify styles interactively with this dialog box

If you click Modify on the Style dialog box, you see the Format Cells dialog box shown in Figure 10.

Figure 10. Use the Format Cells dialog box to modify styles

The Format Cells dialog box displays all the options you can use when formatting cells, and each of the options available in this dialog box is available from within your code. You can use the Styles property of a Workbook object to interact with and apply styles to ranges within the workbook.

You can create, delete, and modify styles, using the Styles property of a Workbook object. Clicking Apply Style on the sample workbook runs the following procedure, which creates a new style (or uses an existing one, if you have run the code already), sets up the various aspects of the style, and applies it to a region:

Friend Sub DoApplyStyle()
    Const STYLE_NAME As String = "PropertyBorder"
    Dim rng As Excel.Range
    ' Get the range containing all the document properties.
    rng = GetDocPropRange()

    Dim sty As Excel.Style
    Try
        sty = Globals.ThisWorkbook.Styles(STYLE_NAME)
    Catch
        sty = Globals.ThisWorkbook.Styles.Add(STYLE_NAME)
    End Try

    sty.Font.Name = "Verdana"
    sty.Font.Size = 12
    sty.Font.Color = ColorTranslator.ToOle(Color.Red)
    sty.Interior.Color = ColorTranslator.ToOle(Color.LightGray)
    sty.Interior.Pattern = Excel.XlPattern.xlPatternSolid
    rng.Style = STYLE_NAME

    rng.Columns.AutoFit()
End Sub
internal void DoApplyStyle()
{
    const String STYLE_NAME = "PropertyBorder";
    // Get the range containing all the document properties.
    Excel.Range rng = GetDocPropRange();
    Excel.Style sty;
    try
    {
        sty = Globals.ThisWorkbook.Styles[STYLE_NAME];
    }
    catch
    {
        sty = Globals.ThisWorkbook.Styles.Add(STYLE_NAME, missing);
    }

    sty.Font.Name = "Verdana";
    sty.Font.Size = 12;
    sty.Font.Color = ColorTranslator.ToOle(Color.Blue);
    sty.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
    sty.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
    rng.Style = STYLE_NAME;
    rng.Columns.AutoFit();
}

The GetDocPropRange method returns the range filled in by the document properties. This procedure uses the Range.End method to find the end of the range filled with document properties, and then creates a new range based on the upper-left corner of the range and the bottom-right corner:

Private Function GetDocPropRange() As Excel.Range
    ' Expects a range named DocumentProperties:
    Dim rng As Excel.Range = Me.DocumentProperties.InnerObject

    Dim rngStart As Excel.Range = _
     CType(rng.Cells(1, 1), Excel.Range)
    Dim rngEnd As Excel.Range = _
     rng.End(Excel.XlDirection.xlDown).Offset(0, 1)

    Return Application.Range(rngStart, rngEnd)
End Function
 private Excel.Range GetDocPropRange()
{
    // Expects a range named DocumentProperties:
    Excel.Range rng =
      this.DocumentProperties.InnerObject;
    Excel.Range rngStart =
      (Excel.Range)rng.Cells[1, 1];
    Excel.Range rngEnd =
      rng.get_End(Excel.XlDirection.xlDown).get_Offset(0, 1);
    return Application.get_Range(rngStart, rngEnd);
}

Tip

For more information on retrieving and working with Range objects, see "Working with Ranges" later in this article.

After you have run the code, the region containing document properties on the sample workbook changes shading and font, as shown in Figure 11.

Figure 11. The region containing document properties changes shading and font after applying a custom style

Clicking Clear Style runs the following procedure, which clears the style for the same region:

Friend Sub DoClearStyle()
    ' Get the range containing all the document properties, and
    ' clear the style
    GetDocPropRange().Style = "Normal"
End Sub
internal void DoClearStyle()
{
    // Get the range containing all the document properties, and
    // clear the style
    GetDocPropRange().Style = "Normal";
}

Working with Sheets

The Workbook class provides a Sheets property that returns a Sheets object. This object contains a collection of Sheet objects, each of which can be either a Worksheet or a Chart object. Clicking List Sheets on the sample workbook runs the following procedure and lists all the existing sheets in the workbook:

Friend Sub DoListSheets()
    Dim i As Integer = 0

    Dim rng As Excel.Range = Me.Sheets.InnerObject
    For Each sh As Excel.Worksheet In Globals.ThisWorkbook.Sheets
        rng.Offset(i, 0).Value = sh.Name
        i = i + 1
    Next sh
End Sub
internal void DoListSheets()
{
    int i = 0;

    // Expects a range named Sheets:
    Excel.Range rng = this.Sheets.InnerObject;
    foreach (Excel.Worksheet sh in Globals.ThisWorkbook.Sheets)
    {
        rng.get_Offset(i, 0).Formula = sh.Name;
        i = i + 1;
    }
}

You may also find the following members of the Sheets class to be useful.

  • The Visible propertyallows you to show or hide an existing sheet without having to delete and recreate the sheet. Set the Visibility property to one of the XlSheetVisibility enumerated values (XlSheetHidden, XlSheetVeryHidden, xlSheetVisible). Using XlSheetHidden allows users to unhide the sheet through the Excel interface; using XlSheetVeryHidden requires that you run code to unhide the sheet:

    Globals.Sheet2.Visible = _
      Excel.XlSheetVisibility.xlSheetVeryHidden
    
    Globals.Sheet2.Visible = 
        Excel.XlSheetVisibility.xlSheetVeryHidden;
    
  • The Add method allows you to add a new sheet to the collection of sheets in the workbook, and accepts four optional parameters indicating the location of the sheet, the number of sheets to add, and the type of sheet (worksheet, chart, and so on):

    Dim sh As Excel.Sheet = ThisWorkbook.Sheets.Add()
    
    Excel.Sheet sh = ThisWorkbook.Sheets.Add(
        missing, missing, missing, missing);
    
  • The Copy method creates a copy of a sheet, and inserts the sheet at the location you specify. You can specify to insert the new sheet either before or after an existing sheet, if you like. If you do not specify, Excel creates a new workbook to contain the new sheet. The following fragment copies a sheet in the current workbook and places the copy after the third sheet:

    Globals.Sheet2.Copy(After:=Globals.ThisWorkbook.Sheets(3))
    
    Globals.Sheet2.Copy(missing, Globals.ThisWorkbook.Sheets[3]);
    
    The Delete method deletes a specified sheet:
    
    Globals.Sheet3.Delete
    
    Globals.Sheet3.Delete();
    
  • The FillAcrossSheets method copies data from a range on one sheet to all the other sheets within the workbook. You specify a range, and whether you want to copy data, formatting, or all, Excel does the rest. The following fragment copies data and formatting from a range named Data on one sheet to the same region on all the sheets in the workbook:

    Globals.ThisWorkbook.Sheets.FillAcrossSheets( _
      Application.Range("Data"), Excel.XlFillWith.xlFillWithAll)
    
    Globals.ThisWorkbook.Sheets.FillAcrossSheets(
        Application.get_Range("Data", missing), 
        Excel.XlFillWith.xlFillWithAll);
    
  • The Move method works much like the Copy method, except that you end up with only one instance of the sheet. You can specify either the sheet to place your sheet before, or the sheet to place it after (but not both). Again, if you do not specify a location for the moved sheet, Excel creates a new workbook to contain it. The following fragment moves a worksheet so that it is the last sheet in the workbook:

    Dim shts As Excel.Sheets = Globals.ThisWorkbook.Sheets
    Globals.Sheet2.Move(After:=shts(shts.Count))
    
    Excel.Sheets shts = Globals.ThisWorkbook.Sheets;
    Globals.Sheet2.Move(missing, shts[shts.Count]);
    

    Tip

    If you have some reason to sort the list of sheets in a workbook, you can use the Move method to perform an inefficient bubble sort. Of course, because you are unlikely to have very many sheets, the speed of the sort is not an issue.

  • The PrintOut method allows you to print the selected object (this method applies to several different objects). You can specify a number of optional parameters, including the pages to print (from and to), the number of copies, whether you want to preview before printing, the name of the printer to use, whether you want to print to a file, whether you want to collate, and the name of the file you want to print to. The following example prints the specified sheet, printing only the first page, requests two copies, and previews the document before printing, using the default printer:

    Globals.Sheet2.PrintOut(From:=1, To:=1, Copies:=2, Preview:=True)
    
    Globals.Sheet2.PrintOut(1, 1, 2, true, missing, missing, 
        missing, missing);
    
  • The PrintPreview method allows you to display the specified object in the PrintPreview window, optionally disallowing changes to the page layout:

    Globals.Sheet2.PrintPreview(False)
    
    Globals.Sheet2.PrintPreview(false);
    
  • The Select method selects the specified object, moving the user's selection. (Use the Activate method instead to bring focus to the object without changing the user's selection.) You can optionally supply a reference to an object to be replaced by the current selection. The following fragment selects the first worksheet:

    CType(Application.ActiveWorkbook.Sheets(1), Excel.Worksheet).Select()
    
    ((Excel.Worksheet)Application.ActiveWorkbook.Sheets[1]).Select(
       missing);
    

    Tip

    Many of the methods listed in this section apply to other classes. For example, the PrintOut method is provided by the Chart, Charts, Range, Sheets, Window, Workbook, Worksheet, and Worksheets classes. The methods work the same in each case; they just act on a different object. The Select method applies to just about every selectable object (and there are many of them).

Methods of the Workbook Class

The Workbook class provides a huge number of methods, many handling very specific situations. Rather than focus on the details, this section discusses some of the methods you are likely to use in every application, leaving the esoteric methods for later study. The following list describes some of the methods you are most likely to need:

  • The Activate method activates a workbook, selecting the first sheet in the workbook:

    Application.Workbooks(1).Activate
    
    Application.Workbooks[1].Activate();
    
  • The Close method closes the specified workbook, optionally specifying whether to save changes. If you have never saved the workbook, you can specify a file name. In addition, if the workbook is to be routed to other users, you can specify whether you want to send the workbook on to the next user. The following fragment closes the workbook, discarding changes:

    Application.Workbooks(1).Close(SaveChanges:=False)
    
    Application.Workbooks[1].Close(false, 
      missing, missing);
    
  • The Protect and Unprotect methods allow you to protect a workbook so that you cannot add or delete worksheets, and to unprotect the workbook again. You can optionally specify a password, and can optionally indicate whether you want the structure protected (so users cannot move sheets around) and whether you want the workbook's windows protected. Protecting a workbook does not keep a user from editing cells. To protect data, you must protect the worksheets. Call the Unprotect method, passing a password if it is required, to unprotect the workbook. The following example assumes a procedure named GetPasswordFromUser, which requests the user to enter a password, and returns the entered value:

    Application.Workbooks(1).Protect(GetPasswordFromUser())
    
    Application.Workbooks[1].Protect(
      GetPasswordFromUser(), missing, missing);
    
  • The Save method saves the workbook, as you might have expected. If you have never saved the workbook, call the SaveAs method instead, so you can specify a path (if you have not yet saved the workbook, Excel saves it in the current folder with the name it had when it was created):

    ' Save all open workbooks.
    Dim wb As Excel.Workbook
    For Each wb in Application.Workbooks
        wb.Save
    Next wb
    
    // Save all open workbooks.
    foreach (Excel.Workbook wb in Application.Workbooks)
    {
        wb.Save();
    } 
    
  • The SaveAs method is far more complex than the Save method. This method allows you to save the specified workbook, optionally specifying a name, a file format, a password, an access mode, and more. See Excel VBA Help for a list of all the options. The following fragment saves the current workbook to a specified location, saving in XML format:

    Application.ActiveWorkbook.SaveAs("C:\MyWorkbook.xml", _
     FileFormat:=Excel.XlFileFormat.xlXMLSpreadsheet)
    
    Application.ActiveWorkbook.SaveAs("C:\\MyWorkbook.xml", 
        Excel.XlFileFormat.xlXMLSpreadsheet, missing, 
        missing, missing, missing, 
        Excel.XlSaveAsAccessMode.xlNoChange, missing, 
        missing, missing, missing, missing);
    

    Tip

    You may want to set the Application.DisplayAlerts property to False before calling the SaveAs method, because saving in some formats requires interaction. For example, when saving a worksheet to XML format, Excel reminds you that you cannot save the VBA project with the workbook. Setting the DisplayAlerts property to False suppresses this warning.

  • The SaveCopyAs method saves a copy of the workbook to a file, but does not modify the open workbook in memory. This method is useful when you want to create a backup copy without modifying the location of the workbook:

    Application.ActiveWorkbook.SaveCopyAs("C:\Test.xls")
    
    Application.ActiveWorkbook.SaveCopyAs("C:\\Test.xls");
    

    Note

    Interactively canceling any of the methods that save or copy the workbook triggers a run-time error in your code. For example, if your procedure calls the SaveAs method but does not disable prompts from Excel, and your user clicks Cancel when prompted, Excel raises a run-time error back to your code. You generally want to trap that exception and handle it, rather than passing it back up to the procedure's caller.

The Worksheet Class

At this point in this document, you have read about most of the concepts you need in order to work with an individual worksheet. Although the Worksheet class provides a large number of members, most of the properties, methods, and events are identical or similar to members provided by the Application and/or Workbook classes. This section focuses on important members and issues specific to the Worksheet class that you have not already seen described elsewhere in this document. (You can find the examples for this section in the sample workbook on the Worksheet Object sheet.)

There is No Sheet Class

Although Excel provides a Sheets collection as a property of a Workbook object, you do not find a Sheet class in Excel. Instead, each member of the Sheets collection is either a Worksheet, or a Chart object. You can think of it this way: the Worksheet and Chart classes are each specialized instances of an internal Sheet class (and there is no way for anyone without access to the source code to know if this matches the actual implementation), but the Sheet class is not publicly available.

Working with Protection

In general, the protection feature in Excel keeps users and/or code from modifying objects within a worksheet. Once you enable protection for a worksheet, unless you make provisions otherwise, users cannot edit or otherwise modify the sheet. Within the user interface, you can enable protection using the Tools|Protection|Protect Sheet menu item. Selecting this item displays the Protect Sheet dialog box shown in Figure 12. You can set a password here or can allow users to take specific actions. By default, all cells are locked once you turn on protection. In addition, you can allow users to edit specific ranges, using the Tools|Protection|Allow Users to Edit Ranges menu item, which displays the dialog box shown in Figure 13. Using a combination of these two dialog boxes, you can lock down the sheet and then allow users to edit specific features and ranges.

Figure 12. In the user interface, use the Protect Sheet dialog box to control protection

Figure 13. Use the Allow Users to Edit Ranges dialog box to allow users to edit specific ranges

You can control the protection of your sheet programmatically using the Protect method of the worksheet. The syntax for the method looks like the following, in which each parameter is optional:

WorksheetObject.Protect(Password, DrawingObjects, Contents, _
  Scenarios, UserInterfaceOnly, AllowFormattingCells, _
  AllowFormattingColumns, AllowFormattingRows, _
  AllowInsertingColumns, AllowInsertingRows, _
  AllowInsertingHyperlinks, AllowDeletingColumns, _
  AllowDeletingRows, AllowSorting, AllowFiltering, _
  AllowUsingPivotTables)
WorksheetObject.Protect(Password, DrawingObjects, Contents, 
  Scenarios, UserInterfaceOnly, AllowFormattingCells, 
  AllowFormattingColumns, AllowFormattingRows, 
  AllowInsertingColumns, AllowInsertingRows, 
  AllowInsertingHyperlinks, AllowDeletingColumns, 
  AllowDeletingRows, AllowSorting, AllowFiltering, 
  AllowUsingPivotTables);

The following list describes the parameters to the Protect method:

  • Set the Password parameter to specify a case-sensitive string that is required to unprotect the worksheet. If you do not specify this parameter, anyone can unprotect the sheet.

  • Set the DrawingObjects parameter to True to protect shapes on the worksheet. The default is False.

  • Set the Contents parameter to True to protect the contents (cells) of the worksheet. The default is True, and may never need to be changed.

  • Set the Scenarios parameter to True to protect the scenarios on the worksheet. The default is True.

  • Set the UserInterfaceOnly parameter to True to allow changes from code, but not from the user interface. The default is False, meaning that neither code nor entries in the user interface can make changes to the protected worksheet. This property setting applies only to the current session. You must include code that sets this property each time your workbook is opened if you want code to be able to manipulate the worksheet in any session.

  • The AllowFormattingCellsparameter, AllowFormattingColumnsparameter, and the remainder of the parameters shown in the previous full listing of the method syntax allow specific formatting capabilities, corresponding to the options in the dialog box shown in Figure 12. By default, all these properties are False.

To protect a worksheet, call the sheet's Protect method, as in the following fragment, which sets the password and allows only sorting:

Globals.Sheet2.Protect("MyPassword", AllowSorting:=True)
Globals.Sheet2.Protect(
    "MyPassword", missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, missing, missing, missing, 
    missing, true, missing, missing);

Tip

Clearly, hard-coding the password within your code is not a great idea. Most likely, you must request the password from the user, and apply it to the workbook without saving it. Generally, you do not find hard-coded passwords in source code.

To unprotect a worksheet, you can use code like the following. This fragment assumes a procedure named GetPasswordFromUser, which requests that the user enter a password, and return the entered value:

Globals.Sheet2.Unprotect(GetPasswordFromUser())
Globals.Sheet2.Unprotect(GetPasswordFromUser());

The Unprotect method removes protection from the worksheet, allowing you to supply an optional password.

Excel also provides two other useful objects when working with protection: the Protection and AllowEditRange objects. The Protection object encapsulates all the information you specify when you call the Protect method, as well as information about unprotected ranges. Calling the Protect method sets properties of the shared Protection object, which provides the following Boolean properties and which correspond to the parameters of the Protect method:

  • AllowDeletingColumns, AllowDeletingRows

  • AllowFiltering

  • AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows

  • AllowInsertingColumns, AllowInsertingHyperlinks, AllowInsertingRows

  • AllowSorting

  • AllowUsingPivotTables

In addition, the Protection class provides the AllowEditRanges property, which allows you to specify the editable ranges on the worksheet, corresponding to the information specified in the dialog box shown in Figure 13. The AllowEditRanges property contains a collection of AllowEditRange objects, each of which provides a number of useful properties, including:

  • Range: gets or sets the range corresponding to the editable area

  • Title: gets or sets the title of the editable region (for display in the dialog box shown in Figure 13.)

  • Users: gets or sets a collection of UserAccess objects For more information on the UserAccess object, see Excel VBA Help.

On the Worksheet Object sheet of the sample workbook (see Figure 14), you can experiment with programmatic protection. Click Protect to protect the sheet so that you can only edit within the shaded regions (the two ranges named Information and Date). Click Unprotect to unprotect the sheet.

Figure 14. Test the protection capabilities of a worksheet

The links on the sample sheet run the following procedures:

Friend Sub DoProtectSheet()
    ' Work with the current sheet:
    Dim ws As Excel.Worksheet = Me.InnerObject

    ' Expects ranges named Information and DateInfo
    With ws.Protection.AllowEditRanges
        .Add("Information", Me.Information.InnerObject)
        .Add("Date", Me.DateInfo.InnerObject)
    End With
    ws.Protect()
End Sub

Friend Sub DoUnprotectSheet()
    ' Work with the current sheet:
    Dim ws As Excel.Worksheet = Me.InnerObject

    ' Unprotect the sheet.
    ws.Unprotect()

    ' Delete all protection ranges, just to clean up.
    ' You must loop through this using the index, 
    ' backwards. This collection does not provide 
    ' an enumeration method, and it does not handle
    ' being resized as you are looping in a nice way.
    With ws.Protection.AllowEditRanges
        For i As Integer = .Count To 1 Step -1
            .Item(i).Delete()
        Next i
    End With
End Sub
internal void DoProtectSheet()
{
    // Work with the current sheet.
    Excel.Worksheet ws = this.InnerObject;

    // Expects ranges named Information and DateInfo:
    Excel.AllowEditRanges ranges = ws.Protection.AllowEditRanges;
    ranges.Add("Information",
        this.Information.InnerObject, missing);
    ranges.Add("Date",
        this.DateInfo.InnerObject, missing);

    ws.Protect(missing, missing, missing,
        missing, missing, missing, missing,
        missing, missing, missing, missing,
        missing, missing, missing,
        missing, missing);
}

internal void DoUnprotectSheet()
{
    // Work with the current sheet:
    Excel.Worksheet ws = this.InnerObject;
    ws.Unprotect(missing);

    // Delete all protection ranges, just to clean up.
    // You must loop through this using the index, 
    // backwards. This collection does not provide 
    // an enumeration method, and it does not handle
    // being resized as you are looping in a nice way.
    Excel.AllowEditRanges ranges = ws.Protection.AllowEditRanges;
    for (int i = ranges.Count; i >= 1; i--)
    {
        ranges[i].Delete();
    }
}

Object Properties

The Worksheet class provides several properties that return objects. The following sections introduce these objects and provide examples of working with them.

Comments

Using the Insert|Comment menu item, you can insert a text comment attached to a range on a worksheet (see Figure 15). You can accomplish the same goal in code using the AddComment method of the Range object. The following code deletes the comment associated with the range named Date if it exists, and then creates a new comment. Finally, the code displays all the comments on the sheet, calling the ShowOrHideComments method described in the next code example (see Figure 16):

Friend Sub DoWorkWithComments()
    ' Add a new comment:
    Dim rng As Excel.Range = Me.DateInfo.InnerObject
    If rng.Comment IsNot Nothing Then
        rng.Comment.Delete()
    End If
    rng.AddComment("Comment added " & DateTime.Now)

    ' Display all the comments:
    ShowOrHideComments(Show:=True)
End Sub
internal void DoWorkWithComments()
{
    // Add a new comment:
    Excel.Range rng = this.DateInfo.InnerObject;
    if (rng.Comment != null)
    {
        rng.Comment.Delete();
    }
    rng.AddComment("Comment added " + DateTime.Now);

    // Display all the comments:
    ShowOrHideComments(true);
}

Figure 15. A new comment inserted into a worksheet through the user interface

Figure 16. After calling the ShowOrHideComments method, all comments are shown on the sample sheet

The Worksheet class provides its Comments property, which returns a Comments object. This collection of Comment objects allows you to iterate through all the Comment objects associated with the Worksheet. The Comment class does not provide many members. You are likely to use the Visible property of the Comment class to show or hide a comment, or the Delete method to delete the comment. In addition, you may find the Text method useful: this method allows you to add text to the comment, either appending or overwriting existing text.

Once you have added a comment, you might want to display the comments on a worksheet. The sample project includes a procedure, ShowOrHideComments, which shows or hides all the comments on the active sheet:

Private Sub ShowOrHideComments(ByVal Show As Boolean)
    ' Show or hide all the comments.
    ' Work with the current sheet.
    Dim ws As Excel.Worksheet = Me.InnerObject

    Dim i As Integer
    For i = 1 To ws.Comments.Count
        ws.Comments(i).Visible = Show
    Next
End Sub
private void ShowOrHideComments(bool show)
{
    // Show or hide all the comments, using
    // the current sheet:
    Excel.Worksheet ws = this.InnerObject;
      
    for (int i = 1; i <= ws.Comments.Count; i++)
    {
        ws.Comments[i].Visible = show;
    }
}

Note

The Comments collection, like many of the subsidiary collection classes in Excel, does not provide a default enumerator. In other words, you cannot use a For Each loop to visit all the elements of the collection. For collections like the Comment collection, you must use an indexed loop to iterate through the collection.

Outline

Excel provides support for grouping rows of data using its outlining feature. You can take advantage of the same functionality from within your code, as well. For example, given the set of rows shown in Figure 17, you can add outlining so you can collapse the rows as shown in Figure 18 and collapse the groups as shown in Figure 19. Outlining is already done in the figures.

Figure 17. Create these groups

Figure 18. Collapsed groups

Figure 19. Completely collapsed groups

The Worksheet class provides an Outline property, which is itself an Outline object. The Outline class does not provide many members, and the following list describes the ones you are likely to use:

  • AutomaticStyles (Boolean) indicates to Excel whether to apply automatic styles to the outlines.

  • SummaryColumn (XlSummaryColumn) gets or sets the location of the summary columns. The XlSummaryColumn enumeration has two possible values: xlSummaryOnLeft and xlSummaryOnRight.

  • SummaryRow (XlSummaryRow) gets or sets the location of the summary rows. The XlSummaryRow enumeration has two possible values: xlSummaryAbove and xlSummaryBelow.

  • ShowLevels allows you to collapse or expand the outline groups to the row level and/or column level you want. You can pass this method two parameters, like the following:

    Dim ws As Excel.Worksheet = _
      CType(Application.ActiveSheet, Excel.Worksheet)
    ' Specify RowLevels and/or ColumnLevels parameters:
    ws.Outline.ShowLevels(RowLevels:=3)
    
    Excel.Worksheet ws = 
        (Excel.Worksheet) Application.ActiveSheet;
    
    // Specify RowLevels and/or ColumnLevels parameters:
    ws.Outline.ShowLevels(3, missing);
    

The sample worksheet contains named ranges corresponding to the data for the years 2001 (Data2001) and 2002 (Data2002), and for the entire set of rows (AllData). These named ranges cover the entire width of the worksheet; in order for grouping to work, you must use ranges that consist of full rows. The data for 2003 does not have a named range associated with it so that the example code can demonstrate how you can work with entire rows as ranges.

Creating groups is simple: you call the Group method of a range that corresponds to one or more full rows to create the group. (You can specify four optional grouping parameters, including the start and end values to be grouped, the group by value, and an optional array of Boolean values indicating grouping periods. The example uses none of these, because you rarely need any of these parameters.) Call the Ungroup method to remove the group. For example, clicking the Work with Groups link on the sample sheet runs the following code:

Friend Sub DoWorkWithGroups()
    ' Set worksheet-level features for the outline.
    ' In this case, summary rows are below
    ' the data rows (so Excel knows where to put
    ' the summary rows), and we do not want Excel
    ' to format the summary rows--that is already done.
    Me.Outline.SummaryRow = Excel.XlSummaryRow.xlSummaryBelow
    Me.Outline.AutomaticStyles = False

    ' Group the two named ranges. Each of these
    ' ranges extends across entire rows.
    Me.Data2001.Group()
    Me.Data2002.Group()
    Me.AllData.Group()

    ' The range of rows from 24 to 27 does not have 
    ' a named range, so you can work with that 
    ' range directly.
    Dim rng As Excel.Range = _
     CType(Me.Rows("24:27"), Excel.Range)
    rng.Group()

    ' Collapse to the second group level.
    Me.Outline.ShowLevels(RowLevels:=2)
End Sub
internal void DoWorkWithGroups()
{
    // Set worksheet-level features for the outline.
    // In this case, summary rows are below
    // the data rows (so Excel knows where to put
    // the summary rows), and we do not want Excel
    // to format the summary rows--that is already done.
    this.Outline.SummaryRow = Excel.XlSummaryRow.xlSummaryBelow;
    this.Outline.AutomaticStyles = false;

    // Group the two named ranges. Each of these
    // ranges extends across entire rows.
    this.Data2001.
      Group(missing, missing, missing, missing);
    this.Data2002.
      Group(missing, missing, missing, missing);
    this.AllData.
      Group(missing, missing, missing, missing);

    // The range of rows from 24 to 27 does not have 
    // a named range, so you can work with that 
    // range directly.
    Excel.Range rng = (Excel.Range)this.Rows["24:27", missing];
    rng.Group(missing, missing, missing, missing);

    // Collapse to the second group level.
    this.Outline.ShowLevels(2, missing);
}

To group the three named ranges, the code simply calls the Group method of the range:

[Visual Basic]
Me.Data2001.Group()

[C#]
this.Data2001.Group(missing, missing, missing, missing);

To group the unnamed range, the code uses the Rows property of the worksheet, given a range of rows. This property returns a range corresponding to the requested rows:

Dim rng As Excel.Range = _
  CType(ws.Rows("24:27"), Excel.Range)
rng.Group()
Excel.Range rng = (Excel.Range)ws.Rows["24:27", missing];
rng.Group(missing, missing, missing, missing);

Clicking the Clear Groups link on the sample worksheet runs similar code to clear the groups:

Friend Sub DoClearGroups()
    ' Specify RowLevels and/or ColumnLevels parameters:
    Me.Outline.ShowLevels(RowLevels:=3)

    Dim rng As Excel.Range = _
     CType(Me.Rows("24:27"), Excel.Range)
    rng.Ungroup()

    Me.Data2001.Ungroup()
    Me.Data2002.Ungroup()
    Me.AllData.Ungroup()
End Sub
internal void DoClearGroups()
{
    // Specify RowLevels and/or ColumnLevels parameters:
    this.Outline.ShowLevels(3, missing);

    Excel.Range rng = (Excel.Range)this.Rows["24:27", missing];
    rng.Ungroup();

    this.Data2001.Ungroup();
    this.Data2002.Ungroup();
    this.AllData.Ungroup();
}

Using these techniques, you can create and remove groups, and control the grouping level displayed on your worksheets.

The Range Object

The Range object is the object you use most within your Excel applications; before you can manipulate any region in Excel, you must express it as a Range object and work with methods and properties of that Range. The Range class is so important; just about every example you have seen so far in this article used a Range object in some way or another. Basically, a Range object represents a cell, a row, a column, a selection of cells containing one or more blocks of cells (which may or may not be contiguous), or even a group of cells on multiple sheets.

Because it is impossible to discuss all the members of the huge Range class, this section focuses on three major issues:

  • Referring to ranges in code

  • Manipulating ranges in code

  • Using the Range object to accomplish specific goals

In other words, because the Range object encompasses so many different uses in so many different situations, this section focuses on answering "How do I...?" questions, rather than providing a comprehensive list of members.

In Visual Studio 2005 Tools for Office, working with named ranges cannot be easier: each time you create a named range at design time, Visual Studio 2005 Tools for Office creates a corresponding host control. This allows you to refer to named ranges as first-class objects, with properties, methods, and events. The samples in the following sections take advantage of this feature whenever possible.

Managing the Selection

Although it is tempting to work with the current selection as a means to modifying the properties and behavior of a range, do your best to avoid it. Just like any other shared resource, the selection within Excel represents the user's selection. If you modify it in code, you have caused the user to lose control over the current selection. The rule of thumb is to call an object's Select method only if your intent is to change the user's selection. Never call the Select method simply because it is convenient for you, as a developer. There are always alternatives, if your only goal is to set a property of a range. Avoiding the Select method not only makes your code run faster, in general, but also makes your users happier.

It is all too easy to write code like the following, to clear the region contiguous to the user's current cell:

Application.ActiveCell.CurrentRegion.Select
CType(Application.Selection, Excel.Range).ClearContents
Application.ActiveCell.CurrentRegion.Select();
((Excel.Range)Application.Selection).ClearContents();

Doing this loses the user's selection. If only a single cell is originally selected, after running the previous code fragment the entire block of contiguous cells is selected. Unless your goal is, in fact, to select the whole range of cells, a better solution is to use code like the following:

Application.ActiveCell.CurrentRegion.ClearContents
Application.ActiveCell.CurrentRegion.ClearContents();

Why does anyone even think of using the first fragment? This kind of code happens because beginning Excel developers have a tendency to use the Excel macro recorder while attempting to discover how to use the various objects and their methods within Excel. This is a great idea, except that the macro recorder writes really terrible code. In general, the macro recorder uses the selection and modifies the selection when recording any task.

Tip

When working with a cell or group of cells, use a range that describes the cells you want to work with, rather than modifying the selection, if at all possible. If your intent is to modify the user's selection, then use the Range.Select method.

Referring to a Range in Code

The Range class is so wildly flexible that you may have too many options when working with ranges programmatically. Sometimes a Range object is a single object and other times it represents a collection of objects. It has Item and Count members, even though a Range object often refers to a single object, making it sometimes tricky to figure out exactly how to use a Range object.

Tip

Several of the following examples retrieve the Address property of a range. This property returns a string containing a representation of the range's coordinates in one of several formats including "$A$1" (the cell at location A1), "$1" (the first row in the worksheet), and "$A$1:$C$5" (the range consisting of all cells within the rectangle bounded by A1 and C5). The "$" indicates an absolute, as opposed to relative, coordinate. Using the Address property is the simplest way to find out the exact location of a range you have retrieved. For more information on the various ways to refer to a range, see Excel VBA Help.

In its simplest sense, you can write code as described in the following list to cause a Range object to refer to a single cell or group of cells. Each example assumes the following setup code:

Dim ws As Excel.Worksheet = _
  CType(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range
Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook.Worksheets[1];
Excel.Range rng, rng1, rng2;

You can use any of the following techniques to refer to a specific range (there are several other ways to obtain a reference to a Range object, as well):

  • Refer to the ActiveCell property of the Application object:

    rng = Application.ActiveCell
    
    rng = Application.ActiveCell;
    
  • Use the Range property of an object, specifying a range. Because C# does not support parameterized non-indexed properties, you must call the get_Range method, which requires two parameters, instead:

    rng = ws.Range("A1")
    rng = ws.Range("A1:B12")
    
    rng = ws.get_Range("A1", missing);
    rng = ws.get_Range("A1:B12", missing);
    
  • Use the Cells property of a worksheet, specifying a single row and column value:

    ' The Cells collection returns an Object--
    ' Convert it to a Range object explicitly:
    rng = CType(ws.Cells(1, 1), Excel.Range)
    
    rng = (Excel.Range)ws.Cells[1, 1];
    
  • Specify the "corners" of a range; you can also refer directly to the Cells, Rows, or Columns property of the range; in each case, the property returns a range:

    rng = ws.Range("A1", "C5")
    rng = ws.Range("A1", "C5").Cells
    rng = ws.Range("A1", "C5").Rows
    rng = ws.Range("A1", "C5").Columns
    
    rng = ws.get_Range("A1", "C5");
    rng = ws.get_Range("A1", "C5").Cells;
    rng = ws.get_Range("A1", "C5").Rows;
    rng = ws.get_Range("A1", "C5").Columns;
    
  • Refer to a named range. You have seen this technique used throughout this article. Note that because the C# get_Range method requires two parameters and the range name only requires one of the parameters, you must specify missing for the second parameter:

    rng = Application.Range("SomeRangeName")
    
    rng = Application.Range("SomeRangeName", missing);
    
  • Refer to a named range created at design time that has an associated host control. For example, if you create a range named MyData at design time, you can refer to it like this, in your code (this is the syntax you have seen used throughout this document):

    rng = ws.MyData
    
    rng = ws.MyData;
    
  • Refer to a particular row or column or range of rows and columns; note that the Rows and Columns properties each return an Object, requiring conversion if you have Option Strict set to On:

    rng = CType(ws.Rows(1), Excel.Range)
    rng = CType(ws.Rows("1:3"), Excel.Range)
    rng = CType(ws.Columns(3), Excel.Range)
    
    rng = (Excel.Range)ws.Rows[1, missing];
    rng = (Excel.Range)ws.Rows["1:3", missing];
    rng = (Excel.Range)ws.Columns[3, missing];
    

    Note

    The IntelliSense for the Columns property is misleading—it indicates that you must specify row then column values. In reality, the values are reversed for the Columns property. For both the Rows and Columns properties, the second parameter is not used.

  • Use the Application object's Selection property to return a range corresponding to the selected cell(s); given the situation shown in Figure 20, the following fragment returns the string "$C$3" (using the "$" to represent absolute coordinates):

    Debug.WriteLine( _
      CType(Application.Selection, Excel.Range).Address)
    
    System.Diagnostics.Debug.WriteLine(
        ((Excel.Range)Application.Selection).
        get_Address(missing, missing, 
        Excel.XlReferenceStyle.xlA1, missing, missing));
    

    Tip

    The Address property is another of those parameterized properties that C# cannot handle directly. Call the get_Address method to retrieve the address corresponding to a Range object. All the parameters of the Address property are optional, but the get_Address method expects to retrieve five parameters—you are only likely to care about the third parameter, which allows you to specify the address formatting.

  • Create a range that contains the union of two other ranges (specify two ranges within the quotes, separated with a comma):

    rng = Application.Range("A1:D4, F2:G5")
    ' You can also use the Application object's Union
    ' method to retrieve the intersection of two ranges:
    rng1 = Application.Range("A1:D4")
    rng2 = Application.Range("F2:G5")
    rng = Application.Union(rng1, rng2)
    
    rng = Application.get_Range("A1:D4, F2:G5", missing);
    // You can also use the Application object's Union
    // method to retrieve the intersection of two ranges, but this
    // is far more effort in C#:
    rng1 = Application.get_Range("A1", "D4");
    rng2 = Application.get_Range("F2", "G5");
    // Note that the Union method requires you to supply thirty
    // parameters: 
    rng = Application.Union(rng1, rng2, 
        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);
    
  • Create a range which refers to the intersection of two other ranges (specify two ranges within the quotes, with no separator):

    rng = Application.Range("A1:D16 B2:F14")
    ' You can also use the Application object's Intersect
    ' method to retrieve the intersection of two ranges:
    rng1 = Application.Range("A1:D16")
    rng2 = Application.Range("B2:F14")
    rng = Application.Intersect(rng1, rng2)
    
    rng = Application.get_Range("A1:D16 B2:F14", missing);
    // You can also use the Application object's Intersect
    // method to retrieve the intersection of two ranges. Note
    // that the Intersect method requires you to pass 30 parameters:
    rng1 = Application.get_Range("A1", "D16");
    rng2 = Application.get_Range("B2", "F14");
    rng = Application.Intersect(rng1, rng2, 
        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);
    
  • Use the Offset property of a range to retrieve a range relative to the original range; the following example adds content to the area under the cell at row 1, column 1:

    rng = CType(ws.Cells(1, 1), Excel.Range)
    
    Dim i As Integer
    For i = 1 To 5
        rng.Offset(i, 0).Value = i.ToString
    Next
    
    rng = (Excel.Range) ws.Cells[1, 1];
    for (int i = 1; i <= 5; i++)
    {
        rng.get_Offset(i, 0).Value2 = i.ToString();
    }
    

    Tip

    The Range.Offset property is a parameterized property, so C# code cannot retrieve its value directly. C# developers must call the get_Offset method instead.

  • Use the CurrentRegion property of a range to retrieve a range that represents the current region, the region bounded by the nearest empty row and column; for example, in Figure 20, the following expression sets the font of the current region to be bold:

    Application.Range("C3").CurrentRegion.Font.Bold = True
    
    Application.get_Range("C3", missing).
        CurrentRegion.Font.Bold = True;
    

    Figure 20. Requesting the CurrentRegion property of cell C3 returns the range A1:E5

  • Use the Areas property of a range to retrieve a collection of ranges, each corresponding to one region of the range's contents. For example, the following fragment displays the addresses for both areas within the named range Test shown in Figure 21, "$B$1:$E$5" and "$C$7:$G$11" (using the "$" to represent absolute coordinates):

    rng = Application.Range("Test")
    Dim i As Integer
    For i = 1 To rng.Areas.Count
      Debug.WriteLine(rng.Areas(i).Address)
    Next
    
    rng = Application.get_Range("Test", missing);
    for (int i = 1; i <= rng.Areas.Count; i++)
    { 
        System.Diagnostics.Debug.WriteLine(
            rng.Areas[i].get_Address(missing, missing, 
            Excel.XlReferenceStyle.xlA1, missing, missing));
    }
    

    Figure 21. Because ranges can contain areas that are not contiguous, use the Areas property to retrieve each individually

  • Use the End property, along with a value from the XlDirection enumeration (xlUp, xlToRight, xlToLeft, xlDown), to retrieve a range that represents the cell at the end of the region, as if you pressed the key described by the enumerated value. Using the selected cell shown in Figure 22, the following fragment ends up with four ranges defined as shown in the code's comments:

    Dim rngLeft, rngRight, rngUp, rngDown as Excel.Range
    rng = CType(Application.Selection, Excel.Range)
    ' E3
    rngRight = rng.End(Excel.XlDirection.xlToRight)
    ' A3
    rngLeft = rng.End(Excel.XlDirection.xlToLeft)
    ' C1
    rngUp = rng.End(Excel.XlDirection.xlUp)
    ' C5
    rngDown = rng.End(Excel.XlDirection.xlDown)
    
    Excel.Range rngLeft, rngRight, rngUp, rngDown;
    rng = (Excel.Range) Application.Selection;
    // Note that the Range.End property is parameterized, so 
    // C# developers cannot retrieve it. You must call the 
    // get_End method, instead:
    // E3
    rngRight = rng.get_End(Excel.XlDirection.xlToRight);
    // A3
    rngLeft = rng.get_End(Excel.XlDirection.xlToLeft);
    // C1
    rngUp = rng.get_End(Excel.XlDirection.xlUp);
    // C5
    rngDown = rng.get_Down(Excel.XlDirection.xlDown);
    

    Figure 22. Use the End property to return ranges corresponding to a range

  • Use the EntireRow or EntireColumn properties to refer to the rows or columns containing the specified range. For example, the following fragment sets the font to bold for rows 7 through 11, using the example shown in Figure 21:

    [Visual Basic]
    rng = Application.Range("Test")
    rng.Areas(2).EntireRow.Font.Bold = True
    
    [C#]
    rng = Application.get_Range("Test", missing);
    rng.Areas[2].EntireRow.Font.Bold = true;
    

Using the Technology

Developers commonly request the capability of changing the font of the entire row containing the selected cell so that the text is bold. This feature is not built into Excel, but is not terribly difficult to add. The Range Class sheet in the sample workbook contains a range that is handled specially: as you select an item, its row becomes bold. Figure 23 shows this behavior in action.

Figure 23. Select an item to make its entire row bold

The sample workbook includes the following procedure to take care of the formatting:

Private Sub BoldCurrentRow(ByVal ws As Excel.Worksheet)
    ' Keep track of the previously bolded row.
    Static intRow As Integer

    ' Work with the current active cell.
    Dim rngCell As Excel.Range = _
      Application.ActiveCell

    ' Bold the current row.
    rngCell.EntireRow.Font.Bold = True

    ' Make sure intRow is not 0 (meaning that 
    ' this is your first pass through here).
    If intRow <> 0 Then
        ' If you are on a different
        ' row than the last time through here,
        ' make the old row not bold.
        If rngCell.Row <> intRow Then
            Dim rng As Excel.Range = _
             CType(ws.Rows(intRow), Excel.Range)
            rng.Font.Bold = False
        End If
    End If
    ' Store away the new row number 
    ' for next time.
    intRow = rngCell.Row
End Sub
private int LastBoldedRow = 0;
private void BoldCurrentRow(Excel.Worksheet ws)
{
    // Keep track of the previously bolded row.

    // Work with the current active cell.
    Excel.Range rngCell = Application.ActiveCell;

    // Bold the current row.
    rngCell.EntireRow.Font.Bold = true;

    // Make sure intRow is not 0 (meaning that 
    // this is your first pass through here).
    if (LastBoldedRow != 0)
    {
        // If you are on a different
        // row than the last time through here,
        // make the old row not bold.
        if (rngCell.Row != LastBoldedRow)
        {
            Excel.Range rng =
              (Excel.Range)ws.Rows[LastBoldedRow, missing];
            rng.Font.Bold = false;
        }
    }
    // Store away the new row number 
    // for next time.
    LastBoldedRow = rngCell.Row;
}

This example takes the following actions in order to make the current row bold, and to make the previously bolded row normal:

  • Declares a variable (static, in Visual Basic) to keep track of the previously selected row:

    Static intRow As Integer
    
    private int LastBoldedRow = 0;
    
  • Retrieves a reference to the current cell using the Application.ActiveCell property:

    private int LastBoldedRow = 0;
    Dim rngCell As Excel.Range = Application.ActiveCell
    
    Excel.Range rngCell = Application.ActiveCell;
    
  • Bolds the current row using the EntireRow property of the active cell:

    rngCell.EntireRow.Font.Bold = True
    
    rngCell.EntireRow.Font.Bold = true;
    
  • Ensures that the current value of intRow is not 0, which indicates that this is the first time through this code:

    If intRow <> 0 Then
        ' Code removed here...
    End If
    
    if (LastBoldedRow != 0)
    {
        // Code removed here...
    }
    
  • Ensures that the current row is different from the previous row. The code only needs to modify the state of the row if it is different than the previous row. The Row property returns an integer indicating the row corresponding to the range:

    If rngCell.Row <> intRow Then
        ' Code removed here...
    End If
    
    if (rngCell.Row != LastBoldedRow)
    {
        // Code removed here...
    }
    
  • Retrieves a reference to a range representing the row selected previously and sets that row to not be bold:

    Dim rng As Excel.Range = _
      CType(ws.Rows(intRow), Excel.Range)
    rng.Font.Bold = False
    
    Excel.Range rng = 
        (Excel.Range)ws.Rows[LastBoldedRow, missing];
    rng.Font.Bold = false;
    

The sample worksheet calls the BoldCurrentRow procedure from its SelectionChange event handler. (The fact that you can react to sheet-based events is new in Visual Studio 2005 Tools for Office. In previous versions, you are restricted to events raised by Excel. In this version, because Visual Studio 2005 Tools for Office itself wraps up the built-in objects in its own classes, you can react to more events in more places.) In that procedure, the code verifies that the new selection is within the correct range (using the Intersect method of the Application object), and calls the BoldCurrentRow procedure if it is:

Private Sub Sheet7_SelectionChange( _
 ByVal Target As Microsoft.Office.Interop.Excel.Range) _
 Handles Me.SelectionChange

    If Application.Intersect( _
     Target, Me.BoldSelectedRow.InnerObject) Is Nothing Then
        ' The selection is within the range where you are making
        ' the selected row bold.
        BoldCurrentRow(Me.InnerObject)
    End If
End Sub
private void Sheet7_SelectionChange(
    Microsoft.Office.Interop.Excel.Range Target)
{
    if (Application.Intersect(
      Target, this.BoldSelectedRow.InnerObject,
      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) != null)
    {
        // The selection is within the range where you are making
        //the selected row bold.
        BoldCurrentRow(this.InnerObject);
    }
}

Working with Ranges

Once you have got a reference to a range, what can you do with it? The list is endless, as you can imagine. This section focuses on a few techniques for working with Range objects and provides simple examples of each technique. All the samples for this section can be found on the Range Class sheet of the sample workbook.

Automatically Filling Ranges

The AutoFill method of the Range class allows you to fill a range with values automatically. Most often the AutoFill method is used to store incrementally increasing or decreasing values into a range. You can specify the behavior by supplying an optional constant from the XlAutoFillType enumeration (xlFillDays, xlFillFormats, xlFillSeries, xlFillWeekdays, xlGrowthTrend, xlFillCopy, xlFillDefault, xlFillMonths, xlFillValues, xlFillYears, or xlLinearTrend). If you do not specify a fill type, Excel assumes you want the default fill type (xlFillDefault) and fills the specified range as it sees fit.

The sample worksheet, shown in Figure 24, includes four regions to be auto-filled. Column B is to include five weekdays; Column C is to include five months; Column D is to include dates incrementing by years, over five years; and Column E is to include a series of numbers, incrementing by two for each row. Figure 25 shows the same region after running the sample code.

Figure 24. Before calling the AutoFill method for four sample ranges

Figure 25. After auto-filling the ranges

Clicking the AutoFill link runs the following procedure:

Private Sub AutoFill()
    Dim rng As Excel.Range = Application.Range("B1")
    rng.AutoFill(Application.Range("B1:B5"), _
      Excel.XlAutoFillType.xlFillDays)

    rng = Application.Range("C1")
    rng.AutoFill(Application.Range("C1:C5"), _
      Excel.XlAutoFillType.xlFillMonths)

    rng = Application.Range("D1")
    rng.AutoFill(Application.Range("D1:D5"), _
      Excel.XlAutoFillType.xlFillYears)

    rng = Application.Range("E1:E2")
    rng.AutoFill(Application.Range("E1:E5"), _
      Excel.XlAutoFillType.xlFillSeries)
End Sub
private void AutoFill()
{
    Excel.Range rng = Application.get_Range("B1", missing);
    rng.AutoFill(Application.get_Range("B1:B5", missing), 
        Excel.XlAutoFillType.xlFillDays);

    rng = Application.get_Range("C1", missing);
    rng.AutoFill(Application.get_Range("C1:C5", missing), 
        Excel.XlAutoFillType.xlFillMonths);

    rng = Application.get_Range("D1", missing);
    rng.AutoFill(Application.get_Range("D1:D5", missing), 
        Excel.XlAutoFillType.xlFillYears);

    rng = Application.get_Range("E1:E2", missing);
    rng.AutoFill(Application.get_Range("E1:E5", missing), 
        Excel.XlAutoFillType.xlFillSeries);
}

In each case, you must specify two ranges:

  • The range calling the AutoFill method, which specifies the starting point of the fill.

  • The range to be filled, passed as a parameter to the AutoFill method; this destination range must include the source range.

The second parameter to the AutoFill method, the XlAutoFillType enumerated value, is optional. Generally, you must supply this value in order to get the behavior you need. For example, try changing this code:

rng.AutoFill(Application.Range("D1:D5"), _
  Excel.XlAutoFillType.xlFillYears)
rng.AutoFill(Application.get_Range("D1:D5", missing), 
    Excel.XlAutoFillType.xlFillYears);

so that it looks like this:

rng.AutoFill(Application.Range("D1:D5"))
rng.AutoFill(Application.get_Range("D1:D5", missing), 
    Excel.XlAutoFillType.xlFillDefault);

Instead of ending up with dates that increment by years, the dates increment by days.

Searching within Ranges

The Find method of the Range class allows you to search for text within the range. This flexible method mimics the behavior of the Find and Replace dialog box in Excel shown in Figure 26—as a matter of fact, it interacts directly with this dialog box. That is, the Range.Find method uses either parameters you pass it to determine the search behavior, or, if you do not pass parameters, it uses the values it finds in the Find and Replace dialog box. Table 4 lists the parameters for the Range.Find method, all but the first of which are optional.

Figure 26. Selections in the Find and Replace dialog box affect the behavior of the Find method

Note

Because almost all the parameters to Range.Find are optional, and because the user might change values from the Find and Replace dialog box, it is up to you to make sure you actually do pass all values to the Find method, unless your goal is to take the user's selections into account. Of course, C# developers need not worry about this problem, because they must supply all the parameters on each method call.

Table 4. Parameters of the Range.Find Method

Parameter

Type

Description

What (required)

Object

The data to find; can be a string or any Excel data type.

After

Range

The range after which you want the search to start (this cell is not included in the search); if you do not specify this cell, the search begins in the upper-left corner of the range.

LookIn

XlFindLookin (xlValue, xlComments, xlFormulas)

The type of information to be searched; cannot be combined using the Or operator.

LookAt

XlLookAt (xlWhole, xlPart)

Determines whether the search matches entire cells, or partial cells.

SearchOrder

XlSearchOrder (xlByRows, xlByColumns)

Determines the order for the search; xlByRows (the default) causes the search to go across and then down, and xlByColumns causes the search to go down and then across.

SearchDirection

XlSearchDirection (xlNext, xlPrevious)

Determines the direction of the search; the default is xlNext.

MatchCase

Boolean

Determines whether the search is case-sensitive.

MatchByte

Boolean

Determines whether double-byte characters match only double-byte characters (True) or equivalent single-byte characters (False); only applies if you have installed double-byte support.

The following example from the sample workbook searches a range named Fruits and modifies the font for cells containing the word "apples" (Figure 27 shows the results of the search). This procedure also uses the FindNext method, which uses the previously set search settings to repeat the search. (The Range.FindPrevious method works much the same as the Range.FindNext method, but is not used in this example.) You specify the cell after which to search, and the FindNext method handles the rest.

Figure 27. Cells containing the word apples are shown in the results of a search

Tip

Search for the FindNext and FindPrevious methods wraps back to the beginning of the search range once it reaches the end of the range. It is up to your code to make sure that you do not wrap around forever, in an infinite loop. The sample procedure shows one way to handle this. If you want to avoid this endless wrapping altogether, or if you want to perform a search that is too complex for the Find, FindNext, and FindPrevious methods, you can also loop through all the cells in a range, using a For Each loop.

Clicking the Find link on the Range Class sheet of the sample workbook runs the following procedure:

Friend Sub DemoFind()
    ' Expects a range named Fruits:
    Dim rng As Excel.Range = Me.Fruits.InnerObject
    Dim rngFound As Excel.Range

    ' Keep track of the first range you find.
    Dim rngFoundFirst As Excel.Range = Nothing

    ' Specify all these parameters
    ' every time you call this method, since they
    ' can be overriden in the user interface.
    rngFound = rng.Find( _
     "apples", , _
      Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
      Excel.XlSearchOrder.xlByRows, _
      Excel.XlSearchDirection.xlNext, False)
    While rngFound IsNot Nothing
        If rngFoundFirst Is Nothing Then
            rngFoundFirst = rngFound
        ElseIf rngFound.Address = rngFoundFirst.Address Then
            Exit While
        End If
        With rngFound.Font
            .Color = ColorTranslator.ToOle(Color.Red)
            .Bold = True
        End With
        rngFound = rng.FindNext(rngFound)
    End While
End Sub
internal void DemoFind()
{
    Excel.Range rng = this.Fruits.InnerObject;
    Excel.Range rngFound;

    // Keep track of the first range you find.
    Excel.Range rngFoundFirst = null;

    // Specify all these parameters
    // every time you call this method, since they
    // can be overriden in the user interface.
    rngFound = rng.Find("apples", missing,
      Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
      Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext,
      false, missing, missing);
    while (rngFound != null)
    {
        if (rngFoundFirst == null)
        {
            rngFoundFirst = rngFound;
        }
        else if (GetAddress(rngFound) == GetAddress(rngFoundFirst))
        {
            break;
        }
        rngFound.Font.Color = ColorTranslator.ToOle(Color.Red);
        rngFound.Font.Bold = true;
        rngFound = rng.FindNext(rngFound);
    }
}

The code takes these actions to achieve its goals:

  • Declares Excel.Range variables for tracking the entire range, the first found range, and the current found range:

    Dim rng As Excel.Range = Me.Fruits.InnerObject
    Dim rngFound As Excel.Range
    Dim rngFoundFirst As Excel.Range
    
    Excel.Range rng = this.Fruits.InnerObject;
    Excel.Range rngFound;
    Excel.Range rngFoundFirst = null;
    
  • Searches for the first match, specifying all the parameters except the cell to search after—by default, the search starts after the cell in the upper-left corner of the range—and searches for "apples" in the cell values, matching partial values, searching by rows in a forward direction, not case sensitive:

    rngFound = rng.Find( _
      "apples", , _
      Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
      Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, _
      False)
    
    rngFound = rng.Find("apples", missing, 
        Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, 
        Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, 
        false, missing, missing);
    
  • Continues searching as long as it continues to find matches:

    While rngFound IsNot Nothing
        ' Code removed here...
    End While
    
    while (rngFound != null)
    {
        // Code removed here...
    }
    
  • Compares the first found range (rngFoundFirst) to Nothing, which it can only be if the code just found the first match. In that case, the code stores away the found range; otherwise, if the address of the found range matches the address of the first found range, the code exits the loop.

    If rngFoundFirst Is Nothing Then
        rngFoundFirst = rngFound
    ElseIf rngFound.Address = rngFoundFirst.Address Then
        Exit While
    End If
    
    if (rngFoundFirst == null ) 
    {
        rngFoundFirst = rngFound;
    }
    else if (GetAddress(rngFound) == GetAddress(rngFoundFirst))
    {
        break;
    }
    
  • Sets the appearance of the found range:

    With rngFound.Font
        .Color = ColorTranslator.ToOle(Color.Red)
        .Bold = True
    End With
    
    rngFound.Font.Color = ColorTranslator.ToOle(Color.Red);
    rngFound.Font.Bold = true;
    
  • Performs another search:

    rngFound = rng.FindNext(rngFound)
    
    rngFound = rng.FindNext(rngFound);
    

Clicking the Reset Find link on the sample sheet runs this simple procedure, putting the range back as it started:

' Expects a range named Fruits:
Friend Sub DoResetFind()
    Dim rng As Excel.Range = Me.Fruits.InnerObject
    With rng.Font
        .Color = ColorTranslator.ToOle(Color.Black)
        .Bold = False
    End With
End Sub
private void ResetFind()
{
    Excel.Range rng = this.Fruits.InnerObject;
    rng.Font.Color = ColorTranslator.ToOle(Color.Black);
    rng.Font.Bold = false;
}

Tip

If you want to both search and replace within a range, check out the Range.Replace method. This method works much like the Find method, but also allows you to specify a value to replace. The Replace method returns a Boolean value indicating whether it performed any replacements. If it replaces even one value, it returns True.

Sorting Data within a Range

Just as you can sort data within a range from the Excel user interface, you can use the Range.Sort method to sort data programmatically. You indicate the range to be sorted and optionally, up to three rows or columns to sort by and several other optional parameters, and Excel handles the rest. Table 5 shows all the parameters for the Sort method. (Visual Basic developers most often use only a few of these parameters, and C# developers must supply a value for each.)

Table 5. Parameters for the Sort Method

Parameter

Type

Description

Key1

Object (String or Range)

First sort field, either as a range name (String) or Range object; determines the values to be sorted.

Order1

XlSortOrder (xlAscending, xlDescending)

Determines the sort order for the values specified in Key1.

Key2

Object (String or Range)

Second sort field; cannot be used when sorting a pivot table.

Type

Object

Specifies which elements to sort when sorting a pivot table; has no effect on a normal range.

Order2

XlSortOrder

Determines the sort order for the values specified in Key2.

Key3

Object (String or Range)

Third sort field; cannot be used when sorting a pivot table.

Order3

XlSortOrder

Determines the sort order for the values specified in Key3.

Header

XlYesNoGuess (xlGuess, xlNo, xlYes)

Specifies whether the first row contains header information, xlNo is the default value; specify xlGuess if you want Excel to guess.

OrderCustom

Integer

Specifies a one-based index into the list of custom sort orders; leaving this parameter out uses the default sort order. Figure 28 shows the technique for creating a custom sort order. For this example, specifying 6 for this parameter sorts based on the "fruits" custom order.

MatchCase

Boolean

Set to True to perform a case-sensitive sort, False to perform non-case sensitive sort; cannot be used with pivot tables.

Orientation

XlSortOrientation (xlSortRows, xlSortColumns)

Orientation for the sort.

SortMethod

XlSortMethod (xlStroke, xlPinYin)

Specifies the sort method; does not apply to all languages (current values only apply to Chinese sorting and has no bearing on other languages).

DataOption1

XlSortDataOption (xlSortTextAsNumbers, xlSortNormal)

Specifies how to sort text in the range specified in Key1; does not apply to pivot table sorting.

DataOption2

XlSortDataOption

Specifies how to sort text in the range specified in Key2; does not apply to pivot table sorting.

DataOption3

XlSortDataOption

Specifies how to sort text in the range specified in Key3; does not apply to pivot table sorting.

Tip

Visual Basic developers have a distinct advantage over C# developers when calling methods like this one. Because you are unlikely to use all the parameters, Visual Basic developers can use named parameters, specifying only the parameters they need. C# developers must pass values for all the unused parameters in order to accept default behaviors.

Figure 28. Create custom sorting lists, and refer to these specific sort orders from your code

Clicking the Sort link on the Range Class sheet runs the following procedure to sort the Fruits range by the data in the first column, and then by the data in the second column:

Friend Sub DemoSort()
    ' Expects a range named Fruits:
    Dim rng As Excel.Range = Me.Fruits.InnerObject
    rng.Sort( _
     Key1:=rng.Columns(1), Order1:=Excel.XlSortOrder.xlAscending, _
     Key2:=rng.Columns(2), Order2:=Excel.XlSortOrder.xlAscending, _
     Orientation:=Excel.XlSortOrientation.xlSortColumns, _
     Header:=Excel.XlYesNoGuess.xlNo)
End Sub
internal void DemoSort()
{
    // Expects range named Fruits.
    Excel.Range rng = this.Fruits.InnerObject;

    rng.Sort(rng.Columns[1, missing],
      Excel.XlSortOrder.xlAscending,
      rng.Columns[2, 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);
}

Clicking the Reset Sort link on the same sheet runs the following procedure, which sorts the second column based on the custom sort shown in Figure 28:

Friend Sub DoResetSort()
    ' Expects a range named Fruits:
    Dim rng As Excel.Range = Me.Fruits.InnerObject

    rng.Sort(rng.Columns(2), OrderCustom:=6, _
    Orientation:=Excel.XlSortOrientation.xlSortColumns, _
    Header:=Excel.XlYesNoGuess.xlNo)
End Sub
internal void DoResetSort()
{
    // Expects range named Fruits.
    Excel.Range rng = this.Fruits.InnerObject;

    rng.Sort(rng.Columns[2, missing],
      Excel.XlSortOrder.xlAscending,
      missing, missing, Excel.XlSortOrder.xlAscending,
      missing, Excel.XlSortOrder.xlAscending,
      Excel.XlYesNoGuess.xlNo, 6, missing,
      Excel.XlSortOrientation.xlSortColumns,
      Excel.XlSortMethod.xlPinYin,
      Excel.XlSortDataOption.xlSortNormal,
      Excel.XlSortDataOption.xlSortNormal,
      Excel.XlSortDataOption.xlSortNormal);
}

Conclusion

Although this document seems long, it barely scratches the surface of the richness provided by the Excel object model. This document introduces the most important classes—Application, Workbook, Worksheet, and Range—but does not describe other classes that may be useful to you. You may need to also investigate the second "tier" of classes provided by the Excel object model, for example, PivotTable and Chart. The completeness of the Excel object model makes it possible for you to accomplish just about any automated task you require, as long as you do the research to find the exact class you need. Armed with the contents of this document, the Object Browser, and the Excel VBA Help, you can tackle just about any task you can imagine in Excel.

Additional Resources

Visual Studio 2005 Tools for Office

Office Developer Center

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, Access Developer's Handbook series, and VBA Developer's Handbook series. Ken co-authored C, ASP.NET, Visual Basic .NET, and ADO.NET courseware for AppDev. Ken is a technical editor for Advisor Publications' VB.NET Technical Journal, and he's a columnist for both MSDN Magazine and CoDe magazine. Ken speaks regularly at a large number of industry events, including Advisor Media's Advisor Live events, FTP's VSLive, DevConnection's VS and ASP Connections, and the annual Tech-Ed conference hosted by Microsoft.