Understanding the Excel Object Model from a .NET Developer's Perspective
Ken Getz
MCW Technologies, LLC
April 2003
Applies to:
Microsoft® Visual Studio® Tools for the Microsoft Office System
Microsoft Office Excel 2003
Microsoft Visual Studio® .NET 2003
Summary: Discusses some of the objects provided by Microsoft Office Excel 2003 and explains how you can use them to create managed code solutions with Microsoft Visual Studio Tools for the Microsoft Office System. The focus is mainly on the Application, Workbook, Worksheet, and Range objects. Visual Basic .NET and Visual C# code examples demonstrate some of the properties, methods, and events of each object. (98 printed pages)
Download the ExcelObj.exe from the Microsoft Download Center.
Contents
Introduction
The Application Object
Members That Control the State and Display in Excel
Members That Return Objects
Members That Execute Actions
Members That Handle File Manipulation
Other Useful Members
Application Events
The Workbook Class
Properties of the Workbook Class
Working with Document Properties
Working with Styles
Working with Sheets
Methods of the Workbook Class
The Worksheet Class
There's No Sheet Class
Working with Protection
Object Properties
The Range Object
Managing the Selection
Referring to a Range in Code
Using the Technology
Working with Ranges
Sorting Data within a Range
What's Next?
Introduction
Developers intending to take advantage of Microsoft® Visual Studio Tools for the Microsoft Office System and those wishing to simply use COM Automation to control Microsoft Office Excel 2003 applications will need to 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's impossible to quantify concretely, a large percentage of the work you'll do with Excel centers around these four classes and their members. In this document, you'll learn how to take advantage of each of these classes, and will be introduced to some of the properties, methods, and events of each. You'll also find examples you can try, demonstrating some of the features of each object.
**Tip **In general, developers who use Microsoft Visual Basic® .NET have an easier time working with Microsoft Office objects than do developers who use Microsoft Visual C#® for one important reason: Visual Basic for Applications (VBA) methods often include optional parameters, and Visual Basic .NET supports optional parameters. C# developers will find that they must supply a value for each and every optional method parameter, whereas Visual Basic .NET developers can simply used named parameters to supply only the values they need. In addition, C# doesn't support properties with parameters other than indexers, yet many Excel properties accept parameters. You'll find that properties such as the Application.Range property, available in VBA and Visual Basic .NET, 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, you'll find that the Excel object model directly emulates its user interface. It wouldn't be 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's impossible to dig into all of them here: you'll get enough of the flavor of the object models to be able to get started, and to use the Excel online help for more details.
**Tip **Throughout this article, you'll see many uses of the DirectCast and CType methods. The reason for this is that the sample project has its Option Strict setting on—this means that Visual Basic .NET 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 .NET, it's possible that you'll write code that compiles fine, but fails at run time. That's the point of Option Strict—it makes it much less likely that an invalid conversion at run time will cause an exception.) If you're a C# developer reading this document, you'll likely appreciate this decision.
This white paper references the sample project, ExcelObjectModel.sln. This project contains an Excel workbook and associated Visual Basic .NET code. Not every sample shown in this paper appears in the example project, but any that involve more than a line or two of code have been placed into the workbook, with a hyperlink within the project set up to call the code.
**Tip **In an article of this limited size, it's not possible to document each and every object or member. It's 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. You'll find that 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's 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'll never need to investigate, and others of which will be 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. Some 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 won't 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're done. Excel won't 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; doesn't take effect until you restart Excel. |
| StandardFontSize | Long | Gets or sets the size of the default font in Excel; doesn't 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're 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's important, however, that you always set this property when you're done with your work, because Excel won't reset it for you. Therefore, you'll 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:
' Visual Basic
Try
ThisApplication.ScreenUpdating = False
' Do your work that updates the screen.
Finally
ThisApplication.ScreenUpdating = True
End Try
// C#
try
{
ThisApplication.ScreenUpdating = false;
// Do your work that updates the screen.
}
finally
{
ThisApplication.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. Some 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 had 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's important to reset the DisplayAlerts property. Because Excel won't reset this property for you, and with it set to False, Excel won't prompt you to save workbooks before you close them; not carefully resetting the DisplayAlerts property can cause you to lose data if you're 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® .NET contains only the ThisApplication and ThisWorkbook objects, you'll 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.
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's on top). If there's 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's 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's 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'll 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'll 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):
' Visual Basic Dim wb As Excel.Workbook = ThisApplication.Workbooks.Add() // C# Excel.Workbook wb = ThisApplication.Workbooks.Add(Type.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:
' Visual Basic ThisApplication.Workbooks.Close() // C# ThisApplication.Workbooks.Close();Open an existing workbook, using the Open method of the Workbooks collection. In its simplest form, you'll use the Open method like you'll 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 won't need to use the optional parameters:
' Visual Basic Dim wb As Excel.Workbook = _ ThisApplication.Workbooks.Open("C:\YourPath\YourWorkbook.xls") // C# Excel.Workbook wb = ThisApplication.Workbooks.Open( "C:\\YourPath\\Yourworkbook.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);**Tip **If you're a C# developer, get used to seeing references to the Type.Missing value in method calls. Because the Excel object model was written with VBA in mind, many of its methods accept optional parameters—sometimes up to 30 optional parameters. You'll need to use many instances of the Type.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 would take 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:
' Visual Basic Dim wb as Excel.Workbook = _ ThisApplication.Workbooks.OpenText("C:\Test.txt", StartRow:=3, _ DataType:=xlDelimited, Comma:=True) // C# Excel.Workbook wb = ThisApplication.Workbooks.OpenText("C:\\Test.txt", Type.Missing, 3, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote, Type.Missing, Type.Missing, Type.Missing, True, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.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'll need to be careful how you refer to it: you must use the name you see in the title bar, which doesn't include the ".xls" extension until you've saved the file:
' Visual Basic Dim wb As Excel.Workbook = ThisApplication.Workbooks(1) ' Before Book1 is saved: wb = ThisApplication.Workbooks("Book1") ' After Book1 is saved: wb = ThisApplication.Workbooks("Book1.xls") // C# Excel.Workbook wb = ThisApplication.Workbooks[1]; // Before Book1 is saved: wb = ThisApplication.Workbooks["Book1"]; // After Book1 is saved: wb = ThisApplication.Workbooks["Book1.xls"];Tip When you refer to a particular workbook, you're 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:
' Visual Basic ThisApplication.Calculate ' Or... ThisWorkbook.Calculate ' Or... ThisApplication.Range("SomeNamedRange").Calculate // C# ThisApplication.Calculate(); // Or... ThisWorkbook.Calculate(); // Or... ThisApplication.get_Range("A1", "B12").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 .NET and C# don't handle Excel members exactly the same. For example, the Range property in Excel, VBA, and Visual Basic .NET can only be accessed in C# using the get_Range method. You'll find several examples of this, and other, accessor members throughout this document.
CheckSpelling: Returns a Boolean indicating whether the supplied parameter is spelled correctly. You can optionally supply the name of a custom dictionary and a Boolean 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:
' Visual Basic Private Sub TestSpelling() Dim rng As Excel.Range = _ ThisApplication.Range("CheckSpelling") Dim strOut As String If ThisApplication.CheckSpelling( _ rng.Offset(0, 1).Value.ToString) Then strOut = "Spelled correctly" Else strOut = "Spelled incorrectly" End If rng.Offset(0, 2).Value = strOut End Sub // C# private void TestSpelling() { // If you specify only a named range in the call // to get_Range, use Type.Missing for the second parameter. Excel.Range rng = ThisApplication. get_Range("CheckSpelling", Type.Missing); // Note that C# requires you to retrieve and set // the Value2 property of the Range, rather than // the Value property, because the Value property // is parameterized, making it unavailable to C# code: rng.get_Offset(0, 2).Value2 = (ThisApplication.CheckSpelling( rng.get_Offset(0, 1).Value2.ToString(), Type.Missing, Type.Missing) ? "Spelled correctly" : "Spelled incorrectly"); }**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. In this case, the Range object refers to the named range, CheckSpelling. 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.
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:
' Visual Basic Private Sub TestEvaluate() Dim rng As Excel.Range = _ ThisApplication.Range("Evaluate") Try Dim rngNew As Excel.Range = _ ThisApplication.Evaluate( _ DirectCast(rng.Offset(0, 1).Value), Excel.Range) rngNew.Value = "Hello, World!" Catch ex As Exception MessageBox.Show(ex.Message, ThisApplication.Name) End Try End Sub // C# private void TestEvaluate() { Excel.Range rng = ThisApplication. get_Range("Evaluate", Type.Missing); try { Excel.Range rngNew = (Excel.Range) ThisApplication.Evaluate( rng.get_Offset(0, 1).Value2); rngNew.Value2 = "Hello, World!"; } catch (Exception ex) { MessageBox.Show(ex.Message, ThisApplication.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 don't need to log on if there's an active session). The following example sends the sample workbook as an attachment to a simple e-mail message:
' Visual Basic Private Sub TestEmail() If ThisApplication.MailSystem = Excel.XlMailSystem.xlMAPI Then If ThisApplication.MailSession Is Nothing Then Dim frm As New SendMail If frm.ShowDialog = DialogResult.OK Then ThisApplication.MailLogon( _ frm.EmailName, frm.EmailPassword, frm.DownloadNewMail) End If End If Dim strEmail As String = _ ThisApplication.Range("SendMail").Offset(0, 1). _ Value.ToString ThisWorkbook.SendMail(strEmail, "Sample Excel Email") ThisApplication.MailLogoff() Else MessageBox.Show( _ "This demonstration works only if MAPI is installed.") End If End Sub // C# private void TestEmail() { if (ThisApplication.MailSystem == Excel.XlMailSystem.xlMAPI ) { if ( ThisApplication.MailSession == null ) { SendMail frm = new SendMail(); if (frm.ShowDialog() == DialogResult.OK ) { ThisApplication.MailLogon(frm.EmailName, frm.EmailPassword, frm.DownloadNewMail); } } string strEmail = ThisApplication. get_Range("SendMail", Type.Missing). get_Offset(0, 1).Value2.ToString(); ThisWorkbook.SendMail(strEmail, "Sample Excel Email", Type.Missing); ThisApplication.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. You'll note that the SendMail method doesn't 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'll to investigate other means of interacting with e-mail. In addition, if you're not currently online and connected to your e-mail system, the previous sample code will fail. You could work around this failure and not attempt to send the mail if the MailSession property returns Nothing.
Quit: Allows****you to quit Excel programmatically. If you've set the DisplayAlerts property to False, you won't be prompted to save any unsaved data. In addition, if you set the Saved property of a Workbook to True, Excel won't ask you to save it whether or not you've made changes:
' Visual Basic ThisApplication.Quit // C# ThisApplication.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's not terribly functional, but it does allow you to undo the last action the user took before executing your code:
' Visual Basic ThisApplication.Undo // C# ThisApplication.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're likely to use. (The 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:
' Visual Basic
' When the workbook opens:
ThisApplication.Range("DefaultFilePath").Value = _
ThisApplication.DefaultFilePath
' When you save the DefaultFilePath property:
ThisApplication.DefaultFilePath = _
ThisApplication.Range("DefaultFilePath"). _
Value.ToString
// C#
// When the workbook opens:
ThisApplication.get_Range("DefaultFilePath", Type.Missing).
Value2 = ThisApplication.DefaultFilePath;
// When you save the DefaultFilePath property:
ThisApplication.DefaultFilePath =
ThisApplication.get_Range("DefaultFilePath", Type.Missing).
Value2.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've 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. (See the section titled "Searching within Ranges" later in this document for more information on the Range.Find method.) 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:
' Visual Basic
' When the workbook opens, convert the enumerated value
' into a string:
ThisApplication.Range("DefaultSaveFormat").Value = _
ThisApplication.DefaultSaveFormat.ToString
// C#
// When the workbook opens, convert the enumerated value
// into a string:
ThisApplication.get_Range("DefaultSaveFormat", Type.Missing).
Value2 = ThisApplication.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:
' Visual Basic
' Retrieve the name of the new save format, as a string:
Dim strSaveFormat As String = _
ThisApplication.Range("DefaultSaveFormat"). _
Value.ToString()
// C#
// Retrieve the name of the new save format,
// as a string:
string strSaveFormat = ThisApplication.
get_Range("DefaultSaveFormat", Type.Missing).
Value2.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:
' Visual Basic
Dim intSaveFormat As Integer = _
CType(ThisApplication.Range("XlFileFormat"). _
Find(strSaveFormat).Offset(0, 1).Value, Integer)
// C#
Excel.Range rng = ThisApplication.
get_Range("xlFileFormat", Type.Missing);
Excel.Range rngFind = rng.Find(strSaveFormat,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSearchDirection.xlNext, Type.Missing, Type.Missing,
Type.Missing);
// In C#, use the get_Offset method instead of the Offset property:
int intSaveFormat =
Convert.ToInt32(rngFind.get_Offset(0, 1).Value2);
Assign the integer value back into the DefaultSaveFormat property:
' Visual Basic
ThisApplication.DefaultSaveFormat = _
CType(intSaveFormat, Excel.XlFileFormat)
// C#
ThisApplication.DefaultSaveFormat =
Excel.XlFileFormat) intSaveFormat;
.gif)
Figure 1. Select a file format from the list of available types.
.gif)
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 will vary 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:
' Visual Basic
Private Sub ListRecentFiles()
Dim i As Integer
Dim rng As Excel.Range = DirectCast( _
ThisApplication.Range("RecentFiles"). _
Cells(1, 1), Excel.Range)
For i = 1 To ThisApplication.RecentFiles.Count
rng.Offset(i - 1, 0).Value = _
ThisApplication.RecentFiles(i).Name
Next
End Sub
// C#
private void ListRecentFiles()
{
Excel.Range rng = (Excel.Range)ThisApplication.
get_Range("RecentFiles", Type.Missing).Cells[1, 1];
for (int i = 1; i <= ThisApplication.RecentFiles.Count; i++)
{
rng.get_Offset(i - 1, 0).Value2 =
ThisApplication.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. To avoid typing the full path for each Office object, the sample project imports this namespace with an Imports or using statement. The code fragments in this document assume that you've also added the appropriate namespace reference to your file:
' Visual Basic
Imports Office = Microsoft.Office.Core
// C#
using Office = Microsoft.Office.Core;
The FileDialog object's Show method displays the dialog box, and returns -1 if you press OK, and 0 if you press Cancel. If you've 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 Figure3. If you select a file or files, the code then calls the Execute method of the FileDialog object to open the requested file(s):
' Visual Basic
With ThisApplication.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
// C#
dlg = ThisApplication.get_FileDialog(
Office.MsoFileDialogType.msoFileDialogOpen);
dlg.Filters.Clear();
dlg.Filters.Add("Excel Files", "*.xls;*.xlw", Type.Missing);
dlg.Filters.Add("All Files", "*.*", Type.Missing);
if(dlg.Show() != 0)
dlg.Execute();
.gif)
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:
' Visual Basic
With ThisApplication.FileDialog( _
Office.MsoFileDialogType.msoFileDialogFolderPicker)
If .Show <> 0 Then
ThisApplication.Range("FolderPickerResults"). _
Value = .SelectedItems.Item(1)
End If
End With
// C#
dlg = ThisApplication.get_FileDialog(
Office.MsoFileDialogType.msoFileDialogFolderPicker);
if (dlg.Show() != 0)
{
ThisApplication.get_Range("FolderPickerResults", Type.Missing).
Value2 = 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, you'll find that 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 don't 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 aren't otherwise provided by VBA. Some of the members are duplicated by Visual Basic .NET and C# operators and methods, so you're unlikely to use those (for example, the And method).
What you will 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're unlikely to take advantage of from the .NET Framework, such as And, Or, Choose, and more.
- Thai-related functions: You'll find an unexplained raft of functions that manipulate Thai numbers, calendar, and currency (rumor has it that the Excel team was once exceedingly fond of Thai food and so added these functions to help calculate the bill at the local Thai restaurant, but that's all apocryphal information at this point), such as BahtText, IsThaiDigit, ThaiDayOfWeek, ThaiDigit, ThaiMonthOfYear, ThaiNumSound, ThaiNumString, ThaiStringLength, ThaiYear, RoundBahtDown, and RoundBahtUp.
From a Visual Studio .NET project, it's easy to take advantage of the WorksheetFunction class. Because the project template provides you with the ThisApplication 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's 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 "naked", with no wrapper methods. The C# code is quite ugly, that's for sure.
Clicking the Demonstrate WorksheetFunction link runs the following code (for more information on the Sort method, see the section "Sorting Data within a Range"):
' Visual Basic
Private Sub TestWorksheetFunction()
Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.ActiveSheet, Excel.Worksheet)
Dim rng As Excel.Range = ws.Range("RandomNumbers")
Dim rnd As New System.Random
Dim i As Integer
For i = 1 To 20
ws.Cells(i, 2) = rnd.Next(100)
Next i
rng.Sort(rng, _
Orientation:=Excel.XlSortOrientation.xlSortColumns)
With ThisApplication.WorksheetFunction
ws.Range("Min").Value = .Min(rng)
ws.Range("Max").Value = .Max(rng)
ws.Range("Median").Value = .Median(rng)
ws.Range("Average").Value = .Average(rng)
ws.Range("StDev").Value = .StDev(rng)
End With
End Sub
// C#
private void TestWorksheetFunction()
{
Excel.Worksheet ws = (Excel.Worksheet) ThisWorkbook.ActiveSheet;
Excel.Range rng = ws.get_Range("RandomNumbers", Type.Missing);
System.Random rnd = new System.Random();
for ( int i = 1 ; i <= 20; i++)
ws.Cells[i, 2] = rnd.Next(100);
rng.Sort(rng, Excel.XlSortOrder.xlAscending,
Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending,
Type.Missing, Excel.XlSortOrder.xlAscending,
Excel.XlYesNoGuess.xlNo, Type.Missing,Type.Missing,
Excel.XlSortOrientation.xlSortColumns,
Excel.XlSortMethod.xlPinYin,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal);
Excel.WorksheetFunction wsf = ThisApplication.WorksheetFunction;
ws.get_Range("Min", Type.Missing).Value2 = wsf.Min(rng,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
ws.get_Range("Max", Type.Missing).Value2 = wsf.Max(rng,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
ws.get_Range("Median", Type.Missing).Value2 = wsf.Median(rng,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
ws.get_Range("Average", Type.Missing).Value2 = wsf.Average(rng,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
ws.get_Range("StDev", Type.Missing).Value2 = wsf.StDev(rng,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
}
.gif)
Figure 4. Select the WorksheetFunction sheet to test out 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 thirty-two parameters, so if you wanted to calculate the average of a fixed list of numbers, you could use code like the following:
' Visual Basic
dblAverage = ThisApplication.WorksheetFunction.Average( _
12, 14, 13, 19, 21)
// C#
// Note the number of Type.Missing values--the method accepts
// 30 parameters.
dblAverage = ThisApplication.WorksheetFunction.Average(
12, 14, 13, 19, 21,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
The Window Class and Windows Collection
As you might expect, it's 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:
' Visual Basic
ThisApplication.Windows.Arrange( _
Excel.XlArrangeStyle.xlArrangeStyleTiled)
// C#
ThisApplication.Windows.Arrange(
Excel.XlArrangeStyle.xlArrangeStyleTiled,
Type.Missing, Type.Missing, Type.Missing);
If you want to programmatically create a new window, you can call the NewWindow method of the workbook, like this:
' Visual Basic
ThisWorkbook.NewWindow()
// C#
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:
' Visual Basic
With ThisWorkbook.NewWindow()
.Caption = "New Window"
.Activate()
End With
// C#
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 could write code like the following to work with a particular window's properties:
' Visual Basic
With ThisApplication.Windows(3)
.GridlineColor = ColorTranslator.ToOle(Color.Red)
.Caption = "A New Window"
.DisplayHeadings = False
.DisplayFormulas = False
.DisplayWorkbookTabs = False
.SplitColumn = 1
End With
// C#
wnd = ThisApplication.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's 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:
' Visual Basic
Private Sub ResetWindows()
Dim i As Integer
For i = ThisApplication.Windows.Count To 2 Step -1
ThisApplication.Windows(i).Close()
Next
ThisApplication.Windows(1).WindowState = _
Excel.XlWindowState.xlMaximized
End Sub
// C#
private void ResetWindows()
{
for (int i = ThisApplication.Windows.Count; i >= 2; i--)
ThisApplication.Windows[i].Close(
false, Type.Missing, Type.Missing);
ThisApplication.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, as well.
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:
' Visual Basic
Dim nm As Excel.Name
nm = ThisApplication.Names.Add( _
"NewName", "='Other Application Members'!$A$6")
// C#
Excel.Name nm;
nm = ThisApplication.Names.Add(
"NewName", @"='Other Application Members'!$A$6",
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
Specify the name and location (along with other optional parameters), and you can then refer to the range in your code:
' Visual Basic
ThisApplication.Range("NewName").Value = "Hello, World!"
// C#
ThisApplication.get_Range(
"NewName", Type.Missing).Value2 = "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:
' Visual Basic
Dim nm As Excel.Name
Dim rng As Excel.Range = ThisApplication.Range("Names")
Dim i As Integer
For i = 0 To ThisApplication.Names.Count – 1
nm = ThisApplication.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
// C#
Excel.Range rng = ThisApplication.get_Range("Names", Type.Missing);
for ( int i = 0 ; i <= ThisApplication.Names.Count - 1; i++)
{
nm = ThisApplication.Names.Item(i + 1,
Type.Missing, Type.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, you'll find a large group of events available. Although it's 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're 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're passed an Object that refers to a sheet, you'll 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've disabled the Option Strict setting in Visual Basic .NET, however, you can take advantage of late binding. You still won't be able to 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's 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 won't 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 won't 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 won't occur on chart sheets.)
SheetCalculate occurs when any sheet is recalculated. Excel passes the event handler an Object containing a reference to the sheet that's been 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 has been 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 doesn't 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. (Note that Excel doesn't pass information about the original selection, before it was 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's provided by the Workbook object, the event only occurs when it affects a sheet within that particular workbook. In addition, you'll find the same events provided by the Worksheet class. In that case, the event name doesn't include the word "Sheet" (for example, you'll find FollowHyperlink instead of SheetFollowHyperlink, and so on), and the event handlers aren't 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. See the WindowActivate event description for more information.
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 don't 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 that was 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 has been 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's 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).
**Warning **If you summarily set the Cancel parameter to True, without taking any conditions into consideration, no workbook will ever be able to 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 being saved, 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 has been 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've seen here. All the events that begin with "Workbook" appear in the Workbook class's list of events without that designation ("Activate" instead of "WorkbookActivate", and so on). The Workbook class event handlers don't 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 won't discuss events, as you've now seen the ones you're 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'll investigate some of the members of this class, including the most-often-used properties and methods.
**Tip **Many of the Application class' members 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 will discuss far fewer members than the previous section, mostly because you've 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're most likely to use. The general rule of thumb is this: If you need some behavior of a workbook, someone else has probably already requested it, and there's 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:
' Visual Basic ThisApplication.Range("WorkbookName").Value = _ ThisWorkbook.Name ThisApplication.Range("WorkbookPath").Value = _ ThisWorkbook.Path ThisApplication.Range("WorkbookFullName").Value = _ ThisWorkbook.FullName // C# ThisApplication.get_Range("WorkbookName", Type.Missing). Value2 = ThisWorkbook.Name; ThisApplication.get_Range("WorkbookPath", Type.Missing). Value2 = ThisWorkbook.Path; ThisApplication.get_Range("WorkbookFullName", Type.Missing). Value2 = ThisWorkbook.FullName;.gif)
Figure 5. Use Workbook properties to retrieve information about the name.
Password (String): Gets or sets the password associated with the workbook. If you've specified a non-empty password, the HasPassword property of the workbook will return 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've 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:
' Visual Basic Private Sub SetPassword() Dim frm As New Password If frm.ShowDialog = DialogResult.OK Then ThisWorkbook.Password = frm.Password End If frm.Dispose() End Sub // C# private void SetPassword() { Password frm = new Password(); if (frm.ShowDialog() == DialogResult.OK) ThisWorkbook.Password = frm.Value; frm.Dispose(); }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're 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 has been set to display only two decimal places in column C. Note that with the PrecisionAsDisplayed property set to True, the sums are different, because rounding has 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):
' Visual Basic Private Sub TestPrecisionAsDisplayed( _ ByVal IsPrecisionAsDisplayedOn As Boolean) ThisWorkbook.PrecisionAsDisplayed = IsPrecisionAsDisplayedOn End Sub // C# private void TestPrecisionAsDisplayed( bool IsPrecisionAsDisplayedOn) { ThisWorkbook.PrecisionAsDisplayed = IsPrecisionAsDisplayedOn; }.gif)
Figure 6. With the PrecisionAsDisplayed property set to True, Excel uses only the visible decimals to perform its calculations.
ReadOnly (Boolean, read-only): Returns True if the workbook was opened as read-only. You may want to take different actions in your application if you're unable to save data to the workbook.
Saved (Boolean): Gets or sets the saved state of the workbook. If the user has made modifications to the workbook's contents or structure, the Saved property is True. Attempting to close the workbook or quit Excel will cause an alert to appear, prompting you to save the workbook (unless you've set the Application.DisplayAlerts property to False). If you set the Saved property value to False in your code, Excel will treat your workbook as if it had already been saved, and 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 Figure7, and you can also select the Custom tab to create and modify custom properties.
.gif)
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's 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:
' Visual Basic
Private Sub DisplayDocumentProperties()
Dim prp As Office.DocumentProperty
Dim prps As Office.DocumentProperties
Dim rng As Excel.Range = _
ThisApplication.Range("DocumentProperties")
Dim i As Integer
Try
ThisApplication.ScreenUpdating = False
Try
prps = DirectCast( _
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 = DirectCast( _
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
ThisApplication.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
// C#
private void DisplayDocumentProperties()
{
Office.DocumentProperty prp = null;
Office.DocumentProperties prps =
(Office.DocumentProperties)
ThisWorkbook.BuiltinDocumentProperties;
Excel.Range rng = ThisApplication.
get_Range("DocumentProperties", Type.Missing);
int i = 0;
try
{
ThisApplication.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, ThisApplication.Name);
}
// Work with custom properties:
try
{
prps = (Office.DocumentProperties)
ThisWorkbook.CustomDocumentProperties;
DumpPropertyCollection(prps, rng, i);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ThisApplication.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", Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ThisApplication.Name);
}
}
finally
{
ThisApplication.ScreenUpdating = true;
}
}
private int DumpPropertyCollection(
Office.DocumentProperties prps, Excel.Range rng, int i)
{
foreach (Office.DocumentProperty prp in prps)
{
rng.get_Offset(i, 0).Value2 = prp.Name;
try
{
if (prp.Value != null )
{
rng.get_Offset(i, 1).Value2 =
prp.Value.ToString();
}
}
catch
{
// Do nothing at all.
}
i += 1;
}
return i;
}
**Tip **The previous code sample, DisplayDocumentProperties, used several enumerations and types from the Microsoft.Office.Core assembly. The sample code includes an Imports/using statement that sets up the text "Office" as an abbreviation for this namespace, just as it does for the "Excel" abbreviation. The project template automatically sets up the "Excel" abbreviation. You'll need to add the "Office" statement yourself.
.gif)
Figure 8. Built-in document properties
**Note **Although you're working with Excel and its objects here, it's Office that supplies the list of available built-in document properties, and Excel doesn't 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, should it occur.
Working with Styles
Much like a 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.
.gif)
Figure 9. Modify styles interactively with this dialog box.
If you click Modify on the Style dialog box, you'll display the Format Cells dialog box shown in Figure 10.
.gif)
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've run the code already), sets up the various aspects of the style, and applies it to a region:
' Visual Basic
Private Sub ApplyStyle()
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 = ThisWorkbook.Styles(STYLE_NAME)
Catch
sty = ThisWorkbook.Styles.Add(STYLE_NAME)
End Try
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 = XlPattern.xlPatternSolid
rng.Style = STYLE_NAME
rng.Columns.AutoFit()
End Sub
// C#
private void ApplyStyle()
{
const String STYLE_NAME = "PropertyBorder";
// Get the range containing all the document properties.
Excel.Range rng = GetDocPropRange();
Excel.Style sty;
try
{
sty = ThisWorkbook.Styles[STYLE_NAME];
}
catch
{
sty = ThisWorkbook.Styles.Add(STYLE_NAME, Type.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:
' Visual Basic
Private Function GetDocPropRange() As Excel.Range
Dim rng As Excel.Range = _
ThisApplication.Range("DocumentProperties")
Dim rngStart As Excel.Range = _
DirectCast(rng.Cells(1, 1), Excel.Range)
Dim rngEnd As Excel.Range = _
rng.End(Excel.XlDirection.xlDown).Offset(0, 1)
Return ThisApplication.Range(rngStart, rngEnd)
End Function
// C#
private Excel.Range GetDocPropRange()
{
Excel.Range rng =
ThisApplication.get_Range("DocumentProperties", Type.Missing);
Excel.Range rngStart =
(Excel.Range) rng.Cells[1, 1];
Excel.Range rngEnd =
rng.get_End(Excel.XlDirection.xlDown).get_Offset(0, 1);
return ThisApplication.get_Range(rngStart, rngEnd);
}
**Tip **For more information on retrieving and working with Range objects, see the section titled "Working with Ranges" later in this document.
Once you've run the code, the region containing document properties on the sample workbook changes shading and font, as shown in Figure 11.
.gif)
Figure 11. After applying a custom style
Clicking Clear Style runs the following procedure, which clears the style for the same region:
' Visual Basic
Private Sub ClearStyle()
' Get the range containing all the document properties, and
' clear the style.
GetDocPropRange().Style = "Normal"
End Sub
// C#
private void ClearStyle()
{
// 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:
' Visual Basic
Private Sub ListSheets()
Dim sh As Excel.Worksheet
Dim rng As Excel.Range
Dim i As Integer
rng = ThisApplication.Range("Sheets")
For Each sh In ThisWorkbook.Sheets
rng.Offset(i, 0).Value = sh.Name
i = i + 1
Next sh
End Sub
// C#
private void ListSheets()
{
int i = 0;
Excel.Range rng =
ThisApplication.get_Range("Sheets", Type.Missing);
foreach (Excel.Worksheet sh in ThisWorkbook.Sheets)
{
rng.get_Offset(i, 0).Value2 = sh.Name;
i = i + 1;
}
}
You may also find the following members of the Sheets class to be useful.
The Visible property****allows 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:
' Visual Basic DirectCast(ThisWorkbook.Sheets(1), Excel.Worksheet).Visible = _ Excel.XlSheetVisibility.xlSheetVeryHidden // C# ((Excel.Worksheet) ThisWorkbook.Sheets[1]).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):
' Visual Basic Dim sh As Excel.Sheet = ThisWorkbook.Sheets.Add() // C# Excel.Sheet sh = ThisWorkbook.Sheets.Add( Type.Missing, Type.Missing, Type.Missing, Type.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 don't specify, Excel creates a new workbook to contain the new sheet. The following fragment copies the first sheet in the current workbook and places the copy after the third sheet:
' Visual Basic DirectCast(ThisWorkbook.Sheets(1), Excel.Worksheet). _ Copy(After:=ThisWorkbook.Sheets((3))) // C# ((Excel.Worksheet) ThisWorkbook.Sheets[1]). Copy(Type.Missing, ThisWorkbook.Sheets[3]);.The Delete method deletes a specified sheet:
' Visual Basic DirectCast(ThisWorkbook.Sheets(1), Excel.Worksheet).Delete // C# ((Excel.Worksheet) ThisWorkbook.Sheets[1]).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:
' Visual Basic ThisWorkbook.Sheets.FillAcrossSheets( _ ThisApplication.Range("Data"), Excel.XlFillWith.xlFillWithAll) // C# ThisWorkbook.Sheets.FillAcrossSheets( ThisApplication.get_Range("Data", Type.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 don't specify a location for the moved sheet, Excel creates a new workbook to contain it. The following fragment moves the first worksheet so that it's last:
' Visual Basic Dim shts As Excel.Sheets = ThisWorkbook.Sheets DirectCast(shts(1), Excel.Worksheet).Move(After:=shts(shts.Count)) // C# Excel.Sheets shts = ThisWorkbook.Sheets; ((Excel.Worksheet)shts[1]).Move(Type.Missing, shts[shts.Count]);**Tip **If you have some reason to want to sort the list of sheets in a workbook, you can make use of the Move method to perform an inefficient bubble sort. Of course, because you're unlikely to have very many sheets, the speed of the sort won't be 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:
' Visual Basic DirectCast(ThisWorkbook.Sheets(1), Excel.Worksheet). _ PrintOut(From:=1, To:=1, Copies:=2, Preview:=True) // C# ((Excel.Worksheet)ThisApplication.Sheets[1]). PrintOut(1, 1, 2, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing);The PrintPreview method allows you to display the specified object in the PrintPreview window, optionally disallowing changes to the page layout:
' Visual Basic DirectCast(ThisWorkbook.Sheets(1), Excel.Worksheet). _ PrintPreview(False) // C# ((Excel.Worksheet)ThisApplication.Sheets[1]).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:
' Visual Basic ActiveWorkbook.Sheets(1).Select() // C# ((Excel.Worksheet)ThisApplication.Sheets[1]).Select(Type.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're likely to use in every application, leaving the esoteric methods for later study. The following list describes some of the methods you're most likely to need:
The Activate method activates a workbook, selecting the first sheet in the workbook:
' Visual Basic ThisApplication.Workbooks(1).Activate // C# ThisApplication.Workbooks[1].Activate;The Close method closes the specified workbook, optionally specifying whether changes should be saved. If the workbook has never been saved, 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:
' Visual Basic ThisApplication.Workbooks(1).Close(SaveChanges:=False) // C# ThisApplication.Workbooks(1).Close(false, Type.Missing, Type.Missing);The Protect and Unprotect methods allow you to protect a workbook so that you can't 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 can't 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's 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:
' Visual Basic ThisApplication.Workbooks(1).Protect(GetPasswordFromUser()) // C# ThisApplication.Workbooks[1].Protect( GetPasswordFromUser(), Type.Missing, Type.Missing);The Save method saves the workbook, as you might have expected. If you've never saved the workbook, you should call the SaveAs method instead, so you can specify a path (if you haven't yet saved the workbook, Excel saves it in the current folder with the name it was given when it was created):
' Visual Basic ' Save all open workbooks. Dim wb As Excel.Workbook For Each wb in ThisApplication.Workbooks wb.Save Next wb // C# // Save all open workbooks. foreach (Excel.Workbook wb in ThisApplication.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 the online help for a list of all the options. The following fragment saves the current workbook to a specified location, saving in XML format:
' Visual Basic ThisApplication.ActiveWorkbook.SaveAs("C:\MyWorkbook.xml", _ FileFormat:=Excel.XlFileFormat.xlXMLSpreadsheet) // C# ThisApplication.ActiveWorkbook.SaveAs("C:\\MyWorkbook.xml", Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.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 won't be able to 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 doesn't 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:
' Visual Basic ThisApplication.ActiveWorkbook.SaveCopyAs("C:\Test.xls") // C# ThisApplication.ActiveWorkbook.SaveCopyAs("C:\\Test.xls");**Warning **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 doesn't disable prompts from Excel, and your user clicks Cancel when prompted, Excel raises a run-time error back to your code.
The Worksheet Class
By the time you've gotten to this point in this document, you've been introduced to 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 haven't 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's No Sheet Class
Although Excel provides a Sheets collection as a property of a Workbook object, you won't 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's no way for anyone without access to the source code to know if this matches the actual implementation), but the Sheet class isn't 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.
.gif)
Figure 12. In the user interface, control protection using this dialog box.
.gif)
Figure 13. Using this dialog box, you can 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:
' Visual Basic
WorksheetObject.Protect(Password, DrawingObjects, Contents, _
Scenarios, UserInterfaceOnly, AllowFormattingCells, _
AllowFormattingColumns, AllowFormattingRows, _
AllowInsertingColumns, AllowInsertingRows, _
AllowInsertingHyperlinks, AllowDeletingColumns, _
AllowDeletingRows, AllowSorting, AllowFiltering, _
AllowUsingPivotTables)
// C#
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 will be required in order to unprotect the worksheet. If you don't 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 you probably won't ever change it.
- 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'll need to 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:
' Visual Basic
DirectCast(ThisApplication.Sheets(1), Excel.Worksheet). _
Protect("MyPassword", AllowSorting:=True)
// C#
((Excel.Worksheet)ThisApplication.Sheets[1]).Protect(
"MyPassword", Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, true, Type.Missing, Type.Missing);
**Tip **Clearly, hard-coding the password within your code isn't a great idea. Most likely, you'll need to request the password from the user, and apply it to the workbook without saving it. Generally, you won't find hard-coded passwords in source code.
To unprotect a worksheet, you could use code like the following. This fragment assumes a procedure named GetPasswordFromUser, which would request the user to enter a password, and return the entered value:
' Visual Basic
DirectCast(ThisApplication.Sheets(1), Excel.Worksheet). _
Unprotect(GetPasswordFromUser())
// C#
((Excel.Worksheet)ThisApplication.Sheets[1]).
Unprotect(GetPasswordFromUser());
The Unprotect method removes protection from the worksheet, allowing you to supply an optional password.
Excel also provides two other objects you'll find useful, 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 (see the online documentation for more information on the UserAccess object)
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.
.gif)
Figure 14. Test the protection capabilities of a worksheet.
The links on the sample sheet run the following procedures:
' Visual Basic
Private Sub ProtectSheet()
Dim ws As Excel.Worksheet = _
DirectCast(ThisApplication.ActiveSheet, Excel.Worksheet)
With ws.Protection.AllowEditRanges
.Add("Information", ThisApplication.Range("Information"))
.Add("Date", ThisApplication.Range("Date"))
End With
ws.Protect()
End Sub
Private Sub UnprotectSheet()
Dim ws As Excel.Worksheet = _
DirectCast(ThisApplication.Sheets("Worksheet Class"), _
Excel.Worksheet)
' Unprotect the sheet.
ws.Unprotect()
' Delete all protection ranges, just to clean up.
' You must loop through this using the index,
' backwards. This collection doesn't provide
' an enumeration method, and it doesn't handle
' being resized as you're looping in a nice way.
Dim i As Integer
With ws.Protection.AllowEditRanges
For i = .Count To 1 Step -1
.Item(i).Delete()
Next i
End With
End Sub
// C#
private void ProtectSheet()
{
Excel.Worksheet ws =
(Excel.Worksheet)ThisApplication.ActiveSheet;
Excel.AllowEditRanges ranges = ws.Protection.AllowEditRanges;
ranges.Add("Information",
ThisApplication.get_Range("Information", Type.Missing),
Type.Missing);
ranges.Add("Date",
ThisApplication.get_Range("Date", Type.Missing), Type.Missing);
ws.Protect(Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,Type.Missing, Type.Missing,
Type.Missing,Type.Missing, Type.Missing, Type.Missing,
Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing);
}
private void UnprotectSheet()
{
Excel.Worksheet ws =
(Excel.Worksheet) ThisApplication.Sheets["Worksheet Class"];
ws.Unprotect(Type.Missing);
// Delete all protection ranges, just to clean up.
// You must loop through this using the index,
// backwards. This collection doesn't provide
// an enumeration method, and it doesn't handle
// being resized as you're 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):
' Visual Basic
Dim rng As Excel.Range = ThisApplication.Range("Date")
If Not rng.Comment Is Nothing Then
rng.Comment.Delete()
End If
rng.AddComment("Comment added " & DateTime.Now)
' Display all the comments:
ShowOrHideComments(Show:=True)
// C#
Excel.Range rng = ThisApplication.get_Range("Date", Type.Missing);
if (rng.Comment != null )
{
rng.Comment.Delete();
}
rng.AddComment("Comment added " + DateTime.Now);
// Display all the comments:
ShowOrHideComments(true);
.gif)
Figure 15. You can easily insert a new comment into a worksheet in the user interface.
.gif)
Figure 16. After showing all the comments 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 doesn't provide many members. You're likely to use the Comment class's Visible property 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've 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:
' Visual Basic
Private Sub ShowOrHideComments(ByVal Show As Boolean)
' Show or hide all the comments:
Dim ws As Excel.Worksheet = _
DirectCast(ThisApplication.Sheets("Worksheet Class"), _
Excel.Worksheet)
Dim i As Integer
For i = 1 To ws.Comments.Count
ws.Comments(i).Visible = Show
Next
End Sub
// C#
private void ShowOrHideComments(bool show)
{
// Show or hide all the comments:
Excel.Worksheet ws =
(Excel.Worksheet) ThisApplication.Sheets["Worksheet Class"];
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, doesn't provide a default enumerator. In other words, you won't be able to 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 (it's been done already in the figures) so you can collapse the rows as shown in Figure 18 and collapse the groups as shown in Figure 19.
.gif)
Figure 17. Create these groups
.gif)
Figure 18. Collapsed groups
.gif)
Figure 19. Completely collapsed groups
The Worksheet class provides an Outline property, which is itself an Outline object. The Outline class doesn't provide many members, and the following list describes the ones you're likely to use:
AutomaticStyles (Boolean) indicates to Excel whether it should 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:
' Visual Basic Dim ws As Excel.Worksheet = _ DirectCast(ThisApplication.ActiveSheet, Excel.Worksheet) ' Specify RowLevels and/or ColumnLevels parameters: ws.Outline.ShowLevels(RowLevels:=3) // C# Excel.Worksheet ws = (Excel.Worksheet) ThisApplication.ActiveSheet; // Specify RowLevels and/or ColumnLevels parameters: ws.Outline.ShowLevels(3, Type.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 doesn't 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:
' Visual Basic
Private Sub WorkWithGroups()
Dim ws As Excel.Worksheet = _
DirectCast(ThisApplication.ActiveSheet, Excel.Worksheet)
' 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 don't want Excel
' to format the summary rows--that's already been done.
ws.Outline.SummaryRow = Excel.XlSummaryRow.xlSummaryBelow
ws.Outline.AutomaticStyles = False
' Group the two named ranges. Each of these
' ranges extends across entire rows.
ThisApplication.Range("Data2001").Group()
ThisApplication.Range("Data2002").Group()
ThisApplication.Range("AllData").Group()
' The range of rows from 24 to 27 doesn't have
' a named range, so you can work with that
' range directly.
Dim rng As Excel.Range = _
DirectCast(ws.Rows("24:27"), Excel.Range)
rng.Group()
' Collapse to the second group level.
ws.Outline.ShowLevels(RowLevels:=2)
End Sub
// C#
private void WorkWithGroups()
{
Excel.Worksheet ws =
(Excel.Worksheet) ThisApplication.ActiveSheet;
// 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 don't want Excel
// to format the summary rows--that's already been done.
ws.Outline.SummaryRow = Excel.XlSummaryRow.xlSummaryBelow;
ws.Outline.AutomaticStyles = false;
// Group the two named ranges. Each of these
// ranges extends across entire rows.
ThisApplication.get_Range("Data2001", Type.Missing).
Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ThisApplication.get_Range("Data2002", Type.Missing).
Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ThisApplication.get_Range("AllData", Type.Missing).
Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// The range of rows from 24 to 27 doesn't have
// a named range, so you can work with that
// range directly.
Excel.Range rng = (Excel.Range)ws.Rows["24:27", Type.Missing];
rng.Group(Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
// Collapse to the second group level.
ws.Outline.ShowLevels(2, Type.Missing);
}
To group the three named ranges, the code simply calls the Group method of the range:
' Visual Basic
ThisApplication.Range("Data2001").Group()
// C#
ThisApplication.get_Range("Data2001", Type.Missing).
Group(Type.Missing, Type.Missing, Type.Missing, Type.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:
' Visual Basic
Dim rng As Excel.Range = _
DirectCast(ws.Rows("24:27"), Excel.Range)
rng.Group()
// C#
Excel.Range rng = (Excel.Range)ws.Rows["24:27", Type.Missing];
rng.Group(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Clicking the Clear Groups link on the sample worksheet runs similar code to clear the groups:
' Visual Basic
Private Sub ClearGroups()
Dim ws As Excel.Worksheet = _
DirectCast(ThisApplication.ActiveSheet, Excel.Worksheet)
' Specify RowLevels and/or ColumnLevels parameters:
ws.Outline.ShowLevels(RowLevels:=3)
Dim rng As Excel.Range = _
DirectCast(ws.Rows("24:27"), Excel.Range)
rng.Ungroup()
ThisApplication.Range("Data2001").Ungroup()
ThisApplication.Range("Data2002").Ungroup()
ThisApplication.Range("AllData").Ungroup()
End Sub
// C#
private void ClearGroups()
{
Excel.Worksheet ws =
(Excel.Worksheet) ThisWorkbook.Sheets["Worksheet Class"];
// Specify RowLevels and/or ColumnLevels parameters:
ws.Outline.ShowLevels(3, Type.Missing);
Excel.Range rng = (Excel.Range) ws.Rows["24:27", Type.Missing];
rng.Ungroup();
ThisApplication.get_Range("Data2001", Type.Missing).Ungroup();
ThisApplication.get_Range("Data2002", Type.Missing).Ungroup();
ThisApplication.get_Range("AllData", Type.Missing).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'll use most within your Excel applications; before you can manipulate any region within Excel, you'll need to 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've seen so far in this article has 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 would be 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.
Managing the Selection
Although it's 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've caused the user to lose control over the current selection. The rule of thumb is that you should call an object's Select method only if your intent is to change the user's selection. You should never call the Select method simply because it's 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 will not only make your code run faster, in general, it will make your users happier.
It's all too easy to write code like the following, to clear the region contiguous to the user's current cell:
' Visual Basic
ThisApplication.ActiveCell.CurrentRegion.Select
DirectCast(ThisApplication.Selection, Excel.Range).ClearContents
// C#
ThisApplication.ActiveCell.CurrentRegion.Select();
((Excel.Range)ThisApplication.Selection).ClearContents();
Doing this loses the user's selection. If only a single cell was originally selected, after running the previous code fragment the entire block of contiguous cells would be selected. Unless your goal was, in fact, to select the whole range of cells, a better solution would be to use code like the following:
' Visual Basic
ThisApplication.ActiveCell.CurrentRegion.ClearContents
// C#
ThisApplication.ActiveCell.CurrentRegion.ClearContents();
Why would 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 writesreally 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'll find yourself with far 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've retrieved. For more information on the various ways to refer to a range, see the Excel online 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:
' Visual Basic
Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range
// C#
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:
' Visual Basic rng = ThisApplication.ActiveCell // C# rng = ThisApplication.ActiveCell;Use the Range property of an object, specifying a range. Because C# doesn't support parameterized non-indexed properties, you must call the get_Range method, which requires two parameters, instead:
' Visual Basic rng = ws.Range("A1") rng = ws.Range("A1:B12") // C# rng = ws.get_Range("A1", Type.Missing); rng = ws.get_Range("A1:B12", Type.Missing);Use the Cells property of a worksheet, specifying a single row and column value:
' Visual Basic ' The Cells collection returns an Object-- ' Convert it to a Range object explicitly: rng = DirectCast(ws.Cells(1, 1), Excel.Range) // C# 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:
' Visual Basic rng = ws.Range("A1", "C5") rng = ws.Range("A1", "C5").Cells rng = ws.Range("A1", "C5").Rows rng = ws.Range("A1", "C5").Columns // C# 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've 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 Type.Missing for the second parameter:
' Visual Basic rng = ThisApplication.Range("SomeRangeName") // C# rng = ThisApplication.Range("SomeRangeName", Type.Missing);Refer to a particular row or column or range of rows and columns; note that the Rows and Columnsproperties each return an Object, requiring conversion if you have Option Strict set to On:
' Visual Basic rng = DirectCast(ws.Rows(1), Excel.Range) rng = DirectCast(ws.Rows("1:3"), Excel.Range) rng = DirectCast(ws.Columns(3), Excel.Range) // C# rng = (Excel.Range)ws.Rows[1, Type.Missing]; rng = (Excel.Range)ws.Rows["1:3", Type.Missing]; rng = (Excel.Range)ws.Columns[3, Type.Missing];**Warning **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 isn't 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):
' Visual Basic Debug.WriteLine( _ DirectCast(ThisApplication.Selection, Excel.Range).Address) // C# System.Diagnostics.Debug.WriteLine( ((Excel.Range)ThisApplication.Selection). get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));**Tip **The Address property is another of those parameterized properties that C# can't 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're 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):
' Visual Basic rng = ThisApplication.Range("A1:D4, F2:G5") ' You can also use the Application object's Union ' method to retrieve the intersection of two ranges: rng1 = ThisApplication.Range("A1:D4") rng2 = ThisApplication.Range("F2:G5") rng = ThisApplication.Union(rng1, rng2) // C# rng = ThisApplication.get_Range("A1:D4, F2:G5", Type.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 = ThisApplication.get_Range("A1", "D4"); rng2 = ThisApplication.get_Range("F2", "G5"); // Note that the Union method requires you to supply thirty // parameters: rng = ThisApplication.Union(rng1, rng2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);Create a range which refers to the intersection of two other ranges (specify two ranges within the quotes, with no separator):
' Visual Basic rng = ThisApplication.Range("A1:D16 B2:F14") ' You can also use the Application object's Intersect ' method to retrieve the intersection of two ranges: rng1 = ThisApplication.Range("A1:D16") rng2 = ThisApplication.Range("B2:F14") rng = ThisApplication.Intersect(rng1, rng2) // C# rng = ThisApplication.get_Range("A1:D16 B2:F14", Type.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 = ThisApplication.get_Range("A1", "D16"); rng2 = ThisApplication.get_Range("B2", "F14"); rng = ThisApplication.Intersect(rng1, rng2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.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:
' Visual Basic rng = DirectCast(ws.Cells(1, 1), Excel.Range) Dim i As Integer For i = 1 To 5 rng.Offset(i, 0).Value = i.ToString Next // C# 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 can't 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:
' Visual Basic ThisApplication.Range("C3").CurrentRegion.Font.Bold = True // C# ThisApplication.get_Range("C3", Type.Missing). CurrentRegion.Font.Bold = True;.gif)
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):
' Visual Basic rng = ThisApplication.Range("Test") Dim i As Integer For i = 1 To rng.Areas.Count Debug.WriteLine(rng.Areas(i).Address) Next // C# rng = ThisApplication.get_Range("Test", Type.Missing); for (int i = 1; i <= rng.Areas.Count; i++) { System.Diagnostics.Debug.WriteLine( rng.Areas[i].get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)); }.gif)
Figure 21. Ranges can contain areas that are not contiguous, and you can 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'd 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:
' Visual Basic Dim rngLeft, rngRight, rngUp, rngDown as Excel.Range rng = DirectCast(ThisApplication.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) // C# Excel.Range rngLeft, rngRight, rngUp, rngDown; rng = (Excel.Range) ThisApplication.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);.gif)
Figure 22. Use the End property to return ranges corresponding to a range.
Use the EntireRow or EntireColumn properties to refer to the row(s) or column(s) 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 = ThisApplication.Range("Test") rng.Areas(2).EntireRow.Font.Bold = True // C# rng = ThisApplication.get_Range("Test", Type.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 isn't built into Excel, but isn't terribly difficult to add. The Range Class sheet in the sample workbook contains a range that's handled specially: as you select an item, its row becomes bold. Figure 23 shows this behavior in action.
.gif)
Figure 23. Select an item to make its entire row bold.
The sample workbook includes the following procedure to take care of the formatting:
' Visual Basic
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 = _
ThisApplication.ActiveCell
' Bold the current row.
rngCell.EntireRow.Font.Bold = True
' Make sure intRow isn't 0 (meaning that
' this is your first pass through here).
If intRow <> 0 Then
' If you're 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 = _
DirectCast(ws.Rows(intRow), Excel.Range)
rng.EntireRow.Font.Bold = False
End If
End If
' Store away the new row number
' for next time.
intRow = rngCell.Row
End Sub
// C#
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 = ThisApplication.ActiveCell;
// Bold the current row.
rngCell.EntireRow.Font.Bold = true;
// Make sure intRow isn't 0 (meaning that
// this is your first pass through here).
if (LastBoldedRow != 0)
{
// If you're 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, Type.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:
' Visual Basic Static intRow As Integer // C# private int LastBoldedRow = 0;Retrieves a reference to the current cell using the Application.ActiveCell property:
' Visual Basic private int LastBoldedRow = 0; Dim rngCell As Excel.Range = ThisApplication.ActiveCell // C# Excel.Range rngCell = ThisApplication.ActiveCell;Bolds the current row using the EntireRow property of the active cell:
' Visual Basic rngCell.EntireRow.Font.Bold = True // C# rngCell.EntireRow.Font.Bold = true;Ensures that the current value of intRow isn't 0, which indicates that this is the first time through this code:
' Visual Basic If intRow <> 0 Then ' Code removed here... End If // C# 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's different than the previous row. The Row property returns an integer indicating the row corresponding to the range:
' Visual Basic If rngCell.Row <> intRow Then ' Code removed here... End If // C# 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:
' Visual Basic Dim rng As Excel.Range = _ DirectCast(ws.Rows(intRow), Excel.Range) rng.Font.Bold = False // C# Excel.Range rng = (Excel.Range)ws.Rows[LastBoldedRow, Type.Missing]; rng.Font.Bold = false;
The sample workbook calls the BoldCurrentRow procedure from its SheetSelectionChange event handler. 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:
' Visual Basic
Private Sub ThisWorkbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Excel.Range) _
Handles ThisWorkbook.SheetSelectionChange
If Not ThisApplication.Intersect(Target, _
ThisApplication.Range("BoldSelectedRow")) Is Nothing Then
' The selection is within the range where you're making
' the selected row bold.
BoldCurrentRow(DirectCast(Sh, Excel.Worksheet))
End If
End Sub
// C#
protected void ThisWorkbook_SheetSelectionChange(
System.Object sh, Excel.Range Target)
{
// Don't forget that the Intersect method requires
// thirty parameters.
if (ThisApplication.Intersect(Target,
ThisApplication.get_Range("BoldSelectedRow", Type.Missing),
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing)
!= null)
{
// The selection is within the range where you're making
//the selected row bold.
BoldCurrentRow((Excel.Worksheet) sh);
}
}
Working with Ranges
Once you've 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 Range class's AutoFill method 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 don't 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.
.gif)
Figure 24. Before calling the AutoFill method for four sample ranges.
.gif)
Figure 25. After auto-filling the ranges.
Clicking the AutoFill link runs the following procedure:
' Visual Basic
Private Sub AutoFill()
Dim rng As Excel.Range = ThisApplication.Range("B1")
rng.AutoFill(ThisApplication.Range("B1:B5"), _
Excel.XlAutoFillType.xlFillDays)
rng = ThisApplication.Range("C1")
rng.AutoFill(ThisApplication.Range("C1:C5"), _
Excel.XlAutoFillType.xlFillMonths)
rng = ThisApplication.Range("D1")
rng.AutoFill(ThisApplication.Range("D1:D5"), _
Excel.XlAutoFillType.xlFillYears)
rng = ThisApplication.Range("E1:E2")
rng.AutoFill(ThisApplication.Range("E1:E5"), _
Excel.XlAutoFillType.xlFillSeries)
End Sub
// C#
private void AutoFill()
{
Excel.Range rng = ThisApplication.get_Range("B1", Type.Missing);
rng.AutoFill(ThisApplication.get_Range("B1:B5", Type.Missing),
Excel.XlAutoFillType.xlFillDays);
rng = ThisApplication.get_Range("C1", Type.Missing);
rng.AutoFill(ThisApplication.get_Range("C1:C5", Type.Missing),
Excel.XlAutoFillType.xlFillMonths);
rng = ThisApplication.get_Range("D1", Type.Missing);
rng.AutoFill(ThisApplication.get_Range("D1:D5", Type.Missing),
Excel.XlAutoFillType.xlFillYears);
rng = ThisApplication.get_Range("E1:E2", Type.Missing);
rng.AutoFill(ThisApplication.get_Range("E1:E5", Type.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'll need to supply this value in order to get the behavior you need. For example, try changing this code:
' Visual Basic
rng.AutoFill(ThisApplication.Range("D1:D5"), _
Excel.XlAutoFillType.xlFillYears)
// C#
rng.AutoFill(ThisApplication.get_Range("D1:D5", Type.Missing),
Excel.XlAutoFillType.xlFillYears);
so that it looks like this:
' Visual Basic
rng.AutoFill(ThisApplication.Range("D1:D5"))
// C#
rng.AutoFill(ThisApplication.get_Range("D1:D5", Type.Missing),
Excel.XlAutoFillType.xlFillDefault);
Instead of ending up with dates that increment by years, the dates increment by days.
Searching within Ranges
The Range class's Find method 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 don't 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.
.gif)
Figure 26. Selections on this dialog box affect the behavior of the Find method.
**Warning **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's 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 needn't worry about this problem, because they must supply all the parameters on each method call.
Table 4. Parameters to 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 won't be included in the search); if you don't 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've 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 won't be used in this example.) You specify the cell after which to search, and the FindNext method handles the rest.
.gif)
Figure 27. The results of searching for cells containing the word "apples"
**Tip **The FindNext (and FindPrevious) method's search wraps back to the beginning of the search range once it has reached the end of the range. It's up to your code to make sure that you don't 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's too complex for the Find/FindNext/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:
' Visual Basic
Private Sub DemoFind()
Dim rng As Excel.Range = ThisApplication.Range("Fruits")
Dim rngFound As Excel.Range
' Keep track of the first range you find.
Dim rngFoundFirst As Excel.Range
' You should 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 Not rngFound Is 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
// C#
private void DemoFind()
{
Excel.Range rng = ThisApplication.
get_Range("Fruits", Type.Missing);
Excel.Range rngFound;
// Keep track of the first range you find.
Excel.Range rngFoundFirst = null;
// You should specify all these parameters
// every time you call this method, since they
// can be overriden in the user interface.
rngFound = rng.Find("apples", Type.Missing,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext,
false, Type.Missing, Type.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:
' Visual Basic Dim rng As Excel.Range = ThisApplication.Range("Fruits") Dim rngFound As Excel.Range Dim rngFoundFirst As Excel.Range // C# Excel.Range rng = ThisApplication. get_Range("Fruits", Type.Missing); 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:
' Visual Basic rngFound = rng.Find( _ "apples", , _ Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _ Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, _ False) // C# rngFound = rng.Find("apples", Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);Continues searching as long as it continues to find matches:
' Visual Basic While Not rngFound Is Nothing ' Code removed here... End While // C# 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.
' Visual Basic If rngFoundFirst Is Nothing Then rngFoundFirst = rngFound ElseIf rngFound.Address = rngFoundFirst.Address Then Exit While End If // C# if (rngFoundFirst == null ) { rngFoundFirst = rngFound; } else if (GetAddress(rngFound) == GetAddress(rngFoundFirst)) { break; }Sets the appearance of the found range:
' Visual Basic With rngFound.Font .Color = ColorTranslator.ToOle(Color.Red) .Bold = True End With // C# rngFound.Font.Color = ColorTranslator.ToOle(Color.Red); rngFound.Font.Bold = true;Performs another search:
' Visual Basic rngFound = rng.FindNext(rngFound) // C# rngFound = rng.FindNext(rngFound);
Clicking the Reset Find link on the sample sheet runs this simple procedure, putting the range back as it started:
' Visual Basic
Private Sub ResetFind()
Dim rng As Excel.Range = ThisApplication.Range("Fruits")
With rng.Font
.Color = ColorTranslator.ToOle(Color.Black)
.Bold = False
End With
End Sub
// C#
private void ResetFind()
{
Excel.Range rng = ThisApplication.
get_Range("Fruits", Type.Missing);
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 .NET developers will most likely 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 | Specified which elements are to be sorted 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 would sort based on the "fruits" custom order. |
| MatchCase | Boolean | Set to True to perform a case-sensitive sort, False to perform non-case sensitive sort; can't be used with pivot tables. |
| Orientation | XlSortOrientation (xlSortRows, xlSortColumns) | Orientation for the sort. |
| SortMethod | XlSortMethod (xlStroke, xlPinYin) | Specifies the sort method; doesn't apply to all languages (current values only apply to Chinese sorting and will have no bearing on other languages). |
| DataOption1 | XlSortDataOption (xlSortTextAsNumbers, xlSortNormal) | Specifies how to sort text in the range specified in Key1; doesn't apply to pivot table sorting. |
| DataOption2 | XlSortDataOption | Specifies how to sort text in the range specified in Key2; doesn't apply to pivot table sorting. |
| DataOption3 | XlSortDataOption | Specifies how to sort text in the range specified in Key3; doesn't apply to pivot table sorting. |
Tip Visual Basic .NET developers have a distinct advantage over C# developers when calling methods like this one. Because you're unlikely to use all the parameters, Visual Basic .NET developers can use named parameters, specifying only the parameters they need. C# developers must pass null values for all the unused parameters in order to accept default behaviors.
.gif)
Figure 28. You can create your own custom sorting lists, and then refer to these specific sort orders from your code.
Clicking the Sort link on the Range Class sample 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:
' Visual Basic
Private Sub DemoSort()
Dim rng As Excel.Range = ThisApplication.Range("Fruits")
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
// C#
private void DemoSort()
{
Excel.Range rng = ThisApplication.
get_Range("Fruits", Type.Missing);
rng.Sort(rng.Columns[1, Type.Missing],
Excel.XlSortOrder.xlAscending,
rng.Columns[2, Type.Missing],Type.Missing,
Excel.XlSortOrder.xlAscending,
Type.Missing, Excel.XlSortOrder.xlAscending,
Excel.XlYesNoGuess.xlNo, Type.Missing, Type.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:
' Visual Basic
Private Sub ResetSort()
Dim rng As Excel.Range = ThisApplication.Range("Fruits")
rng.Sort(rng.Columns(2), OrderCustom:=6, _
Orientation:=Excel.XlSortOrientation.xlSortColumns, _
Header:=Excel.XlYesNoGuess.xlNo)
End Sub
// C#
private void ResetSort()
{
Excel.Range rng = ThisApplication.
get_Range("Fruits", Type.Missing);
rng.Sort(rng.Columns[2, Type.Missing],
Excel.XlSortOrder.xlAscending,
Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending,
Type.Missing, Excel.XlSortOrder.xlAscending,
Excel.XlYesNoGuess.xlNo, 6, Type.Missing,
Excel.XlSortOrientation.xlSortColumns,
Excel.XlSortMethod.xlPinYin,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal);
}
What's Next?
Although this document seems long, it barely scratches the surface of the richness provided by the Excel object model. This document has introduced the most important classes —Application, Workbook, Worksheet, and Range —but hasn't described 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, including PivotTable and Chart, for example. 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're willing to do the research to find the exact class you need. Armed with the contents of this document, the Object Browser, and the Excel VBA online help, you should be able to tackle just about any task you can imagine in Excel.