Key Elements of the Programming Model

[Previous] [Next]

To conclude our introduction to the Spreadsheet component, I'll cover the key elements of the control's programming model so that you'll know how to get the control working and where to go when you want to write script for different functionality. This section is not a full reference to the programming model—that would be a book unto itself. Instead, the properties and methods you'll commonly use are presented here, along with a brief description of each. For more information on any part of the programming model, refer to the online help in the Msowcvba.chm file, which you can find in the local folder under the Office folder (generally Program Files\Microsoft Office\Office\1033 for English-speaking people).

If you are at all familiar with the Excel programming model, you will notice that the Spreadsheet component's programming model is quite similar.

Working with Enumerations in Script

COM and OLE Automation enable components to define enumerations, each of which is a named set of constant values that acts like a type declaration in a programming language. A property or method argument can be typed as an enumeration, which causes environments such as Visual Basic and Microsoft Visual C++ to display the statement completion drop-down list containing the valid constants for that enumeration. The Office Web Components, like many other ActiveX controls, have a predefined set of enumerations.

However, in scripting environments such as a web page or an ASP page, no notion of types exists. Therefore, no ability to use enumeration member names in script languages exists since these languages have no way of knowing what a particular constant, such as ssHAlignLeft, evaluates to. This means your VBScript or ECMA Script code can get littered with magical numbers that are not self-describing.

To alleviate this problem, all the Office Web Components have a top-level property called Constants that can be used in scripting languages to access their various enumeration members. For example, if you want to use ssHAlignLeft in VBScript code to align a cell's contents to the left, you can write code like this:

 MyRange.HAlignment = Spreadsheet1.Constants.ssHAlignLeft 

To use an enumeration constant, you refer to it as though it were a property of the Constants object, and the object will return the correct enumeration member value.

Note that it's useful to set a variable equal to the Constants object if you plan on using it in more than one line of your function, both to avoid unnecessary typing and for better performance. For example, you can write something like this:

 Set c = Spreadsheet1.Constants MyRange.HAlignment = c.ssHAlignRight MyRange.VAlignment = c.ssVAlignBottom 

This Constants object is meant only for scripting languages that have no concept of enumerations. If you are writing code in Visual Basic, VBA, or C++, simply use the enumerations as you normally would and ignore the Constants object.

Getting Data into the Component

I discussed a number of techniques for getting data into the Spreadsheet component earlier. I'll describe those that involve the programming model in more detail here. Table 2-1 lists the relevant properties and methods for loading data, all of which are exposed from the Spreadsheet object's interface.

Table 2-1. Properties and methods for loading data.

Property or Method	Description
Spreadsheet.DataType	A string-based property that tells the Spreadsheet component which of the properties to use for loading data if more than one is set. The value to which you set this property is the name of the other property you want it to use—for example, setting it to HTMLURL will cause the component to load the data returned from the URL in the HTMLURL property.
Spreadsheet.HTMLData	A string-based property that can be used to get or set the spreadsheet's contents in an HTML table format. The format also contains extra attributes and XML tags that are used to keep information that is necessary to reconstruct the spreadsheet model (such as a cell formula) but that is not part of the HTML 3.2 table format. You can set this property to a string containing an HTML table, or you can get the value of this property to obtain the entire contents of the spreadsheet when necessary for persistence.
Spreadsheet.HTMLURL	A string-based property that contains a URL from which to load the spreadsheet. The URL must return an HTML document with a table in it. A spreadsheet saved in HTML format from Excel 2000 can be loaded using this property, or this property can refer to an ASP page or a CGI program that builds tables from a database on the fly.
Spreadsheet.CSVData	A string-based property similar to the HTMLData property, except that the format of the data it accepts or returns is CSV. This property is useful when trying to load data from older systems that aren't capable of emitting HTML.
Spreadsheet.CSVURL	A string-based property similar to the HTMLURL property, except that the format it expects from the URL is again CSV. Just as in the HTMLURL case, this URL can be an ASP page or a CGI program that gets data from a mainframe or minicomputer and returns it to the caller.
Spreadsheet.LoadText	A method used to load a delimited text file into the spreadsheet. Unlike the CSV format, the text file format can use any set of field delimiters. LoadText and its cousin ParseText are also available for the Range object for loading text into a specific range of the spreadsheet. LoadText can refer to a file in your file system or to a URL.

Working with Ranges

The most commonly used programming interface in the Spreadsheet component is that of the Range object. Range objects are returned from many methods and are used whenever you want to modify the contents, formatting, sort order, or filter settings of a range of cells. Table 2-2 shows the properties and methods of the Range object that you should know about when building solutions with the Spreadsheet component.

Table 2-2. Principal Range object properties and methods.

Property or Method	Description
Spreadsheet.Range	This method returns a Range object given a range reference (such as A1:B2 or A:B). Because a range can be just one cell, you can also pass a single cell reference (such as A1). The Range method can also take two different cell references and return a bounding range.
Range.Address	This property returns the address of the range (for example, A1:B2).
Range.Cells	I was highly confused by this property when I first saw it because it's typed to return another Range object. However, you can use this property as a collection of cells, meaning that you can access the individual cells using a For Each loop. The property can also be accessed as a two-dimensional array. For example, MyRange.Cells(1,3).Value will return the value from row 1, column 3 in the range. There is also a Cells property for the Spreadsheet and Worksheet objects, so you can use it instead of the Range method (described earlier) to access specific cells.
Range.Column, Range.Row	These properties indicate the number of the first column and first row in the range. They are useful when you're iterating over a range of columns or rows and want to know what column or row you're at.
Range.Columns, Range.Rows	Although strikingly similar in name to the previous two properties, this duo returns a collection of columns or rows contained in the Range object. Range.Columns.Count and Range.Rows.Count tell you the number of columns and rows in the current range.
Range.HTMLData	This property is similar to Spreadsheet.HTMLData, except that it is read-only for the Range object. Use it to quickly get an HTML table representation of the data in a given range.
Range.Value	This property gets or sets a variant value for the range. Although the help file states that Range.Value returns a two-dimensional array of variants if the range constitutes more than one cell, it actually isn't implemented in this version. However, Range.Value can accept a two-dimensional array of variants for putting data into the range. Use this property when you want to set the cell or cells to a literal value or when you want to get a cell's current unformatted value.
Range.Formula	This property is used to read or write the formula string for a cell. Use it when you want to get or set the formula for a cell or cells in a range, and remember to use the equals sign (=) at the beginning of the formula.
Range.Text	The Range.Text property returns the formatted version of the Range.Value property. It is useful when you need to present the formatted value in a message box or another user interface element, and it's the value you pass when adding AutoFilter criteria.

Formatting

Once you have loaded data into the spreadsheet, you might want to apply formatting programmatically. Each cell can have its own font, alignment, border, color, and number formatting, and all these aspects are set using the properties shown in Table 2-3.

Table 2-3. Formatting properties.

Property	Description
Range.NumberFormat	A string-based property that controls the formatting used for a cell's numeric value. A number of built-in formats that you can use by name exist (such as Currency). You can also construct your own format definitions (for example, #,###, which makes 1000 appear as 1,000).
Range.Font	A property that returns the common Font object used by many of the components. Range.Font lets you set various properties of the Font object such as Name, Size, Bold, Italic, Color, and Underline. Note that you can use the Internet Explorer color names with the Font.Color property if you want.
Range.Halignment, Range.Valignment	Two properties that control the horizontal and vertical text alignment within the range's cells. Enumerations that contain the possible alignment values are defined for each of these properties.
Range.Borders	A property returning the Borders object that lets you set the various aspects of each cell border, such as line weight, line style, and line color.

Component-Level Appearance and Behavior

A number of properties and methods affect the entire Spreadsheet component. Table 2-4 shows the most interesting ones for custom solutions.

Table 2-4. Principal properties that affect the spreadsheet as a whole.

Property	Description
Spreadsheet.AllowPropertyToolbox	Controls whether the Property Toolbox can be shown. If AllowPropertyToolbox is set to False, the Property Toolbox toolbar icon and context menu command are disabled. You use this property any time you want to disable the default formatting user interface and supply your own.
Spreadsheet.AutoFit	Determines whether the control is in AutoFit mode. See the section earlier on AutoFit for more details on how this feature works.
Spreadsheet.Dirty	Tells you if anything has changed in the control. If any cell has been modified, this property returns True. You commonly use the Dirty property to determine whether you need to save the contents in some way. Note that this is a read/write property, so you can also reset it to make the spreadsheet "clean" again.
Spreadsheet.DisplayColHeaders, Spreadsheet.DisplayRowHeaders	Control whether the column and row headers are displayed. These two properties are True by default. Normally, you set them to False if you want to have total control over the spreadsheet surface from your code.
Spreadsheet.DisplayGridlines	Controls whether gridlines are displayed. By default they are, and it's common to turn them off for custom solutions that use borders in specific places where you want cell separator lines.
Spreadsheet.DisplayPropertyToolbox	Controls whether the Property Toolbox is displayed. Set this to True to display it or False to hide it.
Spreadsheet.DisplayTitleBar	Controls whether the title bar is displayed. The title bar is displayed by default. Use the TitleBar property described below to modify the contents and formatting of the title bar.
Spreadsheet.DisplayToolbar	Controls whether the toolbar is displayed. The toolbar is displayed by default.
Spreadsheet.EnableAutoCalculate	Controls how the spreadsheet model recalculates. If this property is set to False, the spreadsheet model will not automatically recalculate; you must call the Calculate method of the Worksheet object to see new results for changed inputs. This property can be useful if you plan to make many changes to a set of inputs and want to recalculate the model only when you're done with all the changes. By default, this property is True—models will automatically recalculate when changed.
Spreadsheet.ScreenUpdating	By default, the screen display of the spreadsheet always reflects the most current data, but you can set this property to False if you plan to perform a number of operations and don't want the spreadsheet to flicker after each one. Setting this property back to True causes a full repaint.
Spreadsheet.Selection	Returns the currently selected object. You can use the TypeName function in VBA or VBScript to determine what type of object it is.
Spreadsheet.TitleBar	Gives you access to the Spreadsheet control's title bar, which you can change the text and formatting of.
Spreadsheet.ViewableRange	Controls what part of the spreadsheet is actually visible. See the earlier discussions of ViewableRange and AutoFit for more details on how this works.

Sorting and Filtering

Table 2-5 lists the properties and methods you will use when sorting and filtering data in the Spreadsheet component.

Table 2-5. Properties and methods for sorting and filtering.

Property or Method	Description
Range.Sort	This method sorts the range given a column and sort direction.
Worksheet.AutoFilter	This property returns the AutoFilter object that can be used to set up the details of a current filter.
AutoFilter.Filters	This property returns the Filters collection for the current AutoFilter range. One Filter object applies to each column in the AutoFilter range, and the index of the Filter object matches the column index in the range.
AutoFilter.Apply	This method applies a new AutoFilter. After you've set up the criteria, you must call this method to actually apply the filter.
Criteria.FilterFunction	This property controls whether the criteria is included in the filter or excluded from it. Include filters include exactly the items in the criteria set, while exclude filters exclude the items in the criteria set but include everything else.
Criteria.ShowAll	This property determines whether all data will be shown. When set to True, the property resets a filter to show all data. When set to False, assuming there are no filter criteria, it shows no data.
Criteria.Add	This method is used to add new criteria to a filter.
Range.AutoFilter	This method is used to turn AutoFilter on for a given range. Call this method first, and then use the Worksheet.AutoFilter property (described above) to access the filters and set up the criteria.

Protection

If you want to protect part of the spreadsheet so that your users cannot modify cell contents or change cell formatting, you need to work with the properties that control protection. Table 2-6 lists the common protection properties and gives a brief description of how each is used.

Note that the protection settings apply to user interactions through the user interface and to operations performed in code. If you want to delete rows while a protection option is enabled, you must set the Enabled property of the Protection object to False before performing the operation, and then set it back to True to return to the protected state.

Table 2-6. Common protection properties.

Property	Description
Worksheet.Protection	Returns the Protection object for which you set the various protection options that enable global actions such as inserting or deleting rows.
Protection.Enabled	Controls whether protection in general is enabled. To use the protection options or lock cells, first set the option or lock the cells and then set this property to True. You can set this property to False to temporarily disable protection while you perform operations in code.
Protection.AllowInsertingColumns, Protection.AllowInsertingRows, Protection.AllowDeletingColumns, Protection.AllowDeletingRows	Enable or disable the ability to insert or delete columns or rows in the spreadsheet. For example, if AllowInsertingRows is set to False, the spreadsheet will disable all commands that can be used to insert a row, including those in the programming model.
Protection.AllowSizingAllColumns, Protection.AllowSizingAllRows	Enable or disable the ability to resize columns or rows. For example, if AllowSizingAllRows is set to False, the spreadsheet won't allow the user to resize the rows, nor will it let you do so through code.
Protection.AllowSorting	Allows or prohibits the sorting of data in the spreadsheet. Set AllowSorting to False to prohibit users from sorting the contents of any range.
Protection.AllowFiltering	Allows or prohibits the use of the AutoFilter feature. Set this property to False to prohibit users from enabling the AutoFilter feature.

Undo

Table 2-7 lists the relevant properties and methods you will use when controlling the Undo mechanism of the Spreadsheet component.

Table 2-7. Properties and methods for controlling undo.

Property or Method	Description
Spreadsheet.BeginUndo	A method that enables you to treat a number of operations as one undo operation. For example, if you call BeginUndo and then perform three different sorts or change many cells, you can undo all these operations at once.
Spreadsheet.EndUndo	A method that marks the end of your logical undo unit. All operations performed between the BeginUndo call and the EndUndo call will be undone as a single unit.
Spreadsheet.EnableUndo	A property that controls whether the undo feature is available. By default, it is. You might want to temporarily disable this feature to save memory or perform a number of operations in code.

Useful Events

A number of events are exposed from the top-level Spreadsheet object, more than from any other control in the OWC library. Table 2-8 lists several of the key events you likely will want to use when developing custom solutions around the Spreadsheet component.

Nearly all the events in the Spreadsheet control pass a single parameter of type SpreadsheetEventInfo to the event handler. SpreadsheetEventInfo is a COM object that you can use to retrieve all kinds of information about the state of the application when the event was fired, including what was selected, what range was affected, where the mouse was, what keys were pressed, and so on. This mirrors the treatment of event information in the DOM.

The biggest reason to use an object as the parameter is to support cancelable events in JavaScript. Parameters passed to an event in JavaScript are always passed by value unless they are object pointers. In other words, if the OWC team had designed the events with a ReturnValue parameter that the script set to True to cancel the event, it wouldn't work in JavaScript because of the parameter being passed by value. However, if the script sets an object's ReturnValue property to True, the control raising the event will see it. So if you want to cancel an event (most of the events whose names begin with "Before" can be canceled), set the ReturnValue property of the SpreadsheetEventInfo object to False.

Table 2-8. Useful events.

Event	Description
Spreadsheet.Change	Fires any time a change is made to a cell or cells in the spreadsheet. Use the Range property of the SpreadsheetEventInfo object to determine the range affected.
StartEdit, EndEdit, CancelEdit	Raised whenever a cell is about to be edited, was just edited, or just had its edit canceled. You can perform data validation in the EndEdit event and set the ReturnValue property of the SpreadsheetEventInfo object to True to deny the new value. Use the EditData property of the SpreadsheetEventInfo object to get the new value for the cell. Use the StartEdit event to replace a displayed value with another element for editing purposes, such as using a TrueType font for displaying a special symbol instead of a text description.
BeforeCommand, Command	Raised just before and after a command—an action such as sorting; filtering; inserting or deleting rows or columns; showing help; and cutting, copying, or pasting—is processed. See the list of SheetCommandEnum constants in the Msowcvba.chm file or in your object browser for all the possible commands you can catch using these events. Again, set the ReturnValue property to False in the BeforeCommand event if you want to cancel the default behavior for an event. For example, you might want to show your own help page when the user clicks the Help button on the spreadsheet toolbar.

Retrieving Version Information

Sometimes you need to find out the version of the control you're working with so that you can either take advantage of new features or use workaround code to solve problems in an older version. Most software programs have service releases between their major version releases, so you often need to verify that the version your code is talking to is indeed the version you expect.

To help you do so, we added the properties listed in Table 2-9 to every control in the Office Web Components library. You can use them to determine the version of the control you're coding against and take the appropriate action.

Table 2-9. Version information properties for all Office Web Components.

Property	Description
MajorVersion	A long integer value that indicates the major version number of the component. For the Office 2000 release, this number is 9.
MinorVersion	A string-based value that indicates the minor version number of the component. For the Office 2000 release, this number is 0 and will be incremented if any minor releases occur before the next major version release. Note that MinorVersion is a string value in case an "a" release occurs. It's best to perform an equality comparison on this value rather than a greater-than or less-than comparison.
BuildNumber	A string-based value that indicates the build number of the component. The build number is incremented with every build of the component DLL, and the value for the Office 2000 release wasn't yet available at the time of this writing. Again, this is a string value, so it can handle cases in which a letter is added to a version number in the event of a minor release.
Version	A string-based value that returns the entire version number. Use the Version property when displaying the version, but use the other properties for determining whether the version is the one you want.