Microsoft Office Web Components Object Models

Select an object model from the following list.

Chart Workspace Object Model

Data Source Control Object Model

PivotTable List Object Model

Spreadsheet Object Model


Microsoft Office Chart Workspace Examples

Creating Data-Bound Charts

Creating a Chart Using Data Specified in Arrays

Creating a Chart Using Data Specified in Strings

Creating a Chart Using Data From an ADO Recordset

Creating a Chart Using Data From a Microsoft Office Spreadsheet

Creating a Chart Using Data From a Microsoft Office Data Source Control

Using Charts

Locating a Specific Chart Axis

Creating a Chart Containing More Than One Chart Type

Why Is There a Charts Collection?

Why Do Multiple Charts in One Chart Workspace Share the Same Categories?


Declaring and Using Event Procedures in VBScript

You declare event procedures in Visual Basic by using the Private and ByVal keywords and arguments with explicit type declarations, as shown in the following example.

Private Sub ChartSpace1_Click(ByVal ChartEventInfo As OWC.WCChartEventInfo)

This procedure declaration will not work in VBScript because VBScript does not use these keywords and because all arguments are passed as Variant. Instead, you declare event procedures in VBScript simply by using the event name and argument name, as shown in the following example.

Sub ChartSpace1_Click(ChartEventInfo)

The argument name itself is simply a convention in any container. By default, the argument in the preceding examples is called "ChartEventInfo", but you could use any argument name.

Caution Some script editors (including Microsoft Script Editor) do not fill in the argument list when they create an event procedure. To ensure that your event procedure runs correctly, consult the Object Browser or the appropriate event topic in Help, and fill in the argument list yourself.


Using Named Constants in VBScript

You cannot use named constants in VBScript code. The following example works in Visual Basic but does not work in VBScript.

Set valueAxis = ChartSpace1.Charts(0).Axes(chAxisPositionLeft)

VBScript regards the named constant chAxisPositionLeft as just another uninitialized variable, so its value is 0 (zero). Because the actual value of chAxisPositionLeft is –3, this code does not work as expected in VBScript.

The Constants property returns an object that allows VBScript programmers to use named constants. This property applies to each of the top-level container objects (ChartSpace, DataSourceControl, PivotTable, and Spreadsheet). It returns an object that contains all of the named constants available in the Microsoft Office Web Components type library (no matter which object the Constants property is applied to, it always returns the complete set of named constants).

To use named constants in VBScript, you can set an object variable to the object returned by the Constants property and then use that object to qualify the named constants in your code, as shown in the following example.

Set c = ChartSpace1.Constants
Set valueAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft)

You can also use the Constants property directly in an expression if you are using only one constant, as shown in the following example.

Spreadsheet1.ActiveSheet.Export "sstest.xls", Spreadsheet1.Constants.ssExportActionNone

Note You can use the Constants property in Visual Basic, but it is neither required nor recommended. Using the Constants property in containers where it is not required will cause your code to run significantly slower.


Using Microsoft Office Web Components on Forms

You can add Microsoft Office Web Components to a form in Microsoft Visual Basic or Visual Basic for Applications in the same way that you add any other ActiveX control to a form. However, note that the Property Toolbox cannot be displayed from a Microsoft Office Web Component on a modal form. When the user of a modal form clicks the Property Toolbox button on the toolbar of a Microsoft Office Web Component or clicks the Property Toolbox command on the shortcut menu, an error message is displayed. In Visual Basic and Visual Basic for Applications, you can use the ShowModal property to make a form modeless at run time. The same consideration applies to modal forms created in design environments other than those in Microsoft Visual Basic and Microsoft Office.


BeforeCollapse Event

Occurs when the collapse button is clicked on a data page.

Syntax

Private Sub DataSourceControl_BeforeCollapse(DSCEventInfo As DSCEVENTINFO)

DSCEventInfo The DSCEventInfo object that contains information about the event.


BeforeCommand Event

Occurs when a worksheet command is used, before the default command action. This event allows you to programmatically cancel or change the command.

Syntax

Private Sub Spreadsheet_BeforeCommand(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event.

Remarks

Set the SpreadsheetEventInfo object’s ReturnValue property to False to prevent the specified change from occurring.

Use the Command property to determine the command that caused the event to occur. The Command property returns one of the SheetCommandEnum constants. Note that the ssHelp command constant is not supported.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


BeforeExpand Event

Occurs whenever the expand button is clicked on a data page.

Syntax

Private Sub DataSourceControl_BeforeExpand(DSCEventInfo As DSCEVENTINFO)

DSCEventInfo The DSCEventInfo object that contains information about the event.


BeforeFirstPage Event

Occurs before the first set of records is displayed on a banded data page.

Syntax

Private Sub DataSourceControl_BeforeFirstPage(DSCEventInfo As DSCEVENTINFO)

DSCEventInfo The DSCEventInfo object that contains information about the specified event.


BeforeLastPage Event

Occurs before the last set of records is displayed on a banded data page.

Syntax

Private Sub DataSourceControl_BeforeLastPage(DSCEventInfo As DSCEVENTINFO)

DSCEventInfo The DSCEventInfo object that contains information about the specified event.


BeforeNextPage Event

Occurs before the next set of records is displayed on a banded data page.

Syntax

Private Sub DataSourceControl_BeforeNextPage(DSCEventInfo As DSCEVENTINFO)

DSCEventInfo The DSCEventInfo object that contains information about the event.


BeforePreviousPage Event

Occurs before the previous set of records is displayed on a banded data page.

Syntax

Private Sub DataSourceControl_BeforePreviousPage(DSCEventInfo As DSCEVENTINFO)

DSCEventInfo The DSCEventInfo object that contains information about the event.


ButtonClick Event

Occurs whenever the user clicks a navigation button.

Syntax

Private Sub RecordNavigationControl_ButtonClick(navButton As navButtonEnum)

navButton Specifies the button that, when clicked, triggered the ButtonClick event. Can be one of the navButtonEnum constants.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


Calculate Event

Occurs when the specified spreadsheet is recalculated.

Syntax

Private Sub Spreadsheet_Calculate(EventInfo As SpreadsheetEventInfo)

EventInfo The SpreadsheetEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


CancelEdit Event

Occurs whenever the user cancels edit mode.

Syntax

Private Sub Spreadsheet_CancelEdit(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


Change Event

Occurs whenever data in one or more cells changes. Both edits and copy-and paste-operations cause this event to occur.

Syntax

Private Sub Spreadsheet_Change(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event.

Remarks

This event occurs after the EndEdit event; at this point, the data has already been changed and the change cannot be canceled.


Click Event

Occurs whenever the user clicks the specified control.

Syntax

Private Sub Object_Click(EventInfo)

Object A ChartSpace, PivotTable, or Spreadsheet object.

EventInfo The SpreadsheetEventInfo or WCChartEventInfo object that contains information about the event. You cannot use this argument with the PivotTable object.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


Click Event Example

This procedure runs whenever the user clicks the mouse with the pointer positioned over the control named “ChartSpace1”.

Sub ChartSpace1_Click(chinfo)
    If chinfo.AltKey then
        ' Handle alt case here.
    End If
End Sub


Command Event

Occurs as soon as a spreadsheet command finishes running. Use the BeforeCommand event if you want to be able to cancel the default command action.

Syntax

Private Sub Spreadsheet_Command(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event.

Remarks

Use the Command property to determine the command that caused the event to occur. The Command property returns one of the SheetCommandEnum constants. Note that the ssHelp command constant is not supported.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


Current Event

Occurs when a record becomes the current record.

Syntax

Private Sub DataSourceControl_Current(DSCEventInfo As DSCEVENTINF)

DSCEventInfo The DSCEventInfo object that contains information about the event.


DataError Event

Occurs whenever a data error occurs.

Syntax

Private Sub DataSourceControl_DataError(DSCEventInfo As DSCEVENTINFO)

DSCEventInfo The DSCEventInfo object that contains information about the event.


DataPageComplete Event

Occurs when the specified data page finishes loading.

Syntax

Private Sub DataSourceControl_DataPageComplete(DSCEventInfo As DSCEVENTINFO)

DSCEventInfo The DSCEventInfo object that contains information about the specified event.


DataSetChange Event

Occurs whenever a chart workspace is data-bound and the data set changes—for example, when a filter operation takes place. This event also occurs when initial data is available from the data source.

Syntax

Private Sub ChartSpace_DataSetChange()

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


DblClick Event

Occurs whenever the user double-clicks the specified control.

Syntax

Private Sub Object_DblClick(EventInfo)

Object A ChartSpace, PivotTable, or Spreadsheet object.

EventInfo The SpreadsheetEventInfo or WCChartEventInfo object that contains information about the event. This parameter is not used with the PivotTable object.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


EndEdit Event

Occurs whenever the user switches from edit mode on the specified spreadsheet.

Syntax

Private Sub Spreadsheet_EndEdit(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event.

Remarks

Set the SpreadsheetEventInfo object’s ReturnValue property to False to prevent the specified change from occurring.

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


KeyDown Event

Occurs whenever the user presses a key on the keyboard. If the user holds the key down, this event repeats itself at the key-repeat interval that has been set on the user’s computer. Keyboard events occur in the following order: KeyDown, KeyPress, KeyUp.

Syntax

Private Sub Object_KeyDown(EventInfo)

EventInfo The SpreadsheetEventInfo or WCChartEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


KeyPress Event

Occurs whenever the user presses and releases a key on the keyboard. Keyboard events occur in the following order: KeyDown, KeyPress, KeyUp.

Syntax

Private Sub Object_KeyPress(EventInfo)

EventInfo The SpreadsheetEventInfo or WCChartEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


KeyUp Event

Occurs whenever the user releases a key on the keyboard. Keyboard events occur in the following order: KeyDown, KeyPress, KeyUp.

Syntax

Private Sub Object_KeyUp(EventInfo)

EventInfo The SpreadsheetEventInfo or WCChartEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


MouseDown Event

Occurs whenever the user presses a mouse button while the pointer is positioned over the spreadsheet or the chart workspace.

Syntax

Private Sub Object_MouseDown(EventInfo)

EventInfo The SpreadsheetEventInfo or WCChartEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


MouseMove Event

Occurs whenever the user moves the mouse pointer over the chart workspace.

Syntax

Private Sub ChartSpace_MouseMove(ChartEventInfo As WCChartEventInfo)

ChartEventInfo The WCChartEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


MouseOut Event

Occurs whenever the user moves the mouse pointer out of a cell on a spreadsheet.

Syntax

Private Sub Spreadsheet_MouseOut(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event. Use this object’s Range property to return the cell that the pointer was moved out of.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


MouseOver Event

Occurs whenever the user moves the mouse pointer into a cell on the specified spreadsheet.

Syntax

Private Sub Spreadsheet_MouseOut(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event. Use this object’s Range property to return the cell that the pointer was moved into.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


MouseUp Event

Occurs whenever the user releases a mouse button while the pointer is positioned over the spreadsheet or the chart workspace.

Syntax

Private Sub Object_MouseUp(EventInfo)

EventInfo The SpreadsheetEventInfo or WCChartEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


PivotTableChange Event

Occurs whenever a PivotTable list field, field set, or total is added or deleted.

Syntax

Private Sub PivotTable_PivotTableChange(Reason As PivotTableReasonEnum)

Reason Specifies how the PivotTable list changed. Can be one of the PivotTableReasonEnum constants.


Query Event

Occurs whenever a PivotTable list query becomes necessary. The query may not occur immediately; it may be delayed until the new data is displayed.

Syntax

Private Sub PivotTable_Query(Reason As PivotQueryReasonEnum)

Reason Required PivotQueryReasonEnum. Specifies the reason for the query.


QueryComplete Event

Occurs after a PivotTable list query has completed.

Syntax

Private Sub PivotTable_QueryComplete()

Remarks

For information about using events with VBSCript, see Declaring and Using Event Procedures in VBScript.

You should not use the Requery method (or any other method that causes a query) while handling the QueryComplete event.


SelectionChange Event

Occurs whenever the user makes a new selection. The user cannot cancel this event.

Syntax

Private Sub Object_SelectionChange(SpreadsheetEventInfo As SpreadsheetEventInfo)

Object A ChartSpace, PivotTable, or Spreadsheet object.

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event. You can use this argument only with the Spreadsheet object.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.

You can use the Selection property to determine the object type of the current selection, as shown in the following example.

Private Sub PivotTable_SelectionChange()
    If TypeName(PivotTable.Selection) = "PivotTotal" Then
        'Handle selection of a total here
    End If
End Sub


SelectionChanging Event

Occurs whenever the user moves the mouse pointer while he or she is selecting a range. This event does not occur when the user selects a range by using the keyboard. The user cannot cancel this event.

Syntax

Private Sub Spreadsheet_SelectionChanging(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


StartEdit Event

Occurs whenever the user enters edit mode while the mouse pointer is in a cell. Set the SpreadsheetEventInfo object’s ReturnValue property to False to prevent the user from entering edit mode.

Syntax

Private Sub Spreadsheet_StartEdit(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


ViewChange Event

Occurs whenever the user opens a different PivotTable list or changes the spreadsheet view.

Syntax 1 (PivotTable object)

Private Sub PivotTable_ViewChange(Reason As PivotViewReasonEnum)

Reason The PivotViewReasonEnum constant that indicates how the view was changed.

Syntax 2 (Spreadsheet object)

Private Sub Spreadsheet_ViewChange(SpreadsheetEventInfo As SpreadsheetEventInfo)

SpreadsheetEventInfo The SpreadsheetEventInfo object that contains information about the event.

Remarks

For information about using events with VBScript, see Declaring and Using Event Procedures in VBScript.


Add Method

Adds an object to a collection. For more information about how this works with a particular collection, click the name of that collection in the following list.

Criteria

ElementExtensions

GroupingDefs

GroupLevels

LookupRelationships

PageFields

ParameterValues

RecordsetDefs

SchemaFields

SchemaParameters

SchemaRelatedFields

 SchemaRelationships

SchemaRowsources

SublistRelationships

WCAxes

WCCharts

WCDataLabelsCollection

WCDataSources

WCErrorbarsCollection

WCSeriesCollection

WCTrendlines


Add Method (Criteria Object)

Adds an AutoFilter criterion.

Syntax

expression.Add(Criterion)

expression An expression that returns a Criteria object.

Criterion Required String. Specifies the new criterion.


Add Method (Criteria Object) Example

This example turns on the AutoFilter for the range A1:J22, and then it sets filters for columns 1 and 3 and applies the filters.

Spreadsheet1.Range("a1:j22").AutoFilter
Set af = Spreadsheet1.ActiveSheet.AutoFilter
Set filterColumnOne = af.Filters(1)
Set filterColumnThree = af.Filters(3)
filterColumnOne.Criteria.Add "blue"    ' Don't show blue in column 1.
filterColumnOne.Criteria.Add "green"    ' Don't show green in column 1.
filterColumnThree.Criteria.Add "yellow"    ' Don't show yellow in column 3.
af.Apply


Add Method (ElementExtensions Collection)

Creates a new ElementExtension object.

Syntax

expression.Add(ElementID, FailIfThere)

expression An expression that returns an ElementExtensions collection.

ElementID Required String. Specifies the ID tag of an HTML element that will be extended by the new ElementExtension object.

FailIfThere Optional Boolean. If this argument is set to True and the object specified by ElementID already has an element extension, the Add method fails. If this argument is set to False (or not specified), the existing element extension is replaced.


Add Method (GroupingDefs Collection)

Creates a new grouping definition. Returns a GroupingDef object.

Syntax

expression.Add(GroupingDefName, GroupingFieldName, PageFieldName, Index)

expression An expression that returns a GroupingDefs object.

GroupingDefName Required String. Specifies the name of the new grouping definition.

GroupingFieldName Required String. Specifies the name of the grouping field used to create the new definition.

PageFieldName Required String. Specifies the name of the page field used to create the new definition.

Index Optional Variant. Specifies the ordinal position, the name, or the actual object reference for the grouping definition before which the new grouping definition will be added. If you do not specify this argument, the new grouping definition is added at the end of the collection.

Remarks

A recordset definition that represents a grouping level has an object type of GroupingDef, which is derived from the RecordsetDef object representing the recordset definition. As far as control binding is concerned, a recordset defined by a GroupingDef object behaves just like a recordset defined by a RecordsetDef object.


Add Method (GroupLevels Collection)

Creates a new group level. Returns a GroupLevel object.

Syntax

expression.Add(RecordSource, FailIfThere)

expression An expression that returns a GroupLevels object.

RecordSource Required String. Specifies the name of the record source for the new group level.

FailIfThere Optional Boolean. If this argument is set to True and the new group level already exists, the Add method fails. The default value is False.


AddIn Method

Adds an add-in to the specified spreadsheet.

Syntax

expression.AddIn(AddIn)

expression An expression that returns a Spreadsheet object.

AddIn Required Object. Specifies the add-in.


AddNew Method

Creates a new schema row source and a recordset definition. Returns a RecordsetDef object.

Syntax

expression.AddNew(Source, RowsourceType, Name)

expression An expression that returns a RecordsetDefs object.

Source Required String. Specifies command text for a new schema row source of type dscCommandText or dscCommandFile, or for the name of a table, view, or stored procedure to be added as a schema row source. For more information, see the Help topic for the CommandText property.

RowsourceType Optional Variant. Specifies the type of the new schema row source. Can be one of the dscRowsourceTypeEnum constants. The default constant is dscCommandText..

Name Optional Variant. Specifies the name of the recordset definition, page row source, and schema row source created by using the AddNew method. If you do not specify this argument and the value of the RowsourceType argument is dscTable, dscView, or dscProcedure, the new name is the same as the name specified in the Source argument (with numerals automatically appended to it to make it unique, if necessary). If you do not specify this argument and the value of the RowsourceType argument is dscCommandText or dscCommandFile, the new name is "CommandN", where N is one or more appended numerals.

Remarks

Page fields are normally created with names that are unique throughout the page data definition. However, when a page field is added to a recordset definition whose primary page row source is of type dscProcedure, dscCommandText, or dscCommandFile, the page field name must be the same as the schema field name. This can result in multiple page field objects with the same name in the AllPageFields collection. To differentiate between objects with the same name, you can use the expression AllPageFields("RecordsetdefName.PagefieldName") where RecordsetdefName is the name of the recordset definition, and PagefieldName is the name of the page field. Note that page field names themselves cannot contain periods.


AddTotal Method

Creates a new total.

Syntax 1

expression.AddTotal(GroupingDefName, GroupingFieldName, PageFieldName, TotalType, Index)

expression An expression that returns a GroupingDefs object.

GroupingDefName Required String. Specifies the GroupingDef object to be used to create the total.

GroupingFieldName Required String. Specifies the grouping field to be used to create the total.

PageFieldName Required String. Specifies the PageField object to be used to create the total.

TotalType Required dscTotalTypeEnum. Specifies the function to be used to create the total.

Index Optional Variant. Specifies where the new total will be placed in the collection.

Syntax 2

expression.AddTotal(Name, Field, Function)

expression An expression that returns a PivotView object.

Name Required String. Specifies the name of the total.

Field Required PivotField. Specifies the field to be used to create the total.

Function Required PivotTotalFunctionEnum. Specifies the function to be used to create the total.


AddTotal Method Example

This example adds a sum total to the PivotTable list in the active view.

Set view = PivotTable1.ActiveView
Set c = PivotTable1.Constants
Set newTotal = view.AddTotal("myTotal", view.FieldSets("Freight").Fields(0), c.plFunctionSum)
view.DataAxis.InsertTotal newTotal


Apply Method

Applies the specified AutoFilter.

Syntax

expression.Apply

expression An expression that returns an AutoFilter object.

Remarks

You typically use the AutoFilter method to turn on the AutoFilter and add one or more criteria to it; you can then use the Apply method to apply the new filter. For more information, see the example for this topic.


Apply Method Example

This example turns on the AutoFilter for the range A1:J22, sets filters for columns 1 and 3, and then applies the filters.

Spreadsheet1.Range("a1:j22").AutoFilter
Set af = Spreadsheet1.ActiveSheet.AutoFilter
Set filterColumnOne = af.Filters(1)
Set filterColumnThree = af.Filters(3)
filterColumnOne.Criteria.Add "blue"    ' Don't show blue in column 1.
filterColumnOne.Criteria.Add "green"    ' Don't show green in column 1.
filterColumnThree.Criteria.Add "yellow"    ' Don't show yellow in column 3.
af.Apply


AutoFilter Method

Displays or hides the AutoFilter drop-down arrows.

Syntax

expression.AutoFilter

expression An expression that returns a Range object.

Remarks

Do not confuse this method with the AutoFilter property. This method applies to a Range object and turns on the AutoFilter, whereas the AutoFilter property returns the AutoFilter object for a given worksheet.

You typically use this method to turn on the AutoFilter and add one or more criteria to it; you can then use the Apply method to apply the new filter. For more information, see the example for this topic.


AutoFilter Method Example

This example turns on the AutoFilter for the range A1:J22, sets filters for columns 1 and 3, and then applies the filters.

Spreadsheet1.Range("a1:j22").AutoFilter
Set af = Spreadsheet1.ActiveSheet.AutoFilter
Set filterColumnOne = af.Filters(1)
Set filterColumnThree = af.Filters(3)
filterColumnOne.Criteria.Add "blue"    ' Don't show blue in column 1.
filterColumnOne.Criteria.Add "green"    ' Don't show green in column 1.
filterColumnThree.Criteria.Add "yellow"    ' Don't show yellow in column 3.
af.Apply


AutoFitColumns Method

Changes the width of the columns in the specified range to achieve the best fit.

Syntax

expression.AutoFitColumns

expression An expression that returns a Range object.


AutoFitColumns Method Example

This example changes the row height and column width in the range A1:B5 on the spreadsheet to achieve the best fit for the data in range.

Spreadsheet1.Range("a1:b5").AutoFitColumns
Spreadsheet1.Range("a1:b5").AutoFitRows


AutoFitRows Method

Changes the height of the rows in the specified range to achieve the best fit.

Syntax

expression.AutoFitRows

expression An expression that returns a Range object.


AutoFitRows Method Example

This example changes the row height and column width in the range A1:B5 on the spreadsheet to achieve the best fit for the data in range.

Spreadsheet1.Range("a1:b5").AutoFitColumns
Spreadsheet1.Range("a1:b5").AutoFitRows


AutoLayout Method

Resets the specified PivotTable list to a default view configuration.

Syntax

expression.AutoLayout(MaxDataFields)

expression An expression that returns a PivotView object.

MaxDataFields Optional Long. Specifies the maximum number of fields that can be addedto the data axis if the data member is a recordset. If this argument is not specified or is zero (0), there is no limit to the number of fields that will be added.

Remarks

If the PivotTable list is bound to a recordset, a field set is added to the data axis for each field in the recordset.

If the provider is multidimensional, all field sets are removed from the axes. This completely clears the current view.


AutoLayout Method Example

This example resets the active view for the PivotTable list.

PivotTable1.ActiveView.AutoLayout


BeginUndo Method

Specifies the beginning of an undo block. This means that all statements between this call and its corresponding EndUndo method call will be undone by a single call to the Undo method. This makes it possible for you to combine entire macros into one statement that can be easily undone. Undo blocks can be nested.

Syntax

expression.BeginUndo

expression An expression that returns a ChartSpace or Spreadsheet object.


BeginUndo Method Example

This example creates an undo block containing code that sets the number format and font for the active cell.

Spreadsheet1.EnableUndo = True
Spreadsheet1.BeginUndo
    Set ac = Spreadsheet1.ActiveCell
    ac.NumberFormat = "0.###"
    ac.Font.Color = "blue"
    ac.Font.Name = "times new roman"
Spreadsheet1.EndUndo


Calculate Method

Causes the specified worksheet to be recalculated..

Syntax

expression.Calculate

expression An expression that returns a Worksheet object.


Calculate Method Example

This example causes the active worksheet on the spreadsheet to be recalculated.

Spreadsheet1.ActiveSheet.Calculate


Clear Method

ChartSpace object: Deletes every chart in the chart workspace and resets all the formatting to the default values. Deletes every data source in the WCDataSources collection.

Range object: Deletes all formatting and data from the specified range.

Syntax

expression.Clear

expression An expression that returns a ChartSpace or Range object.


ClearContents Method

Deletes all data from the specified range.

Syntax

expression.ClearContents

expression An expression that returns a Range object.


ClearContents Method Example

This example deletes all data from column two on the active worksheet.

Spreadsheet1.ActiveSheet.Columns(2).ClearContents


ClearFormats Method

Deletes all formatting from the specified range.

Syntax

expression.ClearFormats

expression An expression that returns a Range object.


ClearFormats Method Example

This example clears the formatting from all cells on the active worksheet.

Spreadsheet1.ActiveSheet.Cells.ClearFormats


Collapse Method

Causes the specified section to collapse.

Syntax

expression.Collapse

expression An expression that returns a Section object.


Copy Method

Copies the specified range to another range or to the Clipboard.

Syntax

expression.Copy(RangeDest)

expression An expression that returns a Range object.

RangeDest Optional Variant. If you do not specify this argument, the source range is copied to the Clipboard. If this argument is a Range object, the source range is copied to the specified range.


Copy Method Example

This example copies cell C3 to cell G5 on the active worksheet.

Set sht = Spreadsheet1.ActiveSheet
sht.Range("c3").Copy sht.Range("g5")


Cut Method

Cuts the specified range and either moves it to the Clipboard or pastes it into a specified destination range.

Syntax

expression.Cut(RangeDest)

expression An expression that returns a Range object.

RangeDest Optional Variant. If you do not specify this argument, the specified range is sent to the Clipboard. If this argument is a Range object, the source range is moved to the specified range.


Cut Method Example

This example moves the formula, data, and formatting from cell A3 to cell G7 on the active worksheet.

Set sht = Spreadsheet1.ActiveSheet
sht.Range("a3").Cut sht.Range("g7")


Delete Method

Hyperlink object (Syntax 1): Deletes the specified hyperlink.

Any collection in the Applies-To list (Syntax 2): Deletes a single object from the specified collection.

Syntax 1

expression.Delete

expression An expression that returns a Hyperlink object.

Syntax 2

expression.Delete(Index)

expression An expression that returns one of the collections in the Applies-To list.

Index Required Variant. Specifies the name or number of the object to be deleted.


DeleteColumns Method

Deletes the specified number of columns from the specified range.

Syntax

expression.DeleteColumns(NumberOfColumns)

expression An expression that returns a Range object.

NumberOfColumns Optional Long. Specifies the number of columns to be deleted. If you do not specify this argument, all columns in the specified range are deleted.


DeleteColumns Method Example

Deleted columns include the specified range. This example deletes columns A and B from the active worksheet.

Spreadsheet1.ActiveSheet.Range("a1").DeleteColumns 2

This example deletes only column C.

Spreadsheet1.ActiveSheet.Range("c3:d3").DeleteColumns 1

This example deletes columns C and D.

Spreadsheet1.ActiveSheet.Range("c3:d3").DeleteColumns


DeleteRecord Method

Deletes a record from the specified data page.

Syntax

expression.DeleteRecord

expression An expression that returns a DataPage object.


DeleteRows Method

Deletes the specified rows.

Syntax

expression.DeleteRows(NumberOfRows)

expression An expression that returns a Range object.

NumberOfRows Optional Long. Specifies the number of rows to be deleted. If you do not specify this argument, all rows in the specified range are deleted.


DeleteRows Method Example

Deleted rows include the specified range. This example deletes rows 2 and 3 from the active worksheet.

Spreadsheet1.ActiveSheet.Range("b2").DeleteRows 2

This example deletes only row 2.

Spreadsheet1.ActiveSheet.Range("b2:f5").DeleteRows 1

This example deletes rows 3 and 4.

Spreadsheet1.ActiveSheet.Range("b3:f4").DeleteRows


DeleteTotal Method

Deletes a PivotTotal object from the PivotTotals collection. You can delete only user-defined totals.

Syntax

expression.DeleteTotal(Total)

expression An expression that returns a PivotView object.

Total Required Variant. Specifies the name or number of the total.


EndUndo Method

Specifies the end of an undo block. This means that all statements between this call and its corresponding BeginUndo method call will be undone by a single call to the Undo method. This makes it possible for you to combine entire macros into one statement that can be easily undone. Undo blocks can be nested.

Syntax

expression.EndUndo

expression An expression that returns a ChartSpace or Spreadsheet object.


EndUndo Method Example

This example creates an undo block containing code that sets the number format and font for the active cell.

Spreadsheet1.EnableUndo = True
Spreadsheet1.BeginUndo
    Set ac = Spreadsheet1.ActiveCell
    ac.NumberFormat = "0.###"
    ac.Font.Color = "blue"
    ac.Font.Name = "times new roman"
Spreadsheet1.EndUndo


Eval Method

Evaluates an expression and returns the result.

Syntax

expression.Eval(Formula)

expression An expression that returns a Worksheet object.

Formula Required String. The expression to be evaluated.


Eval Method Example

This example sets a variable to the sum of the values in the range A1:A10.

s = Spreadsheet1.ActiveSheet.Eval("=sum(a1:a10)")


Execute Method

Returns a Recordset object that represents the recordset resulting from a recordset definition or grouping definition. If the recordset is part of a hierarchy of chaptered recordsets, the data source control creates the entire hierarchy and returns the requested recordset from the hierarchy.

Syntax

expression.Execute(RecordsetName, ExecuteOption, FetchType)

expression An expression that returns a DataSourceControl object.

RecordsetName Required String. The name of the recordset definition or grouping definition.

ExecuteOption Optional ADO ExecuteOptionEnum. Specifies how the query is performed. The default value is adOptionUnspecified.

FetchType Optional dscFetchTypeEnum. If the specified recordset definition contains sublist child recordset definitions, setting this argument to dscFull causes all records to be fetched whenever the Execute method is called. If you do not specify this argument or you set it to dscParameterized, records in child recordsets are fetched only when the child recordset is opened for a given parent. For example, if you have a Customers parent with an Orders child and this argument is set to dscFull, all records for all customers are fetched at run time. If this argument is set to dscParameterized, recordsets for customers with Customer ID 'ANTAR' are fetched only when the Customers recordset is positioned on this Customer ID and the child Orders recordset is opened.


Expand Method

Causes the specified section to expand.

Syntax

expression.Expand

expression An expression that returns a Section object.


Export Method

PivotTable object (Syntax 1): Saves the specified PivotTable list to a file and optionally opens it in Microsoft Excel.

Worksheet object (Syntax 2): Saves the specified worksheet to a file and optionally opens it in Excel.

Syntax 1 (PivotTable object)

expression.Export(FileName, Action)

expression An expression that returns a PivotTable object.

FileName Optional String. Specifies the file name of the saved file. If you do not specify this argument, a temporary file is created in the user's Temp folder.

Action Optional PivotExportActionEnum constant. Specifies what happens after the PivotTable list is saved to a file. If you do not specify this argument, the PivotTable list is opened in Excel. If Excel is not installed on the user's machine, an alert is displayed.

Syntax 2 (Worksheet object)

expression.Export(FileName, ExportAction)

expression An expression that returns a Worksheet object.

FileName Optional String. Specifies the file name of the saved file. If you do not specify this argument, a temporary file is created in the user's Temp folder. You must specify this argument if the ExportAction argument is set to ssExportActionNone.

ExportAction Optional SheetExportActionEnum constant. Specifies whether or not the worksheet is saved to a file. If you do not specify this argument, the worksheet is opened in Excel. If Excel is not installed on the user's machine, an alert is displayed.


ExportPicture Method

Saves the specified chart workspace or PivotTable list as a graphics file.

Syntax

expression.ExportPicture(FileName, FilterName, Width, Height)

expression An expression that returns a ChartSpace or PivotTable object.

FileName Optional String. Specifies the name of the saved file. If you do not specify this argument, the default file name is “Chart.gif” for a chart workspace or “Pivot.gif” for a PivotTable list.

FilterName Optional String. Specifies the name of the graphics filter that is used. Note that the only supported filter name is “GIF”.

Width Optional Long. Specifies the width of the graphic, in pixels. You must specify this argument for server-side charts.

Height Optional Long. Specifies the height of the graphic, in pixels. You must specify this argument for server-side charts.


ExportPicture Method Example

This example saves the chart workspace as a graphics file.

ChartSpace1.ExportPicture "sales.gif", width:=320, height:=240


Find Method

Finds specific information in a given range and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found. Does not affect either the selection or the active cell.

Syntax

expression.Find(What, After, FindLookIn, FindLookAt, SearchOrder, SearchDirection, MatchCase)

expression An expression that returns a Range object.

What Required Variant. The data to be searched for.

After Optional Variant. Specifies the cell after which the search begins. If this argument is not specified, the search begins after the active cell.

FindLookIn Optional SheetFindLookInEnum constant. The default constant is ssValues.

FindLookAt Optional SheetFindLookAtEnum constant. The default constant is ssPart.

SearchOrder Optional SheetSearchOrderEnum constant. The default constant is ssByRows.

SearchDirection Optional SheetSearchDirectionEnum constant. The default constant is ssNext.

MatchCase Optional Boolean. The default value is False.


Find Method Example

This example finds all occurrences of the number “3” in the range A1:F10 and makes those cells bold.

Set c = Spreadsheet1.Constants
Set findRange = Spreadsheet1.Range("a1:f10")
Set foundCell = findRange.Find(3, Spreadsheet1.Range("a1"), c.ssValues, c.ssWhole)
Do While Not foundCell Is Nothing
    foundCell.Font.Bold = True
    Set foundCell = findRange.Find(3, foundCell)
Loop


FlipHorizontal Method

Flips all of the series in the specified chart horizontally.

Syntax

expression.FlipHorizontal

expression An expression that returns a WCPlotArea object.


FlipHorizontal Method Example

This example flips all of the series in the specified chart horizontally.

ChartSpace1.Charts(0).PlotArea.FlipHorizontal


FlipVertical Method

Flips all of the series in the specified chart vertically.

Syntax

expression.FlipVertical

expression An expression that returns a WCPlotArea object.


FlipVertical Method Example

This example flips all of the series in the specified chart vertically.

ChartSpace1.Charts(0).PlotArea.FlipVertical


Follow Method

Displays a cached document if it has already been downloaded. Otherwise, this method resolves the hyperlink, downloads the target document, and then displays the document.

Syntax

expression.Follow(NewWindow)

expression An expression that returns a Hyperlink object.

NewWindow Optional Boolean. True to display the target document in a new window. The default value is False.


Follow Method Example

This example resolves the hyperlink in cell B15 on the active worksheet, downloads the target document, and then displays the document.

Spreadsheet1.ActiveSheet.Range("b15").Hyperlink.Follow


FreezePanes Method

Freezes panes surrounding the specified range.

Syntax

expression.FreezePanes(FreezeDirection)

expression An expression that returns a Range object.

FreezeDirection Required SheetFreezePanesEnum constant.


FreezePanes Method Example

This example splits the worksheet window at row 5 and freezes the pane above the split.

Set c = Spreadsheet1.Constants
Spreadsheet1.Range("b5").FreezePanes c.ssFreezeTop


GetContainingSection Method

Returns the containing section for the specified HTML element.

Syntax

expression.GetContainingSection(Element)

expression An expression that returns a DataSourceControl object.

Element Required Object. Represents the specified HTML element.


GetValue Method

Returns a data value for the specified point.

Syntax

expression.GetValue(Dimension)

expression An expression that returns a WCPoint object.

Dimension Required ChartDimensionsEnum. Specifies the type of data to be returned for the specified point.


InsertColumns Method

Inserts the specified number of columns immediately to the right of the specified range.

Syntax

expression.InsertColumns(NumberOfColumns)

expression An expression that returns a Range object.

NumberOfColumns Optional Long. Specifies the number of columns to be inserted. If you do not specify this argument , only one column is inserted.


InsertColumns Method Example

This example inserts one column to the right of column C and inserts two rows below row 3.

Spreadsheet1.Range("c3").InsertColumns
Spreadsheet1.Range("c3").InsertRows 2


InsertFieldSet Method

Inserts a field set on the specified axis.

Syntax

expression.InsertFieldSet(FieldSet, Before, Remove)

expression An expression that returns a PivotAxis, PivotDataAxis, PivotFilterAxis, or PivotGroupAxis object.

FieldSet Required PivotFieldSet object. Specifies the field set to be inserted.

Before Optional Variant. Specifies the index of the field set before which the inserted field set will be placed.

Remove Optional Boolean. This argument is reserved for future use, and its value is always True. When the field set is added to the specified axis, it is removed from any other axis.


InsertRows Method

Inserts the specified number of rows immediately below the specified range.

Syntax

expression.InsertColumns(NumberOfRows)

expression An expression that returns a Range object.

NumberOfRows Optional Long. Specifies the number of rows to be inserted. If you do not specify this argument, only one row is inserted.


InsertRows Method Example

This example inserts one column to the right of column C and inserts two rows below row 3.

Spreadsheet1.Range("c3").InsertColumns
Spreadsheet1.Range("c3").InsertRows 2


InsertTotal Method

Adds a PivotTotal object to the PivotTotals collection.

Syntax

expression.InsertTotal(Total, Before)

expression An expression that returns a PivotDataAxis object.

Total Required PivotTotal object. Specifies the total to be inserted.

Before Optional Variant. Specifies the index of the total before which the inserted total will be placed. If you do not specify this argument, the total is inserted at the end of the collection.

Remarks

If the PivotTotal object is currently part of the PivotTotals collection, the object is first removed from that collection and then reinserted into it. This changes the display order because totals are displayed in their collection order.


Intersect Method

Returns a Range object that represents the rectangular intersection of two specified ranges.

Syntax

expression.Intersect(Range1, Range2)

expression An expression that returns a Spreadsheet object.

Range1, Range2 Required Range objects. The intersecting ranges.


Intersect Method Example

The function in this example returns True if the passed-in cell is inside the visible range.

Function IsCellVisible(testCell)
    Set vr = Spreadsheet1.ActiveSheet.VisibleRange
    Set ir = Spreadsheet1.Intersect(testCell, vr)
    'if the testCell is not in the visible range,
    'the intersection will be nothing
    IsCellVisible = Not ir Is Nothing
End Function


IsButtonEnabled Method

Returns True if the specified navigation button is enabled.

Syntax

expression.IsButtonEnabled(navbtn)

expression An expression that returns a RecordNavigationControl object.

navbtn Required navButtonEnum. Specifies the navigation button.


Load Method

Loads XML chart data from a URL or local file. The file must consist of only charting XML, and it cannot contain any leading HTML. The string must begin with <script language="XML"> and end with </script>.

Syntax

expression.Load(FileName)

expression An expression that returns a ChartSpace object.

FileName Required String. Specifies the file containing XML chart data.


LoadText Method

Loads and parses the specified text file as a new range or spreadsheet.

Syntax

expression.LoadText(File, Delimiters, ConsecutiveDelimAsOne, TextQualifier)

expression An expression that returns a Range or Spreadsheet object.

File Required String. Specifies the name of the text file.

Delimiters Optional String. Specifies the field delimiters.

ConsecutiveDelimAsOne Optional Boolean. True to have consecutive delimiters considered as a single delimiter. The default value is False.

TextQualifier Optional String. Specifies the text qualifier. The default value is the double quotation mark character.


LoadText Method Example

This example loads a tab-delimited text file.

Spreadsheet1.LoadText "tabfile.txt", Chr$(9)


MakeCurrent Method

Makes the specified section the current section.

Syntax

expression.MakeCurrent

expression An expression that returns a Section object.


Merge Method

Creates a merged cell from the specified range.

Syntax

expression.Merge

expression An expression that returns a Range object. This method fails if the range only partially encloses a previously merged cell.

Remarks

When you create a merged cell, the value in the upper-left cell in the specified range is used for the merged cell value. All other cell values in the merged cell are ignored.


Merge Method Example

This example creates a merged cell from the range B2:C5 and puts a thick red border around the merged cell.

Set c = Spreadsheet1.Constants
Spreadsheet1.Range("b2:c5").Merge
Spreadsheet1.Range("b2").MergeArea.Borders.Color = "red"
Spreadsheet1.Range("b2").MergeArea.Borders.Weight = c.owcLineWeightThick


MoveFirst Method

Moves to the first record in the data page recordset. This method fails if the current record is the first record in the recordset.

Syntax

expression.MoveFirst

expression An expression that returns a DataPage object.


MoveLast Method

Moves to the last record in the data page recordset. This method fails if the current record is the last record in the recordset.

Syntax

expression.MoveLast

expression An expression that returns a DataPage object.


MoveNext Method

Moves to the next record in the data page recordset. This method fails if the current record is the last record in the recordset.

Syntax

expression.MoveNext

expression An expression that returns a DataPage object.


MovePrevious Method

Moves to the previous record in the data page recordset. This method fails if the current record is the first record in the recordset.

Syntax

expression.MovePrevious

expression An expression that returns a DataPage object.


NewRecord Method

Adds a new record to the data page recordset. This method fails if the recordset cannot be updated.

Syntax

expression.NewRecord

expression An expression that returns a DataPage object.


Offset Method

Returns a Range object that represents a range that is offset from the specified range.

Syntax

expression.Offset(NumberOfRows, NumberOfColumns)

expression An expression that returns a Range object.

NumberOfRows Optional Long. The number of rows (positive, negative, or zero (0)) by which the range is to be offset. The default value is zero.

NumberOfColumns Optional Long. The number of columns (positive, negative, or zero) by which the range is to be offset. The default value is zero.


Offset Method Example

This example sets the font for the contents of the cell that is one column to the right of the active cell.

Spreadsheet1.ActiveCell.Offset(0, 1).Font.Bold = True


ParseText Method

Parses the specified text string and places the result in the specified range.

Syntax

expression.ParseText(Text, Delimiters, ConsecutiveDelimAsOne, TextQualifier)

expression An expression that returns a Range object.

Text Required String. Specifies the string to be parsed.

Delimiters Optional String. Specifies the field delimiters.

ConsecutiveDelimAsOne Optional Boolean. True to have consecutive delimiters considered as one delimiter. The default value is False.

TextQualifier Optional String. Specifies the text qualifier. The default value is the double quotation mark character.


ParseText Method Example

This example parses the specified string into the range starting at cell A1.

Spreadsheet1.Range("a1").ParseText "name, address, city, state", ","


Paste Method

Pastes the Clipboard contents into the specified range.

Syntax

expression.Paste

expression An expression that returns a Range object.


Paste Method Example

This example pastes the contents of cell A1 into cells A3, A5, and A7.

Spreadsheet1.Range("a1").Copy
For rw = 3 To 7 Step 2
    Spreadsheet1.Cells(rw, 1).Paste
Next


RangeFromPoint Method

Returns the most detailed object at the specified location in a chart workspace. For example, if you specify the x and y coordinates of a point, the corresponding WCPoint object is returned, even though a series also exists at those coordinates.

Syntax

expression.RangeFromPoint(X, Y)

expression An expression that returns a ChartSpace object.

X Required Integer. Specifies the x coordinate of the object.

Y Required Integer. Specifies the y coordinate of the object.


Refresh Method

Refreshes the specified object (reloads source data or repaints the object).

Syntax

expression.Refresh

expression An expression that returns an object in the Applies To list.


Refresh Method Example

This example causes the chart workspace to repaint itself.

ChartSpace1.Refresh

This example causes a data source control-bound chart to reload its data.

ChartSpace1.ChartDataSources(0).Refresh


RemoveFieldSet Method

Removes a field set from the specified axis.

Syntax

expression.RemoveFieldSet(FieldSet)

expression An expression that returns a PivotDataAxis, PivotFilterAxis, or PivotGroupAxis object.

FieldSet Required Variant. Specifies the field set to be removed. Can be a PivotFieldSet object, a field set name, or a field set index number.


RemoveTotal Method

Removes a total from the specified data axis.

Syntax

expression.RemoveTotal(Total)

expression An expression that returns a PivotDataAxis object.

Total Required Variant. Specifies the total to be removed. Can be a PivotTotal object, a total’s name, or a total’s index number.


Requery Method

Executes the query that returned the recordset and all related recordsets for the specified object.

Syntax

expression.Requery

expression An expression that returns an object in the Applies To list.


RotateClockwise Method

Rotates all series in the specified chart clockwise in 90-degree increments.

Syntax

expression.RotateClockwise

expression An expression that returns a WCPlotArea object.


RotateClockwise Method Example

This example rotates all series in the specified chart clockwise 90 degrees.

ChartSpace1.Charts(0).PlotArea.RotateClockwise


RotateCounterClockwise Method

Rotates all series in the specified chart counterclockwise in 90-degree increments.

Syntax

expression.RotateCounterClockwise

expression An expression that returns a WCPlotArea object.


RotateCounterClockwise Method Example

This example rotates all series in the specified chart counterclockwise 90 degrees.

ChartSpace1.Charts(0).PlotArea.RotateCounterClockwise


Save Method

Saves the current record to the database associated with the specified data page.

Syntax

expression.Save

expression An expression that returns a DataPage object.


Scroll Method

Scrolls the worksheet to move the specified cell or range of cells to the upper-left corner of the visible range. If there are frozen panes on the worksheet, this method applies only to the scrolling pane.

Syntax

expression.Scroll(Range)

expression An expression that returns a Worksheet object.

Range Required Range object. Specifies the cell or range of cells to be displayed.


Scroll Method Example

This example scrolls through the active worksheet until cell Z100 is displayed.

Spreadsheet1.ActiveSheet.Scroll Spreadsheet1.ActiveSheet.Range("z100")


Select Method

Selects the specified object.

Syntax

expression.Select

expression An expression that returns an object in the Applies To list.


Select Method Example

This example selects the cell that is one column to the right of and in the same row as the active cell.

Spreadsheet1.ActiveCell.Offset(0, 1).Select


SetData Method

Sets data for the specified chart object.

Syntax

expression.SetData(Dimension, DataSourceIndex, DataReference)

expression An expression that returns a WCChart, WCErrorBars, or WCSeries object.

Dimension Required ChartDimensionsEnum or ChartErrorBarCustomValuesEnum object. Specifies the data dimension to be set.

DataSourceIndex Required Long. Specifies the data source as the index to the WCDataSources collection, or specifies chDataLiteral to set data from a literal source. For the WCChart object, you can use chDataLiteral only to set series names and categories.

DataReference Optional Variant. For WCChart and WCSeries objects, this argument specifies the data reference as an Excel-style range reference ("A1:D4" , for example), or a row-set column name. When the DataSourceIndex argument is set to chDataLiteral, you can set DataReference to a one-dimensional array or a tab-delimited string. For WCErrorBars objects, this argument specifies an array of Double or String values you can use for error-bar values. Note that you can use this argument only with custom error bars (the error-bar Type property must be set to chErrorBarTypeCustom).


SetData Method Example

This example creates a chart using literal data arrays.

Dim seriesNames(1)
Dim categories(7)
Dim values(7)

seriesNames(0) = "Satisfaction Data"

categories(0) = "Very Good"
categories(1) = "Good"
categories(2) = "N/A"
categories(3) = "Average"
categories(4) = "No Response"
categories(5) = "Poor"
categories(6) = "Very Poor"

values(0) = 10
values(1) = 22
values(2) = 6
values(3) = 31
values(4) = 5
values(5) = 14
values(6) = 12

Set cht = ChartSpace1.Charts.Add
Set c = ChartSpace1.Constants
cht.Type = c.chChartTypeColumnClustered

cht.SetData c.chDimSeriesNames, c.chDataLiteral, seriesNames
cht.SetData c.chDimCategories, c.chDataLiteral, categories
cht.SeriesCollection(0).SetData c.chDimValues, c.chDataLiteral, values

This example creates a chart that is bound to a spreadsheet. The series name is in cell B1, the category names are in cells A2:A28, and the values are in cells B2:B28.

Set c = ChartSpace1.Constants
Set ChartSpace1.DataSource = Spreadsheet1
ChartSpace1.Charts.Add
ChartSpace1.Charts(0).Type = c.chChartTypeLineMarkers
ChartSpace1.Charts(0).SetData c.chDimSeriesNames, 0, "b1"
ChartSpace1.Charts(0).SetData c.chDimCategories, 0, "a2:a28"
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, 0, "b2:b28"


Show Method

Scrolls the spreadsheet window to move the specified range into view.

Syntax

expression.Show

expression An expression that returns a Range object.

Remarks

Use the Scroll method to move a range to the upper-left corner of the spreadsheet.


ShowAllData Method

Makes all filtered rows visible on the specified worksheet and sets all filters to Show All.

Syntax

expression.ShowAllData

expression An expression that returns a Worksheet object.


ShowAllData Method Example

This example makes all filtered rows visible on the active worksheet and sets all filters to Show All.

Spreadsheet1.ActiveSheet.ShowAllData


Sort Method

Sorts the specified range.

Syntax

expression.Sort(ColumnKey, Order, YesNoGuessHeader)

expression An expression that returns a Range object. If the range contains only one cell, the current region is sorted.

ColumnKey Optional Long. The number of the first sort column. The default value is 1.

Order Optional SheetSortOrderEnum. The sort order. The default value is ssAscending.

YesNoGuessHeader Optional SheetYesNoGuessEnum constant. Use ssYes if the first row contains headers, ssNo if the first row does not contain headers, or ssGuess to have the method determine whether the first row contains headers or not. The default value is ssNo.


Sort Method Example

This example sorts the range A1:F10 in descending order based on column 2.

Set c = Spreadsheet1.Constants
Spreadsheet1.Range("a1:f10").Sort 2, c.ssDescending


Undo Method

For the ChartSpace and Spreadsheet objects, undoes the last single action or the last macro block surrounded by BeginUndo and EndUndo method calls.

For the DataPage object, restores the data access page to the condition before the recordset was edited, and if the edits have not been saved.

Syntax

expression.Undo

expression An expression that returns a ChartSpace, DataPage, or Spreadsheet object.


Undo Method Example

This example undoes the last action or displays a message box if this action cannot be undone.

If Spreadsheet1.CanUndo Then
    Spreadsheet1.Undo
Else
    MsgBox "can't undo last action"
End If


Union Method

Returns a Range object that represents the smallest rectangular range that includes the union of two specified ranges.

Syntax

expression.Union(Range1, Range2)

expression An expression that returns a Spreadsheet object.

Range1 Required Range object.

Range2 Required Range object.

Remarks

The Union method always returns a rectangular range. For example, if you specify A1:A5 and F1:F10, the return value is the rectangular range A1:F10. Also, you cannot use this method to create a range containing noncontiguous areas.


Union Method Example

This example sets a variable to the smallest rectangular range that contains range A1:A5 and range F1:F10.

Set r1 = Spreadsheet1.Range("a1:a5")
Set r2 = Spreadsheet1.Range("f1:f10")
Set ur = Spreadsheet1.Union(r1, r2)


UnMerge Method

Separates the specified merged area into individual cells.

Syntax

expression.UnMerge

expression An expression that returns a Range object.

Remarks

When you separate a merged area, the value in the merged area is placed in the cell in the upper-left corner of the area. All other cells are cleared.


UnMerge Method Example

This example separates the merged area containing cell A1.

Spreadsheet1.Range("a1").UnMerge


UpdatePropertyToolbox Method

Updates the property toolbox from the currently selected object.

Syntax

expression.UpdatePropertyToolbox

expression An expression that returns a Spreadsheet object.


AllGroupingDefs Collection Object

AllGroupingDefs collection (GroupingDef object)

Contains all of the GroupingDef objects in the data source control.

Using the AllGroupingDefs Collection

The DataSourceControl object's AllGroupingDefs property returns an AllGroupingDefs object.


AllPageFields Collection Object

AllPageFields collection (PageField object)

Contains all of the PageField objects in the data source control.

Using the AllPageFields Collection

The DataSourceControl object's AllPageFields property returns an AllPageFields object.


AutoFilter Object

Filters collection (Filter object)

Represents the AutoFilter container. The AutoFilter object contains a Filters collection, the Filters collection contains a Filter object for each column in the filtered range, and each Filter object contains a Criteria object.

Using the AutoFilter Object

The Worksheet object’s AutoFilter property returns the AutoFilter object for the specified worksheet.


Border Object

Represents the border of an object. The Line property returns a Border object that is used as a container for line formatting properties for chart axes, error bars, gridlines, series, and trendlines.

For Range objects, the Border object is a member of the Borders collection.

Using the Border Object

The following properties return a Border object.

The Borders object’s Item property

The ChartSpace object’s Border property

The WCAxis object’s Line property

The WCChart object’s Border property

The WCDataLabels object’s Border property

The WCErrorBars object’s Line property

The WCGridlines object’s Line property

The WCLegend object’s Border property

The WCPlotArea object’s Border property

The WCPoint object’s Border property

The WCSeries object’s Border property

The WCSeries object’s Line property

The WCTitle object’s Border property

The WCTrendline object’s Line property


Borders Collection Object

Borders collection (LineFormat object)

A collection of four Border objects that represent the four borders of a worksheet range.

Using the Borders Collection

The Range object’s Borders property returns a Borders collection.


ChartSpace Object

Represents the chart workspace. The chart workspace is the top-level chart container; it can contain more than one chart, with each chart represented by a WCChart object. When a chart workspace is first created, it is empty (it does not contain any charts). Use the Add method of the WCCharts object to create a new chart.

Using the ChartSpace Object

You can use either the CreateObject method or the New keyword to create a new ChartSpace object.

The object ID for a chart control on an HTML page or a Visual Basic form returns a ChartSpace object.

The programmatic identifier for the ChartSpace object is CLSID:0002E500-0000-0000-C000-000000000046. The following example creates a chart workspace named "ChartSpace1" on an HTML page.

<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:350"></object>


Criteria Object

Filters collection (Filter object)
Criteria object (String)

Contains the entire array of AutoFilter criteria. Each criterion is a String value. The AutoFilter object contains a Filters collection, the Filters collection contains a Filter object for each column in the filtered range, and each Filter object contains a Criteria object.

Using the Criteria Object

The Filter object’s Criteria property returns the Criteria object for the specified filter.


DataPage Object

DataPages collection (DataPage object)

Represents the combination of sections that are shown on a data access page when the expand button is clicked. These sections include a caption section, a group header and group footer section for each record, and a navigation section corresponding to the set of visible records from a recordset within a single parent record.

The DataPage object is a member of the DataPages collection.

Using the DataPage Object

The following properties return a DataPage object:

DataPages object Item property

Section object DataPage property


DataPages Collection Object

DataPages collection (DataPage object)

Contains all DataPage objects in the data source control.

Using the DataPages Collection

The DataSourceControl object's DataPages property returns a DataPages object.


DataSourceControl Object

Represents a data source control. The data source control is the top-level container in the data model.

Using the DataSourceControl Object

You can use either the CreateObject method or the New keyword to create a DataSourceControl object.

The object ID for a data source control on an HTML page returns a DataSourceControl object. The programmatic identifier for the DataSourceControl object is CLSID:0002E530-0000-0000-C000-000000000046. The following example creates a data source control named "MSODSC" on an HTML page.

<object id=MSODSC classid=CLSID:0002E530-0000-0000-C000-000000000046></object>


DSCEventInfo Object

DSCEventInfo object

Contains information about the specified data source control event.

Using the DSCEventInfo Object

The following data source control events use a DSCEventInfo object as their only parameter:

The BeforeCollapse event

The BeforeExpand event

The BeforeFirstPage event

The BeforeLastPage event

The BeforeNextPage event

The BeforePreviousPage event

The DataError event

The DataPageComplete event


ElementExtension Object

ElementExtensions collection (ElementExtension object)

Adds data-related properties to HTML elements such as text boxes and drop-down list boxes. Element extensions tie recordset and grouping definition objects such as page fields to an element on the HTML page that can be bound to their resulting data.

The ElementExtension object is a member of the ElementExtensions collection.

Using the ElementExtension Object

The following method and property return an ElementExtension object:

The ElementExtensions object's Add method

The ElementExtensions object's Item property


ElementExtensions Collection Object

ElementExtensions collection (ElementExtension object)

Contains the ElementExtension objects for the specified data source control.

Using the ElementExtensions Collection

The DataSourceControl object's ElementExtensions property returns an ElementExtensions object.


ExpandControl Object

ExpandControl object

Represents an expand control.

Using the ExpandControl Object

You can use either the CreateObject method or the New keyword to create an ExpandControl object.

The object ID for a data source control on an HTML page returns an ExpandControl object. The programmatic identifier for the ExpandControl object is CLSID:0002E532-0000-0000-C000-000000000046. The following example creates an expand control named "Expand1" on an HTML page.

<object id=Expand1 classid=CLSID:0002E532-0000-0000-C000-000000000046></object>


Filter Object

Filters collection (Filter object)
Criteria collection (Criterion object)

Represents a single filter used with the AutoFilter. The Filter object is a member of the Filters collection. The AutoFilter object contains a Filters collection, the Filters collection contains a Filter object for each column in the filtered range, and each Filter object contains a Criteria object.

Using the Filter Object

The Filters object's Item property returns a Filter object.


Filters Collection Object

Filters collection (Filter object)
Criteria object (String)

Represents the collection of filters used with the AutoFilter. Each filter is represented by a Filter object. The AutoFilter object contains a Filters collection, the Filters collection contains a Filter object for each column in the filtered range, and each Filter object contains a Criteria object.

Using the Filters Object

The AutoFilter object’s Filters property returns a Filters object.


GroupingDef Object

GroupingDefs collection (GroupingDef object)
PageFields collection (PageField object)

Represents a grouping definition. A grouping definition is a special type of recordset definition. As with RecordsetDef objects, you can use a grouping definition's name in the Execute method and in the RecordSource property of a group level or element extension.

A grouping definition defines an ADO grouping recordset that appears as a parent of the containing recordset in the hierarchy or recordsets produced by a page. At run time, all of the data for the containing recordset definition is fetched before the grouping recordset is created.

Using the GroupingDef Object

The following methods and property return a GroupingDef object:

The GroupingDefs object's Add method

The GroupingDefs object's AddTotal method

The GroupingDefs object's Item property


GroupingDefs Collection Object

GroupingDefs collection (GroupingDef object)
PageFields collection (PageField object)

Represents the collection of GroupingDef objects that create grouping parents of the detail records in a recordset definition. Sequence is important: the lower the index, the higher the level of grouping. Index 0 (zero) is the lowest grouping level.

Using the GroupingDefs Collection

The RecordsetDef object's GroupingDefs property returns a GroupingDefs object.


GroupLevel Object

GroupLevels collection (GroupLevel object)

Represents the set of all records at a given level of the data page hierarchy. The GroupLevel object is a member of the GroupLevels collection.

Using the GroupLevel Object

The following method and property return a GroupLevel object:

The GroupLevels object’s Add method

The GroupLevels object’s Item property


GroupLevels Collection Object

GroupLevels collection (GroupLevel object)

Represents the collection of GroupLevel objects for the specified data source control. Each GroupLevel object represents the set of all records at a given level of the data page hierarchy.

Using the GroupLevels Collection

The DataSourceControl object's GroupLevels property returns a GroupLevels object.


Hyperlink Object

Represents a hyperlink.

Using the Hyperlink Object

The Range object’s Hyperlink property returns a Hyperlink object.


Interior Object

Represents the interior of an object.

Using the Interior Object

The following properties return an Interior object.

The ChartSpace object’s Interior property

The Range object’s Interior property

The TitleBar object’s Interior property

The WCChart object’s Interior property

The WCDataLabels object’s Interior property

The WCLegend object’s Interior property

The WCPlotArea object’s Interior property

The WCPoint object’ Interior property

The WCSeries object’s Interior property

The WCTitle object’s Interior property


LookupRelationships Collection Object

LookupRelationships collection (PageRelationship object)

Represents the collection of PageRelationship objects that define a lookup join relationship with a page row source. In the object diagram shown in this topic, the first PageRowsource object (above the LookupRelationships collection) is the object on the “many” side of the one-to-many relationship and the second PageRowsource object(below the collection) is the object on the “one” side.

Using the LookupRelationships Collection

The PageRowsource object's LookupRelationships property returns a LookupRelationships object.


LookupSchemaRelationships Collection Object

LookupSchemaRelationships collection (SchemaRelationship object)

Represents the collection of SchemaRelationship objects for which a single schema row source acts as the “many” side of the one-to-many relationship.

Using the LookupSchemaRelationships Collection

The SchemaRowsource object's LookupSchemaRelationships property returns a LookupSchemaRelationships object.


OWCFont Object

Contains the font attributes (font name, font size, color, and so on) for an object used in a chart workspace or on a spreadsheet. Font attributes for objects used with PivotTable lists are contained in PivotFont objects.

Using the OWCFont Object

The following properties return an OWCFont object.

The Range object’s Font property

The TitleBar object’s Font property

The WCAxis object’s Font property

The WCDataLabel object’s Font property

The WCDataLabels object’s Font property

The WCLegend object’s Font property

The WCLegendEntry object’s Font property

The WCTitle object’s Font property


PageField Object

AllPageFields collection (PageField object)
PageFields collection (PageField object)

Represents a field in a recordset or grouping definition. The page field name is used by a control on a data page to bind to data from a recordset.

PageField objects come in three types:

  • Output. This type comes directly from a schema field in a schema row source. For schema row sources of the table type or view type, this means the field is listed in the SELECT list of the SQL statement generated by the data model. For schema row sources of the text type or stored procedure type, all schema fields appear as output and the page field name must be the same as the schema field name.

  • Calculated. This is a locally calculated column added to a recordset. Visual Basic for Applications expression syntax is supported. Expressions can reference page fields of type dscOutput or dscGrouping within the same recordset definition or grouping definition. Expressions can also reference HTML elements by using the document object model (for example, =Quantity*UnitPrice*document.all("Text0").Value). Calculated fields are recalculated whenever an updated record is saved, whenever the page is refreshed, or whenever the recordset's Resync method is called.

  • Grouping. This is a grouping field or aggregate field attached to a GroupingDef or RecordsetDef object.

The PageField object can be a member of the AllPageFields, GroupingFields, OutputFields, or PageFields collection.

Using the PageField Object

The following properties and method return a PageField object:

The AllPageFields object's Item property

The PageFields object's Add method

The PageFields object's Item property

The PageRelatedField object's ManySide property

The PageRelatedField object's OneSide property


PageFields Collection Object

PageFields collection (PageField object)

The collection of PageField objects describing the fields that will be present in the recordset produced by the specified recordset definition. These fields are a combination of output fields from the page row sources that make up this recordset definition and other page fields that are added to this collection. Sequence within this collection is unimportant, because all controls bind by name rather than by their ordinal relationship to fields in the output recordset.

Using the PageFields Collection Object

The RecordsetDef object's PageFields property returns a PageFields object.


PageRelatedField Object

PageRelatedFields collection (PageRelatedField object)

Represents a page instance of the column pairings that make up a page relationship. The names of these fields are used in generating join clauses in SQL and in relating a parent recordset definition to a child recordset definition.

The PageRelatedField object is a member of the PageRelatedFields collection.

Using the PageRelatedField Object

The PageRelatedFields object's Item property returns a PageRelatedField object.


PageRelatedFields Collection Object

PageRelatedFields collection (PageRelatedField object)

The collection of PageRelatedField objects that belong to a given recordset definition. This collection is automatically filled when a recordset definition based on the SchemaRelatedFields collection is created.

Using the PageRelatedFields Collection

The PageRelationship object's PageRelatedFields property returns a PageRelatedFields object.


PageRelationship Object

LookupRelationships collection (PageRelationship object)
SublistRelationships collection (PageRelationship object)

PageRelatedFields collection (PageRelatedField object)

A PageRelationship object ties two page row sources together, either within a recordset definition (a LookupRelationships object) or between recordset definitions (a SublistRelationships object). A page relationship is created from information in a SchemaRelationship object.

The PageRelationship object is a member of the LookupRelationships or SublistRelationships collection.

Using the PageRelationship Object

The following methods and properties return a PageRelationship object:

The LookupRelationships object's Add method

The LookupRelationships object's Item property

The SublistRelationships object's Add method

The SublistRelationships object's Item property


PageRowsource Object

LookupRelationships collection (PageRelationship object)
PageFields collection (PageField object)

A PageRowsource object refers to an instance of a SchemaRowsource object that is currently in use on a data page. A table, view, or stored procedure must be in the data model as a schema row source before it can be added as a page row source; the page row source is then used as a data source for the page.

The PageRowsource object is a member of the PageRowsources collection.

Using the PageRowsource Object

The following properties return a PageRowsource object:

The PageField object's PageRowsource property

The PageRelationship object's ManySide property

The PageRelationship object's OneSide property

The PageRowsources object's Item property

The RecordsetDef object's PrimaryPageRowsource property


PageRowsources Collection Object

PageRowSources collection (PageRowSource object)
LookupRelationships collection (PageRelationship object)
PageFields collection (PageField object)

The collection of PageRowsource objects in the data model.

Using the PageRowsources Collection Object

The RecordsetDef object's PageRowsources property returns a PageRowsources object.


Pane Object

Panes collection (Pane object)

Represents a pane in a window. The Pane object is a member of the Panes collection.

Using the Pane Object

The following properties return a Pane object.

The Panes object’s Item property

The Spreadsheet object’s ActivePane property


Panes Collection Object

Panes collection (Pane object)

The collection of Pane objects for a worksheet.

Using the Panes Collection

The Worksheet object’s Panes property returns a Panes collection.


ParameterValue Object

ParameterValues collection (ParameterValue object)

Represents an input parameter value expression. This expression is evaluated at execute time to provide a run-time parameter value to a row source of type dscProcedure or dscCommandText.

Using the ParameterValue Object

The following method and property return a ParameterValue object:

The ParameterValues object's Add method

The ParameterValues object's Item property


ParameterValues Collection Object

ParameterValues collection (ParameterValue object)

The collection of ParameterValue objects for a recordset definition.

Using the ParameterValues Collection

The RecordsetDef object's ParameterValues property returns a ParameterValues object.


PivotAggregate Object

PivotAggregates collection (PivotAggregate object)

Represents the data associated with a total in a PivotTable list. A total defines what the user wants to see, but the data that results from the total is called the aggregate or aggregate value. The PivotAggregate object is a member of the PivotAggregates collection.

Using the PivotAggregate Object

The PivotAggregates collection’s Item property returns a PivotAggregate object.


PivotAggregates Collection Object

PivotAggregates collection (PivotAggregate object)

The collection of PivotAggregate objects for the specified cell.

Using the PivotAggregates Collection

The PivotCell object’s Aggregates property returns an object from the PivotAggregates collection.


PivotAxis Object

PivotFieldSets collection (PivotFieldSet object)

Used as the base class for the PivotDataAxis, PivotFilterAxis, and PivotGroupAxis objects. There are no properties or methods which with you can use the PivotAxis object directly.


PivotCell Object

Represents a cell (a grouping of data) in a PivotTable list. A cell displays aggregates, and if the underlying detail records are available, you can have the cell display a detail grid. Grouped fields on the row and column axis determine the amount of data that a given cell represents. Even a simple list with no grouped fields is really a single cell displaying a detail grid.

Using the PivotCell Object

The following properties return a PivotCell object:

The PivotData object’s Cells property

The PivotRange object’s BottomRight property

The PivotRange object’s TopLeft property


PivotData Object

PivotMembers collection (PivotMember object)

Represents the data in a PivotTable list.

Using the PivotData Object

The PivotTable object’s ActiveData property returns a PivotData object.


PivotDataAxis Object

PivotFieldSets collection (PivotFieldSet object)

PivotTotals collection (PivotTotals object)

Represents the data axis for a PivotTable list. The data axis contains field sets and totals.

Using the PivotDataAxis Object

The PivotView object’s DataAxis property returns a PivotDataAxis object.


PivotDetailCell Object

Represents a cell in the detail grid for a PivotTable list.

Using the PivotDetailCell Object

The following properties return a PivotDetailCell object:

The PivotCell object’s DetailCells property

The PivotDetailRange object’s BottomRight property

The PivotDetailRange object’s TopLeft property


PivotDetailRange Object

Represents the range of cells in the detail grid for a PivotTable list.

Using the PivotDetailRange Object

The PivotCell object’s DetailRange property returns a PivotDetailRange object.


PivotField Object

Represents a single field in a PivotTable list. The PivotField object is a member of the PivotFields collection.

Using the PivotField Object

The following properties return a PivotField object:

The PivotFields object’s Item property

The PivotFieldSet object’s BoundField property

The PivotMember object’s Field property

The PivotTotal object’s Field property

The PivotDetailCell object’s Field property


PivotFields Collection Object

PivotFields collection (PivotField object)

The collection of PivotField objects in the specified field set.

Using the PivotFields Collection

The PivotFieldSet object’s Fields property returns an object from the PivotFields collection.


PivotFieldSet Object

PivotFieldSets collection (PivotFieldSet object)
PivotFields collection (PivotField object)
PivotMembers collection (PivotMember object)

Represents a set of fields that have been locked together to form a hierarchy. For example, in a field set for geography, the fields might be Continent, Country, State, and City—in that order. Typically, a field set will only contain a single field if the data source is a recordset. The PivotFieldSet object is a member of the PivotFieldSets collection.

Using the PivotFieldSet Object

The following properties return a PivotFieldSet object:

The PivotField object’s FieldSet property

The PivotFieldSets object’s Item property


PivotFieldSets Collection Object

PivotFieldSets collection (PivotFieldSet object)

The collection of PivotFieldSet objects on the specified axis or in the specified view.

Using the PivotFieldSets Collection

The following properties return an object from the PivotFieldSets collection:

The PivotAxis object’s FieldSets property

The PivotDataAxis object’s FieldSets property

The PivotFilterAxis object’s FieldSets property

The PivotGroupAxis object’s FieldSets property

The PivotView object’s FieldSets property


PivotFilterAxis Object

PivotFieldSets collection (PivotFieldSet object)

Represents the filter axis in a PivotTable list.

Using the PivotFilterAxis Object

The PivotView object’s FilterAxis property returns a PivotFilterAxis object.


PivotFont Object

Contains the font attributes (font name, font size, color, and so on) for the specified object.

Using the PivotFont Object

The following properties return a PivotFont object:

The PivotField object’s DetailFont property

The PivotField object’s SubtotalFont property

The PivotLabel object’s Font property

The PivotView object’s FieldLabelFont property

The PivotView object’s MemberFont property

The PivotView object’s TotalFont property


PivotGroupAxis Object

PivotFieldSets collection (PivotFieldSet object)

Represents the group axis in a PivotTable list.

Using the PivotGroupAxis Object

The following properties return a PivotGroupAxis object:

The PivotView object’s ColumnAxis property

The PivotView object’s RowAxis property


PivotLabel Object

Represents the label for the specified object. Contains format attributes (fore color, back color, font, and so on).

Using the PivotLabel Object

The following properties return a PivotLabel object:

The PivotAxis object’s Label property

The PivotDataAxis object’s Label property

The PivotFilterAxis object’s Label property

The PivotGroupAxis object’s Label property

The PivotView object’s Label property

The PivotView object’s TitleBar property


PivotMember Object

Represents the values displayed for a grouped field. The PivotMember object is a member of the PivotMembers collection.

Using the PivotMember Object

The following properties return a PivotMember object:

The PivotCell object’s ColumnMember property

The PivotCell object’s RowMember property

The PivotData object’s Left property

The PivotData object’s Top property

The PivotMember object’s ParentMember property

The PivotMembers object’s Item property

The PivotMembers object’s TotalMember property


PivotMembers Collection Object

PivotMembers collection (PivotMember object)

A collection of PivotMember objects.

Using the PivotMembers Collection

The following properties return an object from the PivotMembers collection:

The PivotData object’s ColumnMembers property

The PivotData object’s RowMembers property

The PivotFieldSet object’s Members property

The PivotMember object’s ChildMembers property


PivotRange Object

Represents a range of cells in a PivotTable list.

Using the PivotRange Object

The PivotData object’s Range property returns a PivotRange object.


PivotTable Object

Represents the container for a PivotTable list.

Using the PivotTable Object

You can use either the CreateObject method or the New keyword to create a PivotTable object.

The object ID for a PivotTable list on an HTML page returns a PivotTable object. The programmatic identifier for the PivotTable object is CLSID:0002E520-0000-0000-C000-000000000046. The following example creates a PivotTable list named "PivotTable1" on an HTML page.

<object id=PivotTable1 classid=CLSID:0002E520-0000-0000-C000-000000000046></object>


PivotTotal Object

PivotTotals collection (PivotTotals object)

Represents a total in a PivotTable list. A total is the aggregate value that is displayed for the contents of a given cell. The PivotTotal object is a member of the PivotTotals collection.

Using the PivotTotal Object

The following properties and methods return a PivotTotal object:

PivotTotals object Item property

PivotView object AddTotal method

PivotField object SortOn property


PivotTotals Collection Object

PivotTotals collection (PivotTotals object)

The collection of PivotTotal objects on the specified data axis or in the specified view.

Using the PivotTotals Collection

The following properties return an object from the PivotTotals collection:

The PivotDataAxis object’s Totals property

The PivotView object’s Totals property


PivotView Object

Represents a specific view of a PivotTable list.

Using the PivotView Object

The PivotTable object’s ActiveView property returns a PivotView object.


Protection Object

Contains the protection properties for a worksheet.

Using the Protection Object

The Worksheet object’s Protection property returns a Protection object.


Range Object

Represents a cell, range of cells, row, or column on a worksheet.

Using the Range Object

The following properties and methods return a Range object.

The Pane object’s VisibleRange property

The Range object’s Cells property

The Range object’s Columns property

The Range object’s CurrentRegion property

The Range object’s EntireColumn property

The Range object’s EntireRow property

The Range object’s Find method

The Range object’s Item property

The Range object’s MergeArea property

The Range object’s Offset method

The Range object’s Range property

The Range object’s Rows property

The Spreadsheet object’s ActiveCell property

The Spreadsheet object’s Cells property

The Spreadsheet object’s Columns property

The Spreadsheet object’s Intersect method

The Spreadsheet object’s Range property

The Spreadsheet object’s Rows property

The Spreadsheet object’s Selection property

The Spreadsheet object’s Union method

The SpreadsheetEventInfo object’s Range property

The Worksheet object’s Cells property

The Worksheet object’s Columns property

The Worksheet object’s Range property

The Worksheet object’s Rows property

The Worksheet object’s UsedRange property

The Worksheet object’s VisibleRange property


RecordNavigationControl Object

RecordNavigationControl object

Represents a record navigation control.

Using the RecordNavigationControl Object

You can use either the CreateObject method or the New keyword to create a RecordNavigationControl object.

The object ID for a record navigation control on an HTML page returns a RecordNavigationControl object. The programmatic identifier for the RecordNavigationControl object is CLSID:0002E531-0000-0000-C000-000000000046. The following example creates a record navigation control named "RNC1" on an HTML page.

<object id=RNC1 classid=CLSID:0002E531-0000-0000-C000-000000000046></object>


RecordsetDef Object

Represents a recordset definition.

The RecordsetDef object is a member of the RecordsetDefs collection.

Using the RecordsetDef Object

The following properties return a RecordsetDef object:

The PageField object's RecordsetDef property

The PageRowsource object's RecordsetDef property

The RecordsetDef object's ParentRecordsetDef property

The RecordsetDefs object's Item property


RecordsetDefs Collection Object

RecordsetDefs collection (RecordsetDef object)

The collection of RecordsetDef objects for the specified data source control.

Using the RecordsetDefs Collection

The following properties return a RecordsetDefs object:

The DataSourceControl object's RecordsetDefs property


SchemaField Object

SchemaFields collection (SchemaField object)

Represents a field in a schema row source. The names of these fields are used in generating SELECT statements for row sources of type dscTable or dscView. The SchemaField object is a member of the SchemaFields collection.

Using the SchemaField Object

The following method and property return a SchemaField object:

The SchemaFields object's Add method

The SchemaFields object's Item property


SchemaFields Collection Object

SchemaFields collection (SchemaField object)

The collection of all available SchemaField objects in a schema row source.

Using the SchemaFields Collection

The SchemaRowsource object's SchemaFields property returns a SchemaFields object.


SchemaParameter Object

SchemaParameters collection (SchemaParameter object)

Represents a single parameter for a schema row source. The SchemaParameter object is a member of the SchemaParameters collection.

Using the SchemaParameter Object

The following method and property return a SchemaParameter object:

The SchemaParameters object's Add method

The SchemaParameters object's Item property


SchemaParameters Collection Object

SchemaParameters collection (SchemaParameter object)

The collection of SchemaParameter objects for the specified schema row source.

Using the SchemaParameters Collection

The SchemaRowsource object's SchemaParameters property returns a SchemaParameters object.


SchemaRelatedField Object

SchemaRelatedFields collection (SchemaRelatedField object)

Describes the column pairings that make up a schema relationship. The names of these fields are used in generating join clauses in SQL and for tying a parent recordset definition to a sublist child recordset definition. The SchemaRelatedField object is a member of the SchemaRelatedFields collection.

Using the SchemaRelatedField

The following method and property return a SchemaRelatedField object:

The SchemaRelatedFields object's Add method

The SchemaRelatedFields object's Item property


SchemaRelatedFields Collection Object

SchemaRelatedFields collection (SchemaRelatedField object)

The collection of SchemaRelatedField objects for a schema relationship.

Using the SchemaRelatedFields Collection

The SchemaRelationship object's SchemaRelatedFields property returns a SchemaRelatedFields object.


SchemaRelationship Object

SchemaRelatedFields collection (SchemaRelatedField object)

Represents a schema relationship. A schema relationship describes how schema row sources are connected, and it always has a “one” side and a “many” side (from a one-to-many relationship). The SchemaRelationship object is a member of the SchemaRelationships collection.

Using the SchemaRelationship Object

The following properties and method return a SchemaRelationship object:

The LookupSchemaRelationships object's Item property

The SchemaRelationships object's Add method

The SchemaRelationships object's Item property

The SublistSchemaRelationships object's Item property


SchemaRelationships Collection Object

SchemaRelationships collection (SchemaRelationship object)
SchemaRelatedFields collection (SchemaRelatedField object)

The collection of SchemaRelationship objects for a data source control.

Using the SchemaRelationships Collection

The DataSourceControl object's SchemaRelationships property returns a SchemaRelationships object.


SchemaRowsource Object

SchemaRowsources collection (SchemaRowsource object)

Represents a schema row source. Every table, view, or stored procedure in the database is a potential schema row source, as are commands defined explicitly to the data source control.

The SchemaRowsource object is a member of the SchemaRowsources collection.

Using the SchemaRowsource Object

The following method and property return a SchemaRowsource object:

The SchemaRowsources object's Add method

The SchemaRowsources object's Item property


SchemaRowsources Collection Object

SchemaRowsources collection (SchemaRowsource object)

The collection of SchemaRowsource objects for the specified data source control. This collection is automatically repopulated with objects in the database whenever the database is opened.

Using the SchemaRowsources Collection

The DataSourceControl object's SchemaRowsources property returns a SchemaRowsources object.


Section Object

An instance of a single group header, footer, caption, or navigation section.

Using the Section Object

The DataSourceControl object's GetContainingSection method returns a Section object.


Spreadsheet Object

Represents the spreadsheet.

Using the Spreadsheet Object

You can use the CreateObject method or the New keyword to create a Spreadsheet object.

The object ID for a spreadsheet control on an HTML page or a Visual Basic form returns a Spreadsheet object.

The programmatic identifier for the Spreadsheet object is CLSID:0002E510-0000-0000-C000-000000000046. The following example creates a spreadsheet named "Spreadsheet1" on an HTML page.

<object id=Spreadsheet1 classid=CLSID:0002E510-0000-0000-C000-000000000046 style="width:49%;height:350"></object>


SpreadsheetEventInfo Object

Contains information about a spreadsheet event (the range where the event occurred, what keys or mouse buttons were pressed, and so on).

Using the SpreadsheetEventInfo Object

All the Spreadsheet object events use a SpreadsheetEventInfo object as their single parameter. For more information, see the Help topics for the following events:

BeforeCommand event

CancelEdit event

Change event

Click event

Command event

DblClick event

EndEdit event

KeyDown event

KeyPress event

KeyUp event

MouseDown event

MouseOut event

MouseOver event

MouseUp event

SelectionChange event

SelectionChanging event

StartEdit event

ViewChange event


SublistRelationships Collection Object

SublistRelationships collection (PageRelationship object)

The collection of PageRelationship objects of type dscSublist that all have the same recordset definition as their “one” side (parent) of a one-to-many relationship. Records in the child of a sublist relationship are retrieved only when they are needed.

Using the SublistRelationships Collection

The RecordsetDef object's SublistRelationships property returns a SublistRelationships object.


SublistSchemaRelationships Collection Object

SublistSchemaRelationships collection (SchemaRelationship object)

The collection of SchemaRelationship objects that all have the same schema row source as their “one” side (from a one-to-many relationship).

Using the SublistSchemaRelationships Collection

The SchemaRowsource object's SublistSchemaRelationships property returns a SublistSchemaRelationships object.


TitleBar Object

Represents the title bar on the spreadsheet.

Using the TitleBar Object

The Spreadsheet object’s TitleBar property returns a TitleBar object.


WCAxis Object

WCAxes collection (WCAxis object)

Represents a single axis on a chart. A chart can have up to eight axes. The WCAxis object is a member of the WCAxes collection.

Using the WCAxis Object

Use the WCAxes object’s Add method to add an axis to a chart.

The following properties and methods return a WCAxis object.

The WCAxes object’s Add method

The WCAxes object’s Item property

The WCAxis object’s CrossingAxis property

The WCGridlines object’s Parent property


WCAxes Collection Object

WCAxes collection (WCAxis object)

The collection of WCAxis objects that represent the axes for a single chart. Each chart can have up to eight axes.

Using the WCAxes Collection

The WCChart object’s Axes property returns a WCAxes collection.


WCChart Object

WCCharts collection (WCChart object)

Represents a single chart in the chart workspace. The chart workspace can contain up to 16 charts. The WCChart object is a member of the WCCharts collection.

Using the WCChart Object

Use the WCCharts object’s Add method to add a chart to the chart workspace.

The following properties and methods return a WCChart object.

The WCAxes object’s Parent property

The WCAxis object’s Parent property

The WCCharts object’s Add method

The WCCharts object’s Item property

The WCPlotArea object’s Parent property

The WCSeries object’s Parent property

The WCSeriesCollection object’s Parent property


WCCharts Collection Object

WCCharts collection (WCChart object)

The collection of WCChart objects in the chart workspace. Each WCChart object represents a single chart. The chart workspace can contain up to 16 charts.

Using the WCCharts Collection

The ChartSpace object’s Charts property returns a WCCharts collection.


WCChartEventInfo Object

WCChartEventInfo object

Contains information about a chart workspace event (what keys or mouse buttons were pressed when the event occurred, and so on).

Using the WCChartEventInfo Object

The following chart workspace events use a WCChartEventInfo object as their only parameter. For more information, see the Help topics for the following events:

Click event

DblClick event

KeyDown event

KeyPress event

KeyUp event

MouseDown event

MouseMove event

MouseUp event


WCDataLabel Object

Represents the data label for a trendline. Only the WCTrendline object contains a WCDataLabel object (data labels for a series are represented by a WCDataLabels object).

Using the WCDataLabel Object

Use the DataLabel property of the WCTrendline object to return the WCDataLabels object for the trendline. A trendline has a data label when its IsDisplayingEquation property or IsDisplayingRSquared property is set to True; otherwise, the property returns Nothing.


WCDataLabels Object

WCDataLabelsCollection collection (WCDataLabels object)

Represents all the data labels for a series. You cannot apply a custom format to a single data label; formats apply to all data labels for the series.

Using the WCDataLabels Object

Use the Add method of the WCDataLabelsCollection object to add data labels to a series.

The following property and method return a WCDataLabels object.

The WCDataLabelsCollection object’s Add method

The WCDataLabelsCollection object’s Item property


WCDataLabelsCollection Collection Object

WCDataLabelsCollection collection (WCDataLabels object)

A collection of WCDataLabels objects. Note that the WCDataLabelsCollection collection can contain only one WCDataLabels object.

Using the WCDataLabelsCollection Collection

The WCSeries object’s DataLabelsCollection property returns a WCDataLabelsCollection collection.


WCDataSource Object

WCDataSources collection (WCDataSource)

Represents a data source. A data source specifies a connection to a spreadsheet, data source control, or PivotTable list. The WCDataSource object is a member of the WCDataSources collection.

Using the WCDataSource Object

The following property and method return a WCDataSource object.

The WCDataSources object’s Add method

The WCDataSources object’s Item property


WCDataSources Collection Object

WCDataSources collection (WCDataSource)

The collection of WCDataSource objects for the chart workspace.

Using the WCDataSources Collection

The ChartSpace object’s ChartDataSources property returns a WCDataSources object.


WCErrorBars Object

WCErrorBarsCollection collection (WCErrorBars object)

Represents the error bars for a series. Error bars indicate the degree of uncertainty for chart data. Only series in radar, polar, area, bar, column, line, and scatter charts can have error bars. Only series in scatter charts can have x and y error bars. The WCErrorBars object is not a collection. There is no object that represents a single error bar; you either have x error bars or y error bars turned on for all points in a series or you have them turned off.

The WCErrorBars object is a member of the WCErrorBarsCollection collection.

Using the WCErrorBars Object

Use the Add method of the WCErrorBarsCollection object to add error bars to a series.

The following methods return a WCErrorBars object. For more information, see the Help topics for these methods:

The WCErrorBarsCollection object’s Add method

The WCErrorBarsCollection object’s Item property


WCErrorBarsCollection Collection Object

WCErrorBarsCollection collection (WCErrorBars object)

The collection of WCErrorBars objects for a single series. Note that this collection can contain only one WCErrorBars object.

Using the WCErrorBarsCollection Collection

The WCSeries object’s ErrorBarsCollection propertyreturns a WCErrorBarsCollection object.


WCGridlines Object

Represents major or minor gridlines on a chart axis. You cannot have gridlines without an axis. Gridlines extend the tick marks on a chart axis to make it easier to see the values associated with the data markers. This object is not a collection. There is no object that represents a single gridline; you either have all gridlines for an axis turned on or all of them turned off.

Using the WCGridlines Object

The following properties return a WCGridlines object.

The WCAxis object’s MajorGridlines property

The WCAxis object’s MinorGridlines property


WCLegend Object

WCLegendEntries collection (WCLegendEntry object)

Represents a chart workspace or chart legend. A chart or c hart workspace can have only one legend. The WCLegend object contains a WCLegendEntries collection that contains one or more WCLegendEntry objects.

Using the WCLegend Object

Use the HasLegend property or the HasChartspaceLegend property to enable the legend.

The following properties return a WCLegend object.

The ChartSpace object’s ChartspaceLegend property

The WCChart object’s Legend property

The WCLegendEntries object’s Parent property

The WCLegendEntry object’s Parent property


WCLegendEntry Object

WCLegendEntries collection (WCLegendEntry object)

Represents a single legend entry. The WCLegendEntry object is a member of the WCLegendEntries collection.

Using the WCLegendEntry Object

The WCLegendEntries object’s Item property returns a WCLegendEntry object.


WCLegendEntries Collection Object

WCLegendEntries collection (WCLegendEntry object)

The collection of WCLegendEntry objects for the specified legend.

Using the WCLegendEntries Collection

The WCLegend object’s LegendEntries property returns a WCLegendEntries collection.


WCMarker Object

Represents a data point on a line, xy scatter, radar, or polar chart.

Using the WCMarker Object

The WCSeries object’s Marker property returns a WCMarker object.


WCPlotArea Object

Represents the plot area on a chart (the area where the chart data is plotted). Pie, doughnut, radar, and polar charts do not have a plot area; instead, these charts draw directly on the chart area.

Using the WCPlotArea Object

The WCChart object’s PlotArea property returns a WCPlotArea object.


WCPoint Object

WCPoints collection (WCPoint object)

Represents a single data point in a series on a chart. The WCPoint object is a member of the WCPoints collection, which contains all the points in a given series.

Using the WCPoint Object

Use this object to format single data points in a series, or use the GetValue method to return a point value.

The WCPoints object’s Item property returns a WCPoint object.


WCPoints Collection Object

WCPoints collection (WCPoint object)

A collection of all the WCPoint objects in a series.The number of data points in a series is limited only by the amount of system memory in the computer being used.

Using the WCPoints Collection

The WCSeries object’s Points property returns a WCPoints object:


WCScaling Object

Contains the scaling properties for an axis, chart, or series.

Using the WCScaling Object

The following properties return a WCScaling object.

The WCAxis object’s Scaling property

The WCChart object’s Scalings property

The WCSeries object’s Scalings property


WCSeries Object

WCSeriesCollection collection (WCSeries object)

Represents a series on a chart. The WCSeries object is a member of the WCSeriesCollection collection.

Using the WCSeries Object

Use the Add method of the WCSeriesCollection collection to add a series to a chart.

The following properties and methods return a WCSeries object:

The WCDataLabels object’s Parent property

The WCDataLabelsCollection object’s Parent property

The WCErrorBars object’s Parent property

The WCErrorBarsCollection object’s Parent property

The WCPoint object’s Parent property

The WCPoints object’s Parent property

The WCSeriesCollection object’s Add method

The WCSeriesCollection object’s Item property

The WCTrendline object’s Parent property

The WCTrendlines object’s Parent property


WCSeriesCollection Collection Object

WCSeriesCollection collection (WCSeries object)

A collection of all the WCSeries objects on a chart. A chart can contain up to 255 series.

Using the WCSeriesCollection Collection

The WCChart object’s SeriesCollection property returns a WCSeriesCollection collection.


WCTitle Object

Represents the title of a chart workspace, axis, or chart.

Using the WCTitle Object

Use the HasTitle or HasChartspaceTitle property to enable titles.

The following properties return a WCTitle object.

The ChartSpace object’s ChartspaceTitle property

The WCAxis object’s Title property

The WCChart object’s Title property


WCTrendline Object

Represents a trendline on a chart. A trendline shows the trend, or direction, of data in a series. The WCTrendline object is a member of the WCTrendlines collection.

Using the WCTrendline Object

Use the Add method of the WCTrendlines object to add a trendline to a series.

The following method and property return a WCTrendline object.

The WCTrendlines object’s Add method

The WCTrendlines object’s Item property


WCTrendlines Collection Object

The collection of WCTrendline objects for a series. Note that the WCTrendlines collection can contain only one WCTrendline object (that is, there can be only one trendline for each series).

Using the WCTrendlines Collection

The WCSeries object’s Trendlines property returns a WCTrendlines collection.


Worksheet Object

Panes collection (Pane object)

Represents a single worksheet in the spreadsheet. Note that the spreadsheet can contain only one worksheet.

Using the Worksheet Object

The following properties return a Worksheet object.

The Range object’s Parent property

The Spreadsheet object’s ActiveSheet property


ActiveCell Property

Returns a Range object that represents the active cell. Read-only.


ActiveCell Property Example

This example sets the number format in the active cell on the worksheet.

Spreadsheet1.ActiveCell.NumberFormat = "0.##"


ActiveData Property

Returns a PivotData object that represents the data in the active PivotTable list. Read-only.


ActivePane Property

Returns a Pane object that represents the active spreadsheet pane. Read-only.


ActivePane Property Example

This example sets the value of the cell in the upper-left corner of the visible range in the active pane on the spreadsheet.

Spreadsheet1.ActivePane.VisibleRange.Cells(1, 1).Value = "top left"


ActiveSheet Property

Returns a Worksheet object that represents the worksheet (the spreadsheet can contain only one worksheet). Read-only.


ActiveSheet Property Example

This example sets the value of the cell in the upper-left corner of the visible range in pane one on the spreadsheet.

Spreadsheet1.ActiveSheet.Panes(1).VisibleRange.Cells(1, 1).Value = "top left"


ActiveView Property

Returns a PivotView object that represents the layout of the active PivotTable list. Read-only.


ActiveView Property Example

This example inserts two field sets in the active view.

Set view = PivotTable1.ActiveView
view.ColumnAxis.InsertFieldSet view.FieldSets("Store Type")
view.RowAxis.InsertFieldSet view.FieldSets("Promotions")


Address Property

Hyperlink object: Returns or sets the address of the target document. Read/write String.

Range object: Returns the address of the specified range (for example, A1). Read-only String.


Address Property Example

This example sets the address of the specified hyperlink.

Spreadsheet1.Range("a22").Hyperlink.Address = "http://www.microsoft.com"

This example returns the address of the visible range.

MsgBox Spreadsheet1.ActiveSheet.VisibleRange.Address


AggregateCount Property

Returns the number of aggregates. Read-only Long.


Aggregates Property

Returns the PivotAggregates collection for the specified cell. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


AllGroupingDefs Property

Returns the AllGroupingDefs collection for the data source control. Read-only.


AllowDeletingColumns Property

True if worksheet columns can be deleted. The default value is False, but this property has no effect if the Enabled property of the Protection object is set to False. Read/write Boolean.


AllowDeletingColumns Property Example

This example locks all cells on the specified spreadsheet, and then it enables the filtering and sorting of rows and columns but disables the inserting, deleting, or sizing of rows and columns.

Spreadsheet1.Cells.Locked = True
Set pt = Spreadsheet1.ActiveSheet.Protection
pt.AllowDeletingColumns = False
pt.AllowDeletingRows = False
pt.AllowFiltering = True
pt.AllowInsertingColumns = False
pt.AllowInsertingRows = False
pt.AllowSizingAllColumns = False
pt.AllowSizingAllRows = False
pt.AllowSorting = True
pt.Enabled = True


AllowDeletingRows Property

True if worksheet rows can be deleted. The default value is False, but this property has no effect if the Enabled property of the Protection object is set to False. Read/write Boolean.


AllowDeletingRows Property Example

This example locks all cells on the specified spreadsheet, and then it enables the filtering and sorting of rows and columns but disables the inserting, deleting, or sizing of rows and columns.

Spreadsheet1.Cells.Locked = True
Set pt = Spreadsheet1.ActiveSheet.Protection
pt.AllowDeletingColumns = False
pt.AllowDeletingRows = False
pt.AllowFiltering = True
pt.AllowInsertingColumns = False
pt.AllowInsertingRows = False
pt.AllowSizingAllColumns = False
pt.AllowSizingAllRows = False
pt.AllowSorting = True
pt.Enabled = True


AllowDetails Property

True if the user can expand an inner member of the specified PivotTable list to display detail records. The default value is True. Read/write Boolean.

Remarks

When this property is False, inner members do not display expand indicators, the Expand command is disabled for most aggregates, and double-clicking an aggregate does not display detail data.

If this property is False, the user can still view details programmatically.


AllowDetails Property Example

This example disables detail viewing for the PivotTable list.

PivotTable1.AllowDetails = False


AllowFiltering Property

Protection object: True if worksheet filtering is enabled for the specified PivotTable list. This property has no effect if the Protection object’s Enabled property is set to False. Read/write Boolean.

PivotTable object: True if the user can modify filters. The default value is True. Read/write Boolean.

When this property is set to False, the following conditions are true:

  • The AutoFilter command is disabled.

  • The Filter By Selection command is disabled.

  • The Move to Filter Area command is disabled.

  • The Move to Detail command is disabled if the selection is a field in the filter area.

  • All AutoFilter drop-down boxes are read-only. Note that the user can still view these boxes even though they are disabled.

  • You can still set filters programmatically.

  • Membership in the filter area is fixed; the user cannot drag a field to or from the filter area.


AllowFiltering Property Example

This example disables filtering for the PivotTable list.

PivotTable1.AllowFiltering = False


AllowGrouping Property

True if the user is able to group fields on the row axis or the column axis in the specified PivotTable list. If this property is set to False, the grouping buttons are disabled but the user can still group fields programmatically. The default value is True. Read/write Boolean.

Remarks

When this property is set to False, pivoting is disabled. This means that membership in the row and column areas is fixed; you cannot drag a field to or from these areas. All commands related to grouping are disabled, including the following commands:

  • Move to Row Area

  • Move to Column Area

  • Move to Detail, if the selection is a field in the row or column area

The report layout is frozen, but the user can still filter the data.


AllowGrouping Property Example

This example disables field grouping for the PivotTable list.

PivotTable1.AllowGrouping = False


AllowInsertingColumns Property

True if worksheet columns can be inserted. The default value is False, but this property has no effect if the Enabled property of the Protection object is set to False. Read/write Boolean.


AllowInsertingColumns Property Example

This example locks all cells on the specified spreadsheet, and then it enables the filtering and sorting of rows and columns but disables the inserting, deleting, or sizing of rows and columns.

Spreadsheet1.Cells.Locked = True
Set pt = Spreadsheet1.ActiveSheet.Protection
pt.AllowDeletingColumns = False
pt.AllowDeletingRows = False
pt.AllowFiltering = True
pt.AllowInsertingColumns = False
pt.AllowInsertingRows = False
pt.AllowSizingAllColumns = False
pt.AllowSizingAllRows = False
pt.AllowSorting = True
pt.Enabled = True


AllowInsertingRows Property

True if worksheet rows can be inserted. The default value is False, but this property has no effect if the Protection Enabled property is set to False. Read/write Boolean.


AllowInsertingRows Property Example

This example locks all cells on the specified spreadsheet, and then it enables the filtering and sorting of rows and columns but disables the inserting, deleting, or sizing of rows and columns.

Spreadsheet1.Cells.Locked = True
Set pt = Spreadsheet1.ActiveSheet.Protection
pt.AllowDeletingColumns = False
pt.AllowDeletingRows = False
pt.AllowFiltering = True
pt.AllowInsertingColumns = False
pt.AllowInsertingRows = False
pt.AllowSizingAllColumns = False
pt.AllowSizingAllRows = False
pt.AllowSorting = True
pt.Enabled = True


AllowPropertyToolbox Property

True if the Property Toolbox is enabled at run time (you cannot disable the Property Toolbox at design time). The default value is True. Read/write Boolean.


AllowPropertyToolbox Property Example

This example disables the Property Toolbox at run time.

Spreadsheet1.AllowPropertyToolbox = False


AllowSizingAllColumns Property

True if worksheet columns can be resized. The default value is False, but this property has no effect if the Enabled of the Protection object property is set to False. Read/write Boolean.


AllowSizingAllColumns Property Example

This example locks all cells on the specified spreadsheet, and then it enables the filtering and sorting of rows and columns but disables the inserting, deleting, or sizing of rows and columns.

Spreadsheet1.Cells.Locked = True
Set pt = Spreadsheet1.ActiveSheet.Protection
pt.AllowDeletingColumns = False
pt.AllowDeletingRows = False
pt.AllowFiltering = True
pt.AllowInsertingColumns = False
pt.AllowInsertingRows = False
pt.AllowSizingAllColumns = False
pt.AllowSizingAllRows = False
pt.AllowSorting = True
pt.Enabled = True


AllowSizingAllRows Property

True if worksheet rows can be resized. The default value is False, but this property has no effect if the Enabled of the Protection object property is set to False. Read/write Boolean.


AllowSizingAllRows Property Example

This example locks all cells on the specified spreadsheet, and then it enables the filtering and sorting of rows and columns but disables the inserting, deleting, or sizing of rows and columns.

Spreadsheet1.Cells.Locked = True
Set pt = Spreadsheet1.ActiveSheet.Protection
pt.AllowDeletingColumns = False
pt.AllowDeletingRows = False
pt.AllowFiltering = True
pt.AllowInsertingColumns = False
pt.AllowInsertingRows = False
pt.AllowSizingAllColumns = False
pt.AllowSizingAllRows = False
pt.AllowSorting = True
pt.Enabled = True


AllowSorting Property

True if the specified worksheet can be sorted. The default value is False, but this property has no effect if the Enabled property of the Protection object is set to False. Read/write Boolean.


AllowSorting Property Example

This example locks all cells on the specified spreadsheet, and then it enables the filtering and sorting of rows and columns but disables the inserting, deleting, or sizing of rows and columns.

Spreadsheet1.Cells.Locked = True
Set pt = Spreadsheet1.ActiveSheet.Protection
pt.AllowDeletingColumns = False
pt.AllowDeletingRows = False
pt.AllowFiltering = True
pt.AllowInsertingColumns = False
pt.AllowInsertingRows = False
pt.AllowSizingAllColumns = False
pt.AllowSizingAllRows = False
pt.AllowSorting = True
pt.Enabled = True


AllPageFields Property

Returns the AllPageFields collection for the data source control. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


AlternateDataSource Property

Returns or sets the ID of the alternate data source (another complex data-bound control that will be used as the data source). Read/write String.


AltKey Property

True if the ALT key was pressed (held down) when the specified event occurred. Read-only Boolean.


AltKey Property Example

This procedure runs when the user clicks the mouse with the pointer positioned over the chart workspace. Note that the event procedure in this example is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures in VBScript.

Private Sub ChartSpace1_Click(ByVal ChartEventInfo As OWC.WCChartEventInfo)
    If ChartEventInfo.AltKey Then
        ' Handle alt case here.
    End If
    If ChartEventInfo.CtrlKey Then
        ' Handle ctrl case here.
    End If
End Sub


Amount Property

Returns or sets the error amount for fixed-value and percentage error bars. You specify data-bound error bar amounts by using the SetData method. Read/write Variant.

Remarks

This value is mathematically correct, meaning that 5% is represented as 0.05 and not 5. An error bar with the fixed amount of 5 will become 500% when changed to a percentage.


Amount Property Example

This example adds error bars to the specified series in the chart workspace and then sets the error amount.

Set eb = ChartSpace1.Charts(0).SeriesCollection(0).ErrorBarsCollection.Add
eb.Amount = 0.05


AutoFilter Property

Returns the AutoFilter object for the specified worksheet.

Remarks

Do not confuse this property with the AutoFilter method. This property returns the AutoFilter object for a given worksheet, whereas the AutoFilter method applies to a Range object and turns on the AutoFilter.


AutoFilterMode Property

True if the AutoFilter drop-down arrows are visible. Read/write Boolean.

Remarks

This property returns True if the AutoFilter drop-down arrows are currently displayed. You can set this property to False to hide the arrows, but you cannot set it to True. Use the AutoFilter method to filter a list and display the drop-down arrows.

If the Auto Filter drop-down arrows are visible but no rows are currently filtered (all rows are visible), the AutoFilterMode property is True and the FilterMode property is False.


AutoFilterMode Property Example

This example turns off the Auto Filter for the active worksheet.

Spreadsheet1.ActiveSheet.AutoFilterMode = False


AutoFit Property

Spreadsheet or PivotTable object: True if the overall size of the spreadsheet or PivotTable list is determined by the number of visible columns and rows. The row height and column width cannot exceed the value of the spreadsheet’s or PivotTable list’s MaxHeight and MaxWidth properties. The default value is True. If the AutoFit property is set to False, the overall size of the spreadsheet or PivotTable list is set based on its Width and Height properties. Read/write Boolean.

PivotTotal object: True if the width of the total is set automatically. The default value is True. When this property is set to True, any layout change also updates the Width and Height property values. When you change the Width and Height values programmatically, the value of the AutoFit property is set to False. Read/write Boolean.


AutoFit Property Example

This example disables automatic sizing for the PivotTable list and then sets its width.

PivotTable1.AutoFit = False
PivotTable1.Object.Width = 8000


Axes Property

Returns the WCAxes collection for the specified chart. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


Axes Property Example

This example sets the font name for the specified axis in the chart workspace.

Set axs = ChartSpace1.Charts(0).Axes(1)
axs.Font.Name = "Arial"


BackColor Property

Returns or sets the back color for the specified object or area. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


BackColor Property Example

This example sets the font size, fore color, and back color for the label in the PivotTable list.

Set view = PivotTable1.ActiveView
view.Label.BackColor = "DarkSalmon"
view.Label.Font.Size = 16
view.Label.ForeColor = "Sienna"


BaseName Property

Returns the base name of the specified field. Read-only String.

Remarks

A field can have several names, as shown in the following table.

Name | Description
Caption | The name the user sees.
Name | The name used to identify the field in code.
DataField | The name of the field in the underlying recordset. (This is not necessarily the same as the base name; you can use a different name if there is a naming conflict or if you want to make the name easier to use and remember.)
BaseName | The name of the field in the original source database.


Bitmap Property

Returns or sets the bitmap type for the Expand control. Read/write ExpandBitmapTypeEnum.


Bold Property

True if the specified font is bold.

OWCFont object: Read/write Variant (returns Null if some cells in the range are bold and some are not). Use the IsNull function to determine whether the return value is Null.

PivotFont object: Read/write Boolean.


Bold Property Example

This example sets font properties for the specified axis in the chart workspace.

Set axs = ChartSpace1.Charts(0).Axes(1)
axs.Font.Name = "Arial"
axs.Font.Size = 8
axs.Font.Bold = True


Border Property

Returns a Border object that represents the border of the specified object. Read-only.

Remarks

The border for a chart series represents the outline color on column, bar, pie, doughnut, area, and high-low-close charts. On charts with markers (such as a line chart with markers), the border represents the outline color for the markers.


Borders Property

Returns a Borders collection that represents the range borders. Read-only.


Borders Property Example

This example adds a medium-weight green border to each cell in the range B5:C10 on the spreadsheet.

Set c = Spreadsheet1.Constants
Spreadsheet1.Range("b5:c10").Borders.Weight = c.owcLineWeightMedium
Spreadsheet1.Range("b5:c10").Borders.Color = "green"


BottomRight Property

PivotDetailRange object: Returns a PivotDetailCell object that represents the cell in the lower-right corner of the specified detail range. Read-only.

PivotRange object: Returns a PivotCell object that represents the cell in the lower-right corner of the specified range. Read-only.


BoundField Property

Returns the PivotField object used for data-binding information when the source is a recordset. When the source is multidimensional, this property returns Nothing. Read-only.


BubbleScale Property

Returns or sets a scale factor for all bubble series on the specified chart. The default value is 100, and the valid range is 0–300. Read/write Double.

Remarks

The value of this property indicates the bubble size relative to the default setting. Setting the BubbleScale property to 300 (three times greater than the default value) produces a chart on which the bubbles appear three times the default size.


BubbleScale Property Example

This example sets the bubble scale factor for the specified chart in the chart workspace. Note that Charts(0) must refer to a bubble chart.

ChartSpace1.Charts(0).BubbleScale = 200


BuildNumber Property

Returns the Microsoft Office Web Components build number. Read-only Long.


BuildNumber Property Example

This example displays the Microsoft Office Web Components build number in the spreadsheet’s title bar.

Spreadsheet1.TitleBar.Caption = "MSOWC Build " & Spreadsheet1.BuildNumber


Button Property

Indicates which mouse button or buttons were pressed down (held down) when a given event occurred, as shown in the following table. Return values can occur together; for example, a return value of 3 indicates that both the left and right buttons were pressed down when the event occurred. Read-only Long.

Value | Meaning
0 | No mouse buttons were pressed down.
1 | The left mouse button was pressed down.
2 | The right mouse button was pressed down.
4 | The center mouse button was pressed down.


CanUndo Property

True if there is a previous action that can be undone. Read-only Boolean.


CanUndo Property Example

This example undoes the last action performed or displays a message box if the action cannot be undone.

If Spreadsheet1.CanUndo Then
    Spreadsheet1.Undo
Else
    MsgBox "can't undo last action"
End If


Caption Property

Returns or sets the caption for the specified object. Read/write String.


CaptionHAlignment Property

Returns or sets the horizontal alignment for the detail footer label in the specified PivotTable list. Read/write PivotHAlignmentEnum.

Remarks

Captions can be displayed only to the left of aggregates in the detail footer. Use the CaptionHAlignment property to align the caption close to the aggregate (plHAlignRight) or to align the caption close to the left edge of the footer (plHAlignLeft). If there is another aggregate displayed to the left, the caption is displayed to the right of the other aggregate. If there is not enough room to display the caption, it is not visible.


CaptionHAlignment Property Example

This example formats totals and their captions in the PivotTable list.

Set c = PivotTable1.Constants
Set view = PivotTable1.ActiveView
For Each ttl In view.DataAxis.Totals
    ttl.HAlignment = c.plHAlignRight
    ttl.CaptionHAlignment = c.plHAlignRight
Next


CaptionSection Property

True if the specified group level has a caption section showing. Read/write Boolean.

Remarks

The caption section appears on a data page only once, above all the visible records at a given group level. The caption section remains visible as you scroll through the records.


Cell Property

Returns a PivotCell object that indicates the location of the aggregate cell or detail cell. Read-only


Cells Property

Range, Spreadsheet, or Worksheet object: Returns a Range object that represents the cells in the object. This property has no arguments when used with any of these three objects. Read-only.

Because the Item property is the default property for the Range object, you can specify the row and column index numbers immediately after the Cells keyword (this actually calls the Item property of the Range object returned by the Cells property).

PivotData object: Returns a PivotCell object based on a row and column member.

Syntax

expression.Cells(Row, Column)

expression An expression that returns a PivotData object.

Row Required PivotMember object. Specifies the row containing the returned cell.

Column Required PivotMember object. Specifies the column containing the returned cell.


Cells Property Example

This example clears all cells on the specified spreadsheet.

Spreadsheet1.Cells.Clear

This example uses the default Item property of the Range object returned by the Cells property to reference a single cell. The example displays the row and column numbers for each cell in the range A1:E5.

For rw = 1 To 5
    For col = 1 To 5
        Spreadsheet1.Cells(rw, col).Value = "row " & rw & " column " & col
    Next
Next


ChartDataSources Property

Returns the WCDataSources collection for the specified chart workspace.

For information about returning a single member of a collection, see Returning an Object from a Collection.


ChartLayout Property

Returns or sets the layout for all the charts in the specified chart workspace. Read/write ChartChartLayoutEnum.

Because the chart workspace can contain one or more charts, you can use both the ChartLayout and ChartWrapCount properties to specify how multiple charts are positioned. The ChartLayout property makes it possible to create custom chart arrangements, such as three charts positioned horizontally in a single row.

There are two distinct layout types for charts:

ChChartLayoutHorizontal Charts are positioned horizontally from left to right until the number of charts specified by the ChartWrapCount property is reached. When this occurs, a new row is created below the active row and the positioning process begins again at the left. This method continues (wrapping every ChartWrapCount number) until all charts have been placed.

ChChartLayoutVertical Charts are positioned vertically from top to bottom until the number of charts specified by the ChartWrapCount property is reached. When this occurs, a new column is created to the right of the active column and positioning begins again at the top. This method continues (wrapping every ChartWrapCount number of charts) until all charts have been placed.


ChartLayout Property Example

This example sets the ChartWrapCount and ChartLayout properties and then adds three additional charts to the specified chart workspace.

Dim chts(3)

ChartSpace1.ChartWrapCount = 2
ChartSpace1.ChartLayout = ChartSpace1.Constants.chChartLayoutHorizontal
For k = 0 To 2
    Set chts(k) = ChartSpace1.Charts.Add
Next


Charts Property

Returns the WCCharts collection for the specified chart workspace. Read-only.

By default, a new chart workspace contains no charts. After you create a new chart workspace, you must add a Chart object to it before you can create a chart. Use the Add method to create a new chart.

For information about returning a single member of a collection, see Returning an Object from a Collection.


ChartSpaceLegend Property

Returns a WCLegend object that represents the chart workspace legend. Returns Nothing if the chart workspace does not have a legend. Read-only.


ChartSpaceLegend Property Example

This example sets the chart workspace title and positions the chart workspace legend on the left side of the workspace.

Set c = ChartSpace1.Constants
ChartSpace1.HasChartSpaceTitle = True
ChartSpace1.ChartSpaceTitle.Caption = "Monthly Sales Data"
ChartSpace1.HasChartSpaceLegend = True
ChartSpace1.ChartSpaceLegend.Position = c.chLegendPositionLeft


ChartSpaceTitle Property

Returns a WCTitle object that represents the chart workspace title. Returns Nothing if the chart workspace does not have a title. Read-only.


ChartSpaceTitle Property Example

This example sets the chart workspace title and positions the chart workspace legend on the left side of the workspace.

Set c = ChartSpace1.Constants
ChartSpace1.HasChartSpaceTitle = True
ChartSpace1.ChartSpaceTitle.Caption = "Monthly Sales Data"
ChartSpace1.HasChartSpaceLegend = True
ChartSpace1.ChartSpaceLegend.Position = c.chLegendPositionLeft


ChartWrapCount Property

Returns or sets the number of charts that are placed horizontally or vertically before wrapping occurs. For a more complete discussion of layout and wrapping, see the Help topic for the ChartLayout property. Read/write Long.


ChartWrapCount Property Example

This example sets the ChartWrapCount and ChartLayout properties and then adds three additional charts to the specified chart workspace.

Dim chts(3)

ChartSpace1.ChartWrapCount = 2
ChartSpace1.ChartLayout = ChartSpace1.Constants.chChartLayoutHorizontal
For k = 0 To 2
    Set chts(k) = ChartSpace1.Charts.Add
Next


ChildLabel Property

Contains the string that is displayed as the specified control's label. Read/write String.


ChildMembers Property

Returns a PivotMembers collection that represents the child members of the specified member. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


ChildSection Property

Returns a Section object that represents the child section for the specified section. This property fails if the specified section is not expanded. Read-only.


Color Property

Returns or sets the primary color of the specified object. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.

On charts, you can also use either of the following two special constants: chColorAutomatic (to reset the color to the default value) or chColorNone (to indicate no color, or transparency). However, you cannot set a font color to chColorNone.


Color Property Example

This example adds a medium-weight green border to all cells in the range B5:C10 on the spreadsheet.

Set c = Spreadsheet1.Constants
Spreadsheet1.Range("b5:c10").Borders.Weight = c.owcLineWeightMedium
Spreadsheet1.Range("b5:c10").Borders.Color = "green"


Column Property

Returns the number of the first column in the specified range. Read-only Long.


ColumnAxis Property

Returns a PivotGroupAxis object that represents the column axis in the specified PivotTable list. Read only.


ColumnAxis Property Example

This example inserts two field sets into the PivotTable list in the active view.

Set view = PivotTable1.ActiveView
view.ColumnAxis.InsertFieldSet view.FieldSets("Store Type")
view.RowAxis.InsertFieldSet view.FieldSets("Promotions")


ColumnCount Property

Returns the number of inner members on the column axis across all field sets in the specified PivotTable list. Read-only Long.

Remarks

The number returned by this property is equal to the number of cell coordinates on the column axis.


ColumnFieldCount Property

Returns the number of fields on the column axis across all field sets in the specified PivotTable list. Read-only Long.

Remarks

The number returned by this property does not include fields whose IsIncluded property is set to False.


ColumnMember Property

Returns a PivotMember object that represents the innermost member on the column axis that intersects the specified cell. Read-only.


ColumnMembers Property

Returns a PivotMembers collection that represents all the members of the specified column. Read-only.

Remarks

The collection of column members does not include any members that you have filtered by setting their IsFiltered property to True.


Columns Property

Range object: Returns a Range object that represents the columns in the specified range. Read-only.

Spreadsheet object: Returns a Range object that represents all the columns on the active worksheet. Read-only.

Worksheet object: Returns a Range object that represents all the columns on the specified worksheet. Read-only.


Columns Property Example

This example sets the font to bold in every other column in the visible range on the active worksheet.

For Each col In Spreadsheet1.ActiveSheet.VisibleRange.Columns
    If col.Column Mod 2 = 0 Then col.Font.Bold = True
Next

This example deletes all data from column 2 on the active worksheet.

Spreadsheet1.ActiveSheet.Columns(2).ClearContents


ColumnWidth Property

Returns or sets the width of all columns in the specified range. Returns Null if the columns in the range are not all the same width. Use the IsNull function to determine whether the return value is Null. Read/write Variant.


ColumnWidth Property Example

This example sets the formula, column width, and number format for all the cells in the range A1:F10 on the active worksheet.

Set rg = Spreadsheet1.ActiveSheet.Range("a1:f10")
rg.Formula = "=rand()"
rg.ColumnWidth = 100
rg.NumberFormat = "#.###"


Command Property

Indicates the command that caused the spreadsheet event to occur. Read-only SheetCommandEnum.


CommandText Property

Returns or sets the command string for the specified object. Read/write String.

For type dscCommandText, this property returns or sets a text string (usually SQL) that returns a single result set from the provider.

For type dscCommandFile, this property returns or sets the URL of an XML file containing data for the specified SchemaRowsource object.


Connection Property

Returns the ADO Connection object used by the data source control. Read-only.

Remarks

Use caution when calling methods or setting properties for the Connection object returned by this property. For example, you should not call the Connection object's Close method.


ConnectionString Property

Returns or sets the ADO connection string for a two-tier database connection. Read/write String.

Remarks

This property is equivalent to CurrentProject.BaseConnectionString in Microsoft Access. When the data source control creates a connection, the value of the data source control ConnectionString property may not be the same as the value returned by the ConnectionString property on the Connection object because the data source control uses other OLE DB providers to supply additonal services. For example, on an HTML page containing a data source control with ID "MSODSC" the following expressions may not be equivalent.

MSODSC.ConnectionString
MSODSC.Connection.ConnectionString


Constants Property

Returns an object that allows script users to use named constants. Read-only.

Remarks

For more information, see Using Named Constants in VBScript.

This property is required only on HTML pages. In other containers (such as Visual Basic), you can use defined constants from the object model directly, without first using the Constants property. Using the Constants property in other containers will work but is not recommended, as it will cause your code to run significantly slower.


Constants Property Example

This example adds a medium-weight green border to each cell in the range B5:C10 on the spreadsheet. Because named constants are not available in VBScript, the example sets a variable to the object returned by the Constants property.

Set c = Spreadsheet1.Constants
Spreadsheet1.Range("b5:c10").Borders.Weight = c.owcLineWeightMedium
Spreadsheet1.Range("b5:c10").Borders.Color = "green"


ConsumesRecordset Property

True for complex controls if data is supplied by the data source control. Any data-bound control has either a ControlSource property (used with a simple HTML control) or a ConsumesRecordset property (used with a complex control such as a PivotTable list or chart workspace). Read/write Boolean.


ControlSource Property

Returns or sets the name of the control to which the specified control is bound. Read/write String.

Remarks

This property returns or sets the values of the Name property and the Source property for the field to which the specified control is bound. The property values are returned as a string in the following format: "PageField.Name:PageField.Source" where the strings in italics are replaced with the actual values of the Name and Source properties. If the Name and Source property values are identical, only one value is returned.

If you set this property to the name of a schema field in a schema row source used by the section containing the specified control, the schema field is automatically added as a page field.


Count Property

Returns the number of objects in the specified collection. Read-only Long.


Count Property Example

This example sets variables to the number of columns and the number of rows in the visible range on the active worksheet.

Set vr = Spreadsheet1.ActiveSheet.VisibleRange
cc = vr.Columns.Count
rc = vr.Rows.Count


Criteria Property

Returns the Criteria object for the specified filter.


Criteria Property Example

This example turns on the AutoFilter for the range A1:J22, sets filters for columns 1 and 3, and then applies the filters.

Spreadsheet1.Range("a1:j22").AutoFilter
Set af = Spreadsheet1.ActiveSheet.AutoFilter
Set filterColumnOne = af.Filters(1)
Set filterColumnThree = af.Filters(3)
filterColumnOne.Criteria.Add "blue"    ' Don't show blue in column 1.
filterColumnOne.Criteria.Add "green"    ' Don't show green in column 1.
filterColumnThree.Criteria.Add "yellow"    ' Don't show yellow in column 3.
af.Apply


Crosses Property

Returns or sets a value that indicates how the specified axis crosses another axis. Can be one of the two ChartAxisCrossesEnum constants listed in the following table. Read/write.

Value | Meaning
chAxisCrossesAutomatic | The crossing point for the two axes is set automatically.
chAxisCrossesCustom | The CrossesAtValue and CrossingAxis properties specify the crossing point.


CrossesAtValue Property

Returns or sets the crossing point for the specified axis. Read/write Double.

Remarks

When you set this value for an axis, you are setting the value on the other axis where the axis you are setting will cross that other axis. For example, setting this property on the value (y) axis sets the category number where the value axis will cross the category (x) axis. The following example causes the value axis to cross the category axis at the third category.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).CrossesAtValue = 3


CrossesAtValue Property Example

This example sets the category axis to cross the value axis at value zero (0) in the chart workspace.

Set c = ChartSpace1.Constants
Set valueAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft)
Set categoryAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionBottom)
valueAxis.CrossingAxis = categoryAxis
categoryAxis.CrossesAtValue = 0


CrossingAxis Property

Returns or sets the crossing axis. If the specified axis is deleted, this property is set to Null, and the Crosses property is reset to chAxisCrossesAutomatic. Read/write WCAxis.


CrossingAxis Property Example

This example sets the category axis to cross the value axis at value zero (0) in the chart workspace.

Set c = ChartSpace1.Constants
Set valueAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft)
Set categoryAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionBottom)
valueAxis.CrossingAxis = categoryAxis
categoryAxis.CrossesAtValue = 0


CSVData Property

Returns or sets spreadsheet data as a comma-delimited list. Read/write String.


CSVData Property Example

This example sets the values for cells A1:G1.

Spreadsheet1.CSVData = "1,1,2,3,5,8,13"


CSVURL Property

Returns or sets the URL (Internet address) for the comma-delimited spreadsheet data file. Read/write String.


CSVURL Property Example

This example sets spreadsheet data from a file on the user's computer.

Spreadsheet1.CSVURL = "file:\test.csv"


CtrlKey Property

True if the CTRL key was pressed (held down) when the specified event occured. Available only with keyboard and mouse events. Read-only Boolean.


CtrlKey Property Example

This procedure runs whenever the user clicks the mouse with the pointer positioned over the control named “ChartSpace1.” Note that the procedure in this example is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures in VBScript.

Private Sub ChartSpace1_Click(ByVal ChartEventInfo As OWC.WCChartEventInfo)
    If ChartEventInfo.AltKey Then
        ' handle alt case here
    End If
    If ChartEventInfo.CtrlKey Then
        ' handle ctrl case here
    End If
End Sub


CurrentRegion Property

Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. Read-only


CurrentRegion Property Example

The functionin this example returns True if the entire current region for cell A1 on the active worksheet is visible (if the current region extends outside the visible range, the function returns False).

Function IsCurrentRegionVisible()
    Set cr = Spreadsheet1.ActiveSheet.Cells(1, 1).CurrentRegion
    Set vr = Spreadsheet1.ActivePane.VisibleRange
    Set ir = Spreadsheet1.Intersect(cr, vr)
    IsCurrentRegionVisible = (ir.Address = cr.Address)
End Function


CurrentSection Property

Returns a Section object that represents the current section (the section containing the control that currently has the focus). Read-only.


DataAxis Property

Returns a PivotDataAxis object that represents the data axis. Read-only


DataAxis Property Example

This example adds a total to the PivotTable list in the active view and inserts a field set on the data axis.

Set view = PivotTable1.ActiveView
Set c = PivotTable1.Constants

Set newTotal = view.AddTotal("myTotal", view.FieldSets("Freight").Fields(0), c.plFunctionSum)
view.DataAxis.InsertTotal newTotal
view.DataAxis.InsertFieldSet view.FieldSets("OrderDate")


DataEntry Property

True if the specified page is used only for data entry (False if the page includes a populated recordset). The default value is False. Read/write Boolean.


DataField Property

Returns the name of the field that the PivotField object is bound to if the data is coming from a recordset. Read-only String.


DataLabel Property

Returns a WCDataLabel object that represents the data label associated with the specified trendline. Read-only.


DataLabelsCollection Property

Returns a WCDataLabelsCollection object that contains the data labels for the specified series. Each series can contain only one set of data labels. Read-only.

For more information about returning a single member of a collection, see Returning an Object from a Collection.


DataLabelsCollection Property Example

This example adds data labels to the specified series in the chart workspace.

Set ch = ChartSpace1.Charts(0)
ch.SeriesCollection(0).DataLabelsCollection.Add

This example sets the font for the data labels for the specified series.

ChartSpace1.Charts(0).SeriesCollection(0).DataLabelsCollection(0).Font.Bold = True


DataMember Property

Returns or sets the data member name (the name of the recordset that the specified control will request from the data source). Read/write DataMember.

Remarks

A DataMember is a special String type.


DataPage Property

Returns a DataPage object that represents the data page for the specified section. Read-only.

Remarks

A data page is a cluster of sections that share a common record navigation control.


DataPages Property

Returns the DataPages collection for the data source control. Read-only.


DataPageSize Property

Returns or sets the number of records shown for the specified banding level on a banded data page. The default value is 5 for a banded page and 1 for a non-banded page. Read/write Long.


DataSource Property

Returns or sets the ADO DataSource object that represents the data source for the specified control. Read/write.


DataSourceType Property

Returns or sets the data source type for the specified chart workspace. Read/write ChartDataSourceTypeEnum.


DataType Property

PivotField or PageField object: Returns the specified field’s data type. Read-only ADO DataTypeEnum.

SchemaField or SchemaParameter object: Returns or sets the specified field’s data type. Read/write ADO DataTypeEnum.

Spreadsheet object: Specifies the spreadsheet’s data format and source, as shown in the following table. Read/write String.

Value | Meaning
HTMLURL | The data source is the URL specified by the HTMLURL property.
HTMLData | The data source is the string specified by the HTMLData property…
CSVURL | The data source is the URL specified by the CSVURL property.
CSVData | The data source is the string specified by the CSVData property.


DefaultControlType Property

Returns or sets the default control type for the specified data source control. Read/write DefaultControlTypeEnum.


DefaultRecordset Property

Returns the default ADO Recordset object for the specified control. Read-only.

Remarks

You should not use this property with sections that are bound to recordsets; instead, use the DataPage object's Recordset property.


DefaultSort Property

Returns or sets the field (or fields) on which the specified banding level is sorted. The field must be part of the banding level. If you use this property to specify multiple fields, use commas to separate the fields. Read/write String.


DefaultValue Property

Returns or sets the value that is placed in the specified control whenever the user creates a new record. Applies to any bound control or any control with a Value property, or any control that meets both of these criteria. Read/write String.


DetailAutoFit Property

PivotField object :True if the column width for the specified field is set automatically when the field is used on the data axis. Read/write Boolean.

PivotView object: True if the width of the detail grid is determined by the width of the detail fields. The width of the detail grid cannot exceed the value of the DetailMaxWidth property. If the DetailAutoFit property is set to False, the width of the detail grid is set based on the value of the DetailWidth property. If the DetailAutoFit property is set to True, any layout change automatically updates the DetailWidth property. Read/write Boolean.


DetailBackColor Property

Returns or sets the back color for the specified field when the field is displayed in a detail grid. The default back color is white. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


DetailCells Property

Returns a PivotDetailCell object based on a row and column index.

Syntax

expression.Cell(Row, Column)

expression An expression that returns a PivotCell object.

Row Required Long. Specifies the row that contains the specified cell.

Column Required Long. Specifies the column that contains the specified cell.


DetailFont Property

Returns a PivotFont object that represents the font used when the specified field is displayed in the detail grid. Read-only.


DetailForeColor Property

Returns or sets the fore color for the specified field when the field is displayed in a detail grid. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


DetailHAlignment Property

Returns or sets the way field values are horizontally aligned when the specified field is displayed in a detail grid. By default, values are left-aligned. Can be one of the PivotHAlignmentEnum enumerated values. Read/write.


DetailLeft Property

Returns or sets the leftmost visible column in the detail grid. Read/write Long.

Syntax

expression.DetailLeft(Column)

expression An expression that returns a PivotData object.

Column Required PivotMember object. Specifies the leftmost visible column in the detail grid.


DetailMaxHeight Property

Returns or sets the maximum height (in pixels) that the detail grid can attain when the value of the DetailAutoFit property is True. A scroll bar is displayed if the height of the detail grid would exceed the maximum height. The default value is 250 pixels. This property is ignored if the value of the DetailAutoFit property is False. Read/write Long.


DetailMaxWidth Property

Returns or sets the maximum width (in pixels) that the detail grid can attain when the value of the DetailAutoFit property is True. A scroll bar is displayed if the width of the detail grid would exceed the maximum width. The default value is 1024 pixels. This property is ignored if the value of the DetailAutoFit property is False. Read/write Long.


DetailRange Property

Returns a PivotDetailRange object for the specified area.

Syntax

expression.DetailRange(TopLeft, BottomRight)

expression An expression that returns a PivotCell object.

TopLeft Required PivotDetailCell object. Specifies the upper-left cell in the detail range.

BottomRight Required PivotDetailCell object. Specifies the lower-right cell in the detail range.


DetailRowHeight Property

Returns or sets the row height for the detail grid (in pixels). The default value is 10 pixels. Read/write Long.


DetailSortOrder Property

Returns or sets the sort order for fields on the summary axis. Read/write Variant.

Remarks

Fields are sorted in array order. If a field included in the sort order array has its SortDirection property set to plSortDirectionDefault, the field is ignored.


DetailTop Property

Returns or sets the index of the uppermost visible row in the detail grid. Read/write Long.


DetailWidth Property

PivotField object: Returns or sets the width of the specified field (in pixels) when it is displayed in the detail grid. Read/write Long.

PivotView object: Returns or sets the width of the detail grid (in pixels). Read/write Long.

Use the PivotView object’s DetailRowHeight property to set the detail grid height.

If the PivotView object’s DetailAutoFit property is set to True, the detail width is automatically reset when the PivotTable list is refreshed. If you set the DetailWidth property programmatically, the DetailAutoFit property is automatically set to False.


Direction Property

SchemaParameter object: Sets the Direction parameter for the SchemaRowSource object. Read-only ParameterDirectionEnum (ADO).

WCErrorBars object: Returns or sets the error-bar direction for the specified error bars. Read/write ChartErrorBarDirectionEnum.

Remarks

You can use the chErrorBarDirectionX constant only in xy scatter, polar, bar, or bubble charts.


Direction Property Example

This example adds error bars to the specified chart and sets the error-bar direction. Charts(0) must refer to an xy scatter, polar, bar, or bubble chart.

Set eb = ChartSpace1.Charts(0).SeriesCollection(0).ErrorBarsCollection.Add
eb.Direction = ChartSpace1.Constants.chErrorBarDirectionX


Dirty Property

True if changes have been made to the spreadsheet since the last time it was saved. Read/write Boolean.


Dirty Property Example

This example exports the spreadsheet to a file if changes have been made to it since the last time it was saved.

If Spreadsheet1.Dirty Then
    Spreadsheet1.ActiveSheet.Export "sstest.xls", Spreadsheet1.Constants.ssExportActionNone
End If


DisplayAlerts Property

True if message-box alerts are displayed. The default value is True. Read/write Boolean.


DisplayColHeaders Property

True if column headers are displayed on the specified spreadsheet. The default value is True. Read/write Boolean.


DisplayColHeaders Property Example

This example hides row and column headers on the spreadsheet.

Spreadsheet1.DisplayColHeaders = False
Spreadsheet1.DisplayRowHeaders = False


DisplayEmptyMembers Property

True if empty members are displayed, even if they do not have aggregates. The default value is False. Read/write Boolean.


DisplayExpandIndicator Property

True if expansion indicators are displayed for members with available child members or detail records. When the expansion indicator is hidden, the member display name appears in this space. The default value is True. Read/write Boolean.


DisplayFieldList Property

True if the Field List is open. The default value is False. Read/write Boolean.


DisplayGridlines Property

True if gridlines are displayed on the specified spreadsheet. The default value is True. Read/write Boolean.


DisplayGridlines Property Example

This example hides gridlines on the spreadsheet.

Spreadsheet1.DisplayGridlines = False


DisplayHorizontalScrollBar Property

True if the horizontal scroll bar is displayed on the specified spreadsheet. The default value is True. Read/write Boolean.


DisplayHorizontalScrollBar Property Example

This example hides the horizontal scroll bar on the spreadsheet.

Spreadsheet1.DisplayHorizontalScrollBar = False


DisplayPropertyToolbox Property

True if the Property Toolbox is visible. Read/write Boolean.


DisplayRowHeaders Property

True if row headers are displayed on the specified spreadsheet. The default value is True. Read/write Boolean.


DisplayRowHeaders Property Example

This example hides row and column headers on the spreadsheet.

Spreadsheet1.DisplayColHeaders = False
Spreadsheet1.DisplayRowHeaders = False


DisplayTitleBar Property

True if the title bar on the specified spreadsheet is displayed. The default value is True. Read/write Boolean.


DisplayTitleBar Property Example

This example hides the spreadsheet’s title bar.

Spreadsheet1.DisplayTitleBar = False


DisplayToolbar Property

True if the toolbar on the specified spreadsheet or PivotTable list is displayed. The default value is True. Read/write Boolean.

Remarks

Hiding the toolbar does not change the height of the PivotTable list, but it does cause a layout change. The width is unchanged unless the AutoFit property is True and the PivotTable list was sized wider than the necessary to accomodate the toolbar.


DisplayToolbar Property Example

This example hides the spreadsheet’s toolbar.

Spreadsheet1.DisplayToolbar = False


DisplayVerticalScrollBar Property

True if the vertical scroll bar on the specified spreadsheet is displayed. The default value is True. Read/write Boolean.


DisplayVerticalScrollBar Property Example

This example hides the spreadsheet’s vertical scrollbar.

Spreadsheet1.DisplayVerticalScrollBar = False


EditData Property

Returns the string entered by the user in edit mode. This property is valid only when used with the EndEdit event. Read-only String.


EditData Property Example

The event procedure in this example runs whenever the user types data in a spreadsheet cell. If the data is anything other than an integer between 1 and 6, the procedure displays a message box and cancels the entry. Note that this procedure is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures.

Private Sub Spreadsheet1_EndEdit(ByVal EventInfo As OWC.SpreadsheetEventInfo)
    Select Case EventInfo.EditData
        Case "1", "2", "3", "4", "5", "6"
        Case Else
            MsgBox "Please enter an integer between 1 and 6 inclusive."
            EventInfo.ReturnValue = False
    End Select
End Sub


ElementExtensions Property

Returns the ElementExtensions object for the data source control. Read-only.


ElementID Property

Specifies the ID tag for the HTML element used with the specified extension. Read-only String.


EnableAutoCalculate Property

True if the specified spreadsheet or worksheet is automatically recalculated; False if the user must request a recalculation. The default value is True. Read/write Boolean.


EnableAutoCalculate Property Example

This example disables automatic recalculation for the active worksheet.

Spreadsheet1.ActiveSheet.EnableAutoCalculate = False


EnableAutoFilter Property

True if the specified worksheet can be filtered. This property has no effect if the Protection Enabled property is set to False. Read/write Boolean.


EnableAutoFilter Property Example

This example disables the Auto Filter for the active worksheet.

Spreadsheet1.ActiveSheet.EnableAutoFilter = False
Spreadsheet1.ActiveSheet.Protection.Enabled = True


Enabled Property

True if worksheet protection is enabled for the specified worksheet. If this property is set to False, the other Protection object property settings are ignored. Read/write Boolean.


Enabled Property Example

This example prevents a user from inserting rows on the active worksheet.

Set pt = Spreadsheet1.ActiveSheet.Protection
pt.AllowInsertingRows = False
pt.Enabled = True


EnableEvents Property

True if events are enabled for the spreadsheet or the chart workspace. Read/write Boolean.


EnableEvents Property Example

This example prevents spreadsheet event procedures from running.

Spreadsheet1.EnableEvents = False


EnableUndo Property

True if the undo functionality is enabled for the specified spreadsheet. Read/write Boolean.


EnableUndo Property Example

This example creates an undo block containing code that sets the number format and font for the active cell.

Spreadsheet1.EnableUndo = True
Spreadsheet1.BeginUndo
    Set ac = Spreadsheet1.ActiveCell
    ac.NumberFormat = "0.###"
    ac.Font.Color = "blue"
    ac.Font.Name = "times new roman"
Spreadsheet1.EndUndo


EndStyle Property

Returns or sets the end style for error bars. The default constant is chEndStyleCap. Read/write ChartEndStyleEnum.


EndStyle Property Example

This example adds error bars with no end caps to the specified series.

Set eb = ChartSpace1.Charts(0).SeriesCollection(0).ErrorBarsCollection.Add
eb.Amount = 0.1
eb.EndStyle = ChartSpace1.Constants.chEndStyleNone


EntireColumn Property

Returns a Range object that represents the entire column (or columns) containing the specified range. Read-only.


EntireColumn Property Example

This example sets the font color for the entire column containing the active cell.

Spreadsheet1.ActiveCell.EntireColumn.Font.Color = "green"


EntireRow Property

Returns a Range object that represents the entire row (or rows) containing the specified range. Read-only.


EntireRow Property Example

This example sets the font color for the entire row containing the active cell.

Spreadsheet1.ActiveCell.EntireRow.Font.Color = "red"


Error Property

Returns an ADO Error object that contains error information about the specified event. Read-only.


ErrorBarsCollection Property

Returns a WCErrorbarsCollection collection that contains a WCErrorBars object for each set of error bars in the specified series. A series can have only one set of error bars, so the WCErrorBarsCollection collection never contains more than one object. Read-only.


ErrorBarsCollection Property Example

This example adds error bars to the specified series.

Set eb = ChartSpace1.Charts(0).SeriesCollection(0).ErrorBarsCollection.Add


Expanded Property

PivotCell object: True if the detail grid is being displayed for the specified cell. If this property is set to False, the detail grid is not displayed, and aggregates are displayed if they are available. Read/write Boolean.

PivotMember object: True if child members or details are being displayed for the specified member; False if child members or details are collapsed. Read/write Boolean.

PivotField object: Returns or sets the expanded state of all the members of the specified field at the same time. Setting the Expanded property for the field immediately sets the Expanded property for all the members associated with the field. Setting the Expanded property for a single member of the field does not affect the Expanded property for the field itself.


ExpandedByDefault Property

True if the specified group level is expanded by default. The default value is False. Read/write Boolean.


Explosion Property

Returns or sets the explosion value for the specified pie-chart or doughnut-chart slice. The valid range is from 0 through 1000. The explosion value is equal to a percentage of the chart’s radius. Note that setting this property to 100 moves the tips of the slices away from the center of the chart to a distance equal to the chart’s radius. Read/write Long.


Explosion Property Example

This example moves the tips of the pie-chart or doughnut-chart slices in the specified series to a distance equal to one tenth the radius of the chart.

ChartSpace1.Charts(0).SeriesCollection(0).Explosion = 10


Field Property

Returns a PivotField object that represents the field associated with the specified member or total. Read-only.


FieldLabelBackColor Property

Returns or sets the back color used for field labels for rows, columns, and filters. The default value is 25% gray. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


FieldLabelFont Property

Returns a PivotFont object that represents the field label font for rows, columns, and filters. Read-only.


FieldLabelForeColor Property

Returns or sets the fore color used for field labels for rows, columns, and filters. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


FieldLabelHeight Property

Returns the height of the field labels for rows, columns, and filters. Read-only Long.


Fields Property

Returns the PivotFields collection for the specified field set. Read-only


FieldSet Property

Returns a PivotFieldSet object that represents the field set to which the specified field belongs. Read-only.


FieldSets Property

Returns a PivotFieldSets object that contains the field sets associated with the specified axis or view. Read-only.


FieldType Property

Returns the field type for the specified field. Read-only dscFieldTypeEnum.


Filter Property

Returns the ADO filter string for the recordset that corresponds to the data page. Read/write Variant.


FilterAxis Property

Returns a PivotFilterAxis object that represents the filter axis. Read-only.


FilterFunction Property

Criteria object: Returns or sets how the specified criteria work with the AutoFilter. If this property is set to ssFilterFunctionInclude, the specified items are displayed in the filtered list and all other items are removed. If this property is set to ssFilterFunctionExclude, the specified items are removed from the filtered list and all other items are displayed. Read/write SheetFilterFunction.

PivotField object: Returns or sets the filter function for the specified field. Read/write PivotFieldFilterFunctionEnum.


FilterMember Property

Returns or sets the filter member when the specified field set is on the filter axis. The value of this property can be the unique name of the filter member (as returned or set by the UniqueName property), or a reference to the filter member object. Read/write Variant.


FilterMembers Property

Returns or sets a list of members to be used in a filter operation. Read/write Variant.

Remarks

You can set this property when a field is used on the row, column, or data axis. If this property is set for a field on the data axis, the field is used to filter the detail records but not the members and aggregates. If this property is set on a field on the summary axis, the field is used to filter the detail records and aggregates.

You can set this property by using a Variant array of PivotMember objects or string member identifiers (retrieved by using the Caption, Name, or UniqueName property), as shown in the following examples.

This example uses an array of String values.

PivotTable1.ActiveView.ColumnAxis.Fieldsets("State").Fields(0).FilterMembers = Array("WA", "OR")

This example uses an array of objects.

Set tmpMemberCol = PivotTable1.ActiveView.Fieldsets("State").Members(0).ChildMembers("WA")
Set tmpMemberRow = PivotTable1.ActiveView.Fieldsets("State").Members(0).ChildMembers("OR")
PivotTable1.ActiveView.ColumnAxis("State").FilterMembers = Array(tmpMemberCol, tmpMemberRow)


FilterMode Property

True if any worksheet rows are currently hidden by AutoFilter criteria. The default value is False. Read-only Boolean.

Remarks

If the Auto Filter drop-down arrows are visible but no rows are currently filtered (all rows are visible), the AutoFilterMode property is True and the FilterMode property is False.


Filters Property

Returns the Filters collection for the specified AutoFilter. The Filters collection contains one Filter object for each column in the filtered range. Read-only.


Filters Property Example

This example turns on the AutoFilter for the range A1:J22, sets filters for columns 1 and 3, and then applies the filters.

Spreadsheet1.Range("a1:j22").AutoFilter
Set af = Spreadsheet1.ActiveSheet.AutoFilter
Set filterColumnOne = af.Filters(1)
Set filterColumnThree = af.Filters(3)
filterColumnOne.Criteria.Add "blue"    ' Don't show blue in column 1.
filterColumnOne.Criteria.Add "green"    ' Don't show green in column 1.
filterColumnThree.Criteria.Add "yellow"    ' Don't show yellow in column 3.
af.Apply


FirstSection Property

Returns a Section object that represents the first section on the specified data page. Read-only.


FirstSliceAngle Property

Returns or sets the angle of the first pie-chart or doughnut-chart slice, in degrees (clockwise from vertical). Applies only to pie and doughnut charts. Read/write Long.


FirstSliceAngle Property Example

This example sets the angle of the first pie-chart or doughnut-chart slice to 45 degrees. Charts(0) must refer to a pie or doughnut chart.

ChartSpace1.Charts(0).FirstSliceAngle = 45


Font Property

Returns an OWCFont or PivotFont object that represents the font for the specified object (the returned object type depends on the object to which this property is applied). Read-only.


FontName Property

Returns or sets the name of the font in the specified control. Read/write String.

This example sets the font name to "Courier New" for control RNC1.

RNC1.FontName = "Courier New"


ForeColor Property

Returns or sets the fore color for the specified object or area. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


Formula Property

Returns or sets the formula for the specified range. Returns an empty string if the range is empty. Read/write String.


FormulaLocal Property

Returns the range formula in the language that the user is working in. Read-only String.


FormulaLocal Property Example

The event procedure in this example displays the range formula in the language that the user is working in whenever the user double-clicks in the spreadsheet. Note that this procedure is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures.

Private Sub Spreadsheet1_DblClick(ByVal EventInfo As OWCCtl.SpreadsheetEventInfo)
    MsgBox EventInfo.Range.FormulaLocal
End Sub


FreezePanes Property

True if the specified worksheet contains frozen panes. Read-only Boolean.


FreezePanes Property Example

This example removes any frozen panes from the active worksheet.

Set c = Spreadsheet1.Constants
If Spreadsheet1.ActiveSheet.FreezePanes Then _
    Spreadsheet1.Range("a1").FreezePanes c.ssFreezeNone


Function Property

Returns or sets the function to be used for generating aggregate values. Read/write PivotTotalFunctionEnum.

Remarks

The Sum operator is the default for numeric data types. The Count operator is the default for other data types.


GapWidth Property

Returns or sets the amount of spacing between markers in adjacent categories, as a percentage of the column width. A value of zero provides no space between adjacent category markers, while positive values create a gap. Read/write Long.


GapWidth Property Example

This example sets the spacing between markers in adjacent categories to 250 points.

ChartSpace1.Charts(0).GapWidth = 250


GridX Property

Returns or sets the number of dotted gridlines per inch on the x-axis in the specified data page’s designer default section. Read/write Long.


GridY Property

Returns or sets the number of dotted gridlines per inch on the y-axis in the specified data page’s designer default section. Read/write Long.


GroupedAutoFit Property

True if the specified field's column width is set automatically when the field is used on the row axis or the column axis. Read/write Boolean.


GroupedWidth Property

Returns or sets the width of the specified field (pixels) when the field is displayed on the row axis or the column axis. The default value is 50 pixels. Read/write Long.


GroupFilterControl Property

Returns or sets the ID of the list box or combo box used to set currency. Read/write String.


GroupFilterField Property

Returns or sets the filter string used by the group filter control (the field on which the control will set currency). This string must be the name of a page field in the group level's record source. Read/write String.


GroupFooter Property

True if the specified section has a footer. You can set this property for any banding level except the innermost one. The default value is False. Read/write Boolean.


GroupHeader Property

True if the specified section has a header. The default value is True. Read/write Boolean.


GroupingDefs Property

Returns the GroupingDefs collection for the specified recordset definition. This collection contains the GroupingDef objects that create grouping levels for the detail records. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


GroupInterval Property

Returns or sets the group interval. Read/write Long.


GroupLevel Property

Returns the GroupLevel object for the specified data page. A GroupLevel object corresponds to a recordset produced by a recordset definition or grouping definition.


GroupLevels Property

Returns the GroupLevels collection for the specified data source control. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


GroupOn Property

Returns or sets the way GroupLevel objects or PageField objects are grouped. Read/write dscGroupOnEnum.


HAlignment Property

Returns or sets the way data is aligned in the specified range, label, or total. Read/write PivotHAlignmentEnum for the PivotLabel and PivotTotal objects; read/write Variant for the Range object (can be one of the SheetHAlignEnum constants, or returns Null if the all the cells in the range are not aligned in the same way). Use the IsNull function to determine whether the return value is Null.


HAlignment Property Example

This example centers the values in the range A1:F1.

Spreadsheet1.Range("a1:f1").HAlignment = c.ssHAlignCenter


HasAutoCaption Property

True if the name for the specified trendline is generated automatically from the trendline type and series index ("Poly. (Series 1)", for example). Setting the trendline’s Caption property sets this property to False. Read/write Boolean.


HasAutoCaption Property Example

This example sets the caption for the specified trendline caption if the caption is currently set to be generated automatically. Note that SeriesCollection(0) must refer to a series that already has a trendline.

Set sZero = ChartSpace1.Charts(0).SeriesCollection(0)
sZero.Line.Color = "red"
Set trndline = sZero.Trendlines(0)
If trndline.HasAutoCaption Then trndline.Caption = "data trend"


HasAutoMajorUnit Property

True if the major unit for the specified axis is determined automatically. The default value is True. You should use this property only for value axes. Read/write Boolean.


HasAutoMajorUnit Property Example

This example sets the major unit for the specified axis if the major unit is currently set to be determined automatically.

Set c = ChartSpace1.Constants
Set axs = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft)
If axs.HasAutoMajorUnit Then axs.MajorUnit = 5000


HasAutoMaximum Property

True if the maximum scale value for the specified axis is set to be determined automatically. The default value is True. Read/write Boolean.


HasAutoMaximum Property Example

This example sets the maximum scale value for the specified axis if the maximum value is currently set to be determined automatically.

Set c = ChartSpace1.Constants
Set axisScale = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Scaling
If axisScale.HasAutoMaximum Then axisScale.Maximum = 50000


HasAutoMinimum Property

True if the minimum scale value for the specified axis is set to be determined automatically. The default value is True.Read/write Boolean.


HasAutoMinimum Property Example

This example sets the minimum scale value for the specified axis if the minimum value is currently set to be determined automatically.

Set c = ChartSpace1.Constants
Set axisScale = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Scaling
If axisScale.HasAutoMinimum Then axisScale.Minimum = 10


HasAutoMinorUnit Property

True if the minor unit for the specified axis is set to be determined automatically. The default value is True. You should use this property only for value axes. Read/write Boolean.


HasAutoMinorUnit Property Example

This example sets the minor unit for the specified axis to increments of 500 if the unit is currently set to be determined automatically.

Set c = ChartSpace1.Constants
Set axs = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft)
If axs.HasAutoMinorUnit Then axs.MinorUnit = 500


HasBubbleSize Property

True if every data label for the specified series or chart currently displays its bubble size. The default value is False. Note that this property is available only for bubble charts. Read/write Boolean.

Remarks

Data label components are always displayed in the following order: [SeriesName] [CategoryName] [Value] [BubbleSize] [Percentage].


HasBubbleSize Property Example

This example causes the data labels for the specified series to display their bubble size. Note that Charts(0) must refer to a bubble chart.

Set dl = ChartSpace1.Charts(0).SeriesCollection(0).DataLabelsCollection.Add
dl.HasBubbleSize = True


HasCategoryName Property

True if every data label for the specified series or chart currently displays its category name or label. The default value is False. Read/write Boolean.

Remarks

Data label components are always displayed in the following order: [SeriesName] [CategoryName] [Value] [BubbleSize] [Percentage]


HasCategoryName Property Example

This example causes the data labels for the specified series to display their category and series names.

Set dl = ChartSpace1.Charts(0).SeriesCollection(0).DataLabelsCollection(0)
dl.HasCategoryName = True
dl.HasSeriesName = True


HasChartSpaceLegend Property

True if the specified chart workspace has a legend. Read/write Boolean.

Remarks

Setting this property to False causes the legend to be deleted from the chart workspace. When this happens, all custom formatting is lost and must be reset if the property is subsequently set to True.


HasChartSpaceLegend Property Example

This example sets the chart workspace title and positions the chart workspace legend on the left side of the workspace.

Set c = ChartSpace1.Constants
ChartSpace1.HasChartSpaceTitle = True
ChartSpace1.ChartSpaceTitle.Caption = "Monthly Sales Data"
ChartSpace1.HasChartSpaceLegend = True
ChartSpace1.ChartSpaceLegend.Position = c.chLegendPositionLeft


HasChartSpaceTitle Property

True if the specified chart workspace has a title. Read/write Boolean.

Remarks

Setting this property to False causes the title to be deleted from the chart workspace. When this happens, all custom formatting is lost and must be reset if the property is subsequently set to True.


HasChartSpaceTitle Property Example

This example sets the chart workspace title and positions the chart workspace legend on the left side of the workspace.

Set c = ChartSpace1.Constants
ChartSpace1.HasChartSpaceTitle = True
ChartSpace1.ChartSpaceTitle.Caption = "Monthly Sales Data"
ChartSpace1.HasChartSpaceLegend = True
ChartSpace1.ChartSpaceLegend.Position = c.chLegendPositionLeft


HasDetails Property

True if detail records can be displayed for a given cell; False if detail records are not available for display. This property is automatically reset whenever the data is requeried. If this property is set to False, the expansion indicators are not displayed for inner members. This property is always set to False if the provider is multidimensional. Read-only Boolean.


HasFormula Property

True if all cells in the range contain formulas, False if none do, and Null if some cells contain formulas and others do not. Read-only Variant. Use the IsNull function to determine if the return value is Null.


HasFormula Property Example

This example recalculates the active worksheet if any cell on the worksheet contains a formula.

hf = Spreadsheet1.ActiveSheet.Cells.HasFormula
If IsNull(hf) Or hf Then Spreadsheet1.ActiveSheet.Calculate


HasLegend Property

True if the specified chart has a legend. Read/write Boolean.


HasLegend Property Example

This example sets the specified chart to have a legend.

ChartSpace1.Charts(0).HasLegend = True


HasMajorGridlines Property

True if the specified axis has major gridlines. Note that any axis can have gridlines. Read/write Boolean.


HasMajorGridlines Property Example

This example turns off major gridlines on the specified axis.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).HasMajorGridlines = False


HasMergeCells Property

True if the range is part of a merged cell, False if it is not, or Null if the range overlaps a merged cell (part of it is in a merged cell and part is not). Use the IsNull function to determine if the return value is Null. Read-only Variant.


HasMergeCells Property Example

If cell A7 is in a merged cell, this example sets the value for the merged cell to 15.

Set ma = Spreadsheet1.Range("a7").MergeArea
If Spreadsheet1.Range("a7").HasMergeCells Then ma.Value = 15


HasMinorGridlines Property

True if the specified axis has minor gridlines. Note that any axis can have gridlines. Read/write Boolean.


HasMinorGridlines Property Example

This example turns off minor gridlines on the specified axis.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).HasMinorGridlines = False


HasPercentage Property

True if every data label for the specified series or chart currently displays its percentage value. The default value is False. This property is available only for pie, doughnut, and stacked charts. Read/write Boolean.

Remarks

Data label components are always displayed in the following order: [SeriesName] [CategoryName] [Value] [BubbleSize] [Percentage].


HasPercentage Property Example

This example adds data labels showing percentage value to a series. Note that Charts(0) must refer to a pie, doughnut, or stacked chart.

Set dl = ChartSpace1.Charts(0).SeriesCollection(0).DataLabelsCollection.Add
dl.HasPercentage = True


HasSeriesName Property

True if every data label for the specified series or chart currently displays its series name. The default value is False. This property is available only for pie, doughnut, and stacked charts. Read/write Boolean.

Remarks

Data label components are always displayed in the following order: [SeriesName] [CategoryName] [Value] [BubbleSize] [Percentage].


HasSeriesName Property Example

This example causes the data labels for the specified series to display their category and series names.

Set dl = ChartSpace1.Charts(0).SeriesCollection(0).DataLabelsCollection(0)
dl.HasCategoryName = True
dl.HasSeriesName = True


HasSplit Property

True if the scale for the specified axis has a split point between the value of its SplitMinimum property and the value of its SplitMaximum property. The default value is False. Read/write Boolean.


HasSplit Property Example

This example splits the specified axis and sets the split minimum and split maximum values.

Set c = ChartSpace1.Constants
Set axisScale = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Scaling
axisScale.HasSplit = True
axisScale.SplitMinimum = 17000
axisScale.SplitMaximum = 45000


HasTickLabels Property

True if the specified axis has a label at each major tick mark. The default value is True. Read/write Boolean.


HasTickLabels Property Example

This example turns off tick-mark labels on the specified axis.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).HasTickLabels = False


HasTitle Property

True if the specified chart or axis has a title. The default value is False. Read/write Boolean.


HasTitle Property Example

This example causes the title of the specified chart to be displayed and sets the title font.

ChartSpace1.Charts(0).HasTitle = True
ChartSpace1.Charts(0).Title.Font.Name = "times new roman"


HasValue Property

True if every data label for the specified series or chart currently displays its y-axis value. The default value is False. Read/write Boolean.

Remarks

Data label components are always displayed in the following order: [SeriesName] [CategoryName] [Value] [BubbleSize] [Percentage].

Setting this property to True on a scatter chart or polar chart causes both x-axis values and y-axis values to be displayed.


HasValue Property Example

This example sets the data labels for the specified series to display their y-axis values.

Set dl = ChartSpace1.Charts(0).SeriesCollection(0).DataLabelsCollection(0)
dl.HasValue = True


Height Property

Returns or sets the height of the specified object, in points. Read/write Long for the PivotTable object; read-only Long for the PivotMember and Range objects.


HeightRatio Property

Returns or sets the height ratio for the specified chart in relation to the other charts in the chart workspace. The default value is 100. Read/write Long.

Remarks

This property sets the height of the specified chart relative to the height of the other charts in the chart workspace. For this property to have any effect, you must have more than one chart in the chart workspace. When more than one chart is displayed, the charts are displayed in a grid (for more information, see the Help topics for the ChartLayout and ChartWrapCount properties). Initially, the HeightRatio and WidthRatio properties are set to 100 for all charts in the grid, and all charts are the same size.

To change the height of charts in the grid, adjust the HeightRatio property settings. For example, if each chart is displayed in three rows, all the charts have an initial HeightRatio setting of 100. If you want row 3 to be only half the available height, set its HeightRatio setting to 200; the remaining half of the chart height is divided between rows 1 and 2. Because the height specified by the HeightRatio property is relative, you can set this property for the three rows to 1,1,2; 100,100,200; or 20,20,40, all of which have the same effect.

If the chart workspace contains charts displayed in more than one row, the largest HeightRatio setting in each row is used to set the relative height for the entire row.

This property is useful for creating price and volume stock charts in which the volume chart is half the size of the price chart.


Hidden Property

True if the specified range is currently hidden. The range must span an entire column or row. Read/write Boolean.


Hidden Property Example

This example hides column 2 on the active worksheet.

Spreadsheet1.ActiveSheet.Columns(2).Hidden = True


HoleSize Property

Returns or sets the hole size for the specified doughnut chart. The hole size must be a value from 0 through 90 (the default value is 50). Read/write Long.

Remarks

Setting this property to zero (0) changes the doughnut chart to a pie chart (the Type property for the WCChart object returns chChartTypePieStacked).


HoleSize Property Example

This example sets hole size for the specified chart. Note that Charts(0) must refer to a doughnut chart.

ChartSpace1.Charts(0).HoleSize = 25


HTMLContainer Property

Returns the HTML DIV that contains the specified section. Read-only.


HTMLData Property

Returns or sets spreadsheet data as a properly formatted HTML string. Read/write String.


HTMLURL Property

Returns or sets the URL (Internet address) for the spreadsheet HTML data file. Read/write String.


Hyperlink Property

Returns a Hyperlink object that represents the hyperlink for the specified range. Read-only.


Hyperlink Property Example

This example resolves the hyperlink in cell B15 on the active worksheet, downloads the target document, and then displays the document.

Spreadsheet1.ActiveSheet.Range("b15").Hyperlink.Follow


Include Property

Returns or sets the error bar elements that will be included on the specified chart. The default value is chErrorBarIncludeBoth. Read/write ChartErrorBarIncludeEnum.


Include Property Example

This example adds error bars to the specified chart and sets the error bars to display only plus values.

Srt c = ChartSpace1.Constants
Set eb = ChartSpace1.Charts(0).SeriesCollection(0).ErrorBarsCollection.Add
eb.Include = c.chErrorBarIncludePlusValues


Index Property

Returns the index number of the specified object within the collection of similar objects. Read-only Long.

For the WCSeries object, you can set the index number of a series object. The specified series is moved to the specified position, and any other series are reordered as necessary.


Index Property Example

This event procedure runs whenever the user clicks inside the chart workspace. If the user clicks a point, the procedure displays the series and point index values. Note that the event procedure in this example is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures in VBScript.

Private Sub ChartSpace1_Click(ByVal ChartEventInfo As OWC.WCChartEventInfo)
    Set c = ChartSpace1.Constants
    If ChartSpace1.SelectionType = c.chSelectionPoint Then
        seriesNumber = ChartSpace1.Selection.Parent.Index
        pointNumber = ChartSpace1.Selection.Index
        MsgBox "series " & seriesNumber & " point " & pointNumber
    End If
End Sub

This example moves series 1 to the fourth index position of an XY chart containing 6 series. During the move, series 2 through 4 are reordered to series 1 through 3. Series 0 and series 5 stay in the same position.

ChartSpace1.Charts(0).SeriesCollection(1).Index = 4


Interior Property

Returns an Interior object that represents the interior of the specified object. Read-only.

Remarks

For a chart series or point, the Interior property can be used to set the fill color for columns, bars, pies, areas, and markers.


Interior Property Example

This example sets the interior color for the specified series.

ChartSpace1.Charts(0).SeriesCollection(0).Interior.Color = "red"


IsDisplayingEquation Property

True if the equation for the trendline for the specified series is displayed on the chart (in the same data label as the R-squared value). The default value is True. Read/write Boolean.


IsDisplayingEquation Property Example

This example adds a trendline to the specified series. Only the trendline equation is displayed.

Set tl = ChartSpace1.Charts(0).SeriesCollection(0).Trendlines.Add
tl.IsDisplayingEquation = True
tl.IsDisplayingRSquared = False


IsDisplayingRSquared Property

True if the R-squared value for the trendline for the specified series is displayed on the chart (in the same data label as the equation). Setting this property to True automatically turns on data labels. The default value is True. Read/write Boolean.


IsDisplayingRSquared Property Example

This example adds a trendline to the specified series. Only the trendline equation is displayed.

Set tl = ChartSpace1.Charts(0).SeriesCollection(0).Trendlines.Add
tl.IsDisplayingEquation = True
tl.IsDisplayingRSquared = False


IsExpanded Property

True if the specified section is expanded. Read-only Boolean.


IsFiltered Property

True if the current filter is applied. The default value is True. Filter settings are based on the current values of the FilterMember property of the filter axis field set, and the FilterMembers property of fieldsets in the row, column and data axis. Read/write Boolean.


IsFilterOn Property

True if the data page filter is applied. Read/write Boolean.


IsIncluded Property

True if the field is included and active in the specified field set. Read/write Boolean.

Remarks

Inactive fields are not visible in the PivotTable list. A field set must have at least one field for which the IsIncluded property is set to True. Attempting to set this property to False for the only field where it is set to True generates a run-time error. This property is ignored when the field set is used on the filter axis. If the user hides the last field in the user interface, the field set is removed from the axis.


IsTotal Property

True if the specified member is used to display a subtotal. Read-only Boolean.


Italic Property

True if the font style is italic. Read/write Boolean for the PivotFont object; read/write Variant for the OWCFont object (returns Null if some portions of the text are italic and some are not). Use the IsNull function to determine whether the return value is Null.


Italic Property Example

This example sets the font to italic for the contents of column 2.

Spreadsheet1.Columns(2).Font.Italic = True


Item Property

Returns a single object from the specified collection. The Item property is the default property for most collections, so it is usually not necessary to explicitly use this property. For more information about returning a single member of a collection, see Returning an Object from a Collection.

Syntax

expression.Item(Index)

expression An expression that returns an object in the Applies To list.

Index Required Variant. The name or index number of the object that will be returned.

Remarks

When applied to the Range object, the Item property can specify the row and column index numbers as two arguments instead of the cell index number as one argument. When two arguments are used, the first should specify the row containing the returned cell, and the second should specify the column containing the returned cell.


JoinType Property

Returns or sets the way a query will be formed. Read/write dscJoinTypeEnum.

Remarks

You can use this property only if the PageRelationship object's Type property is set to dscLookup.


KeyChar Property

Returns the key character for the key that caused the event. The value is an empty string ("") unless the event is KeyDown, KeyPress, or KeyUp. Read-only String.


KeyChar Property Example

The event procedure in this example makes it possible for the user to rotate the specified chart by using the number keys. Note that Charts(0) must refer to either a pie chart or a doughnut chart. Note also that this event procedure is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures in VBScript.

Private Sub ChartSpace1_KeyPress(ByVal ChartEventInfo As OWC.WCChartEventInfo)
    kc = Asc(ChartEventInfo.KeyChar)
    If kc > 47 And kc < 58 Then 'key was a number key
        ChartSpace1.Charts(0).FirstSliceAngle = (kc - 48) * 10
    End If
End Sub


KeyCode Property

Returns the key code for the key that caused the event. Valid only for the KeyDown and KeyUp events. Read-only Long.


KeyCode Property Example

The event procedure in this example makes it possible for the user to rotate the specified chart by using the left and right arrow keys. Note that Charts(0) must refer to either a pie chart or a doughnut chart. Note also that this event procedure is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures in VBScript.

Private Sub ChartSpace1_KeyDown(ByVal ChartEventInfo As OWC.WCChartEventInfo)
    Select Case ChartEventInfo.KeyCode
        Case 37 'left arrow
            i = -10
        Case 39 'right arrow
            i = 10
    End Select
    ChartSpace1.Charts(0).FirstSliceAngle = _
        ChartSpace1.Charts(0).FirstSliceAngle + i
End Sub


Label Property

Returns a PivotLabel object that represents the view label or the axis label. Read-only.


Left Property

PivotData object: Returns or sets the member immediately above the leftmost visible column. Read/write PivotMember.

Range object: Returns the distance from the left edge of the spreadsheet to the left edge of the specified range. This value can be negative if the range is outside the visible range. Read-only Long.


Left Property Example

This example scrolls to the left through the spreadsheet window to display cell F1 if this cell is currently to the left of the visible range.

Set r = Spreadsheet1.Range("f1")
If r.Left < 0 Then Spreadsheet1.ActiveSheet.Scroll r


Legend Property

Returns a WCLegend object that represents the legend for the specified chart. Read-only.


LegendEntries Property

Returns a WCLegendEntries collection for the specified legend. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


LegendEntries Property Example

This example hides the specified legend entry.

ChartSpace1.Charts(0).Legend.LegendEntries(1).Visible = False


Length Property

Returns or sets the maximum field length or parameter length. Read/write Long.


Line Property

Returns a Border object that you can use to change the appearance of the specified chart element. Read-only.

Remarks

You can use this property to format axes, trendlines, lines on line or stock charts, error bars, and gridlines.


LineStyle Property

Returns or sets the border line style for the specified range. Can be one of the LineStyleEnum constants; returns Null if the borders do not all have the same style. Use the IsNull function to determine whether the return value is Null. Read/write Variant.

Remarks

You cannot set this property for a Border object that represents a chart element.


LineStyle Property Example

This example puts a green dash-dot border around each cell in the range A1:E5.

Spreadsheet1.Range("a1:e5").Borders.LineStyle = owcLineStyleDashDot
Spreadsheet1.Range("a1:e5").Borders.Color = "green"


ListBoundField Property

Returns or sets the field that is bound in the specified list or combo box (as opposed to the field that is displayed). Applies only to list boxes and combo boxes. Read/write String.

Remarks

Use the ListDisplayField property to return or set the displayed field.

This property is used with the ListRowSource property and the ListDisplayField property to control how data is displayed in a list box or combo box. For example, consider a list box which displays product names in a section called Order Details. The recordset behind the Order Details section includes a field named ProductID which is the product for a given Order Detail record. But instead of displaying ProductID in a textbox, the page author wants to display product names in a dropdown list box. The list box can be filled with a recordset definition named Products which contains fields called ProdID and ProductName from the Product table. The properties of this list box element would be set as shown in the following table.

Property | Value
ListRowSource | Products
ListBoundField | ProdID
ListDisplayField | ProductName
ControlSource | ProductID


ListDisplayField Property

Returns or sets the field that is displayed in the list or combo box (as opposed to the field that is bound). There can be only one displayed field. This property applies only to list boxes and combo boxes. Read/write String.

Remarks

Use the ListBoundField property to return or set the bound field.

This property is used with the ListRowSource property and the ListBoundField property to control how data is displayed in a list box or combo box. For example, consider a list box which displays product names in a section called Order Details. The recordset behind the Order Details section includes a field named ProductID which is the product for a given Order Detail record. But instead of displaying ProductID in a textbox, the page author wants to display product names in a dropdown list box. The list box can be filled with a recordset definition named Products which contains fields called ProdID and ProductName from the Product table. The properties of this list box element would be set as shown in the following table.

Property | Value
ListRowSource | Products
ListBoundField | Prod ID
ListDisplayField | Product Name
ControlSource | Product ID


ListRowSource Property

Specifies the data source for a list box or combo box. This property applies only to list boxes and combo boxes, and it must be set before the ListBoundField or ListDisplayField property is set. Read/write String.

Remarks

This property is used with the ListBoundField property and the ListDisplayField property to control how data is displayed in a list box or combo box. For example, consider a list box which displays product names in a section called Order Details. The recordset behind the Order Details section includes a field named ProductID which is the product for a given Order Detail record. But instead of displaying ProductID in a textbox, the page author wants to display product names in a dropdown list box. The list box can be filled with a recordset definition named Products which contains fields called ProdID and ProductName from the Product table. The properties of this list box element would be set as shown in the following table.

Property | Value
ListRowSource | Products
ListBoundField | Prod ID
ListDisplayField | Product Name
ControlSource | Product ID


Location Property

Returns or sets the location of the calculated field evaulation. Read/write dscLocationEnum.

Remarks

This property controls where calculated fields are evaluated. When this property is set to dscServer, the expression is included in the SQL sent to the database and must be in the correct SQL syntax for the database. When this property is set to dscClient, the expression is evaluated on the client computer using Visual Basic for Applications expressions.


Locked Property

True if all cells in the specified range are locked, False if none of the cells are locked, and Null if some cells are locked and some are not. The default value is True. Use the IsNull function to determine whether the return value is Null. Read/write Variant.


Locked Property Example

This example locks only the cells in column 2.

Spreadsheet1.Cells.Locked = False
Spreadsheet1.Columns(2).Locked = True
Spreadsheet1.ActiveSheet.Protection.Enabled = True


LogBase Property

Sets the base of the logarithm when you are using log scales. Attempting to set this property to a value less than or equal to zero (0) causes an error. Read/write Double.


LogBase Property Example

This example causes the specified axis to use a base 2 logarithmic scale.

Set c = ChartSpace1.Constants
Set sc = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Scaling
sc.Type = c.chScaleTypeLogarithmic
sc.LogBase = 2


LookupRelationships Property

Returns the LookupRelationships collection for the specified page row source. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.

Remarks

LookupRelationship objects refer to row sources that have a one-to-many relationship with the specified page row source. Fields from these row sources can appear in the same section or PivotTable list as fields from the specified row source.


LookupSchemaRelationships Property

Returns the LookupSchemaRelationships collection for the specified schema row source. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


MajorGridlines Property

Returns a WCGridlines object that represents the major gridlines for the specified axis. Note that you can use gridlines on any axis. Read-only.


MajorGridlines Property Example

This example sets the color and line weight for the major gridlines on the specified axis.

Set c = ChartSpace1.Constants
Set mg = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorGridlines
mg.Line.Color = "white"
mg.Line.Weight = 5


MajorTickMarks Property

Returns or sets the major tick-mark type for the specified axis. Read/write ChartTickMarkEnum.


MajorTickMarks Property Example

This example turns off major tick marks on the specified axis.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorTickMarks = c.chTickMarkNone


MajorUnit Property

Returns or sets the major unit for the specified axis. Use this property only with a value axis. Read/write Double.


MajorUnit Property Example

This example sets the major unit for the specified axis.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorUnit = 7


MajorVersion Property

Returns the major version of the Microsoft Office Web Components object library. Read-only Long.


ManySide Property

PageRelatedField object: Returns a PageField object that represents the field corresponding to the page row source on the many side of the specified one-to-many relationship. Read-only.

PageRelationship object: Returns a PageRowsource object that represents the page row source on the many side of the specified one-to-many relationship. Read-only.

SchemaRelatedField object: Returns the name of the schema field in the schema row source on the many side of the specified one-to-many relationship. Read-only String.

SchemaRelationship object: Returns the name of the schema row source on the many side of the specified one-to-many relationship. Read-only String.


Marker Property

Returns a WCMarker object that represents the markers for every point in the specified series. Read-only.


Marker Property Example

This example sets the chart type and then sets the marker type and interior color for the specified series.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Type = c.chChartTypeLineMarkers
ChartSpace1.Charts(0).SeriesCollection(0).Marker.Style = c.chMarkerStyleSquare
ChartSpace1.Charts(0).SeriesCollection(0).Interior.Color = "blue"


MaxHeight Property

Returns or sets the maximum height that the specified PivotTable list or spreadsheet can attain when the value of the AutoFit property is True. A vertical scroll bar is displayed if there is more data available that cannot be displayed within the maximum height. For PivotTable lists, the value is in pixels. For spreadsheets, the value is in pixels or a percentage of the container window height (expressed as a string such as "50%"). The default value is 32,000 pixels for PivotTable lists, and 80% for spreadsheets. Read/write Long for PivotTable; read/write Variant for Spreadsheet.


Maximum Property

Returns or sets the maximum value for the specified scale. Read/write Double.


Maximum Property Example

This example sets the maximum and minimum values for the specified axis scale.

Set c = ChartSpace1.Constants
Set axisScale = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Scaling
axisScale.Maximum = 70
axisScale.Minimum = -10


MaxRecords Property

Returns or sets the maximum number of records that the connection will bring to the local computer. Read/write Long.


MaxWidth Property

Returns or sets the maximum width that the specified PivotTable list or spreadsheet can attain when the value of the AutoFit property is True. A horizontal scroll bar is displayed if there is more data available that cannot be displayed within the maximum width. For PivotTable lists, the value is in pixels. For spreadsheets, the value is in pixels or a percentage of the container window width (expressed as a string such as "50%"). The default value is 32,000 pixels for PivotTable lists, and 80% for spreadsheets. Read/write Long for PivotTable; read/write Variant for Spreadsheet.


MemberBackColor Property

Returns or sets the back color used for all members in the specified view. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


MemberExpand Property

Returns or sets the way members are expanded when their data is refreshed. Read/write PivotTableMemberExpandEnum.

Remarks

If this property is set to plMemberExpandAutomatic, the default expanded state of members is determined by the provider type. If the provider is relational or unknown, members are expanded by default. If the provider is multidimensional, members are collapsed by default.

Setting this property immediately sets the Expanded property on all field sets.

Setting the Expanded property for a field or member does not affect the MemberExpand property.


MemberFont Property

Returns a PivotFont object that represents the font used for all members in the specified view. The default value is Arial 10 point Normal. Read-only.


MemberForeColor Property

Returns or sets the fore color used for all members in the specified view. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


MemberHAlignment Property

Returns or sets the horizontal alignment for member captions and field labels displayed in the member area. Read/write PivotHAlignmentEnum.


MemberHeight Property

Returns the height (in pixels) of members displayed on the column axis. Read-only Long.


Members Property

Returns a PivotMembers collection that contains all the members in the specified field set. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.

Remarks

The collection of members is complete regardless of any filtering that is active. To determine whether a particular member is filtered, check to see whether it exists in the collection returned by the RowMembers or ColumnMembers property.


MergeArea Property

Returns a Range object that represents the merged range containing any part of the specified range. The specified range can contain more than one cell; if the range does not contain any merged cells, it is returned unchanged. Read-only.


MergeArea Property Example

This example creates a merged cell from the range B2:C5 and puts a thick red border around the merged cell.

Set c = Spreadsheet1.Constants
Spreadsheet1.Range("b2:c5").Merge
Spreadsheet1.Range("b2").MergeArea.Borders.Color = "red"
Spreadsheet1.Range("b2").MergeArea.Borders.Weight = c.owcLineWeightThick


Minimum Property

Returns or sets the minimum value for the specified scale. Read/write Double.


Minimum Property Example

This example sets the maximum and minimum values for the specified axis scale.

Set c = ChartSpace1.Constants
Set axisScale = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Scaling
axisScale.Maximum = 70
axisScale.Minimum = -10


MinorGridlines Property

Returns a WCGridlines object that represents the minor gridlines for the specified axis. Note that you can use gridlines on any axis. Read-only.


MinorGridlines Property Example

This example turns on minor gridlines and minor tick marks for the specified axis and sets the gridline color and tick-mark style.

Set c = ChartSpace1.Constants
Set axs = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft)
axs.HasMinorGridlines = True
axs.MinorGridlines.Line.Color = "green"
axs.MinorTickMarks = c.chTickMarkOutside


MinorTickMarks Property

Returns or sets the minor tick-mark type for the specified axis. Read/write ChartTickMarkEnum.


MinorTickMarks Property Example

This example turns on minor gridlines and minor tick marks for the specified axis and sets the gridline color and tick-mark style.

Set c = ChartSpace1.Constants
Set axs = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft)
axs.HasMinorGridlines = True
axs.MinorGridlines.Line.Color = "green"
axs.MinorTickMarks = c.chTickMarkOutside


MinorUnit Property

Returns or sets the minor unit for the specified axis. Use this property only with a value axis. Read/write Double.

Remarks

Setting this property causes the HasAutoMinorUnit property to be set to False.


MinorUnit Property Example

This example sets the minor unit for the specified axis.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MinorUnit = 2.5


MinorVersion Property

Returns the minor version of the Microsoft Office Web Components object library. Read-only Long.


MoveAfterReturn Property

True if the focus is moved when the user presses the ENTER key. The default value is True. Read/write Boolean.


MoveAfterReturnDirection Property

Returns or sets the direction in which the focus is moved when the user presses the ENTER key. Read/writeSheetDirectionEnum.


MoveAfterReturnDirection Property Example

This example causes the cell to the right of the active cell to be selected after the user presses the ENTER key.

Set c = Spreadsheet1.Constants
Spreadsheet1.MoveAfterReturn = True
Spreadsheet1.MoveAfterReturnDirection = c.ssToRight


Name Property

Returns or sets the name of the specified object. Read/write Variant for the OWCFont object; read/write String for the Hyperlink, PivotFont, WCAxis, WCChart, and WCTrendline objects; read-only String for all other objects to which this property applies.


Name Property Example

This example sets the name of the specified chart.

ChartSpace1.Charts(0).Name = "Line Chart"

After the chart name has been set, you can refer to the chart by name, as shown in the following example.

ChartSpace1.Charts("Line Chart").HasLegend = True


NextSection Property

Returns a Section object that represents the next physical section on the specified page, regardless of siblings or parents. This property fails on the final section on the page. Read-only.


NextSibling Property

Returns a Section object that represents the next sibling in the current data page. This may cause the next page of records to populate itself. This property will not cross parents, and it will fail on the last sibling of the current data page. Read-only.


NumberFormat Property

Returns or sets the number format for the specified object. Read/write Variant for the Range object; read/write String for the PivotField, PivotTotal, WCAxis, and WCDataLabels objects.


NumberFormat Property Example

The following example sets the number format for the ranges A1:E10 and F1:F10 on the active worksheet.

Spreadsheet1.ActiveSheet.Range("a1:e10").NumberFormat = "0.###"
Spreadsheet1.ActiveSheet.Range("f1:f10").NumberFormat = "Currency"


NumericScale Property

Returns the maximum number of digits you can have to the right of the decimal point for numeric types. Read-only Long.


OneSide Property

PageRelatedField object: Returns a PageField object that represents the field corresponding to the page row source on the “one” side of the specified one-to-many relationship. Read-only.

PageRelationship object: Returns a PageRowsource object that represents the page row source on the “one” side of the specified one-to-many relationship. Read-only.

SchemaRelatedField object: Returns the name of the schema field in the schema row source on the “one” side of the specified one-to-many relationship. Read-only String.

SchemaRelationship object: Returns the name of the schema row source on the “one” side of the specified one-to-many relationship. Read-only String.


Order Property

Returns or sets the polynomial order for the specified trendline (an integer value greater than 1). If you set this property for a linear trendline, it becomes a polynomial trendline. Read/write Long.


Order Property Example

This example sets the order for the specified trendline.

ChartSpace1.Charts(0).SeriesCollection(0).Trendlines(0).Order = 2


Orientation Property

PivotFieldSet object: Returns the field set orientation. A field set can be located on both the summary axis and the column, row, or filter axis at the same time. Read-only PivotFieldSetOrientationEnum.

WCScaling object: Returns or sets the scaling orientation. Changing the value of this property flips the chart. Read/write ChartScaleOrientationEnum.


Orientation Property Example

This example sets the scaling orientation for the specified axis.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(1).Scaling.Orientation = c.chScaleOrientationMaxMin


Overlap Property

Returns or sets the amount of overlap between markers within a single category. Positive values cause the markers to overlap, and negative values cause the markers to separate. The default value is zero (0), and the valid range is from –100 through 100. Read/write Long.


Overlap Property Example

This example sets the overlap value for the specified chart.

ChartSpace1.Charts(0).Overlap = 25


PageFields Property

Returns the PageFields collection for the specified recordset definition. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


PageRelatedFields Property

Returns the PageRelatedFields collection for the specified page relationship. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


PageRowsource Property

Returns the PageRowsource object for the specified page field. Read-only.


PageRowsources Property

Returns the PageRowsources collection for the specified recordset definition.


Panes Property

Returns the Panes collection for the specified worksheet. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


Panes Property Example

This example sets a variable for the visible range in the specified pane.

Set vr = Spreadsheet1.ActiveSheet.Panes(1).VisibleRange


ParameterValues Property

Returns the ParameterValues collection for the specified recordset definition. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


Parent Property

Returns the parent object for the specified object. Read-only.


ParentMember Property

Returns a PivotMember object that represents the parent member for the specified member. Read-only.


ParentRecordsetDef Property

Returns a RecordsetDef object that represents the parent for the specified recordset definition. Read-only.


ParentSection Property

Returns a Section object that represents the parent of the specified section. Read-only.


PlotArea Property

Returns a WCPlotArea object that represents the plot area on the specified chart. Note that pie, doughnut, radar, and polar charts do not have plot areas. Read-only.


PlotArea Property Example

This example sets the interior color for the plot area on the specified chart.

ChartSpace1.Charts(0).PlotArea.Interior.Color = &HD0D0D0


Points Property

Returns the WCPoints collection for the specified series.

For information about returning a single member of a collection, see Returning an Object from a Collection.


Points Property Example

This example sets the interior color for point two in the specified series.

ChartSpace1.Charts(0).SeriesCollection(0).Points(2).Interior.Color = "green"


Position Property

Returns or sets the position of the object. Read/write ChartAxisPositionEnum for the WCAxis object, read/write ChartDataLabelPositionEnum for the WCDataLabels object, read/write ChartLegendPositionEnum for the WCLegend object, and read/write ChartTitlePositionEnum for the WCTitle object.


Position Property Example

This example puts the legend for the specified chart to the left of the plot area.

Set c = ChartSpace1.Constants
ChartSpace1.Charts("Line Chart").HasLegend = True
ChartSpace1.Charts(0).Legend.Position = c.chLegendPositionLeft


Precision Property

Returns the maximum number of digits you can use for numeric types. Read-only Long.


PreviousSection Property

Returns a Section object that represents the previous physical section on the specified page, regardless of siblings or parents. This property fails on the first section on the page. Read-only.


PreviousSibling Property

Returns a Section object that represents the previous sibling in the current data page. This property Ffails on the first sibling of the current data page. Read-only.


PrimaryPageRowsource Property

Returns a PageRowsource object that represents the primary page row source for the specified recordset definition. When a recordset definition is created, the row source named in the Add or AddNew method becomes the primary page row source. Read-only.


Protection Property

Returns a Protection object that contains the protection properties for the specified worksheet. The protection properties are ignored if the Enabled property is set to False. Read-only.


Protection Property Example

This example locks the cells in column 2.

Spreadsheet1.Columns(2).Locked = True
Spreadsheet1.ActiveSheet.Protection.Enabled = True


ProviderType Property

Returns the provider type for the data source control or PivotTable list. Read-only ProviderType.


Range Property

SpreadsheetEventInfo object: Returns a Range object that represents the range where the specified event occurred. For more information, see the specific event topics. This property has no arguments when it is used with the SpreadsheetEventInfo object. Read-only.

PivotData object (Syntax 1): Returns a PivotRange object that represents a cell or a range of cells. Read-only.

Range, Spreadsheet, or Worksheet object (Syntax 2): Returns a Range object that represents a cell or rectangular range of cells. It is not possible to create a nonrectangular range or a range composed of discontiguous areas. Read-only.

Syntax 1 (PivotData object)

expression.Range(TopLeft, BottomRight)

expression An expression that returns a PivotData object.

TopLeft Required PivotCell object. Specifies the upper-left cell in the specified range.

BottomRight Required PivotCell object. Specifies the lower-right cell in the specified range.

Syntax 2 (Range, Spreadsheet, or Worksheet object)

expression.Range(Cell1, Cell2)

expression An expression that returns a Range, Spreadsheet, or Worksheet object.

Cell1 Required Variant. Specifies the entire range as an A1-style reference ("A1:C3", for example). The reference can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but these are ignored. The reference can also specify the cell in the upper-left corner of the range as a Range object that contains a single cell, an entire column, or an entire row, or as a string that names a single cell. If Cell1 specifies the upper-left cell in the range, Cell2 is required.

Cell2 Optional Variant. (Required if Cell1 specifies the upper-left cell in the specified range.) Specifies the cell in the lower-right corner of the range. Can be a Range object that contains a single cell, an entire column, or an entire row, or it can be a string that names a single cell.

Remarks

When this property is applied to a Range object, the arguments specify cells relative to the range. The following example sets a variable to a Range object that represents cell B5.

Set tempRange = Spreadsheet1.Range("b5:b10").range("a1")


Range Property Example

This example sets the row height to 15 points for rows 1 through 10.

Spreadsheet1.Range("a1:a10").RowHeight = 15

This example sets a variable to a range that includes the second through last row of columns 3 and 4 in the current region for cell A1.

Set curRegion = Spreadsheet1.Range("a1").CurrentRegion
Set tempRange = curRegion.Range(curRegion.Cells(2, 3), _
    curRegion.Cells(curRegion.Rows.Count, 4))


RecordNavigationSection Property

True if there is a navigation section for any given banding section. The default value is True. Read/write Boolean.


Recordset Property

PivotCell object: Returns a an ADO Recordset object that contains the detail records for the cell if they are available. Read-only.

DataPage object: Returns an ADODB Recordset object for the specified data access page. Read-only.


RecordsetDef Property

Returns the containing RecordsetDef object for the specified page field or page row source. Read-only.


RecordsetDefs Property

Returns the RecordsetDefs collection for the data source control. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


RecordsetLabel Property

Returns or sets the recordset label for the record navigation control. Read/write String.


RecordsetType Property

Returns the recordset type for the data source control. Read-only dscRecordsetTypeEnum.


RecordSource Property

Returns or sets the record source (the name of a recordset definition or grouping definition) for the section. Applies only to DIV sections. Read/write String.


ResyncCommand Property

Specifies an SQL command parameterized by the key field values from the specified recordset’s Unique table such that the command returns exactly one record. The resync command is executed to "fix up" a row after an update or insertion is made. Read/write String.

You must set this property for any form based on a stored procedure that contains a join or an expression column. In all other cases, Microsoft Access can fix tables automatically. If you do not set this property, users will not see fixed-up field values after an update or insertion, but the update or insertion will still be executed correctly.


ReturnValue Property

Returns or sets the return value for the specified event. You can cancel the default action for some events by setting this property to False. Read/write Boolean.


ReturnValue Property Example

This example displays a message box if the user tries to export the specified spreadsheet and then cancels the command. Note that the event procedure in this example is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures.

Private Sub Spreadsheet1_BeforeCommand(ByVal EventInfo As OWC.SpreadsheetEventInfo)
    Set c = Spreadsheet1.Constants
    If EventInfo.Command = c.ssExport Then
        MsgBox "you cannot export this spreadsheet"
        EventInfo.ReturnValue = False
    End If
End Sub


RevisionNumber Property

Returns the Microsoft Office Web Components revision number. Read-only String.


RightToLeft Property

True if right-to-left language support is enabled. For example, setting this property to True causes scroll bars to be displayed on the left. Read/write Boolean.


Row Property

Returns the number of the first row in the specified range. Read-only Long.


RowAxis Property

Returns a PivotGroupAxis object that represents the row axis. Read-only.


RowCount Property

Returns the number of innermembers on the row axis across all field sets. The number of innermost members is equal to the number of cell coordinates on the axis. Read-only Long.


RowFieldCount Property

Returns the number of fields on the row axis across all field sets. This number does not include fields for which the IsIncluded property is set to False. Read-only Long.


RowHeight Property

Returns or sets the height (in points) of all rows in the specified range. Returns Null if the rows are not all the same height. Use the IsNull function to determine whether the return value is Null. Read/write Variant.


RowHeight Property Example

This example sets the row height to 15 points for rows 1 through 10.

Spreadsheet1.Range("a1:a10").RowHeight = 15


RowMember Property

Returns a PivotMember object that represents the inner member on the row axis that intersects the specified cell. Read-only.


RowMembers Property

Returns a PivotMembers collection that contains the members on the row axis. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


Rows Property

Range object: Returns a Range object that represents all the rows in the specified range. Read-only.

Spreadsheet object: Returns a Range object that represents all the rows on the active worksheet. Read-only.

Worksheet object: Returns a Range object that represents all the rows on the specified worksheet. Read-only.


Rows Property Example

This example sets variables for the number of columns and rows in the visible range.

Set vr = Spreadsheet1.ActiveSheet.VisibleRange
cc = vr.Columns.Count
rc = vr.Rows.Count

This example makes every other row bold in the current region for cell A1.

For Each rw In Spreadsheet1.Cells(1, 1).CurrentRegion.Rows
    If rw.Row Mod 2 = 0 Then rw.Font.Bold = True
Next

This example makes row 2 bold.

Spreadsheet1.Rows(2).Font.Bold = True


Scaling Property

Returns the WCScaling object for the specified axis. Read-only.


Scalings Property

Returns a WCScaling object for the specified chart or series. Read-only.

Syntax

expression.Scalings(Index)

expression An expression that returns a WCChart or WCSeries object.

Index Required ChartDimensionsEnum. Specifies the WCScaling object to be returned.


Scalings Property Example

This example sets the minimum value for the specified WCScaling object.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).SeriesCollection(0).Scalings(c.chDimCategories).Minimum = -10


SchemaFields Property

Returns the SchemaFields collection for the specified schema row source. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


SchemaParameters Property

Returns the SchemaParameters collection for the specified schema row source. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


SchemaRelatedFields Property

Returns the SchemaRelatedFields collection for the specified schema relationship. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


SchemaRelationships Property

Returns the SchemaRelationships collection for the data source control. Read-only.


SchemaRowsources Property

Returns the SchemaRowsources collection for the data source control. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.

Remarks

In design mode, this collection is automatically populated with information from the database. In browse mode, this collection contains any schema objects that are used on the specified page, plus any that are explicitly added by the user.


ScreenUpdating Property

True if screen updating is turned on. The default value is True. Read/write Boolean.

Remarks

Setting this property to False causes the chart workspace or spreadsheet to stop redrawing. To prevent screen flicker or to prevent the user from seeing individual updates, set this property to False, perform your update operations, and then reset this property to True.


ScreenUpdating Property Example

This example turns off screen updating.

Spreadsheet1.ScreenUpdating = False


Section Property

Returns a Section object that represents the section where the specified event occurred. Read-only.


Selection Property

Returns the selected object. Use the SelectionType property or the TypeName function to determine the type of the selected object. Read-only.


SelectionType Property

Returns a constant that represents the type of object currently selected in the chart workspace. Read-only ChartSelectionsEnum.


SelectionType Property Example

The procedure in this example runs whenever the selection in the chart workspace changes. If the user selects an axis, the procedure displays the minimum and maximum values for the axis.

Private Sub ChartSpace1_SelectionChange()
    Set c = ChartSpace1.Constants
    If ChartSpace1.SelectionType = c.chSelectionAxis Then
        minval = ChartSpace1.Selection.Scaling.Minimum
        maxval = ChartSpace1.Selection.Scaling.Maximum
        MsgBox "minimum = " & minval & " maximum = " & maxval
    End If
End Sub


Separator Property

Returns or sets the string that separates data label components in the specified series or chart. Read/write String.


Separator Property Example

This example changes the data-label separator character for the specified series.

Set dl = ChartSpace1.Charts(0).SeriesCollection(0).DataLabelsCollection.Add
dl.HasCategoryName = True
dl.Separator = ":"


SeriesCollection Property

Returns the WCSeriesCollection collection for the specified chart. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


SeriesCollection Property Example

This example sets the line color to red for the specified series.

ChartSpace1.Charts(0).SeriesCollection(0).Line.Color = "red"


ServerFilter Property

Returns or sets the server filter string for the specified RecordsetDef object.

Remarks

A server filter is a criterion for specifying the type or types of records to be fetched from the server. The Data Model adds a server filter string to the WHERE clause of the SQL statement that it generates. You can set a server filter only on recordsets that have a primary row source of type dscTable or dscView.


ShiftKey Property

True if the SHIFT key was pressed (held down) when the specified event occurred. Read-only Boolean.


ShiftKey Property Example

The event procedure in this example displays the Microsoft Office Web Components version number whenever the user double-clicks the chart workspace with the SHIFT key held down. Note that the event procedure in this example is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures.

Private Sub ChartSpace1_DblClick(ByVal ChartEventInfo As OWC.WCChartEventInfo)
    If ChartEventInfo.ShiftKey Then
        vers = ChartSpace1.MajorVersion & "." & ChartSpace1.MinorVersion & _
            " build " & ChartSpace1.BuildNumber
        MsgBox vers, Title:="MSOWC Version"
    End If
End Sub


ShowAll Property

Clears AutoFilter criteria when set to True. Adding criteria sets this property to False. If there are no criteria and this property is set to False, no data will be shown. Read/write Boolean.


ShowAll Property Example

This example clears the AutoFilter criteria for column 1 on the active worksheet and then reapplies the AutoFilter to the worksheet.

Set af = Spreadsheet1.ActiveSheet.AutoFilter
af.Filters(1).Criteria.ShowAll = True
af.Apply


ShowDelButton Property

True if the Delete button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowFilterBySelectionButton Property

True if the Filter by Selection button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowFirstButton Property

True if the First button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowHelpButton Property

True if the Help button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowLabel Property

True if the record navigation control label is displayed. The default value is True. Read/write Boolean.


ShowLastButton Property

True if the Last button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowNewButton Property

True if the New button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowNextButton Property

True if the Next button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowPrevButton Property

True if the Previous button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowSaveButton Property

True if the Save button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowSortAscendingButton Property

True if the Sort Ascending button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowSortDescendingButton Property

True if the Sort Descending button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowToggleFilterButton Property

True if the Toggle Filter button (record navigation control) is displayed. The default value is True. Read/write Boolean.


ShowUndoButton Property

True if the Undo button (record navigation control) is displayed. The default value is True. Read/write Boolean.


Size Property

Returns or sets the font or marker size (in points). Returns Null if it is used on a range in which the characters are not all the same size. Use the IsNull function to determine whether the return value is Null. Read/write Long for the PivotFont and WCMarker objects; read/write Variant for the OWCFont object.


SizeRepresents Property

Returns or sets what the bubble size represents on a bubble chart. Read/write ChartSizeRepresentsEnum.


SizeRepresents Property Example

This example sets the SizeRepresents property for the specified bubble chart.

ChartSpace1.Charts(0).SizeRepresents = chSizeIsArea


SortDirection Property

Returns or sets the direction in which the specified field is to be sorted. Read/write PivotFieldSortDirectionEnum.

Remarks

The field is sorted based on the sorting rules of the underlying data provider. For example, a data provider might sort dates alphanumerically (April, December, and so on) or in a chronological monthly sequence (January, February, and so on)


SortOn Property

Returns or sets the total used to sort the specified field. Read/write PivotTotal.


SortOnScope Property

Returns or sets the array of strings used to narrow the sorting scope for the specified field. Read/write Variant.


Source Property

Returns the source for the specified page field or page row source. Read/write String.


SplitMaximum Property

If the specified WCScaling object has a split, this property returns or sets the maximum value for the split. This value should be greater than the value of the SplitMinimum property. Read/write Double.


SplitMaximum Property Example

This example splits the specified axis and sets the minimum and maximum values for the split.

Set c = ChartSpace1.Constants
Set sc = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Scaling
sc.HasSplit = True
sc.SplitMinimum = 100
sc.SplitMaximum = 500


SplitMinimum Property

If the specified WCScaling object has a split, this property returns or sets the minimum value for the split. This value should be less than the value of the SplitMaximum property. Read/write Double.


SplitMinimum Property Example

This example splits the specified axis and sets the minimum and maximum values for the split.

Set c = ChartSpace1.Constants
Set sc = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Scaling
sc.HasSplit = True
sc.SplitMinimum = 100
sc.SplitMaximum = 500


Style Property

Returns or sets the marker style for the specified series or chart. Read/write ChartMarkerStyleEnum.


Style Property Example

This example sets the marker style for the specified series.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).SeriesCollection(0).Marker.Style = c.chMarkerStyleStar


Subaddress Property

Returns or sets the location (within the document) associated with the hyperlink (the string to the right of the # symbol in the hyperlink address). Read/write String.


Subaddress Property Example

This example sets the address and subaddress for the specified hyperlink.

Set hyp = Spreadsheet1.Range("a12").Hyperlink
hyp.Address = "http://www.microsoft.com/ExcelDev/e-tips.htm"
hyp.Subaddress = "top"


SublistRelationships Property

Returns the SublistRelationships collection for the specified recordset definition. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.

Remarks

A sublist relationship refers to a row source in another recordset definition that has a many-to-one relationship with the primary page row source in the specified recordset definition.


SublistSchemaRelationships Property

Returns the SublistSchemaRelationships collection for the specified schema row source. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


SubtotalBackColor Property

Returns or sets the back color for the subtotal in the specified field. For subtotals, this property’s setting overrides the TotalBackColor property’s setting. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


SubtotalFont Property

Returns a PivotFont object that represents the font for subtotals. Read-only.


SubtotalForeColor Property

Returns or sets the fore color for subtotals for the specified field. For subtotals, this property’s setting overrides the TotalForeColor property’s setting. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


Subtotals Property

True if the subtotal is displayed for the specified field. Read/write Boolean.

Syntax

expression.Subtotals(Subtotals)

expression An expression that returns a PivotField object. The field must be on a row or column axis.

Subtotals Required Long. Specifies the subtotal. Must be 1.


Text Property

PivotAggregate or PivotDetailCell object: Returns the specified value as a string based on the current NumberFormat property setting. Read-only String.

Range object: Returns the formatted value displayed in the specified cell. Returns Null if the range includes more than one cell and the cells do not all contain the same value. Use the IsNull function to determine whether the return value is Null. Read-only Variant.


Text Property Example

This example sets a variable for the formatted value of cell A1.

txt = Spreadsheet1.Range("a1").Text


TickLabelSpacing Property

Returns or sets the number of categories between tick-mark labels for the specified axis. Applies only to category axes. Read/write Long.


TickLabelSpacing Property Example

This example sets the number of categories between tick-mark labels for the specified axis to two.

ChartSpace1.Charts(0).Axes(1).TickLabelSpacing = 2


TickMarkSpacing Property

Returns or sets the number of categories between tick marks on the specified axis. Applies only to category axes. Use the MajorUnit and MinorUnit properties to set tick-mark spacing on value axes. Read/write Long.


TickMarkSpacing Property Example

This example sets the number of categories between tick marks on the specified axis to two.

ChartSpace1.Charts(0).Axes(1).TickMarkSpacing = 2


Title Property

Returns a WCTitle object that represents the title of the specified axis or chart. Read-only.


TitleBar Property

PivotView object: Returns a PivotLabel object that represents the title of the specified view. Read-only.

Spreadsheet object: Returns a TitleBar object that represents the title of the specified spreadsheet. Read-only.


TitleBar Property Example

This example sets the title caption for the spreadsheet.

Spreadsheet1.TitleBar.Caption = "Monthly Sales"


Top Property

PivotData object: Returns or sets the member to the left of the topmost visible summary row. Read/write PivotMember.

Range object: Returns the distance from the top of the spreadsheet window to the top of the specified range (in points). Read-only Long. This value can be a negative number if the specified range is above the visible range.


TopLeft Property

PivotDetailRange object: Returns a PivotDetailCell object that represents the upper-left cell in the detail range. Read-only.

PivotRange object: Returns a PivotCell object that represents the upper-left cell in the specified range. Read-only.


Total Property

Returns a PivotTotal object that represents the total for the specified aggregate. Read-only


TotalAllMembers Property

True if filtered members are included in subtotals. If this property is set to False, subtotals do not include members that have been filtered out of the view. The default value is False. Read/write Boolean.


TotalBackColor Property

Returns or sets the back color for all totals. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


TotalFont Property

Returns a PivotFont object that represents the font used for aggregates displayed in a cell or in a detail grid footer. Read-only.


TotalForeColor Property

Returns or sets the fore color for all totals. Read/write Variant.

Remarks

When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).

This property always returns the color as a Long value representing a red-green-blue color value.


TotalMember Property

Returns a PivotMember object that represents the member used to display subtotals. Read-only.


TotalOrientation Property

Returns or sets the orientation used to display summary totals when there is more than one total. Read/write PivotViewTotalOrientationEnum.


Totals Property

PivotDataAxis object: Returns a PivotTotals collection that contains all the PivotTotal objects on the summary axis. The totals are displayed in the summary area for each cell. Read-only.

PivotView object: Returns a PivotTotals collection that contains all the totals in the current view. Read-only.

For information about returning a single member of a collection, see Returning an Object from a Collection.


TotalType Property

Returns or sets the type of total. Read/write dscTotalTypeEnum.

Remarks

Values other than dscNone are valid only with page fields of type dscGrouping.


Trendlines Property

Returns the WCTrendlines collection for the specified series. Note that a series can have only one trendline.

For information about returning a single member of a collection, see Returning an Object from a Collection.


Trendlines Property Example

This example adds a trendline to the specified series and then hides the trendline’s R-squared value.

ChartSpace1.Charts(0).SeriesCollection(0).Trendlines.Add
ChartSpace1.Charts(0).SeriesCollection(0).Trendlines(0).IsDisplayingRSquared = False


Type Property

Returns or sets the object type for the specified object, as shown in the following table.

Object | Constant | Read/write or read-only status
PageRelationship | dscPageRelTypeEnum | Read-only
PivotField | PivotFieldTypeEnum | Read-only
PivotFieldSet | PivotFieldSetTypeEnum | Read-only
PivotTotal | PivotTotalTypeEnum | Read-only
SchemaRowsource | dscRowsourceTypeEnum | Read/write
Section | SectTypeEnum | Read-only
WCAxis | ChartAxisTypeEnum | Read-only
WCChart | ChartChartTypeEnum | Read/write
WCDataSource | ChartDataSourceTypeEnum | Read/write
WCErrorBars | ChartErrorBarTypeEnum | Read/write
WCScaling | ChartScaleTypeEnum | Read/write
WCSeries | ChartChartTypeEnum | Read/write
WCTrendline | ChartTrendlineTypeEnum | Read/write


Type Property Example

This example sets the chart type for the specified chart.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Type = c.chChartTypeLineMarkers


Underline Property

Returns or sets the font underline style for the specified range. Read/write UnderlineStyleEnum for the PivotFont object; read/write Variant for the OWCFont object (returns Null if the characters in the font do not all have the same underline style; otherwise, returns one of the UnderlineStyleEnum constants). Use the IsNull function to determine whether the return value is Null.


Underline Property Example

This example adds a single underline to all cell values in row 1.

Set c = Spreadsheet1.Constants
Spreadsheet1.Rows(1).Font.Underline = c.owcUnderlineStyleSingle


UniqueName Property

Returns the unique name of the PivotMember object (the unique member reference returned by the provider). Returns Null if the member source is a RecordsetDef object. Read-only String.


UniqueTable Property

Specifies the name of the updatable table when a form is bound to a multitable view or stored procedure. Read/write String.


UsedRange Property

Returns a Range object that represents the used range on the specified worksheet. Read-only.


UsedRange Property Example

This example adjusts the row height and column width for the used range on the active worksheet to fit the data contained in the range.

Set ur = Spreadsheet1.ActiveSheet.UsedRange
ur.AutoFitRows
ur.AutoFitColumns


UseRemoteProvider Property

True if the data source control is using a remote provider. Read/write Boolean.

Remarks

When this property is set to True, the data source control uses the Microsoft Remote Data Services provider for data connections. You can use this property only with pages that are read from a Microsoft Internet Information Server using an HTTP or HTTPS address. The Microsoft Remote Data Services provider fetches data by passing HTTP or HTTPS requests to IIS, which then makes an OLE DB connection to the database.


VAlignment Property

Returns or sets the way data is aligned in the specified range. Can be one of the SheetVAlignEnum constants. Returns Null if the cells in the range are not all aligned in the same way. Use the IsNull function to determine whether the return value is Null. Read/write Variant.


VAlignment Property Example

This example creates a merged cell, places a value in the cell, and then centers the value within the cell.

Spreadsheet1.Range("a1:c3").Merge
Spreadsheet1.Range("a1").Value = "Monday"
Set c = Spreadsheet1.Constants
Spreadsheet1.Range("a1").VAlignment = c.ssVAlignCenter
Spreadsheet1.Range("a1").HAlignment = c.ssHAlignCenter


Value Property

Returns or sets the value for the specified object. Read-only Variant for PivotAggregate and PivotDetailCell objects; read/write Variant for ParameterValue and Range objects.

Remarks

If the specified range contains more than one cell, this property returns an array containing the values for all the cells.


Value Property Example

This example creates a merged cell from the specified range and then places a value in the merged cell.

Spreadsheet1.Range("a1:c3").Merge
Spreadsheet1.Range("a1").Value = "Monday"


Version Property

Returns the Microsoft Office Web Components version. Read-only String.


ViewableRange Property

Returns or sets the range of cells that the user can view. This makes it possible for you to hide worksheet cells (you may want to hide cells that contain intermediate calculations, for example). Read/write String.

Remarks

Do not confuse this property with the VisibleRange property, which returns a Range object that represents all the cells that are currently visible.


ViewableRange Property Example

This example sets the viewable range on the spreadsheet.

Spreadsheet1.ViewableRange = "a1:g20"


Visible Property

True if the specified object is visible. Read/write Boolean.


VisibleRange Property

Returns a Range object that represents all the cells that are currently visible. Read-only.

Remarks

Do not confuse this property with the ViewableRange property, which returns a String that specifies the range that the user can view (part of that range may not be currently visible).


VisibleRange Property Example

This example sets the font to bold in every other column in the visible range on the active worksheet.

For Each col In Spreadsheet1.ActiveSheet.VisibleRange.Columns
    If col.Column Mod 2 = 0 Then col.Font.Bold = True
Next

The function in this example returns True if the entire current region for cell A1 is visible (if the current region extends outside the visible range, the function returns False).

Function IsCurrentRegionVisible()
    Set cr = Spreadsheet1.ActiveSheet.Cells(1, 1).CurrentRegion
    Set vr = Spreadsheet1.ActiveSheet.VisibleRange
    Set ir = Spreadsheet1.Intersect(cr, vr)
    IsCurrentRegionVisible = (ir.Address = cr.Address)
End Function


Weight Property

Returns or sets the weight for the specified border or line. Can be one of the LineWeightEnum constants, or can be Null if the borders are not all the same weight. Read/write Variant.


Weight Property Example

This example sets the chart workspace and the axis weight.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Line.Weight = owcLineWeightThick
ChartSpace1.Border.Weight = c.owcLineWeightThick


Width Property

Returns or sets the width of the specified object (in points). Read-only Long for Range objects; read/write Long for all other objects to which this property applies.


WidthRatio Property

Returns or sets the width ratio for the specified chart in relation to the other charts in the chart workspace. The default value is 100. Read/write Long.

Remarks

This property sets the width of the specified chart relative to the other charts in the chart workspace. For this property to have any effect, you must have more than one chart in the chart workspace. When more than one chart is displayed, the charts are displayed in a grid (for more information, see the Help topics for the ChartLayout and ChartWrapCount properties). Initially, the HeightRatio and WidthRatio properties are set to 100 for all charts in the grid, and all charts are the same size.

To change the width of charts in the grid, adjust the WidthRatio property settings. For example, if each chart is displayed in three columns all charts have an initial WidthRatio setting of 100. If you want column 3 to be only half the available column width, set its WidthRatio setting to 200; the remaining half of the chart width will be divided between columns 1 and 2. Because the width specified by the WidthRatio property is relative, you can set this property for the three columns to 1,1,2; 100,100,200; or 20,20,40. all of which have the same effect.

If the chart workspace contains charts displayed in more than one column, the largest WidthRatio setting in each column is used to set the relative width for the entire column.

This property is useful for creating price and volume stock charts in which the volume chart is half the size of the price chart.


X Property

Specifies the distance (in pixels) from the left edge of the spreadsheet or chart workspace to the mouse pointer when the event occurred. Read-only Long.


XMLData Property

Returns or sets the XML data. Read/write String.


Y Property

Specifies the distance (in pixels) from the top edge of the spreadsheet or chart workspace to the mouse pointer when the event occurred. Read-only Long.


ChartAxisCrossesEnum Constants

Constant | Value
chAxisCrossesAutomatic | 0
chAxisCrossesCustom | 3


ChartAxisPositionEnum Constants

Constant | Value
chAxisPositionCircular | -6
chAxisPositionRadial | -5
chAxisPositionRight | -4
chAxisPositionLeft | -3
chAxisPositionBottom | -2
chAxisPositionTop | -1


ChartAxisTypeEnum Constants

Constant | Value
chCategoryAxis | 0
chValueAxis | 1


ChartChartLayoutEnum Constants

Constant | Value
chChartLayoutAutomatic | 0
chChartLayoutHorizontal | 1
chChartLayoutVertical | 2


ChartChartTypeEnum Constants

Constant | Value
chChartTypeCombo | -1
chChartTypeColumnClustered | 0
chChartTypeColumnStacked | 1
chChartTypeColumnStacked100 | 2
chChartTypeBarClustered | 3
chChartTypeBarStacked | 4
chChartTypeBarStacked100 | 5
chChartTypeLine | 6
chChartTypeLineMarkers | 7
chChartTypeLineStacked | 8
chChartTypeLineStackedMarkers | 9
chChartTypeLineStacked100 | 10
chChartTypeLineStacked100Markers | 11
chChartTypeSmoothLine | 12
chChartTypeSmoothLineMarkers | 13
chChartTypeSmoothLineStacked | 14
chChartTypeSmoothLineStackedMarkers | 15
chChartTypeSmoothLineStacked100 | 16
chChartTypeSmoothLineStacked100Markers | 17
chChartTypePie | 18
chChartTypePieExploded | 19
chChartTypePieStacked | 20
chChartTypeScatterMarkers | 21
chChartTypeScatterSmoothLineMarkers | 22
chChartTypeScatterSmoothLine | 23
chChartTypeScatterLineMarkers | 24
chChartTypeScatterLine | 25
chChartTypeScatterLineFilled | 26
chChartTypeBubble | 27
chChartTypeBubbleLine | 28
chChartTypeArea | 29
chChartTypeAreaStacked | 30
chChartTypeAreaStacked100 | 31
chChartTypeDoughnut | 32
chChartTypeDoughnutExploded | 33
chChartTypeRadarLine | 34
chChartTypeRadarLineMarkers | 35
chChartTypeRadarLineFilled | 36
chChartTypeRadarSmoothLine | 37
chChartTypeRadarSmoothLineMarkers | 38
chChartTypeStockHLC | 39
chChartTypeStockOHLC | 40
chChartTypePolarMarkers | 41
chChartTypePolarLine | 42
chChartTypePolarLineMarkers | 43
chChartTypePolarSmoothLine | 44
chChartTypePolarSmoothLineMarkers | 45


ChartColorIndexEnum Constants

Constant | Value
chColorNone | -2
chColorAutomatic | -1


ChartDataGroupingFunctionEnum Constants

Constant | Value
chDataGroupingFunctionMinimum | 0
chDataGroupingFunctionMaximum | 1
chDataGroupingFunctionSum | 2
chDataGroupingFunctionAverage | 3


ChartDataLabelPositionEnum Constants

Constant | Value
chLabelPositionAutomatic | 0
chLabelPositionCenter | 1
chLabelPositionInsideEnd | 2
chLabelPositionInsideBase | 3
chLabelPositionOutsideEnd | 4
chLabelPositionOutsideBase | 5
chLabelPositionLeft | 6
chLabelPositionRight | 7
chLabelPositionTop | 8
chLabelPositionBottom | 9


ChartDataSourceTypeEnum Constants

Constant | Value
chDataSourceTypeUnknown | 0
chDataSourceTypeSpreadsheet | 1
chDataSourceTypeRecordset | 2
chDataSourceTypePivotlist | 3


ChartDimensionsEnum Constants

Constant | Value
chDimSeriesNames | 0
chDimCategories | 1
chDimValues | 2
chDimYValues | 3
chDimXValues | 4
chDimOpenValues | 5
chDimCloseValues | 6
chDimHighValues | 7
chDimLowValues | 8
chDimBubbleValues | 9
chDimRValues | 10
chDimThetaValues | 11


ChartEndStyleEnum Constants

Constant | Value
chEndStyleNone | 1
chEndStyleCap | 2


ChartErrorBarCustomValuesEnum Constants

Constant | Value
chErrorBarPlusValues | 12
chErrorBarMinusValues | 13


ChartErrorBarDirectionEnum Constants

Constant | Value
chErrorBarDirectionY | 0
chErrorBarDirectionX | 1


ChartErrorBarIncludeEnum Constants

Constant | Value
chErrorBarIncludePlusValues | 0
chErrorBarIncludeMinusValues | 1
chErrorBarIncludeBoth | 2


ChartErrorBarTypeEnum Constants

Constant | Value
chErrorBarTypeFixedValue | 0
chErrorBarTypePercent | 1
chErrorBarTypeCustom | 2


ChartFillStyleEnum Constants

Constant | Value
chNone | -1
chSolid | 0


ChartLegendPositionEnum Constants

Constant | Value
chLegendPositionAutomatic | 0
chLegendPositionTop | 1
chLegendPositionBottom | 2
chLegendPositionLeft | 3
chLegendPositionRight | 4


ChartMarkerStyleEnum Constants

Constant | Value
chMarkerStyleNone | 0
chMarkerStyleSquare | 1
chMarkerStyleDiamond | 2
chMarkerStyleTriangle | 3
chMarkerStyleX | 4
chMarkerStyleStar | 5
chMarkerStyleDot | 6
chMarkerStyleDash | 7
chMarkerStyleCircle | 8
chMarkerStylePlus | 9


ChartPivotDataReferenceEnum Constants

Constant | Value
chPivotRowAggregates | -4
chPivotColAggregates | -3
chPivotRows | -2
chPivotColumns | -1


ChartScaleOrientationEnum Constants

Constant | Value
chScaleOrientationMinMax | 0
chScaleOrientationMaxMin | 1


ChartScaleTypeEnum Constants

Constant | Value
chScaleTypeLinear | 0
chScaleTypeLogarithmic | 1


ChartSelectionsEnum Constants

Constant | Value
chSelectionNone | -1
chSelectionAxis | 0
chSelectionChart | 1
chSelectionPlotArea | 2
chSelectionDataLabels | 3
chSelectionErrorbars | 4
chSelectionGridlines | 5
chSelectionLegend | 6
chSelectionLegendEntry | 7
chSelectionPoint | 8
chSelectionSeries | 9
chSelectionTitle | 10
chSelectionTrendline | 11
chSelectionWebChart | 12


ChartSeriesByEnum Constants

Constant | Value
chSeriesByRows | 0
chSeriesByColumns | 1


ChartSeriesScalingsEnum Constants

Constant | Value
chCategoryScaling | 0
chValueScaling | 1
chValueScaling1 | 2
chValueScaling2 | 3


ChartSizeRepresentsEnum Constants

Constant | Value
chSizeIsWidth | 0
chSizeIsArea | 1


ChartSpecialDataSourcesEnum Constants

Constant | Value
chDataNone | -2
chDataLiteral | -1


ChartTickMarkEnum Constants

Constant | Value
chTickMarkAutomatic | 0
chTickMarkNone | 1
chTickMarkInside | 2
chTickMarkOutside | 3
chTickMarkCross | 4


ChartTitlePositionEnum Constants

Constant | Value
chTitlePositionAutomatic | 0
chTitlePositionTop | 1
chTitlePositionBottom | 2
chTitlePositionLeft | 3
chTitlePositionRight | 4


ChartTrendlineTypeEnum Constants

Constant | Value
chTrendlineTypeExponential | 0
chTrendlineTypeLinear | 1
chTrendlineTypeLogarithmic | 2
chTrendlineTypePolynomial | 3
chTrendlineTypePower | 4


ChartWizardTypeEnum Constants

Constant | Value
chWizardFull | 0
chWizardChartType | 1
chWizardSourceData | 2


DaPivotLocationEnum Constants

Constant | Value
dplRowArea | 1
dplColumnArea | 2
dplFilterArea | 3
dplDataArea | 4
dplDetails | 5


DefaultControlTypeEnum Constants

Constant | Value
ctlTypeTextBox | 0
ctlTypeBoundHTML | 1


DscAdviseTypeEnum Constants

Constant | Value
dscAdd | 1
dscDelete | 2
dscMove | 3
dscLoad | 4
dscChange | 5
dscDeleteComplete | 6


DscDropLocationEnum Constants

Constant | Value
dscAbove | 1
dscWithin | 2
dscBelow | 3


DscDropTypeEnum Constants

Constant | Value
dscDefault | 0
dscGrid | 1
dscFields | 2


DscFetchTypeEnum Constants

Constant | Value
dscFull | 1
dscParameterized | 2


DscFieldTypeEnum Constants

Constant | Value
dscParameter | -1
dscOutput | 1
dscCalculated | 2
dscGrouping | 3


DscGroupOnEnum Constants

Constant | Value
dscNoGrouping | -1
dscEachValue | 0
dscPrefix | 1
dscYear | 2
dscQuarter | 3
dscMonth | 4
dscWeek | 5
dscDay | 6
dscHour | 7
dscMinute | 8
dscInterval | 9


DscHyperlinkPartEnum Constants

Constant | Value
dschlDisplayedValue | 0
dschlDisplayText | 1
dschlAddress | 2
dschlSubAddress | 3
dschlScreenTip | 4
dschlFullAddress | 5


DscJoinTypeEnum Constants

Constant | Value
dscInnerJoin | 1
dscLeftOuterJoin | 2
dscRightOuterJoin | 3


DscLocationEnum Constants

Constant | Value
dscSystem | -1
dscClient | 0
dscServer | 1


DscObjectTypeEnum Constants

Constant | Value
dscobjUnknown | -1
dscobjSchemaRowsource | 1
dscobjSchemaField | 2
dscobjSchemaRelationship | 4
dscobjRecordsetDef | 8
dscobjPageRowsource | 16
dscobjPageField | 32
dscobjSublistRelationship | 64
dscobjLookupRelationship | 128
dscobjGroupingDef | 256
dscobjDatamodel | 512
dscobjPageRelatedField | 1024
dscobjParameterValue | 2048
dscobjSchemaRelatedField | 4096
dscobjSchemaParameter | 8192


DscPageRelTypeEnum Constants

Constant | Value
dscSublist | 1
dscLookup | 2


DscRecordsetTypeEnum Constants

Constant | Value
dscSnapshot | 1
dscUpdatableSnapshot | 2


DscRowsourceTypeEnum Constants

Constant | Value
dscTable | 1
dscView | 2
dscCommandText | 3
dscProcedure | 4
dscCommandFile | 5


DscTotalTypeEnum Constants

Constant | Value
dscNone | 0
dscSum | 1
dscAvg | 2
dscMin | 3
dscMax | 4
dscCount | 5
dscAny | 6
dscStdev | 7


ExpandBitmapTypeEnum Constants

Constant | Value
ecBitmapPlusMinus | 0
ecBitmapUpDownArrow | 1
ecBitmapOpenCloseFolder | 2


LineStyleEnum Constants

Constant | Value
owcLineStyleNone | 0
owcLineStyleAutomatic | 1
owcLineStyleSolid | 2
owcLineStyleDash | 3
owcLineStyleDot | 4
owcLineStyleDashDot | 5
owcLineStyleDashDotDot | 6


LineWeightEnum Constants

Constant | Value
owcLineWeightHairline | 0
owcLineWeightThin | 1
owcLineWeightMedium | 2
owcLineWeightThick | 3


NavButtonEnum Constants

Constant | Value
navbtnMoveFirst | 0
navbtnMovePrev | 1
navbtnMoveNext | 2
navbtnMoveLast | 3
navbtnNew | 4
navbtnDelete | 5
navbtnSave | 6
navbtnUndo | 7
navbtnSortAscending | 8
navbtnSortDescending | 9
navbtnApplyFilter | 10
navbtnToggleFilter | 11
navbtnHelp | 12


PivotExportActionEnum Constants

Constant | Value
plExportActionNone | 0
plExportActionOpenInExcel | 1


PivotFieldFilterFunctionEnum Constants

Constant | Value
plFilterFunctionNone | 0
plFilterFunctionInclude | 1
plFilterFunctionExclude | 2


PivotFieldGroupOnEnum Constants

Constant | Value
plGroupOnEachValue | 0
plGroupOnPrefixChar | 1
plGroupOnYears | 2
plGroupOnQtrs | 3
plGroupOnMonths | 4
plGroupOnWeeks | 5
plGroupOnDays | 6
plGroupOnHours | 7
plGroupOnMinutes | 8
plGroupOnSeconds | 9
plGroupOnInterval | 10


PivotFieldSetOrientationEnum Constants

Constant | Value
plOrientationNone | 0
plOrientationColumnAxis | 1
plOrientationRowAxis | 2
plOrientationFilterAxis | 4
plOrientationDataAxis | 8


PivotFieldSetTypeEnum Constants

Constant | Value
plFieldSetTypeTime | 1
plFieldSetTypeOther | 2
plFieldSetTypeUnknown | 3
plFieldSetTypeUserDefined | 4


PivotFieldSortDirectionEnum Constants

Constant | Value
plSortDirectionDefault | 0
plSortDirectionAscending | 1
plSortDirectionDescending | 2


PivotFieldTypeEnum Constants

Constant | Value
plTypeRegular | 1
plTypeCalculated | 2
plTypeTime | 3
plTypeTimeYears | 4
plTypeTimeHalfYears | 5
plTypeTimeQuarters | 6
plTypeTimeMonths | 7
plTypeTimeWeeks | 8
plTypeTimeDays | 9
plTypeTimeHours | 10
plTypeTimeMinutes | 11
plTypeTimeSeconds | 12
plTypeTimeUndefined | 13
plTypeUnknown | 14
plTypeUserDefined | 15


PivotHAlignmentEnum Constants

Constant | Value
plHAlignAutomatic | 0
plHAlignLeft | 1
plHAlignCenter | 2
plHAlignRight | 3


PivotQueryReasonEnum Constants

Constant | Value
plQueryReasonInsertFieldSet | 0
plQueryReasonRemoveFieldSet | 1
plQueryReasonInsertTotal | 2
plQueryReasonRemoveTotal | 3
plQueryReasonAllowDetailsChange | 4
plQueryReasonSortDirectionChange | 5
plQueryReasonSortOnChange | 6
plQueryReasonSortOnScopeChange | 7
plQueryReasonFilterFunctionChange | 8
plQueryReasonFilterMemberChange | 9
plQueryReasonIsIncludedChange | 10
plQueryReasonExpressionChange | 11
plQueryReasonDisplayEmptyMembersChange | 12
plQueryReasonTotalFunctionChange | 13
plQueryReasonUser | 14
plQueryReasonDataSourceChange | 15
plQueryReasonDataMemberChange | 16
plQueryReasonGroupOnChange | 17
plQueryReasonUnknown | 18
plQueryReasonGroupOffsetChange | 19
plQueryReasonGroupIntervalChange | 20
plQueryReasonIsFilteredChange | 21


PivotTableMemberExpandEnum Constants

Constant | Value
plMemberExpandAutomatic | 0
plMemberExpandAlways | 1
plMemberExpandNever | 2


PivotTableReasonEnum Constants

Constant | Value
plPivotTableReasonTotalAdded | 0
plPivotTableReasonTotalDeleted | 1
plPivotTableReasonFieldSetAdded | 2
plPivotTableReasonFieldSetDeleted | 3
plPivotTableReasonFieldAdded | 4


PivotTotalFunctionEnum Constants

Constant | Value
plFunctionSum | 1
plFunctionCount | 2
plFunctionMin | 3
plFunctionMax | 4


PivotTotalTypeEnum Constants

Constant | Value
plTotalTypeIntrinsic | 1
plTotalTypeUserDefined | 2


PivotViewReasonEnum Constants

Constant | Value
plViewReasonSelectionChange | 0
plViewReasonSubtotalsChange | 1
plViewReasonSystemColorChange | 2
plViewReasonQuery | 3
plViewReasonFontNameChange | 4
plViewReasonFontSizeChange | 5
plViewReasonFontBoldChange | 6
plViewReasonFontItalicChange | 7
plViewReasonFontUnderlineChange | 8
plViewReasonMemberExpandedChange | 9
plViewReasonCellExpandedChange | 10
plViewReasonDetailRowHeightChange | 11
plViewReasonFieldDetailWidthChange | 12
plViewReasonFieldGroupedWidthChange | 13
plViewReasonViewDetailWidthChange | 14
plViewReasonFieldSetWidthChange | 15
plViewReasonTotalWidthChange | 16
plViewReasonForeColorChange | 17
plViewReasonBackColorChange | 18
plViewReasonAlignmentChange | 19
plViewReasonNumberFormatChange | 20
plViewReasonDetailTopChange | 21
plViewReasonDetailLeftChange | 22
plViewReasonTopChange | 23
plViewReasonLeftChange | 24
plViewReasonRightToLeftChange | 25
plViewReasonTotalOrientationChange | 26
plViewReasonDisplayOutlineChange | 27
plViewReasonFieldCaptionChange | 28
plViewReasonFieldSetCaptionChange | 29
plViewReasonLabelCaptionChange | 30
plViewReasonMemberCaptionChange | 31
plViewReasonTotalCaptionChange | 32
plViewReasonAllowFilteringChange | 33
plViewReasonAllowGroupingChange | 34
plViewReasonWidthChange | 35
plViewReasonHeightChange | 36
plViewReasonLabelVisibleChange | 37
plViewReasonDisplayToolbarChange | 38
plViewReasonMaxHeightChange | 39
plViewReasonMaxWidthChange | 40
plViewReasonAutoFitChange | 41
plViewReasonFieldExpandedChange | 42
plViewReasonCellsExpandedChange | 43
plViewReasonDetailMaxWidthChange | 44
plViewReasonDetailMaxHeightChange | 45


PivotViewTotalOrientationEnum Constants

Constant | Value
plTotalOrientationRow | 1
plTotalOrientationColumn | 2


ProviderType Constants

Constant | Value
providerTypeUnknown | 1
providerTypeRelational | 2
providerTypeMultidimensional | 3


SectTypeEnum Constants

Constant | Value
sectTypeNone | 0
sectTypeCaption | 1
sectTypeHeader | 2
sectTypeFooter | 3
sectTypeRecNav | 4


SheetBorderTypeEnum Constants

Constant | Value
ssEdgeAll | 0
ssEdgeBottom | 1
ssEdgeLeft | 2
ssEdgeRight | 3
ssEdgeTop | 4
ssInsideHorizontal | 5
ssInsideVertical | 6


SheetCommandEnum Constants

Constant | Value
ssCalculate | 0
ssInsertRows | 2
ssInsertColumns | 3
ssDeleteRows | 4
ssDeleteColumns | 5
ssCut | 6
ssCopy | 7
ssPaste | 8
ssExport | 9
ssUndo | 10
ssSortAscending | 11
ssSortDescending | 12
ssFind | 13
ssClear | 14
ssAutoFilter | 15
ssProperties | 16
ssHelp | 17


SheetDirectionEnum Constants

Constant | Value
ssDown | 0
ssToLeft | 1
ssToRight | 2
ssUp | 3


SheetExportActionEnum Constants

Constant | Value
ssExportActionNone | 0
ssExportActionOpenInExcel | 1


SheetFilterFunction Constants

Constant | Value
ssFilterFunctionInclude | 1
ssFilterFunctionExclude | 2


SheetFindLookAtEnum Constants

Constant | Value
ssPart | 0
ssWhole | 1


SheetFindLookInEnum Constants

Constant | Value
ssFormulas | 0
ssValues | 1


SheetFreezePanesEnum Constants

Constant | Value
ssFreezeLeft | 1
ssFreezeRight | 2 (reserved for future use)
ssFreezeTop | 4
ssFreezeBottom | 8 (reserved for future use)
ssFreezeNone | 16


SheetHAlignEnum Constants

Constant | Value
ssHAlignGeneral | 0
ssHAlignLeft | 1
ssHAlignCenter | 2
ssHAlignRight | 3


SheetSearchDirectionEnum Constants

Constant | Value
ssNext | 0
ssPrevious | 1


SheetSearchOrderEnum Constants

Constant | Value
ssByColumns | 0
ssByRows | 1


SheetSortOrderEnum Constants

Constant | Value
ssAscending | 0
ssDescending | 1


SheetTriStateEnum Constants

Constant | Value
ssAuto | -1
ssOff | 0
ssOn | 1


SheetVAlignEnum Constants

Constant | Value
ssVAlignBottom | 0
ssVAlignCenter | 1
ssVAlignTop | 2


SheetYesNoGuessEnum Constants

Constant | Value
ssGuess | 0
ssYes | 1
ssNo | 2


UnderlineStyleEnum Constants

Constant | Value
owcUnderlineStyleNone | 0
owcUnderlineStyleSingle | 1
owcUnderlineStyleDouble | 2
owcUnderlineStyleSingleAccounting | 3
owcUnderlineStyleDoubleAccounting | 4


Creating a Chart Using Data Specified in Arrays

This example creates a chart using data specified in arrays. The arrays are set to the category and value data, and then these arrays are used with the SetData method to set the chart data.

To run this example, copy the remainder of the text in this topic into an HTML page.

<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:350"></object>

<script language=vbs>
Sub Window_OnLoad()
Dim categories(3), values(3)

    ' Create a column chart with three series and four categories, showing the
    ' percentage of voters for the 1992 presidential election by race/ethic group.

    ' Create an array of strings representing the categories.
    ' The categories will be the same for all three series.
    categories(0) = "White"
    categories(1) = "Black"
    categories(2) = "Asian"
    categories(3) = "Latino"

    ' Clear the contents of the chart workspace. This removes
    ' any old charts that may already exist and leaves the chart workspace
    ' completely empty. One chart object is then added.
    ChartSpace1.Clear
    ChartSpace1.Charts.Add
    Set c = ChartSpace1.Constants

    ' Add three series to the chart.
    ChartSpace1.Charts(0).SeriesCollection.Add
    ChartSpace1.Charts(0).SeriesCollection.Add
    ChartSpace1.Charts(0).SeriesCollection.Add

    ' Series one contains election data for Perot.
    ' Set the series caption (the text that appears in the legend).
    ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Perot"

    ' Set the categories for the first series (this collection is zero-based)
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, c.chDataLiteral, categories

values(0) = 0.2 ' The White value.
values(1) = 0.06 ' The Black value.
values(2) = 0.17 ' The Asian value.
values(3) = 0.13 ' The Latino value.

ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Perot"
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, c.chDataLiteral, categories
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, c.chDataLiteral, values

    ' Series two contains election data for Clinton.
    ' Update the values array, then set the chart data.
    values(0) = 0.38 ' The White value.
    values(1) = 0.82 ' The Black value.
    values(2) = 0.28 ' The Asian value.
    values(3) = 0.62 ' The Latino value.

    ChartSpace1.Charts(0).SeriesCollection(1).Caption = "Clinton"
    ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimCategories, c.chDataLiteral, categories
    ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimValues, c.chDataLiteral, values

    ' Series two contains election data for Bush.
    ' Update the values array, and then set the chart data.
    values(0) = 0.42 ' The White value.
    values(1) = 0.12 ' The Black value.
    values(2) = 0.55 ' The Asian value.
    values(3) = 0.25 ' The Latino value.

    ChartSpace1.Charts(0).SeriesCollection(2).Caption = "Bush"
    ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimCategories, c.chDataLiteral, categories
    ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimValues, c.chDataLiteral, values

    ' Make the chart legend visible, format the left value axis as percentage,
    ' and specify that value gridlines are at 10% intervals.
    ChartSpace1.Charts(0).HasLegend = True
    ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).NumberFormat = "0%"
    ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorUnit = 0.1
End Sub
</script>


Creating a Chart Using Data From a Microsoft Office Data Source Control

This example creates a chart from data returned by an SQL query. A data source control is used to connect to the SQL database and run the query. The chart workspace data source is then set to the data source control, and the data member is set to the created recordset. Finally, the SetData method is used to set the chart categories and values using fields in the recordset.

To run this example, copy the remainder of the text in this topic into an HTML page.

<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:480"></object>
<object id=DataSourceControl1 classid=CLSID:0002E530-0000-0000-C000-000000000046></object>

<script language=vbs>
Sub window_onLoad()
    Dim rsd

    DataSourceControl1.ConnectionString = "DRIVER={SQL Server};SERVER=vdata;DATABASE=pubs;UID=sa;PWD=;"
    Set rsd = DataSourceControl1.RecordsetDefs.AddNew("SELECT lname, job_lvl FROM Employee ORDER BY lname DESC", 3)

    Set c = ChartSpace1.Constants
    ChartSpace1.DataSource = DataSourceControl1
    ChartSpace1.DataMember = rsd.Name
    ChartSpace1.Charts.Add
    ChartSpace1.Charts(0).Type = c.chChartTypeBarClustered
    ChartSpace1.Charts(0).SetData c.chDimCategories, 0, "lname"
    ChartSpace1.Charts(0).SetData c.chDimValues, 0, "job_lvl"
End Sub
</script>


Creating a Chart Using Data From an ADO Recordset

This example creates a chart using data from an ADO recordset. A tab-delimited string is created from the returned recordset data, and then this string is used with the SetData method to set the chart data.

To run this example, copy the remainder of the text in this topic into an HTML page.

<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:350"></object>
<object id=ADOConnection1 classid=CLSID:00000514-0000-0010-8000-00AA006D2EA4></object>

<script language=vbs>
Sub Window_OnLoad()
    Dim rs, categories, values

    ' This example connects to the Northwind sample database
    ' and charts the Query "Category Sales for 1995". ADO is used to open a
    ' connection to the database and return the entire recordset. The query
    ' contains two columns: Category Name and Category Sales.
    ' The records are then extracted into strings.

    categories = ""
    values = ""

    ' Open the connection and execute the query.
    ADOConnection1.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\nwind.mdb"
    Set rs = ADOConnection1.Execute("SELECT * FROM [Category Sales for 1995]")

    ' Start at the first record and move through the entire recordset.
    ' Field 0 is the category name, Field 1 is the sales value.
    ' Create a tab-delimited string for the names and one for the values.
    rs.MoveFirst
    While Not rs.EOF
        categories = categories & rs.Fields(0).Value & Chr(9)
        values = values & rs.Fields(1).Value & Chr(9)
        rs.MoveNext
    Wend
    rs.Close
    ADOConnection1.Close

    ' Remove the leftover tab character at the end of the strings.
    categories = Left(categories, Len(categories) - 1)
    values = Left(values, Len(values) - 1)

    ' Create a chart with one series (called "Sales").
    ChartSpace1.Clear
    ChartSpace1.Charts.Add
    ChartSpace1.Charts(0).SeriesCollection.Add
    ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Sales"

    'Set the series categories and values using the strings created from the recordset.
    Set c = ChartSpace1.Constants
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, c.chDataLiteral, categories
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, c.chDataLiteral, values

    ' Set the chart type and format the axis as U.S. dollars.
    ChartSpace1.Charts(0).Type = c.chChartTypeBarClustered
    ChartSpace1.Charts(0).Axes(c.chAxisPositionBottom).NumberFormat = "$#,##0"
End Sub
</script>


Creating a Chart Using Data From a Microsoft Office Spreadsheet

This example creates a chart from data specified in a Microsoft Office spreadsheet. The chart is bound to the spreadsheet using the DataSource property, and then the SetData method is used to set the chart data from ranges on the spreadsheet.

To run this example, copy the remainder of the text in this topic into an HTML page.

<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:49%;height:350"></object>
<object id=Spreadsheet1 classid=CLSID:0002E510-0000-0000-C000-000000000046 style="width:49%;height:350"></object>
<br><small><b>Source: </b>Voters Research and Surveys</small><p>

<script language=vbs>
Sub Window_OnLoad()
    ' This example starts by putting the data into Spreadsheet1. Normally,
    ' the data would already be loaded, but this code was added for completeness.
    Spreadsheet1.ActiveSheet.Cells.Clear
    Spreadsheet1.ActiveSheet.Cells(2, 1).Value = "White"
    Spreadsheet1.ActiveSheet.Cells(3, 1).Value = "Black"
    Spreadsheet1.ActiveSheet.Cells(4, 1).Value = "Asian"
    Spreadsheet1.ActiveSheet.Cells(5, 1).Value = "Latino"

    Spreadsheet1.ActiveSheet.Cells(1, 2).Value = "Perot"
    Spreadsheet1.ActiveSheet.Cells(2, 2).Value = 0.2
    Spreadsheet1.ActiveSheet.Cells(3, 2).Value = 0.06
    Spreadsheet1.ActiveSheet.Cells(4, 2).Value = 0.17
    Spreadsheet1.ActiveSheet.Cells(5, 2).Value = 0.13

    Spreadsheet1.ActiveSheet.Cells(1, 3).Value = "Clinton"
    Spreadsheet1.ActiveSheet.Cells(2, 3).Value = 0.38
    Spreadsheet1.ActiveSheet.Cells(3, 3).Value = 0.82
    Spreadsheet1.ActiveSheet.Cells(4, 3).Value = 0.28
    Spreadsheet1.ActiveSheet.Cells(5, 3).Value = 0.62

    Spreadsheet1.ActiveSheet.Cells(1, 4).Value = "Bush"
    Spreadsheet1.ActiveSheet.Cells(2, 4).Value = 0.42
    Spreadsheet1.ActiveSheet.Cells(3, 4).Value = 0.12
    Spreadsheet1.ActiveSheet.Cells(4, 4).Value = 0.55
    Spreadsheet1.ActiveSheet.Cells(5, 4).Value = 0.25

    ' Clear the contents of the chart workspace. This removes
    ' any old charts that may already exist and leaves the chart workspace
    ' completely empty. One chart object is then added.
    ChartSpace1.Clear
    ChartSpace1.Charts.Add
    Set c = ChartSpace1.Constants

    ' Set the chart DataSource property to the spreadsheet.
    ' It is possible to specify multiple data sources, but this example uses only one.
    ChartSpace1.DataSource = Spreadsheet1

    ' Add three series to the chart.
    ChartSpace1.Charts(0).SeriesCollection.Add
    ChartSpace1.Charts(0).SeriesCollection.Add
    ChartSpace1.Charts(0).SeriesCollection.Add

    ' Connect the chart to data by specifying spreadsheet cell references
    ' for the different data dimensions. Notice that the SetData method uses
    ' a data source index of 0; this is the first data source, which was previously
    ' set to the spreadsheet. If you had created multiple data sources,
    ' you could specify the index to any item in the WCDataSources collection for the
    ' data source index. For example, if two spreadsheet controls were attached to this
    ' chart workspace, you could set data from the first control using index 0
    ' and set data from the second control using index 1.

    ' Notice that the series name is also bound to a spreadsheet cell. Changing
    ' the contents of the cell "B1" will also change the name that appears in the legend.
    ' If you don't want this behavior, set SeriesCollection(0).Caption instead of
    ' using the SetData method to bind the series name to the spreadsheet.

    ' Series one contains election data for Perot.
    ' Bind the series name, the category names, and the values.
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimSeriesNames, 0, "B1"
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, 0, "A2:A5"
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, 0, "B2:B5"

    ' Series two contains election data for Clinton.
    ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimSeriesNames, 0, "C1"
    ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimCategories, 0, "A2:A5"
    ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimValues, 0, "C2:C5"

    ' Series two contains election data for Bush.
    ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimSeriesNames, 0, "D1"
    ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimCategories, 0, "A2:A5"
    ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimValues, 0, "D2:D5"

    ' Make the chart legend visible, format the left value axis as percentage,
    ' and specify that value gridlines are at 10% intervals.
    ChartSpace1.Charts(0).HasLegend = True
    ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).NumberFormat = "0%"
    ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorUnit = 0.1
End Sub
</script>


Creating a Chart Using Data Specified in Strings

This example creates a chart using data specified in strings. Tab-delimited strings are set to the category and value data, and then these strings are used with the SetData method to set the chart data.

To run this example, copy the remainder of the text in this topic into an HTML page.

<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:350"></object>
<br><small><b>Source: </b>Voters Research and Surveys</small>

<script language=vbs>
Sub Window_OnLoad()
    Dim categories, values

    ' Create a column chart with three series and four categories, showing the
    'percentage of voters for the 1992 presidential election by race/ethic group.

    ' Clear the contents of the chart workspace. This removes
    ' any old charts that may already exist and leaves the chart workspace
    ' completely empty. One chart object is then added.
    ChartSpace1.Clear
    ChartSpace1.Charts.Add
    Set c = ChartSpace1.Constants

    ' Set a tab-delimited string to the category data.
    categories = "White" & Chr(9) & "Black" & Chr(9) & "Asian" & Chr(9) & "Latino"

    ' Add three series to the chart.
    ChartSpace1.Charts(0).SeriesCollection.Add
    ChartSpace1.Charts(0).SeriesCollection.Add
    ChartSpace1.Charts(0).SeriesCollection.Add

    ' Series one contains election data for Perot.
    ' Set the series caption (the text that appears in the legend).
    ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Perot"

    ' Set the categories for the first series (this collection is zero-based).
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, c.chDataLiteral, categories

    ' Set a tab-delimited string to the values for Perot, and then set the series values.
    values = "0.2" & Chr(9) & "0.06" & Chr(9) & "0.17" & Chr(9) & "0.13"
    ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, c.chDataLiteral, values

    ' Series two contains election data for Clinton.
    ' Update the values string, and then set the chart data.
    values = "0.38" & Chr(9) & "0.82" & Chr(9) & "0.28" & Chr(9) & "0.62"
    ChartSpace1.Charts(0).SeriesCollection(1).Caption = "Clinton"
    ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimCategories, c.chDataLiteral, categories
    ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimValues, c.chDataLiteral, values

    ' Series two contains election data for Bush.
    ' Update the values string, and then set the chart data.
    values = "0.42" & Chr(9) & "0.12" & Chr(9) & "0.55" & Chr(9) & "0.25"
    ChartSpace1.Charts(0).SeriesCollection(2).Caption = "Bush"
    ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimCategories, c.chDataLiteral, categories
    ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimValues, c.chDataLiteral, values

    ' Make the chart legend visible, format the left value axis as percentage,
    ' and specify that value gridlines are at 10% intervals.
    ChartSpace1.Charts(0).HasLegend = True
    ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).NumberFormat = "0%"
    ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorUnit = 0.1
End Sub
</script>


Locating a Specific Chart Axis

Every chart contains a WCAxes collection, which can contain up to eight category and value axes. This collection is unordered, so locating a specific axis requires enumerating the collection.

Example 1: Find and return the first category axis.

Function FindCategoryAxis(chart)
   Dim i, c

   Set c = chart.Parent.Constants
   For i = 0 To chart.Axes.Count - 1
     If chart.Axes(i).Type = c.chCategoryAxis Then
        Set FindCategoryAxis = chart.Axes(i)
        Exit Function
     End If
   Next
   Set FindCategoryAxis = Nothing
End Function

Example 2: Find and return the first vertical value axis.

Function FindVerticalValueAxis(chart)
   Dim i, c

   Set c = chart.Parent.Constants
   For i = 0 To chart.Axes.Count - 1
     If chart.Axes(i).Type = c.chValueAxis And _
       (chart.Axes(i).Position = c.chAxisPositionLeft Or _
        chart.Axes(i).Position = c.chAxesPositionRight) Then
           Set FindVerticalValueAxis = chart.Axes(i)
           Exit Function
     End If
   Next
   Set FindVerticalValueAxis = Nothing
End Function


Creating a Chart Containing More Than One Chart Type

You can create a combination chart that contains a mixture of columns, lines, and areas. Note that the combined chart types must have compatible stacking (that is, you cannot combine stacked columns with lines; you can only combine stacked columns with stacked lines).

Both charts and series have a Type property. Setting the Type property for the WCChart object changes the chart type for every series. Setting the Type property for the WCSeries object changes only that series if the new series type must be compatible with the rest of the chart. If the new series type is not compatible, the entire chart will change type to accommodate the changed series. This means that if you change one series to a line series on a clustered column chart, you will create a combination column-line chart. On the other hand, if you attempt to change a series on a clustered column chart to a pie series, the entire chart will become a pie chart.


Returning an Object from a Collection

The Item property returns a single object from a collection. The following example sets the variable thisChart to a WCChart object that represents chart one.

Set thisChart = ChartWorkspace1.Charts.Item(1)

The Item property is the default property for most collections, so you can write the same statement more concisely by omitting the Item keyword.

Set thisChart = ChartWorkspace1.Charts(1)

Some collections use an enumerated type with their Item property to return specific members of the collection. For example, the WCAxes collection uses the ChartAxisPositionEnum enumerated type, as shown in the following example.

Set c = ChartSpace1.Constants
Set valueAxis = ChartSpace1.Charts(0).Axes.Item(c.chAxisPositionLeft)
Set categoryAxis = ChartSpace1.Charts(0).Axes.Item(c.chAxisPositionBottom)

Again, you can omit the Item keyword, as shown in the following example.

Set c = ChartSpace1.Constants
Set valueAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft)
Set categoryAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionBottom)

For more information about a specific collection, see the Help topic for that collection.


Why Is There a Charts Collection?

Why would I ever want more than one chart?

There are many cases in which you might use multiple charts in one chart workspace. Here are some typical scenarios:

  • You want to have multiple pie charts.

  • You want a stock chart showing price and volume.

  • You want to compare similar information, with one variable changing from chart to chart. An example of this is a chart showing SAT scores by region. This might entail four charts—North, South, East, and West—each of which compares school districts within that region by median score.


Why Do Multiple Charts in One Chart Workspace Share the Same Categories?

The main reason for having multiple charts in one chart workspace is to show related information across all of the charts. This works best if all of the charts have the same categories. If you want to use multiple unrelated charts, use more than one chart workspace.


Add Method (LookupRelationships Collection)

Creates a new lookup join relationship. Returns a PageRelationship object.

Syntax

expression.Add(PageRowsource, SchemaRelationship)

expression An expression that returns a LookupRelationships object.

PageRowsource Required PageRowsource object. Specifies the page row source on the “one” side of the one-to-many page relationship.

SchemaRelationship Required SchemaRelationship object. Describes the connecting fields between the parent page row source on the “many” side of the one-to-many relationship and the page row source specified in this method.


Add Method (PageFields Collection)

Creates a new page field. Returns a PageField object.

Syntax

expression.Add(Source, FieldType, Name, TotalType, Index)

expression An expression that returns a PageFields object.

Source Required Variant. Specifies the source for the new page field. For new fields of type dscOutput, this argument specifies a SchemaField object or the name of a schema field. For fields of type dscCalculated, this argument specifies the expression for the calculated field. For new fields of type dscGrouping, this argument specifies a PageField object or the name of a page field on which this grouping is based

FieldType Optional Variant. Specifies the new field type. The default value and allowed values depend on the parent object used with this method. For more information, see the “Remarks” section in this topic.

Name Optional Variant. Specifies the name of the new page field. If you do not specify this argument, the name of an output field is the same as the schema field it is based on, the name of a grouping field is "By<pagefieldname>" or "<agg function>of<pagefieldname>", and the name of a calculated field is "Expr". In all cases, numerals are automatically appended to the new name to make it unique, if necessary.

TotalType Optional dscTotalTypeEnum. The default value is dscNone.

Index Optional Variant. Specifies the ordinal position, the name, or the actual object reference for the page field before which the new page field is added. If you do not specify this argument, the new page field is added at the end of the collection. You use this argument only with a GroupingDef object's PageFields collection. For more information, see the “Remarks” section following this paragraph.

Remarks

The PageFields collection has three different parent objects: RecordsetDef, PageRowsource, and GroupingDef. You use the same Add method for all of these parent objects. There are, however, some differences in defaults and allowed values depending on which parent object you use.

  • For RecordsetDef.PageFields.Add, the default value for FieldType is dscOutput. Other allowed FieldType values are dscCalculated and dscGrouping. When FieldType is dscOutput, the specified source field will be added to the primary page row source in the recordset definition; thus, the source field must exist in the corresponding schema row source. When FieldType is dscGrouping, the TotalType value must be something other than dscNone (a nonaggregate grouping field can exist only in a grouping definition, not in a recordset definition). You cannot use the Index argument in this scenario.

  • For PageRowsource.PageFields.Add, the default and only allowed value for FieldType is dscOutput. The only allowed value for TotalType is dscNone. You cannot use the Index argument in this scenario. Using the Add method, you can add output fields to look up row sources.

  • For GroupingDef.PageFields.Add, the default value for FieldType is dscGrouping. The other allowed FieldType value is dscCalculated. You can use the Index argument in this scenario (sequence is important in groupings of definitions).


Add Method (ParameterValues Collection)

Creates a new parameter value. Returns a ParameterValue object.

Syntax

expression.Add(ParameterName, Value)

expression An expression that returns a ParameterValues collection.

ParameterName Required String. Specifies the parameter name.

Value Optional Variant. Specifies the value of the parameter.


Add Method (RecordsetDefs Collection)

Creates a new recordset definition. Returns a RecordsetDef object.

Syntax

expression.Add(SchemaRowsource, Name)

expression An expression that returns a RecordsetDefs object.

SchemaRowsource Required Variant. Specifies the schema row source that will be used to create the new recordset definition. This argument can be a SchemaRowsource object or the name of an existing schema row source.

Name Optional Variant. Specifies the name of the new recordset definition. If you do not specify this argument, the new recordset definition has the same name as the schema row source specified by the SchemaRowsource argument; if necessary, numerals are automatically appended to the new name to make it unique.

Remarks

Using a single AddNew method call, you can create a recordset definition, page row source, and schema row source.


Add Method (SchemaFields Collection)

Creates a new schema field. Returns a SchemaField object.

Syntax

expression.Add(Name, DataType, Length)

expression An expression that returns a SchemaFields object.

Name Required String. Specifies the name of the new schema field.

DataType Required ADO DataTypeEnum. Specifies the data type of the new schema field.

Length Optional Variant. Specifies the length of the new schema field.

Remarks

In design mode in Microsoft Access, the SchemaFields collection is automatically filled with the available schema fields.


Add Method (SchemaParameters Collection)

Creates a new schema parameter. Returns a SchemaParameter object.

Syntax

expression.Add(Name, DataType, Size, Scale, Precision, Direction)

expression An expression that returns a SchemaParameters object.

Name Required String. Specifies the name of the new schema parameter.

DataType Optional Variant. Specifies the data type of the new schema parameter. Can be one of the ADO DataTypeEnum constants.

Size Optional Variant. Specifies the maximum length of the new schema parameter.

Scale Optional Variant. Specifies the maximum number of digits to the right of the decimal point.

Precision Optional Variant. Specifies the maximum number of digits used for the parameter.

Direction Optional Variant. Specifies whether the parameter is an input parameter, an output parameter, both an input and output parameter, or a procedure return value. As only input parameters are supported, you should not specify this argument.

Remarks

In design mode in Microsoft Access, the SchemaParameters collection is automatically filled with the available schema parameters.


Add Method (SchemaRelatedFields Collection)

Creates a new related field. Returns a SchemaRelatedField object.

Syntax

expression.Add(ManySchemaField, OneSchemaField)

expression An expression that returns a SchemaRelatedFields object.

ManySchemaField Required String. Specifies the name of the schema field on the “one” side of the one-to-many relationship.

OneSchemaField Required String. Specifies the name of the schema field on the “many” side of the one-to-many relationship.

Remarks

A SchemaRelatedField object describes the column pairings that make up a SchemaRelationship object. The names of these fields are used in generating join clauses in SQL and parent-child relationships in a hierarchy.

Remarks

In design mode in Microsoft Access, the SchemaFields collection is automatically filled with the available schema fields.


Add Method (SchemaRelationships Collection)

Creates a new schema relationship. Returns a SchemaRelationship object.

Syntax

expression.Add(Name, ManySchemaRowsource, OneSchemaRowsource, ManySchemaField, OneSchemaField)

expression An expression that returns a SchemaRelationships object.

Name Required String. Specifies the name of the new schema relationship.

ManySchemaRowsource Required String. Specifies the name of the row source on the “many” side of this one-to-many relationship.

OneSchemaRowsource Required String. Specifies the name of the row source on the “one” side of this one-to-many relationship.

ManySchemaField Required String. Specifies the name of the schema field on the “many” side of this one-to-many relationship.

OneSchemaField Required String. Specifies the name of the schema field on the “one” side of this one-to-many relationship.

Remarks

In design mode in Microsoft Access, the SchemaRelationships collection is automatically filled with the available schema relationships.


Add Method (SchemaRowsources Collection)

Creates a new schema row source. Returns a SchemaRowSource object.

Syntax

expression.Add(Name, RowsourceType, CommandText)

expression An expression that returns a SchemaRowsources object.

Name Required String. Specifies the name of the new row source.

RowsourceType Required dscRowsourceTypeEnum. Specifies the type of the new row source.

CommandText Optional Variant. The schema row source command text. For more information, see the Help topic for the CommandText property.

Remarks

In design mode in Microsoft Access, the SchemaRowsources collection is automatically filled with the available schema row sources.


Add Method (SublistRelationships Collection)

Creates a new sublist relationship. Returns a PageRelationship object.

Syntax

expression.Add(PageRowsource, SchemaRelationship)

expression An expression that returns a SublistRelationships object.

PageRowsource Required PageRowsource object.

SchemaRelationship Required SchemaRelationship object.

Remarks

The SublistRelationships collection contains PageRelationship objects of type dscSublist for which a RecordsetDef object is the “one” side (the parent) of a one-to-many relationship.


Add Method (WCAxes Collection)

Adds an axis to a chart. Creates a new, uninitialized WCAxis object with all properties set to Automatic. You must assign a scaling, position, and type to each added axis.

Syntax

expression.Add(Scaling, Pos, Type)

expression An expression that returns a WCAxes object.

Scaling Required WCScaling object. A scaling object from another axis or series.

Pos Required ChartAxisPositionEnum. Specifies the new axis position. For more information, see the Position property for the WCAxis object.

Type Required ChartAxisTypeEnum. Specifies the new axis type. For more information, see the Type property for the WCAxis object.

Remarks

A chart can contain no more than eight axes.


Add Method (WCAxes Collection) Example

This example adds a second value axis to the right side of the chart.

Set c = ChartSpace1.Constants
Set cht = ChartSpace1.Charts(0)
cht.Axes.Add cht.Axes(c.chAxisPositionLeft).Scaling, c.chAxisPositionRight, c.chValueAxis


Add Method (WCCharts Collection)

Creates a new, empty chart. Returns a WCChart object.

Syntax

expression.Add(Index)

expression An expression that returns a WCCharts object.

Index Optional Long. Specifies the position of the new chart. Specifying zero (0) inserts the chart at the beginning of the collection. If you do not specify this argument, the new chart is inserted at the end of the collection.


Add Method (WCCharts Collection) Example

This example adds a chart to the chart workspace.

Set cht = ChartSpace1.Charts.Add


Add Method (WCDataLabelsCollection Collection)

Adds data labels to the specified series. Creates a new, uninitialized WCDataLabels object and adds it to the specified WCDataLabelsCollection collection. A series can have only one set of data labels; this method fails if the WCDataLabelsCollection collection already contains a WCDataLabels object.

Syntax

expression.Add

expression An expression that returns a WCDataLabelsCollection object.


Add Method (WCDataLabelsCollection Collection) Example

This example adds data labels to the specified series in the chart workspace.

Set ch = ChartSpace1.Charts(0)
ch.SeriesCollection(0).DataLabelsCollection.Add


Add Method (WCDataSources Collection)

Creates a new, uninitialized WCDataSource object and adds it to the end of the WCDataSources collection.

Syntax

expression.Add

expression An expression that returns a WCDataSources collection.


Add Method (WCErrorbarsCollection Collection)

Adds error bars to a series. Creates a new, uninitialized WCErrorBars object and adds it to the specified WCErrorBarsCollection collection. A chart can contain only one set of error bars; this means that an xy scatter chart can contain either x or y error bars, but not both.

Syntax

expression.Add

expression An expression that returns a WCErrorbarsCollection object.


Add Method (WCErrorbarsCollection Collection) Example

This example adds error bars to the specified series in the chart workspace.

Set eb = ChartSpace1.Charts(0).SeriesCollection(0).ErrorBarsCollection.Add


Add Method (WCSeriesCollection Collection)

Adds a series to a chart. Creates a new, empty WCSeries object at the specified position in the WCSeriesCollection collection.

Syntax

expression.Add(Index)

expression An expression that returns a WCSeriesCollection object.

Index Optional Long. Specifies the position of the new series in the collection. Specifying zero (0) places the new series at the beginning of the collection. If you do not specify this argument, the new series is placed at the end of the collection.


Add Method (WCSeriesCollection Collection) Example

This example adds a new series to the specified chart in the chart workspace and then sets the values for the new series.

Dim values(7)

values(0) = 0
values(1) = 1
values(2) = 10
values(3) = 25
values(4) = 30
values(5) = 40
values(6) = 60

Set s2 = ChartSpace1.Charts(0).SeriesCollection.Add
Set c = ChartSpace1.Constants
s2.SetData c.chDimValues, c.chDataLiteral, values


Add Method (WCTrendlines Collection)

Adds a trendline to a series. Creates a new, uninitialized WCTrendline object and adds it to the end of the specified WCTrendlines collection. Each series can contain only one trendline.

Syntax

expression.Add

expression An expression that returns a WCTrendlines object.


Add Method (WCTrendlines Collection) Example

This example adds a trendline to the specified series in the chart workspace.

Set trndline = ChartSpace1.Charts(0).SeriesCollection(0).Trendlines.Add


Clear Method Example

This example deletes all charts in the specified chart workspace.

ChartSpace1.Clear


Delete Method Example

This example deletes the data labels from the specified series in the chart workspace.

ChartSpace1.Charts(0).SeriesCollection(0).DataLabelsCollection.Delete 0


Export Method Example

This example exports the active worksheet to the specified file.

set  c = Spreadsheet1.Constants
Spreadsheet1.ActiveSheet.Export "ssheet1.htm", c.ssExportActionNone


Load Method Example

This example loads the specified XML data file.

ChartSpace1.Load "chart.xml"


Show Method Example

This example scrolls the spreadsheet until cell P75 is visible.

Spreadsheet1.Range("p75").Show


AutoFilter Property Example

This example turns on the AutoFilter for the range A1:J22, sets filters for columns 1 and 3, and then applies the filters.

Spreadsheet1.Range("a1:j22").AutoFilter
Set af = Spreadsheet1.ActiveSheet.AutoFilter
Set filterColumnOne = af.Filters(1)
Set filterColumnThree = af.Filters(3)
filterColumnOne.Criteria.Add "blue"    ' Don't show blue in column 1.
filterColumnOne.Criteria.Add "green"    ' Don't show green in column 1.
filterColumnThree.Criteria.Add "yellow"    ' Don't show yellow in column 3.
af.Apply


Border Property Example

This example sets the line weight for the border for the specified series in the chart workspace.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).SeriesCollection(0).Border.Weight = c.owcLineWeightMedium


Caption Property Example

This example displays the Microsoft Office Web Components build number in the spreadsheet’s title bar.

Spreadsheet1.TitleBar.Caption = "MSOWC Build " & Spreadsheet1.BuildNumber


Charts Property Example

This example sets font properties for the specified axis.

Set axs = ChartSpace1.Charts(0).Axes(1)
axs.Font.Name = "Arial"
axs.Font.Size = 8
axs.Font.Bold = True

This example adds a chart to the specified chart workspace.

Set cht = ChartSpace1.Charts.Add


Column Property Example

This example sets the font to bold in every other column in the visible range on the active worksheet.

For Each col In Spreadsheet1.ActiveSheet.VisibleRange.Columns
    If col.Column Mod 2 = 0 Then col.Font.Bold = True
Next


Command Property Example

This event procedure runs whenever the user runs a spreadsheet command. If the user tries to insert columns on the specified worksheet, the procedure displays a message box and cancels the command. Note that the procedure in this example is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures.

Private Sub Spreadsheet1_BeforeCommand(ByVal EventInfo As OWC.SpreadsheetEventInfo)
    Set c = Spreadsheet1.Constants
    If EventInfo.Command = c.ssInsertColumns Then
        MsgBox "you cannot insert columns on this sheet"
        EventInfo.ReturnValue = False
    End If
End Sub


Crosses Property Example

This example sets the category axis to cross the value axis at value zero (0) in the chart workspace if a custom crossing point has not alreadyu been set for the category axis.

Set c = ChartSpace1.Constants
Set valueAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft)
Set categoryAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionBottom)
If categoryAxis.Crosses = c.chAxisCrossesAutomatic Then
    valueAxis.CrossingAxis = categoryAxis
    categoryAxis.CrossesAtValue = 0
End If


DataLabel Property Example

This example adds a trendline to the specified series in the chart workspace, sets the font for the data label to bold, and causes the trendline to display only its equation (the R-squared value is not displayed).

Set trndline = ChartSpace1.Charts(0).SeriesCollection(0).Trendlines.Add
trndline.DataLabel.Font.Bold = True
trndline.IsDisplayingRSquared = False


DataSource Property Example

This example creates a chart that is bound to a spreadsheet. The series name is in cell B1, the category names are in cells A2:A28, and the values are in cells B2:B28.

Set c = ChartSpace1.Constants
Set ChartSpace1.DataSource = Spreadsheet1.Object
ChartSpace1.Charts.Add
ChartSpace1.Charts(0).Type = c.chChartTypeLineMarkers
ChartSpace1.Charts(0).SetData c.chDimCategories, 0, "a2:a28"
ChartSpace1.Charts(0).SetData c.chDimSeriesNames, 0, "b1"
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, 0, "b2:b28"


Font Property Example

This example sets the number format and font for the active cell.

Set ac = Spreadsheet1.ActiveCell
ac.NumberFormat = "0.###"
ac.Font.Color = "blue"
ac.Font.Name = "times new roman"


Formula Property Example

This example sets the formula, column width, and number format for the range A1:F10 on the active worksheet.

Set rg = Spreadsheet1.ActiveSheet.Range("a1:f10")
rg.Formula = "=5*rand()"
rg.ColumnWidth = 100
rg.NumberFormat = "#.###"


Height Property Example

This example sets the height of the PivotTable list to 36 points.

PivotTable1.Object.Height = 36


Legend Property Example

This example causes the specified chart to display its legend and then sets the legend font.

ChartSpace1.Charts(0).HasLegend = True
ChartSpace1.Charts(0).Legend.Font.Name = "times new roman"


Line Property Example

This example sets the line color for the specified series.

ChartSpace1.Charts(0).SeriesCollection(0).Line.Color = "red"


MoveAfterReturn Property Example

This example causes the active cell to remain selected after the user presses the ENTER key.

Spreadsheet1.MoveAfterReturn = False


Parent Property Example

The event procedure in this example runs whenever the user clicks in the chart workspace. If the user clicks a point, the procedure displays the series and point index values. Note that this procedure is declared as required by Visual Basic. For more information about using event procedures, see Declaring and Using Event Procedures.

Private Sub ChartSpace1_Click(ByVal ChartEventInfo As OWC.WCChartEventInfo)
    Set c = ChartSpace1.Constants
    If ChartSpace1.SelectionType = c.chSelectionPoint Then
        seriesNumber = ChartSpace1.Selection.Parent.Index    ' Point parent is series.
        pointNumber = ChartSpace1.Selection.Index
        MsgBox "series " & seriesNumber & " point " & pointNumber
    End If
End Sub


Row Property Example

This example makes every other row green in the current region for cell A1.

For Each rw In Spreadsheet1.Cells(1, 1).CurrentRegion.Rows
    If rw.Row Mod 2 = 0 Then rw.Interior.Color = "lightgreen"
Next


Scaling Property Example

This example adds a second value (y) axis to the right side of the specified chart. The second axis uses the same scale as the primary value axis.

Set c = ChartSpace1.Constants
Set axisScale = ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Scaling
ChartSpace1.Charts(0).Axes.Add axisScale, c.chAxisPositionRight, c.chValueAxis


Selection Property Example

The procedure in this example runs whenever the selection in the chart workspace changes. If the user selects an axis, the procedure displays the minimum and maximum values for the axis.

Private Sub ChartSpace1_SelectionChange()
    Set c = ChartSpace1.Constants
    If ChartSpace1.SelectionType = c.chSelectionAxis Then
        minval = ChartSpace1.Selection.Scaling.Minimum
        maxval = ChartSpace1.Selection.Scaling.Maximum
        MsgBox "minimum = " & minval & " maximum = " & maxval
    End If
End Sub


Size Property Example

This example sets the font size to 6 points for the specified axis.

Set c = ChartSpace1.Constants
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).Font.Size = 6


Title Property Example

This example sets the specified chart to include a title and sets the title text.

ChartSpace1.Charts(0).HasTitle = True
ChartSpace1.Charts(0).Title.Caption = "Satisfaction Data"


Top Property Example

This example sets a variable for the distance from the top of the spreadsheet window to cell A35.

t2 = Spreadsheet1.Range("a35").Top


Visible Property Example

This example displays the legend for the specified chart and hides the specified legend entry.

ChartSpace1.Charts(0).HasLegend = True
ChartSpace1.Charts(0).Legend.LegendEntries(1).Visible = False


Chart Workspace Object Model

ChartSpace object

WCDataSources collection (WCDataSource)
WCCharts collection (WCChart object)
WCAxes collection (WCAxis object)

WCLegendEntries collection (WCLegendEntry object)

WCSeriesCollection collection (WCSeries object)

WCDataLabelsCollection collection (WCDataLabels object)

WCErrorbarsCollection collection (WCErrorbars object)

WCPoints collection (WCPoint object)

WCTrendlines collection (WCTrendline object)

Legend

yellow box Object and collection
bluebox Object only

right arrow Click red arrow to expand chart


Data Source Control Object Model

AllGroupingDefs collection (GroupingDef object)
PageFields collection (PageField object)

GroupingDefs collection (GroupingDef object)
PageFields collection (PageField object)

PageRowSources collection (PageRowSource object)
ParameterValues collection (ParameterValue object)
SublistSchemaRelationships collection (SchemaRelationship object)

PageRelatedFields collection (PageRelatedField object)

LookupRelationships collection (PageRelationship object)
PageFields collection (PageField object)
AllPageFields collection (PageField object)
DataPages collection (DataPage object)
ElementExtensions collection (ElementExtension object)
GroupLevels collection (GroupLevel object)
RecordsetDefs collection (RecordsetDef object)
SchemaRelationships collection (SchemaRelationship object)
SchemaRelatedFields collection (SchemaRelatedField object)
SchemaRowsources collection (SchemaRowsource object)
LookupSchemaRelationships collection (SchemaRelationship object)
SchemaFields collection (SchemaField object)
SchemaParameters collection (SchemaParameter object)
SublistSchemaRelationships collection (SchemaRelationship object)

Legend

yellow box Object and collection
bluebox Object only

right arrow Click red arrow to expand chart


PivotTable List Object Model

PivotTable object

PivotFieldSets collection (PivotFieldSet object)
Pivot Fields collection (PivotField object)

PivotTotals collection (PivotTotal object)

PivotFieldSets collection (PivotFieldSet object)

PivotFieldSets collection (PivotFieldSet object)

PivotFieldSets collection (PivotFieldSet object)

PivotTotals collection (PivotTotal object)

PivotAggregates collection (PivotAggregate object)

PivotMembers collection (PivotMember object)

Legend

yellow box Object and collection
bluebox Object only

right arrow Click red arrow to expand chart


Spreadsheet Object Model

Borders collection (LineFormat object)

Filters collection (Filter object)
Criteria object (String)
Panes collection (Pane object)

Legend

yellow box Object and collection
bluebox Object only

right arrow Click red arrow to expand chart