Worksheet Methods

Name Returns Parameters Description

Activate

Activates the worksheet

Calculate

Calculates all the formulas in the worksheet

ChartObjects Object

[Index]

Returns either a chart object (ChartObject) or a collection of chart objects (ChartObjects) in a worksheet

CheckSpelling

[CustomDictionary], [IgnoreUppercase], [AlwaysSuggest], [SpellLang]

Checks the spelling of the text in the worksheet. A custom dictionary can be specified (CustomDictionary), all uppercase words can be ignored (IgnoreUppercase), and Excel can be set to display a list of suggestions (AlwaysSuggest)

CircleInvalid

Circles the invalid entries in the worksheet

ClearArrows

Clears out all the tracer arrows in the worksheet

ClearCircles

Clears all the circles around invalid entries in a worksheet

Name

Returns

Parameters

Description

Copy

[Before],[After]

Adds a new copy of the worksheet to the position specified at the Before or After parameter

Delete

Deletes the worksheet

Evaluate

Variant

Name

Evaluates the Name string expression as if it were entered into a worksheet cell

Type As Variant, Exports a file to a format specified

FileName, Quality, by using the xlFixedFormatType

IncludeDoc constants

Properties, IgnorePrintAreas, From, To,

OpenAfterPublish

ExportAs FixedFormat

Type As Variant, Exports a file to a format specified

FileName, Quality, by using the xlFixedFormatType

IncludeDoc constants

Properties, IgnorePrintAreas, From, To,

OpenAfterPublish

Move [Before], [After] Moves the worksheet to the position specified by the parameters

OLEObjects Object [Index] Returns either a single OLE object

(OLEObject) or a collection of OLE objects (OLEObjects) for a worksheet

Pastes the contents of the clipboard into the worksheet. A specific destination range can be specified with the Destination parameter. Set Link to True to establish a link to the source of the pasted data. Either the Destination or the Link parameter can be used

PasteSpecial

[Format],[Link],

Pastes the clipboard contents into the

[DisplayAsIcon],

current worksheet. The format of the

[IconFileName],

clipboard data can be specified with

[IconIndex],

the string Format parameter. Set

[IconLabel],

Link to True to establish a link to

[NoHTMLFormatting]

the source of the pasted data. Set DisplayAsIcon to True to display the pasted data as an icon and the IconFileName, IconIndex, and IconLabel to specify the icon and label. A destination range must be already selected in the worksheet

ExportAs FixedFormat

Paste [Destination],

Name Returns Parameters Description

PivotTables Object [Index]

PivotTable Pivot [SourceType],

Wizard Table [SourceData],

[TableDestination],

[TableName],

[RowGrand],[Column

Grand],[SaveData],

[HasAutoFormat],

[AutoPage],

[Reserved],

[BackgroundQuery],

[OptimizeCache],

[PageFieldOrder],

[PageFieldWrap

Count],[ReadData],

[Connection]

[ActivePrinter],

[PrintToFile],

[Collate],

[PrToFileName],

[IgnorePrintAreas]

Returns either a single PivotTable report (PivotTable) or a collection of PivotTable reports (Pivot-Tables) for a worksheet

Creates a PivotTable report. The SourceType uses the XLPivot TableSourceType constants to specify the type of SourceData being used for the PivotTable. TableDestination holds the range in the parent worksheet where that report will be placed. TableName holds the name of the new report. Set RowGrand or ColumnGrand to True to show grand totals for rows and columns, respectively. Set HasAuto Format to True for Excel to format the report automatically when it is refreshed or changed. Use the Auto Page parameter to set if a page field is created automatically for consolidation. Set BackgroundQuery to True for Excel to query the data source asynchronously. Set OptimizeCache to True for Excel to optimize the cache when it is built. Use the PageFieldOrder with the xlOrder constants to set how new page fields are added to the report. Use the PageFieldWrapCount to set the number of page fields in each column or row. Set ReadData to True to copy the data from the external database into a cache. Finally, use the Connection parameter to specify an ODBC connection string for the PivotTable's cache

Prints out the worksheet. The printer, number of copies, collation, and whether a print preview is desired can be specified with the parameters. Also, the sheets can be printed to a file using the Print ToFile and PrToFileName parameters. The From and To parameters can be used to specify the range of printed pages

Name

Returns Parameters

Description

PrintPreview

Protect

ResetAllPage Breaks

SaveAs

[EnableChanges]

[Password],

[DrawingObjects],

[Contents],

[Scenarios],[User

InterfaceOnly],

[AllowFormatting

Cells],[Allow

FormattingColumns]

[AllowFormatting

Rows],[Allow

InsertingColumns],

[AllowInserting

Rows],[Allow

Inserting

Hyperlinks],

[AllowDeleting

Columns],[Allow

DeletingRows],

[AllowSorting],

[AllowFiltering],

[AllowUsing

PivotTables]

Filename As String, [FileFormat], [Password], [WriteResPassword] [ReadOnly Recommended], [CreateBackup], [AddToMru], [Text Codepage],[Text VisualLayout] , [Local]

Displays the worksheet in a print preview mode. Set the EnableChanges parameter to False to disable the Margins and Setup buttons, hence not allowing the viewer to modify the page setup

Protects the worksheet from changes. A case-sensitive Password can be specified. Also specifies whether shapes are protected (Drawing Objects), whether the entire contents are protected (Contents), or whether only the user interface is protected (UserInterfaceOnly)

Resets all the page breaks in the worksheet

Saves the worksheet as FileName. The type of file to be saved can be specified with the FileFormat parameter. The file can be saved with the optional passwords in the Password and WriteResPassword parameters. Set ReadOnly Recommended to True to display a message to the user every time the worksheet is opened. Set Create Backup to True to create a backup of the saved file. Set the AddToMru parameter to True to add the worksheet to the recently opened files list

Name

Returns

Parameters

Description

Scenarios

Object

[Index]

Returns either a single scenario (Scenario) or a collection of scenarios (Scenarios) for a worksheet

Select

[Replace]

Selects the worksheet

SetBackground Picture

Filename As String

Sets the worksheet's background to the picture specified by the File Name parameter

ShowAllData

Displays all of the data that is currently filtered

ShowDataForm

Displays the data form that is part of the worksheet

Unprotect

[Password]

Deletes the protection set up for a worksheet. If the worksheet was protected with a password, the password must be specified now

XmlDataQuery

Range

[XPath] As String, [Selection Namespaces],[Map]

Represents cells mapped to a particular XPath

XmlMapQuery

Range

[XPath] As String, [Selection Namespaces][Map]

Represents cells mapped to a particular XPath

0 0

Post a comment