Office Object Model: Excel

Properties and methods

`activate()`	Show the window
`activateNext()`, `activatePrevious()`
`activeCell`
`activeChart`
`activePane`
`activeSheet`
`activeSheetView`
`application`
`autoFilterDateGrouping`
`caption`	Reads or writes the name of the Window's title bar.
`close()`
`creator`
`displayFormulas`	Controls if the window's cells display formulas or their calculated values.
`displayGridlines`
`displayHeadings`
`displayHorizontalScrollBar`
`displayOutline`
`displayRightToLeft`
`displayRuler`	Window must be in Page Layout mode in order to show the ruler.
`displayVerticalScrollBar`
`displayWhitespace`	Controls if whitespace is shown when in Page Layout mode. Whitespace refers to the non-printable area around a document page's content (header, margin, …)
`displayWorkbookTabs`	Controls if the sheet tabs (aka workbook tabs) at the bottom of worksheet are shown. Corresponds to File -> Options -> Advanced -> Display options for this workbook -> Show sheet tabs.
`displayZeros`	Controls if a (numerical?) `0` is shown.
`enableResize`	?
`freezePanes`	Create or eliminate panes. See also `.panes` and `.split`.
`gridlineColor`
`gridlineColorIndex`
`height`, `left`, `width`, `top`	Dimension of the window, in points. When assigning values to these properties in order to resize or move the window, `.windowState` should be set to `xlNormal`, otherwise, Excel throws Run-time error '1004': Unable to set the … property of the Window class.
`hWnd`
`index`
`largeScroll()`	Scroll the window's content by «pages» (Vertical scrolls are similar to using the keys Page Up, Page Down). See also `smallScroll()`
`newWindow()`
`onWindow`	The name of a procedure that is triggered when the window is activated.
`panes`	The shown panes in the window. (`.freezePanes` creates or eliminates panes)
`parent`
`pointsToScreenPixelsX()`, `pointsToScreenPixelsY()`	Converts dimensions that are measured in points to pixels.
`printOut()`
`printPreview()`
`rangeFromPoint()`
`rangeSelection`	Returns the selected Range, even if a graphic object is also selected (and `appliction.selection` or `window.selection` would not return a range).
`scrollColumn`, `scrollRow`	Returns or sets the left/top most column/row in a pane or the window.
`scrollIntoView()`	Scroll the shown document such that the indicated range (measured in points!) becomes visible.
`scrollWorkbookTabs()`	Scrolls the sheet tabs (aka workbook tabs) at the bottom of the window.
`selectedSheets`
`selection`
`sheetViews`
`smallScroll()`	Scrolls the shown document by the indicated number of rows and columns. See also `largeScroll()`.
`split`	`true` if the window is split, `false` otherwise. Note: it's possible that `.freezePanes` is `true` and `.split` is false.
`splitColumn`, `splitRow`	Number of columns to the left/rows toward the top where a window is split into panes.
`splitHorizontal`, `splitVertical`
`tabRatio`
`type`
`usableHeight`
`usableWidth`
`view`	Controls if the window is in normal, page break or page layout view-mode.
`visible`
`visibleRange`	The Range that is currently visible in the window.
`windowNumber`
`windowState`	Controls if the window is maximized, minimized or normal
`zoom`

General display options

The way Excel looks can be changed to a certain degree in the window (and application) object:

option explicit

public sub main()

    cells(1,1).value = "=3+4"
    cells(2,1).value = 0
    cells(3,1).value = 1

    with application.activeWindow

  ' Make formula visible ...
   .displayFormulas            = true

  ' ... but don't show formula bar.
  '    (Note the application. here)
  '
    application.displayFormulaBar = false

   .displayGridlines           = false

  ' let the column names (A ... ) and row numbers (1 ...)
  ' disappear
   .displayHeadings            = false

  ' no scrollbars
   .displayHorizontalScrollbar = false
   .displayVerticalScrollbar   = false

  ' If display ruler is false, the horizontal and
  ' vertical rulers won't be displayed, irrespective
  ' of their value
   .displayRuler               = false

    range( cells(2,1), cells(5,1) ).rows.group
  ' dont show grouping symbols (aka «outline»).
   .displayOutline             = false

   .displayRightToLeft         = false

   .displayWorkbookTabs        = false

   .displayZeros               = false

    end with

    application.width          = 200
    application.height         = 160

end sub

Github repository about-MS-Office-object-model, path: /Excel/Window/display.bas

TODO: .displayGridlines corresponds to the GUI option Page Layout -> Sheet Options -> Gridlines.

Complete(?) list of .display* properties:

? activeWindow.displayFormulas
? activeWindow.displayGridlines
? activeWindow.displayHeadings
? activeWindow.displayHorizontalScrollBar
? activeWindow.displayOutline
? activeWindow.displayRightToLeft
? activeWindow.displayRuler
? activeWindow.displayVerticalScrollBar
? activeWindow.displayWhitespace
? activeWindow.displayWorkbookTabs
? activeWindow.displayZeros

Compare with the .display properties of the application object and the display options of the worksheetView object.

freezePanes

.freezePanes splits a worksheet into two areas. One are is fixed and does not respond to scrolling (thus is frozen) while the other region can be scrolled.

The split takes place on the left and/or top edge of the selected cell, row or column. Thus, the following example freezes the first row on a worksheet:

activeSheet.rows(2).select
activeWindow.freezePanes = true ' although a verb, it must be set to true or false (otherwise: Invalid use of property)

hwnd

The .hwnd property is particularly useful when needed in WinAPI functions.

Office Object Model: Excel - Window

Properties and methods

General display options

freezePanes

hwnd

See also