Excel Object Model: Worksheet

Properties and methods

`activate()`	Makes the worksheet the active worksheet, see also `activeSheet`
`application`
`autoFilter`	If filtering is enabled, returns the corresponding `autoFilter` object which allows to show or hide rows based on criteria of the values of the worksheet. See also `.filterMode`
`autoFilterMode`	A boolean that controls if AutoFilter drop-down arrows are displayed.
`calculate()`	(Re-)calculates all formulas on the given worksheet.
`cells`	A Range that represents all cells of the worksheet.
`chartObjects()`
`checkSpelling()`	Display the spelling dialog. There is also a `checkSpelling` on the range object.
`circleInvalid()`, `clearCircles()`	(un-)Circle invalid entries on the worksheet.
`circularReference`
`clearArrows()`	Remove the tracer arrows
`codeName`	See `name` vs `codeName` and the VB Script example Change the `codeName` property of a worksheet.
`columns`
`comments`
`commentsThreaded`
`consolidationFunction`
`consolidationOptions`
`consolidationSources`
`copy()`	Make a copy of the worksheet (Possibly into another workbook)
`creator`
`customProperties`	The worksheet's `customProperties` collection. Compare with the `customDocumentProperties` and `builtinDocumentProperties` properties of the `workbook` object.
`delete()`	Delete the worksheet. Temporarily set `application.displayAlerts` to `false` to prevent the confirm-dialog-box from being shown.
`displayPageBreaks`
`displayRightToLeft`
`enableAutoFilter`
`enableCalculation`
`enableFormatConditionsCalculation`
`enableOutlining`
`enablePivotTable`
`enableSelection`	controls what can be selected by a user. Requires the worksheet to be protected to be in force.
`evaluate()`
`exportAsFixedFormat()`	Export as PDF or XPS. Compare with `workbook.exportAsFixedFormat`
`filterMode`	read-only boolean that indicates if the worksheet is in filter mode. See also `.autoFilter`
`HPageBreaks`, `VPageBreaks`	A collection of Horizontal and vertical page preaks
`hyperlinks`
`index`	The position of the worksheet within the workbook. See also `.move()` and `copy()`
`listObjects`
`mailEnvelope`
`move()`	Move the worksheet to another location within the workbook. See also the `.index` property.
`name`	The name of a worksheet uniquely identifies the worksheet within a workbook. See `name` vs `codeName`
`names`	A collection of `name` objects.
`next`, `previous`	The «next»/«previous» worksheet.
`OLEObjects()`
`outline`
`pageSetup`
`parent`
`paste()`
`pasteSpecial()`
`pivotTables()`
`pivotTableWizard()`	Create a new PivotTable report without display the PivotTable Wizard (not available for OLE DB data sources).
`printedCommentPages`
`printOut()`
`printPreview()`
`protect()`, `unprotect()`
`protectContents`
`protectDrawingObjects`
`protection`
`protectionMode`
`protectScenarios`
`queryTables`
`range`
`resetAllPageBreaks()`
`rows`
`saveAs()`
`scenarios()`
`scrollArea`	defines the area within which a user can scroll a worksheet or select cells.
`select()`	Selects the worksheet and optionally adds it to the currently selected set of worksheets.
`setBackgroundPicture()`
`shapes`
`showAllData()`	Show currently filtered rows in a list. If an `AutoFilter` is set, its arrows are changed to All.
`showDataForm()`	Display the data form that is associated with the respective worksheet.
`sort`	Read/only: The worksheet's `sort` object.
`standardHeight`, `standardWidth`	Default height/width of rows/columns, measured in points.
`tab`	Returns a `tab` object (which can be used to change the color of the worksheet tab).
`transitionExpEval`
`transitionFormEntry`
`type`	Returns the worskheet type (`xlChart`, `xlDialogSheet`, `xlExcel4IntlMacroSheet`, `xlExcel4MacroSheet`, `xlWorksheet`)
`usedRange`
`visible`	Specifes if the sheet is visible, hidden or very hidden. The value of `visible` can be set to one of the corresponding constants `xlSheetVisible`, `xlSheetHidden` or `xlSheetVeryHidden`.
`xmlDataQuery()`
`xmlMapQuery()`

Collections of GUI elements

`.buttons`
`.dropDowns`
`.checkBoxes`
`.spinners`
`.listBoxes`
`.listBoxes`
`.optionButtons`
`.groupBoxes`
`.labels`
`.scrollBars`

Compare with the oleObjects collection.

usedRange

usedRange returns the range that have had or still have a value.

calculate

Re-calculates the formulas in a worksheet.

A re-calculation of formulas can also be triggered with some F9 keyboard shortcuts.

If a Workbook's forceFullCalculation property is set to true, the entire worksheet will be forced to be recalculated.

evaluate

worksheet.evaluate(formula) evaluates a formula in the context of the worksheet on which evaluate was called. Compare with application.evaluate.

getWorksheet

getWorksheet is a function that returns a worksheet with a given name.

If no such worksheet exists, it is created.

function getWorksheet(name_ as string) as excel.worksheet
 '
 '  Return worksheet with the given name.
 '  If it doesn't exist, it is created.
 '

    on error goto createWorksheet
       set getWorksheet = thisWorkbook.sheets(name_)
    '  Worksheet exists, we can return the function:
       exit function

    createWorksheet:
    '  Error encountered, we have to create the worksheet

       set getWorksheet = thisWorkbook.sheets.add(after := thisWorkbook.sheets(thisWorkbook.sheets.count))
           getWorksheet.name = name_

end function

sub main() ' {
    dim ws as worksheet

    set ws = getWorksheet("tq84")
    ws.cells(1,1) = "Hello world"

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Worksheet/getWorksheet.bas

Deleting a worksheet

Keyboard shortcuts

The keyboard shorcuts ctrl+pageUp and ctrl+pageDown rotate through worksheets.

alt+shift+F1 inserts a new worksheet.

The current worksheet can be deleted with alt+h -> d -> s -> enter. This corresponds to the menu/ribbon sequence Home -> Cells -> Delete -> Delete Sheet.

TODO

activeSheet.chartObjects("fooChart"). …