Setting Options with Program Code

Setting Options with Program Code

Most Excel options are set with numerous properties of the Application object. Options that do not affect Excel as a whole, but only a file, a window, or a chart, for example, can be changed via the properties of the corresponding object (Worksheet, Window, etc.), where the association is not always logical.

The settings for the page format, headers and footers, and so on, are carried out with the PageSetup object, which is set for every sheet object (WorkSheet, Chart, etc.) and can also be addressed with the Window object. It is not possible to change all at once the page format of several sheets through program code. (Execute a loop over the sheets in question and change PageSetup for each individual object.)

The active printer, on the other hand, is set with the ActivePrinter property of the Application object. However, there is no possibility of using VBA code to obtain a list of all available printers.

The following tables give an overview of the most important properties and methods.

APPLICATION OBJECT (GENERAL OPTIONS)
ActivePrinter / set the currently active printer
AddIns(…) / access to add-ins
AutoRecover.Enabled / determines whether Excel 2002 should automatically make a backup copy
AutoRecover.Path / specifies the directory for backup copies
Calculation / recalculation of worksheets automatic/manual
CommandBars(…) / access to menu bars and toolbars (see Chapter 8)
DisplayAlerts / display alerts
DisplayFormulaBar / formula bar on/off (True/False)
DisplayFullScreen / full screen mode on/off
DisplayNoteIndicators / red markings in cells to indicate notes
DisplayStatusBar / status bar
ErrorCheckingOptions / options for error checking
MoveAfterReturn / cursor moves on Return into the next cell of a table
MoveAfterReturnDirection / direction of cursor movement on Return
OnEvent … / various event procedures (see Chapter 4)
PromptForSummaryInformation / form for input of information on saving
ScreenUpdating / update screen during macro execution
SheetsInNewWorkbook / number of empty worksheets in a new file
SmartTagRecognizers.Recognize / activation of the smart tag function (Excel 2002)
SmartTagRecognizers(n).Enabled / activation of individual smart tag modules (Excel 2002) activation of the smart tag module (Excel 2002)
Speech.SpeakCellOnEnter / automatic speech output of the cell's contents
StandardFont / name of the default font in worksheets
StandardFontsize / size of the default font in worksheets
WORKBOOK OBJECT (FILE-SPECIFIC OPTIONS)
ChangeFileAccess / change access privileges
Colors / access file's color palette (56 colors)
CreateBackup / create backup file on saving
DisplayDrawingObjects / display drawing objects
EnableAutoRecover / turn on/off automatic backup copy for the file (Excel 2002)
Protect / turn write protection on and off
SmartTagOptions.DisplaySmartTags / displays smart tags (Excel 2002)
SmartTagOptions.EnableSmartTags / save smart tags with file (Excel 2002)
Styles(…) / access to templates
Visible / file visible/invisible (hidden)
WORKSHEET OBJECT (WORKSHEET-SPECIFIC OPTIONS)
DisplayAutomaticPageBreaks / display page breaks in worksheets
EnableAutoFilter / enables display of autofilters
EnableOutlining / enables display of grouping
EnablePivotTable / enables the creation of pivot tables
FilterMode / autofilter on/off
PageSetup / access to page and printer settings
Protection / sheet protection options
SetBackgroundPicture / set background picture
Visible / worksheet is visible/invisible
WINDOW OBJECT (WINDOW-SPECIFIC OPTIONS)
DisplayFormulas / display formulas instead of results
DisplayGridlines / display gridlines
DisplayHeadings / display row and column headings
DisplayHorizontalScrollbar / display horizontal scroll bar
DisplayOutline / display grouping
DisplayZeros / display 0 values (or display empty cell)
DisplayVerticalScrollbar / display vertical scroll bar
DisplayWorkbookTabs / display workbook tabs
FreezePanes / split window frozen/unfrozen
GridLineColor / set color (RGB value) of gridlines
GridLineColorIndex / color of gridlines from the color palette (0 to 55)
PageSetup / access to page and printer settings
Split / window split/not split
SplitColumn / column in which the window is split
SplitRow / row in which the window is split
TabRatio / ratio of tab area to horizontal scroll bar
Zoom / zoom factor
PAGESETUP OBJECT (PAGE LAYOUT, SET SEPARATELY FOR EACH SHEET)
BlackAndWhite / print in black and white
BottomMargin / bottom margin, in points (1/72 inch = 0.35 mm)
CenterFooter / footer, central part
CenterHeader / header, central part
CenterHorizontal / print horizontally centered
CenterVertical / print vertically centered
FirstPageNumber / start value for pagination
FooterMargin / size of footer
HeaderMargin / size of header
LeftFooter / footer, left part
LeftHeader / header, left part
LeftMargin / left margin in points (= 1/72 inch = 0.35 mm)
Orientation / print in vertical or horizontal format
PaperSize / paper size
PrintArea / area of page to be printed
PrintTitleColumns / column title (printed on each page)
PrintTitleRows / row title (printed on each page)
RightFooter / footer, right side
RightHeader / header, right side
RightMargin / right margin in points (= 1/72 inch = 0.35 mm)
TopMargin / top margin in points (= 1/72 inch = 0.35 mm)
DEFAULTWEBOPTIONS (EXCEL GLOBAL) / WEBOPTIONS (FILE-SPECIFIC)
AllowPNG / encode pictures in PNG format
DownloadComponents / download missing web components.
Encoding / desired character set for web browser
LocationOfComponents / location to which web components are to be saved
OrganizeInFolder / save pictures, etc., in their own folder
RelyOnCSS / use Cascading Style Sheets
RelyOnVML / use Vector Markup Language