15.2 Alphabetical Reference

15.2 Alphabetical Reference

In the reference that follows all basic objects of Excel are described as well as the most important objects of the Office, MS Forms, and ADO libraries. Further, an attempt has been made to emphasize the most important and characteristic properties and methods and to explain their functions.

Enumeration Objects

For enumeration objects the like-named method is given to the left, and the parameters of this method to the right. More precisely, to the right we are actually dealing with the Item method, which is almost never written out (that is, instead of Axes.Item(xlValue) one typically writes the short form Axes(xlValue) ). In most cases an object of an enumeration is identified by specifying an index number or name ( Sheets(1) or Sheets("Table 1") ).

With most methods that lead to enumeration objects, data fields are also allowed as arguments. For example, the method Sheets(Array(1,2,3)) returns a Sheets object that contains sheets 1, 2, and 3. Now, for example, you can select all three sheets at once with Select .

With almost all enumeration objects of the Excel library, the first object is addressed with enumeration(1) (for example, Sheets(1) ), the last with the number Count (thus for example, Sheets(Sheets.Count) ). An exception to this rule is the Watches enumeration, for which the first element has index 0 ( Watches(0) ), and the last index is Count-1 . It is also typical of many external libraries (such as ADO) that the index ranges from 0 to Count-1 .

Identical or Similar Properties of Multiple Objects

Properties that are associated with practically all objects in the same or similar form will not be described anew each time. In particular, we have Count (specifies the number of elements of an enumeration object), Index (specifies the index number of an object within an enumeration), Parent (refers to the object lying above), Application (refers to the object Application ), and Creator (contains an ID number of the program from which the object comes, as a rule the ID number of Excel).

Default Properties, Default Objects, Shorthand Notation

With many properties and methods the object Application is considered the default object. An object must be named only if the property or method refers to a different object (for example, to a particular Workbook object).

With all objects that possess a Value property this property is the default property, and need not be explicitly named.

In accessing ranges of cells as well as objects in worksheets the shorthand form [A1] is permitted. The complete notation would be Range("A1") . The shorthand with square brackets corresponds internally to a call to the Evaluate or Item method.

Formalities

In order that we may distinguish from among the many like-named methods and properties without further reference, in this section all objects are written in boldface.

In the left portion of the syntax box are listed methods and properties that lead to the object in question (symbol — ). In the right-hand portion of the syntax box are listed the methods, properties, or indices that lead from the object under consideration to other objects (symbol ˜ ).

In the upper right corner of the syntax box appears the library from which the object comes. (In order for objects from the ADO, Binder, Office, and MS Forms libraries to be able to be used, the library in question must be activated with ToolsReferences.)

ADO	An object of the ActiveX Data Objects library (access to external databases).
Excel	A normal Excel object. These objects are available in every VBA program under Excel 2000.
Excel 2002	Excel object available only since version 2002.
Office	An object of the Office library (document properties, menu bars, toolbars , etc.).
Office 2002	Office object available only since version 2002 (alias XP).
OfficeBinder	An object of the OfficeBinder library (folders).
MSForms	Controls and other objects of the MS Forms library (structure of forms).
Scripting	An object from the Microsoft Scripting-Runtime library (concerns above all File Scripting Objects).
StdOLE	An object from the OLE Automation library (concerns only StdPicture and StdFont ).
VBA	An object that has been made available from the programming language. VBA (concerns only Collection and ErrObject ).
VBE	An object from the VBA Extension library (VBIDE in the object browser).

Order in the Object Hierarchy

For each object in the gray syntax boxes, to the left are placed the most important higher-ranking objects that lead to the object described. To the right are named the most important properties or methods of the described object that lead to lower-ranking objects. In this way an immediate ordering of the object in the object hierarchy is achieved.

For example, take a look at the Axis object (a couple of pages further): The method Axes of the higher-ranked Chart object leads to the Axis object. The two properties MajorGridlines and MinorGridlines refer to the lower-ranked object Gridlines .

The cross references in the left and right sides of the syntax box are in many cases incomplete. For example, dozens of higher-ranking methods and properties lead to the Range object. Conversely, above the properties and methods of the Workbook object there are countless further objects that can be reached. Thus a complete cross reference is essentially impossible due to restrictions of space and the desideratum of comprehensibility.

	AddIn	Excel
Application .AddIns(..) —

The object AddIn contains certain data for the add-ins registered in Excel. The property Installed specifies whether the add-in is currently activated or deactivated. A change in the value of this property also changes the state of the add-in. Path , Name , and FullName specify the file name of the add-in file, Title the title displayed in the ADD-IN dialog, and Comments a brief related explanation.

	AddIns	Excel
Application .AddIns —		(index or name) ˜ AddIn

The object refers to the list of all add-ins registered in Excel. Whether these add-ins are actually activated can be determined via the Installed property of the add-in object. With the method Add you can install new add-ins. For this you have merely to specify the file name of the add-in.

	Adjustments	Excel
Shape .Adjustments —
ShapeRange .Adjustments —

Many Shape objects (to which belong, among others, all AutoShapes, see the Drawing toolbar) possess yellow adjustment points with which in interactive mode the appearance of the object can be changed (such as the width of an arrow or the shape of its point). These changes are stored in several floating-point numbers , which can be addressed with Adjustments(n) . Whether and how many adjustment parameters are available can be determined with Adjustments.Count . It is more difficult to determine the meaning of the n th parameter for the object, since it is not documented. Take the advice of the on-line help and use the macro recorder.

	AllowEditRange	Excel 2002
AllowEditRanges (n) —		.Range ˜ Range
AllowEditRanges .Add —		.Users ˜ UserAccessList

This object contains a range of cells of a worksheet and an associated password. In the case of protected worksheets, the user can make changes to a this range if he or she knows the password. (Different ranges of a worksheet can be protected with different passwords. This can be of practical value if a number of individuals have access to the same file.)

The range of cells can be determined from the Range property, the name of the range from the Title property. The password cannot be immediately read out, but it can be changed with the method ChangePassword . Optionally, with Users.Add one can specify users who are permitted to change the range of cells without a password.

	AllowEditRanges	Excel 2002
Protection .AllowEditRanges —		(index) ˜ AllowEditRange

This enumeration refers to all AllowEditRange objects in a worksheet.

	Application	Excel
Excel .Application —		.Workbooks ˜ Workbook
		.ActiveWorkbook ˜ Workbook
		. Windows ˜ Window
		.ActiveWindow ˜ Window
		.ActiveSheet ˜ Worksheet, Chart
		.Commandbars ˜ Commandbar
		.AddIns ˜ AddIn

The object Application lies above all Excel objects. With its various properties and methods you can reach, sometimes directly, sometimes indirectly, all other objects described in this chapter. In the syntax box above are listed only some particularly frequently needed properties and methods.

Application refers to many properties that control global Excel options (for example, DisplayFormulaBar , Calculation , ScreenUpdating , WindowState , DisplayFullScreen , DisplayStatusBar , DisplayAlerts ). Path specifies the path to Excel.exe . Version contains the number of the current Excel version.

With Application you can access certain worksheet functions, so these can be used in VBA code as well (such as Count , Index , Sum , VLookup , Lookup ).

Application is the default object for many methods and properties, so it does not always have to be specifically named (such as ActiveSheet , ActiveWindow ).

If you are controlling Excel from an external program via ActiveX automation, you must prefix Excel to the Application property in order to specify that the command is to be applied to Excel. Excel can also be used in VBA code within Excel, but it has no effect there.

	Areas	Excel
Range .Areas —		(index) Range

If a cell range is made up of several partial ranges, then the object Areas refers to the rectangular partial areas of this range. Whether a composite range is at hand can be determined by the Count property of Areas .

	AutoCorrect	Excel
Application .AutoCorrect —

The AutoCorrect object controls that autocorrect feature in Excel (optional) during input of text. The property ReplaceText specifies whether the automatic correction is active. ReplacementList contains a data field with the text to be replaced and the associated corrections. With the methods AddReplacement and DeleteReplacement entries to this list can be added or deleted. CapitalizeNamesOfDays specifies whether the first letter of a weekday should be automatically written in uppercase; TwoInitialCapitals determines whether such a typographical error (e.g., GOliath) should be automatically corrected.

	AutoFilter	Excel
Worksheet .AutoFilter —		.Filters ˜ Filter

The AutoFilter object is listed in the object browser, but it is not, alas, documented. (The same can be said for the related objects Filters and Filter ). Thus the following remarks should be taken with due caution.

With the AutoFilter method of the Range object you can construct filters for databases and lists in worksheets. Within a worksheet only one autofilter can be active at a given time. However, this can contain several filters (one for each column of the database). The AutoFilter object enables access to the corresponding Filter objects ( AutoFilter.Filters.Count and AutoFilter.Filters(n) ). The property Range specifies the range of cells of the entire database that should be processed by the autofilter.

	AutoRecover	Excel 2002
Application .AutoRecover —

This object determines whether (property Enabled ), in what interval ( Time ), and in what directory ( Path ) Excel 2002 stores backup copies of all open files.

	Axes	Excel
Chart .Axes —		(type, group ) ˜ Axis

The enumeration object Axes refers to the coordinate axes of a chart (see Axis ). Identification comes from the input of the type ( xlCategory for the X -axis, xlValue for the Y -axis, and xlSeries for the third axis in the case of a 3-D chart). For 2-D charts with two Y -axes the axis group must be specified via the second parameter ( xlPrimary or xlSecondary ).

There are two methods for adding or deleting axes. Axes are generated and deleted by changing the AxisGroup properties of the objects Chart and Series , respectively.

	Axis	Excel
Chart .Axes(..) —		.AxisTitle ˜ AxisTitle
		.MajorGridlines ˜ Gridlines
		.MinorGridlines ˜ Gridlines
		.TickLabels ˜ TickLabels

The Axis object is used in charts for describing the features of the coordinate axes. Charts are usually equipped with X - and Y -axes, while 3-D charts have a Z -axis as well. In the case of combination charts it is possible to have two independent X - and Y -axes (ordered by AxisGroup ). Access to all axes is accomplished via the method Axes .

The most important properties are MinimumScale and MaximumScale (range of values), MajorUnit and MinorUnit (distance between major and minor tick marks and gridlines), MajorTickMark and MinorTickMark (for turning on and off the subsidiary tick marks), and ScaleType (for choosing between linear and logarithmic scaling).

Visual details for labeling can be set by the subsidiary objects AxisTitle and TickLabels .

	AxisTitle	Excel
Axis .AxisTitle —		.Interior ˜ Interior
		.Border ˜ Border
		.Font ˜ Font

The object AxisTitle describes the appearance of the title of a coordinate axis of a chart. The text of the axis label is controlled by the Caption or Text property, and the font by the like-named lower-ranking object. The location of the title can be changed via Left and Top . In order that unlabeled axes can be labeled, first the HasTitle property of the Axis object must be set to True .

	Binder	OfficeBinder
CreateObject("Office.Binder") —		.ActiveSection ˜ Section
GetObject( , "Office.Binder") —		.Sections ˜ Section
.BuiltinDocumentProperties ˜ DocumentProperty
.CustomDocumentProperties ˜ DocumentProperty

The Binder object enables VBA control of binders. Integration into the Excel object model is via ActiveX automation. An object of type Binder must therefore be created with CreateObject or GetObject . Finally, the individual sections of the binder can be accessed via the properties ActiveSection and Sections .

For managing the binders there are available, among others, the methods Open , Close , Save , SaveAs , and PrintOut . The visibility of the binder bar at the left border can be controlled with the property LeftPane . Path and Name specify the path and name of the binder file (identifier *.odb ). Additional binder properties can be read and altered via the XxxDocumentProperties properties.

	Border	Excel
Range .Borders(..) —
chartobject . Border —

The object Border controls the border and line style of individual cells, ranges of cells, and countless chart objects (such as Series , ChartObject , ChartTitle , Oval , Legend ). The three most important properties are Color , LineStyle (such as dotted , for example), and Weight (width of the lines). To facilitate the setting of the Border properties for a range of cells one has the Range method BorderAround .

	Borders	Excel
FormatCondition .Borders —		(index) ˜ Border
Range .Borders —
Style .Borders —

This enumeration object refers to the six Border objects that determine the appearance of the border lines of a range of cells as well as a pair of diagonal lines. As index the XlBordersIndex constant is specified.

	CalculatedFields	Excel
PivotTable. CalculatedFields —		(name or index) ˜ PivotField

This enumeration object refers to those pivot fields of a pivot table that do not come directly from the source data but are calculated. (If in the source data there are columns with price and quantity , then from the product of these numbers a new pivot field sales can be calculated. In interactive mode you execute the command FORMULASCALCULATED FIELD in the pivot table toolbar.) For calculated pivot fields one has IsCalculated=True . The property Formula contains the calculational formula.

	CalculatedItems	Excel
PivotField. CalculatedItems —		(name or index) ˜ PivotItem

This enumeration object refers to pivot elements (not fields) that result from a formula. (In interactive mode to create such formulas you use the command FORMULASCALCULATED ITEM from the pivot table toolbar. You can thus, for example, insert a partial result row into a pivot table that summarizes data from several rows.) For calculated pivot items one has, as with pivot fields, IsCalculated=True . The property Formula contains the calculational formula.

	CalculatedMember[s]	Excel 2002
PivotTable .CalculatedMembers —

This enumeration object refers to the fields of a pivot table that contain calculated data from an OLAP data source. The property CalculateMember. Formula contains the calculational formula, where a special syntax for addressing multidimensional data is allowed.

	CalloutFormat	Excel
Shape .CalloutFormat —
ShapeRange . CalloutFormat —

According to the on-line help, the CalloutFormat object controls various properties of Shape objects of the type "legend with line." However, after several attempts I have been unable to generate an AutoForm object that would allow access to CalloutFormat without an error message.

	CellFormat	Excel 2002
Application .FindFormat —		.Font ˜ Font
Application .ReplaceFormat —

This object describes the cell format that is used in finding and replacing cells. To set the format you access the object via the properties FindFormat and ReplaceFormat . Clear deletes all previously valid settings. For the format settings to be used in finding and replacing, you must set the optional parameters SearchFormat:=True and ReplaceFormat:=True in the respective methods Find and Replace .

	Characters	Excel
Range .Characters(..) —		.Font ˜ Font
object .Characters(..) —

With the Characters method you can access individual characters of a text in a cell or in an object (such as ChartTitle , TextBox ) and change its type style. The properties Text and Caption contain the selected text. The number of the first character and the number of characters must be passed to the Characters method.

	Chart	Excel
Workbook .Charts(..) —		.ChartArea ˜ ChartArea
ChartObject .Chart —		.Axes ˜ Axis
Workbook .ActiveChart —		.SeriesCollection ˜ Series

The Chart object can refer either to an embedded chart in a worksheet or to the principal chart in a chart sheet. (There is no independent type of object for chart sheets.) In each case Chart represents the basic object for the content and visual appearance of a chart. For charts that are embedded in worksheets there is, in addition, the ChartObject , standing between the worksheet and the chart, which determines the position and size of the chart.

From the Chart object more than thirty properties and methods refer to subordinate objects, by means of which most details of content and form in the chart can be controlled. The three most important properties and methods are named in the syntax box above. A complete overview is given in the previous section by the object hierarchy list, under the topic of chart objects.

Ordered directly beneath the Chart object are the properties Type and SubType , through which the chart type is set. The method ChartWizard creates a new chart. Elevation , Rotation , and Perspective determine the viewing direction of a 3-D chart.

	ChartArea	Excel
Chart .ChartArea —		.Interior ˜ Interior
		.Border ˜ Border

The object ChartArea describes the visual appearance of the entire chart (including axes, legend, title, etc.). In contrast to ChartArea , we have the object PlotArea , which concerns only the background portion of the chart.

The appearance of the chart area is handled principally by the subobjects Interior (color and pattern) and Border (border). This object acquires additional significance via the methods Copy , ClearFormats , ClearContents , and Clear : These methods concern not the chart area, but the entire chart.

	ChartColorFormat	Excel
ChartFillFormat .Fore-/BackColor —

This object enables the setting of color transitions in various chart objects. There is no recognizable difference between this and ColorFormat . See also ChartFillFormat and ColorFormat .

	ChartFillFormat	Excel
ChartObject .Fill —	.Fore-/BackColor ˜ ChartColorFormat

With this object background effects can be set, as with FillFormat (which see). However, ChartFillFormat is specifically for chart objects, while FillFormat is only for Shape and ShapeRange .

	ChartGroup	Excel
Chart .ChartGroups(..) —		.SeriesCollection ˜ Series
Chart .XxxGroups(..) —

Chart groups bring together within a chart several data series with a common chart type. Chart groups are required only for combination charts (combination charts are charts in which two chart types are united, for example, a line chart and a scatter chart or two line charts with different Y -axes). The most important properties are Type and SubType , through which the chart type of the group is determined.

	ChartGroups	Excel
Chart .ChartGroups —		(name or index) ˜ ChartGroup

This enumeration object refers to ChartGroup objects, which are necessary for constructing combination charts (see above). In addition to ChartGroups there exist countless other methods that refer to specific subcollections of ChartGroup objects, for example, AreaGroups , PieGroups , and LineGroups . There is no separate method for constructing combination charts, which arise simply from the separate settings of the Type or SubType property of individual data series.

	ChartObject	Excel
Worksheet .ChartObjects(..) —		.Chart ˜ Chart
		.Interior ˜ Interior

The chart object lies between an embedded Chart object and the worksheet. Above all, it determines the size and position of the chart. Chart objects are allowed, in principle, in chart sheets and dialog sheets as well, though they seldom appear in those contexts. Chart objects can be reproduced with the method Duplicate and with Copy can be copied to the clipboard.

	ChartObjects	Excel
Worksheet .ChartObjects —		(index or name) ˜ Chart

This enumeration object refers to the embedded ChartObject objects of a worksheet, dialog sheet, or chart sheet. See ChartObject .

	Charts	Excel
Workbook .Charts —		(index or name) ˜ Chart

This enumeration object refers to chart sheets. Note that there is no separate object type for chart sheets, for which reason Charts actually refers to the main chart of a chart sheet. The page/chart is activated by the method Select and sent to the printer or represented in page view with PrintOut . To access embedded charts in a worksheet you must use the object ChartObjects .

	ChartTitle	Excel
Chart .ChartTitle —		.Interior ˜ Interior
		.Font ˜ Font

This object describes text, font, position, and appearance of the title of a chart. Whether a chart has a title at all is determined by the Chart property HasTitle . Finally, the appearance of the title can be set with the properties/methods Caption , Interior , Border , and Font .

	CheckBox	MS Forms
UserForm .Controls(..) —

This object represents the check box (yes/no choice) in MS Forms dialogs. The most important property is Value , which contains, according to the setting, True , False , or Null (undetermined).

	CodeModule	VBE
CodePane .CodeModule —
VBComponent .CodeModule —

The CodeModule object enables changes in program code. For this purpose methods such as InsertLines , DeleteLines , AddFromFile are available.

	CodePane[s ]	VBE
VBE .CodePanes —		.CodeModule ˜ CodeModule
		.Window ˜ Window

The enumeration object CodePanes and the derived CodePane objects describe regions of code in the VBA development environment. (Tip: If you wish to edit VBA code you must rely on the CodeModule object.)

	Collection	VBA
		(index or name) ˜ object

This object enables the definition of separate listings (enumeration objects). New objects can be added with Add , while existing objects can be deleted with Remove . The number of saved objects can be determined with Count .

	ColorFormat	Excel
FillFormat .Fore-/BackColor —
LineFormat. Fore-/BackColor —
ShadowFormat .ForeColor —
ThreeDFormat .ExtrusionColor —

With some objects that were introduced in Excel 97 the color is not set directly as an RGB value, but via the detour of a ColorFormat object. The default property of ColorFormat is RGB , and according to the Type setting the color can be set as well with the property SchemeColor . SchemeColor expects index numbers for the valid color palette (whose setting, however, can be neither read nor changed).

	ComAddIn[s ]	Office
Application .ComAddIns(..) —

Application .ComAddins refers to the like-named enumeration of all COM add-ins registered with Excel. ComAddIns refers to the individual ComAddIn objects, whose property Description contains the name of the COM add-in (that is, the text that is displayed in the dialog TOOLSCOM ADD-INS). The property Connect determines whether the add-in is currently active. A change in Connect has the same effect as a change in the corresponding checkbox in the COM ADD-INS dialog.

	ComboBox	MS-Forms
UserForm .Controls(..) —

This control offers a combination of a collapsible listbox with a text field. List(n) enables access to the list. With RowSource the list can be taken from a range of cells in the table. (When the control is used in a worksheet, instead of the above, ListFillRange must be used. LinkedCell then specifies into which cell the result of the selection should be transferred.) Text contains the selected or input text, while Value contains, according to the setting of BoundColumn , the text of the number of the selected list elements.

	Command	ADO
		.ActiveConnection ˜ Connection
		.Parameters( ) ˜ Parameter
		.Execute ˜ Recordset

This object makes it possible to execute SQL commands with parameters and so-called stored procedures (SQL procedures that are managed by the database server). The SQL code of the query or name of the stored procedure is given in CommandText . Then the values of the parameters are set. Finally, the command can be executed with the method Execute . If the command deals with a query, then Execute returns a Recordset object.

	CommandBar	Office
Application .CommandBars(..) —	.Controls ˜ CommandBarControls

This object describes a menu bar or toolbar. More precisely, there are three types (property Type ): normal toolbars ( msoBarTypeNormal ), regular menu bars ( msoBarTypeMenuBar ), and pop-up menus ( msoBarTypePopup ).

	CommandBarButton	Office
CommandBar .Controls(..) —

This object represents a normal entry in a menu or toolbar. Depending on the setting of Style the object is represented as an icon and/or text.

	CommandBarComboBox	Office
CommandBar .Controls(..) —

A menu element can be used either as a text input field or as a listbox (in a toolbar). The type of use is determined by Style ( msoControlDropdown , msoControl- Edit , or msoControlComboBox ). When a listbox is used the list is processed with the methods AddItem , RemoveItem , and Clear . In each case the input text or selected entry can be taken from Text .

	CommandBarControl	Office
CommandBar .Controls(..) —	(index or name) ˜ CommandBarControl

CommandBarControl is an object lying above CommandBarButton , CommandBarComboBox , or CommandBarPopup . Which object type is currently valid can be determined with Type . (Please note that there are some CommandBar types that appear in built-in menus, but currently cannot be used in programming and therefore are represented by their own objects.)

Important properties are Caption (the label text), TooltipText (the yellow infotext if this differs from Caption ), BeginGroup (display a separation line above or to the left), and OnAction (the event procedure to be called).

	CommandBarControls	Office
CommandBar .Controls —	(index or name) ˜ CommandBarControl

This enumeration object leads to the individual entries of a toolbar or menu bar or of a menu or submenu. Formally, with subordinate objects we are dealing with CommandBarControl objects. In fact, you usually receive a CommandBarButton , CommandBarComboBox , or CommandBarPopup object (see CommandControl ). With Add new menu items can be added.

	CommandBarPopup	Office
CommandBar .Controls(..) —	.Controls ˜ CommandBarControls

This object is the key to individual menus in a menu bar or toolbar, to submenus in menus, and so on. Controls refers to the subordinate entries that come into play with a CommandBarButton , CommandBarComboBox , or CommandBarPopup object.

The possibility of placing this object at any level of the hierarchy makes access to the object complicated. Furthermore, this object is not suited for pop-up menus, which must be defined as CommandBar objects with Position=msoBarPopup .

	CommandBars	Office
Application .CommandBars —	(index or name) ˜ CommandBar

This object enumerates all predefined and user-defined toolbars and menu bars.

	CommandButton	MS-Forms
UserForm .Controls(..) —		.Picture ˜ StdPicture

The most important button property is Caption , for the label text. Optionally, a picture can be displayed in the button (properties Picture and PicturePosition ). A click triggers, not surprisingly, a Click event.

If you use a control in a worksheet, you should set TakeFocusOnClick to False . You thereby avoid the input focus remaining in the button when it is clicked (which in VBA code can lead to problems).

	Comment	Excel
Range .Comment —		. Next /.Previous ˜ Comment
Range.AddComment(..) —		.Shape ˜ Shape

This object stores the content and other information as notes (also called comments since Excel 97). With the method Text the comment can be read and edited. The methods Previous and Next refer to additional comments in the worksheet. New comments can be created with the method AddComment of the Range object. Range. ClearComments deletes all comments in a given range of cells.

	Comments	Excel
Worksheet .Comments —		(index or name) ˜ Comment

This enumeration object enables access to all comments within a worksheet without the necessity of examining all the cells.

	Connection	ADO
Recordset .ActiveConnection —		.Execute ˜ Recordset

Before data can be read from a database, access thereto must be established via the Connection object. For this a character string is passed with the method Open that contains all necessary parameters (the desired database driver, the name of the database, the network name of the database server, login name and password, etc.).

	ConnectorFormat	Excel
Shape/ShapeRange .ConnectorFormat — .Begin-/EndConnectedShape ˜ Shape

ConnectorFormat describes the connection between two Shape objects. For example, two AutoForm rectangles can be connected with an AutoForm connection line. In this case the connection is described by the ConnectorFormat object of the Shape object

The most important properties are BeginConnectedShape and EndConnectedShape , which refer to the two objects that are connected to each other. To create or delete the connection the methods BeginConnect/EndConnect and BeginDisconnect/EndDisconnect are available (for the source and goal objects).

Control

MS-Forms

This object makes available common properties, methods, and events for all MS Forms controls. It is seldom used directly (at most in the declaration of variables or parameters).

	ControlFormat	Excel
Shape .ControlFormat —

If a Shape object is used to embed a control (usually from the MS Forms library) into a worksheet, the ControlFormat object has some objects available for the control. The object enables the communication between worksheet and control. Among others, these properties include LinkedCell , ListFillRange , and PrintObject . See also Chapter 7.

	Controls	MS-Forms
Frame .Controls —		(index or name) ˜ control
Page. Controls —
UserForm .Controls —

This enumeration object refers to all controls of a frame field, dialog page, or an entire dialog. The only property is Count . With the methods Add and Remove new controls can be added or deleted via program code.

	Corners	Excel
Chart .Corners —

This object describes the corner points of the rectangular box that surrounds a 3- D chart. The only useful method is Select . This object is of no significance for programming. In manual editing of charts the corners can be selected and the entire chart then turned with the mouse.

	CubeField	Excel
CubeFields ( ) —	.TreeViewControl ˜ TreeViewControl
PivotField .CubeField —

This object describes certain features of pivot fields that hold specifically for OLAP data sources (for example, CubeFieldType=xlHierarchy or xlMeasure ). A host of additional properties have the same meaning as they do with PivotField , which refers to pivot fields of traditional data sources.

	CubeFields	Excel
PivotTable. CubeFields —		(index or name) ˜ CubeField

This enumeration object refers to all OLAP pivot fields and corresponds essentially to PivotFields for pivot fields of traditional data sources.

	CustomProperty/CustomProperties	Excel 2002
Worksheet. CustomProperties —

With the CustomProperties enumeration you can equip a worksheet with additional information. Each CustomProperty object consists of a name (property Name ) and an arbitrary object ( Value ).

However, the purpose of this enumeration, introduced with Excel 2002, is a mystery. The terse information in the Help file is not much help: "This information can be used as metadata for XML." In any case, tests have shown that CustomProperty objects that have been added to a worksheet are not saved when the worksheet is saved as an XML file. The information is retained only if the file is stored in the usual *.xls format.

	CustomView	Excel
Workbook. CustomViews(..) —

Since Excel 97, for one Excel file several settings for the printer and for the display of columns and rows (hide and unhide) can be saved. In interactive mode these settings can be saved or activated with ViewCustom Views.

With the CustomView object one can determine whether printer settings ( PrintSettings ) or column and row settings ( RowColSettings ) are saved for a particular view. What these settings look like can be determined only when the view in question is activated by Show .

	CustomViews	Excel
Workbook .CustomViews —	(index or name) ˜ CustomView

This object enumerates all saved views belonging to a workbook. With the Add method the current settings for the printer or for row and column views are saved as a new view. See also CustomView .

	DataLabel	Excel
Series .DataLabels(..) —		.Interior ˜ Interior
Point .DataLabel —		.Border ˜ Border
Trendline .DataLabel —		.Font ˜ Font

With this object the label of individual data points in a data series of a chart can be set. Before the object DataLabel can be changed the property HasDataLabels of the object Series must be set to True .

The type of label (value, percentage, or separate text) is set with the Type property. Individual texts are specified with Caption or Text . Visual formatting is accomplished via Orientation , Interior , Border , and Font .

In many cases it is easier to use the method ApplyDataLabels than to label each individual data point. With it all points of a Series object or all data series of a Chart object can be labeled uniformly with values, percentages, etc.

	DataLabels	Excel
Series .DataLabels —		(index) ˜ DataLabel

This enumeration object refers to DataLabel objects of a data series. As index the number of the data point must be given. See DataLabel .

DataObject

MS-Forms

This object is passed with OLE Drop events ( BeforeDragOver , BeforeDropOrPaste ) to the corresponding event procedure. It makes possible the evaluation of drag- and-drop operations, for example, the placement of a file from Explorer in an MS Forms dialog. The object can be used to read data from or write it to the clipboard (methods GetFromClipboard and PutInClipboard ).

	DataTable	Excel
Chart .DataTable —		.Border ˜ Border
		.Font ˜ Font

Since Excel 97 source data can be displayed as numerical values within a chart object (usually below the chart itself) (Chart OptionsData Source). In program code the data table can be activated or deleted with Chart . HasDataTable=True/False . The appearance of the data table can be controlled with the properties of DataTable , for example, with HasBorderOutline , ShowLegendKey .

Debug

VBA

With the object Debug we are dealing with a general VBA object. Debug refers to the immediate window in the programming environment. There is only one method for Debug : Print directs output to the immediate window.

	DefaultWebOptions	Excel
Application .DefaultWebOptions —

The properties of this object control the parameters of HTML export by Excel (FileSave AsToolsWeboptions). In spite of the -s on the end this is not an enumeration object. If the web properties are to be set not globally for Excel but individually for one file, then the WebOptions object can be used (access via Workbook .WebOptions ).

	Diagram	Excel 2002
DiagramNode. Diagram —		.Nodes ˜ DiagramNodes
Shape. Diagram —		.Parent ˜ Shape

This object describes a diagram (such as an organization chart or Venn diagram). The diagram type is determined by the property Type . The components of the diagram are managed by DiagramNode objects, which are addressed via the Nodes property.

Diagrams as well as their components are represented internally by Shape objects. With a Shape object the property HasDiagram can be used to determine whether a Diagram object is located within it.

	DiagramNode	Excel 2002
Diagram. DiagramNode(..) —	.Children ˜ DiagramNodesChildren
DiagramNode. AddNode —		.TextShape ˜ Shape
DiagramNodeChildren. AddNode

This object describes a part of a diagram, that is, for example, a box in an organization chart or a circle in a radial diagram. If the diagram is one that be represented hierarchically, then Children can be used to address subordinate elements.

	DiagramNodeChildren	Excel 2002
DiagramNode. Children —		(index) ˜ DiagramNode

This enumeration collects several DiagramNode objects at a given hierarchical level. The properties FirstChild and LastChild can be used to address the first and last elements.

	DiagramNodes	Excel 2002
Application. Dialogs(..) —		(index) ˜ DiagramNode

This enumeration, too, helps in addressing DiagramNode objects. In contrast to DiagramNodeChildren , the properties FirstChildren and LastChildren are not available. Instead, there is the method AddNode . (If the concept had been thought out more carefully , one could have easily done without one or the other of these two enumerations.)

	Dialog	Excel
Application .Dialogs(..) —

This object serves for the internal management of predefined Excel dialogs. These dialogs can be displayed with the Show method. Custom dialogs are managed with the DialogSheet object.

	Dialogs	Excel
Application .Dialogs —		(index) ˜ Dialog

This enumeration object contains a list of all predefined Excel dialogs. Selection proceeds by specifying the index in an xlDialogNamexxx constant.

	Dictionary	Scripting
		(index or name) ˜ object

This object corresponds essentially to the VBA object Collection . However, it is somewhat more versatile. It enables the definition of individual listings (enumeration objects). New objects can be added with Add , while existing objects can be deleted with Remove . The number of saved objects is determined with Count .

	DisplayUnitLabel	Excel
Axis .DisplayUnitLabel —		.Font ˜ Font

This object describes text, style, position, and appearance of the label of the scaling unit of a coordinate axis of a chart. Whether this axis is scaled at all is determined by the Axis property HasTitle . If one of the predefined factors is used (for example, Axis .DisplayUnit = xlMillions ), then Axis .DisplayUnitLabel.Text automatically contains a suitable label character string (for example, "Millions" ). On the other hand, if some other arbitrary factor is used as scaling factor (property DisplayUnitCustom ), then DisplayUnitLabel.Text is to be set accordingly .

	DocumentProperties	Office
Workbook. BuiltinDocumentProperties — (name or index) ˜ DocumentProperty
Workbook . CustomDocumentProperties —
Binder :XxxDocumentProperties —

This enumeration object contains a list of all properties of the specified Excel file or binder. These properties serve to identify and search for documents, and they were new in the Office 95 package. Among the twenty-eight properties for Excel workbooks are "Title" , "Subject" , "Author" , "Last Author" , "Revision Number" . Additionally, with the Add method custom properties may be defined. In interactive mode the document properties can be set or newly defined via FileProperties.

	DocumentProperty	Office
Workbook. BuiltinDocumentProperties(..) —
Binder . CustomDocumentProperties(..) —

The DocumentProperty object describes a property of an Excel file or binder. Here Name specifies the property name, Type the type, and Value the current setting. With the properties LinkSource and LinkToContent the value of a custom (userdefined) property can be linked directly with the contents of a worksheet. For this LinkToContent must be set to True and LinkSource allocated to a named range of cells.

	Drive	Scripting
Drives ( ) —		RootFolder ˜ Folder

This object describes a hard drive, a floppy drive, a CD-ROM drive, etc. DriveType specifies the type of drive, TotalSize the total capacity, FreeSpace the amount of available space. RootFolder refers to the root directory (through which the files and all other directories can be addressed).

	Drives	Scripting
FileScriptingObject .Drives —		(name or index) ˜ Drive

This enumeration object refers to all drives on the computer.

	DropLines	Excel
ChartGroup .DropLines —		.Border ˜ Border

With line charts and area charts vertical drop lines can be applied to the individual data points. For this the ChartGroup property HasDropLines must be set to True .

The drop lines stretch from the X -axis to the data point. With the object DropLines (or its subobject Border ) the appearance of the drop line can be set.

ErrObject

VBA

This object contains information about the most recent error and can be evaluated, for example, in an error-handling routine. The two most important properties are Number , with the error number, and Description , with a brief description. This object is seldom used, because the same information is available from the previously available functions Err and Error.

	Error/Errors	ADO
Connection .Errors —

This enumeration object refers to the error that occurred during the previous ADO database operation. (During the execution of a single command several errors can occur that are reported by different database libraries or the database system itself.) The usually cryptic error text is to be found in Description . Furthermore, Number and NativeNumber contain the internal provider error number as well as the ADO error number.

	Error/Errors	Excel 2002
Range. Errors —

This enumeration refers to possible errors within a cell, to the extent possible by automated error-checking. For example, it could be dealing with numbers that have been mistakenly stored as character strings or dates with only a two-digit year. The extent of error-checking is set by the ErrorCheckingOptions object.

Please note that the property Range.Errors can be used only on individual cells, not on a range of cells. The behavior of the Errors enumeration is also atypical and counterintuitive: There is neither Count , for determining the number of errors within a cell, nor the possibility of constructing a For “Each loop. Instead, as index, one of the seven XlErrorChecks constants must be used, as in the example below. If the property Value of the thus addressed Error object contains True , then there is an error present. (Why the Error[s] objects have been conceived in such a complex manner is a mystery known only to Microsoft.)

 Dim c As Range Set c = [a2] If c.Errors(xlEmptyCellReferences).Value = True Then   MsgBox "formula refers to empty cells" End If If c.Errors(xlInconsistentFormula).Value = True Then   MsgBox "formula is inconsistent" End If 

	ErrorBars	Excel
Series .ErrorBars —		.Border ˜ Border

Error bars are small vertical or horizontal lines associated with each data point of a two-dimensional chart that indicate the range of possible error of the data point. Error bars are normally generated with the like-named method of the Series object. With the Border subobject the visual display of the error bars can be set. Setting the Series property HasErrorBars to False removes the error bars.

	ErrorCheckingOptions	Excel 2002
Application. ErrorCheckingOptions —

The various properties of this object (e.g., NumberAsText and TextDate ) determine the extent of automatic error-checking (ToolsError Checking).

	Field	ADO
Recordset !name —
Fields ("name") —

This object enables access to a single field of the currently active data record of a Recordset object. The property Value receives the contents of the field. Name , Type , Attributes , etc., provide additional information about the type of the field. Recordset !name is the usual shorthand for Recordsets. Fields("name").Value .

	Fields	ADO
Recordset. Fields —		(index or name) ˜ Field

This enumeration object refers to all data fields of a Recordset object.

	File	Scripting
Files ( ) —		.Drive ˜ Drive
		.OpenAsStream ˜ TextStream

This object describes a file on the hard drive (or on another drive). Important properties are Name (the file name), Path (combination of drive, directory, and file name), Size (file size), Attributes (for example, write protected). Files can be copied with Copy , moved with Move , and deleted with, you guessed it, Delete . Additionally, text files can be opened as a TextStream object.

	FileDialog	Office 2002
Application. FileDialog(..) —		.Filters ˜ FileDialogFilters
	.SelectedItems ˜ FileDialogSelectedItems

This object enables the selection of a file or directory. With the Show method an appropriate dialog (File Open, Save As, etc.) is shown. Before that, various properties of the dialog can be set, for example, the window title ( Title ), title for the OK button ( ButtonName ), or the file types to be displayed in the dialog ( Filters ). When the dialog is closed with OK, Show returns True as result. The selected names may now be determined via the SelectedItems property.

	FileDialogFilter	Office 2002
FileDialog. Filters(n) —
FileDialogFilters. Add —

This object describes a file type (e.g., *.txt ) for file selection with the FileDialog object. The file type is described via the properties Extension (e.g., " txt ") and Description (e.g., " Textfile ").

	FileDialogFilters	Office 2002
FileDialog. Filters —		(index) ˜ FileDialogFilter
		.Add ˜ FileDialogFilter

This enumeration refers to all FileDialogFilter objects of a file selection dialog. In the dialogs msoFileDialogOpen and msoFileDialogSaveAs a number of filters are already defined, which cannot be altered. If you wish to define your own filters, you must use the dialog msoFileDialogFilePicker . You can then add new filters: fd.Filters.Add("Textfiles", "*.txt") .

	FileDialogSelectedItems	Office 2002
FileDialog. SelectedItems —

This object contains all selected files and directories as character strings. The number can be determined with Count , and access to the individual character strings is accomplished via (index) , where the first element is addressed with index=1 .

	Files	Scripting
Folder .Files —		.Drives ˜ Drive

This enumeration object refers to all files within a directory.

	FileSystemObject	Scripting
		.Drives ˜ Drive
		.GetSpecialFolder ˜ Folder

This object forms the foundation of the File Scripting Objects (FSO for short), which enable object-oriented access to directories and files. Lower-ranked objects are Drive[s] , Folder[s] , and File[s] .

	FillFormat	Excel
Shape/ShapeRange .Fill —	.Fore-/BackColor ˜ ColorFormat

With this object it is possible to set background effects for various drawing objects ( Shape ). For color transitions two colors may be specified with ForeColor and BackColor ; the color transition is then controlled via the properties GradientDegree and GradientStyle . Additionally, a texture (background bitmap) can be associated to the object with the properties TextureName and TextureType . For chart objects one has available the related object ChartFillFormat .

	Filter	Excel
AutoFilter .Filters —		(index or name) ˜ Filter

The three properties Operator , Criteria1 , Criteria2 of the Filter object describe the filter criterion for a column of an AutoFilter object. On tells whether the filter is active.

	Filters	Excel
AutoFilter .Filters —		(index or name) ˜ Filter

This enumeration object refers to all filters of an autofilter (one for each column of the database).

	Floor	Excel
Chart .Floor —		.Interior ˜ Interior
		.Border ˜ Border

Floor describes the floor of three-dimensional charts. The visual appearance is determined via the two subobjects Interior and Border . The method ClearFormats restores the standard formatting of the floor. See also Walls for the side walls of a 3-D chart.

	Folder	Scripting
Folders ( ) —		.Files ˜ Files
FileSystemObject .GetSpecialFolder —		.SubFolders ˜ Folders

This object describes a directory on the hard drive (or another drive). Important properties are Name and Path (combination of drive and directory names), Size (size of all files contained therein), Attributes (for example, write protected).

	Folders	Scripting
Folder .SubFolders —		.Drives ˜ Drive

This enumeration object refers to all subdirectories of a directory.

	Font	Excel
Range .Font —
Characters .Font —
ChartObject .Font —

The Font object serves to set the font of a range of cells, of individual characters, and various chart objects and controls. The most important properties are Name (for the font name), Size , Bold , Italic , Underline , Subscript , Superscript , Color , and Background .

	FormatCondition	Excel
Range .FormatConditions(..) —		.Borders(..) ˜ Border
		.Font ˜ Font
		.Interior ˜ Interior

The formatting of a cell or range of cells can be made dependent on at most three conditions (FORMATCONDITIONAL FORMATTING). In this way, for example, all values that exceed a given value can be set in boldface.

The properties Formula1 and Formula2 as well as Operator describe the conditions Borders , Font , and Interior of the resulting formatting. With Delete the conditional formatting can be cleared.

	FormatConditions	Excel
Range .FormatConditions —	(index or name) ˜ FormatCondition

This enumeration object refers to the (at most three) conditional formats of a cell or range of cells. With Add a new format can be added, while Delete clears the conditional format.

	Frame	MS-Forms
UserForm .Controls(..) —		.Controls(..) ˜ control

The frame field provides visual separation in MS Forms dialogs. Controls contained within the frame are moved together with their container field and with Zoom can be enlarged or shrunk independently of the rest of the dialog. Controls provides access to the controls.

	FreeFormBuilder	Excel
Shapes .BuildFreeForm(..) —

Free forms are composed of an arbitrary number of lines or curves and are normally drawn in interactive mode. If you wish to generate free-form objects in code, then you must use the method BuildFreeForm . This method returns a FreeFormBuilder object, which can be extended with the method AddNodes . Finally, the method ConvertToShape changes the object into a Shape object.

	Gridlines	Excel
Axis .MajorGridlines —		.Border ˜ Border
Axis .MinorGridlines —

This object describes the gridlines in the background of a chart. Gridlines are associated to a coordinate axis, that is, horizontal lines to the Y -axis and vertical lines to the X -axis. Whether and which gridlines are displayed is determined by the two Axis properties HasMajorGridlines and HasMinorGridlines . The distance between the principal lines is determined by the Axis property MajorUnit . Auxiliary lines are drawn between these, at a displacement of MinorUnit . The visual appearance of gridlines (color and line type) is set via the subobject Border .

The object Gridlines has nothing to do with the gridlines in worksheets. Whether such a grid is displayed is determined by the Window properties Display- Gridlines and GridlineColor .

	GroupShapes	Excel
Shape/ShapeRange .GroupItems —		(index or name) ˜ Shapes

When several objects are collected into a group through the pop-up menu command Grouping, Excel generates a new Shape object, to which the previously independent objects are subordinated in the hierarchy. Access to the individual objects is via the GroupShapes enumeration, whose two most important properties are Count and Item . See also Shape .

	HiLoLines	Excel
ChartGroup .HiLoLines —		.Border ˜ Border

This object describes the appearance of HiLo lines in a line chart. These are vertical lines that connect the smallest and largest data values from several data series. Whether these lines are displayed is determined by the ChartGroup property HasHiLoLines . The appearance of the lines is set by the subobject Border .

	HPageBreak	Excel
Worksheet .HPageBreaks(..) —		.Location ˜ Range

This object indicates a horizontal page break in a worksheet.

	HPageBreaks	Excel
Worksheet .HPageBreaks —		( index or name ) ˜ HPageBreak

This enumeration object enables access to all manual page breaks in a worksheet.

	HyperLink	Excel
object .property —		.Range ˜ Range
Shape.HyperLink —		.Shape ˜ Shape

This object describes a cross reference to a document. This can refer to a particular location in the active file, another local file, or a document on the Internet. The address or file name is specified in Address , while the exact position in the document (a cell address, for example) is in SubAddress .

	HyperLinks	Excel
Chart/Worksheet .Hyperlinks —		( index or name ) ˜ HyperLink
Range. Hyperlinks —

This enumeration object enables access to all cross references and Internet links of a sheet or range of cells.

	Image	MS-Forms
UserForm .Controls(..) —		.Picture ˜ StdPicture

The property Image serves to display bitmaps in dialogs. The property Picture refers to a StdPicture object of the StdOLE library. The image can be loaded from a file by means of the function LoadPicture . Various properties of Image offer formatting options (clipping, adjusting the image to the size of the control, and so on).

	Interior	Excel
Range .Interior —
ChartObject .Interior —

This object describes the color and pattern of the interior (that is, the background) of numerous objects. Almost all chart objects, format templates, and such like refer to the Interior object. The most important properties are Color , Pattern , and PatternColor . Colors are set by allocating an RGB value. Alternatively, the properties Colors and PatternColorIndex can be used, to which are allocated a color index between 1 and 56 or the constant xlNone or xlAutomatic .

	Label	MS-Forms
UserForm .Controls(..) —		.Font ˜ NewFont

The label field serves to label other controls is MS Forms dialogs. The text is set with Caption .

	LeaderLines	Excel
Series .LeaderLines —		.Border ˜ Border

This object enables, in certain chart types, the formatting of lines that are drawn between chart elements and label text (Dialog FORMAT DATA POINTDATA LABELSSHOW LEADER LINES). The lines are displayed only if the property HasLeaderLines of Series is set to True .

	Legend	Excel
Chart .Legend —		LegendEntries ˜ LegendEntry
		Border ˜ Border

The Legend object describes the legend of a chart. (The legend is a rectangular box in which the lines, colors, or pattern of the chart is explained and labeled.

With this object's properties position, size, and format are determined ( Left , Top , Width , Height , Interior , Border , Font , Shadow ). Whether a chart has a legend at all is determined by the Chart property HasLegend . The actual details of the legend, that is, the pattern and labeling, are set by the subobject LegendEntry .

	LegendEntries	Excel
Legend .LegendEntries —		( index ) ˜ LegendEntry

This enumeration object refers to the entries in the legend of a chart. There is no way of increasing the number of entries with Add ; every data series and trend line is associated to a fixed legend entry.

	LegendEntry	Excel
Legend .LegendEntries(..) —		.Font ˜ Font
		.LegendKey ˜ LegendKey

This object describes a single entry within the legend of a chart. This object has relatively few of its own properties and methods: Font for the label text, LegendKey for the visual appearance of the data series (see further below), and Delete to delete the entire entry. (However, the entry cannot be recreated or reinserted.

You must delete the entire legend with False and with =True create it anew, this time with all legend entries.)

The label text of the legend entry is determined by the Name property of the Series object. The ordering of the legend entries within the legend is handled automatically by Excel (depending on the amount of space available).

	LegendKey —	Excel
LegendEntry .LegendKey		.Border ˜ Border

This object determines the visual display of a data series within the legend (see Legend object). A change in formatting changes the formatting of the associated data series in the chart (and conversely). The most important properties of this object are MarkerStyle , MarkerBackgroundStyle , and MarkerForegroundColor as well as Border .

	LineFormat	Excel
Shape/ShapeRange .Line —		. Fore-/Backcolor ˜ ColorFormat

This object describes the appearance of lines and arrows that are displayed by AutoForm objects (see Shape ). Among the most important properties are ForeColor and BackColor , DashStyle , and Weight . Arrow properties are set by BeginArrowheadLength , BeginArrowheadStyle , BeginArrowheadWidth , and EndArrowheadLength , EndArrowheadStyle , EndArrowheadWidth .

	LinkFormat	Excel
Shape .LinkFormat —

This object contains essentially the property AutoUpdate , which for linked OLE objects specifies whether the object is to be updated automatically after a change in the source data. The method Update carries out this updating manually.

	ListBox	MS-Forms
UserForm .Controls(..) —

ListBox enables a convenient selection of one or more entries from a list. Access to list elements is via List . With RowSource the list can be taken from a range of cells in a worksheet. (If a control is used in a worksheet, then instead of the above, ListFillRange must be used. LinkedCell specifies into which cell the result of the selection is to be transferred.) Text contains the selected or input text, while Value holds, depending on the setting of BoundColumn , likewise the text or else the number of the selected list element.

	Mailer	Excel
Workbook .Mailer —

This object controls the transfer of a workbook over a network. It is of interest only if you work with an Apple Macintosh computer with the network extension PowerTalk. For a direct mail sending, set the properties of Mailer and start the distribution with SendMailer .

	MultiPage	MS-Forms
UserForm .Controls(..) —		.Pages(..) ˜ Page

This object serves for the management of multipage dialogs. Each dialog sheet is represented by its own Page object (access via Pages(n) ). Value specifies the page of the dialog that is currently visible.

	Name	Excel
Workbook .Names(..) —

This object normally describes a named range of cells. This range can be used in worksheet formulas as well as in VBA code by specifying its name (for example, [profit] instead of [F17] ). For reasons of compatibility with Excel 4, Name objects can also refer to traditional macros, which is reflected in numerous extraneous properties.

The most important properties are Name (with the name of the Name object) and RefersTo (with a formula that contains the cell reference, for example, "=Table1!$A$1" ).

	Names	Excel
Workbook .Names —		( index or name ) ˜ Name

This enumeration object refers to all defined names in a workbook. As a rule, names are used for denoting cell ranges (command InsertNameDefine). In program code ranges can be named simply by changing the Name property of the range.

	NewFont	MS-Forms
ControlElement .Font —

This object serves the internal representation of fonts ( Font property of many controls). The same function is also fulfilled by the object StdFont of the StdOLE library.

	ODBCError	Excel
Application .ODBCErrors(n) —

This object contains in the property ErrorString an error message for the most recent ODBC error. Such errors can occur during access to a database server. You may, perhaps, suppose that this object belongs rather in the ADO library for database programming. However, ODBC errors can also occur directly in Excel, such as when access is made to external data in a pivot table or QueryTable object.

	ODBCErrors	Excel
Application .ODBCErrors —		( index ) ˜ ODBCError

This object lists all errors that have occurred in the most recent ODBC access. (Data access via ODBC is processed by a whole conglomerate of function libraries. At each level ”the lowest represents the database server itself ”errors can occur. For this reason, it is possible that an ODBC access returns several error messages.) Whether errors have occurred can be determined via the Count property.

	OLEDBError[s]	Excel
Application .OLEDBErrors(n) —

These two objects correspond to ODBCError[s] , but hold for errors that were caused by OLE DB libraries (for example, during access to an OLAP cube via a pivot table).

	OLEFormat	Excel
Shape .OLEFormat —		.Object ˜ oleprogram

This object is a smaller variant of OLEObject ; that is, it contains only a subset of the properties of OLEObject . This object serves especially for processing OLE objects that are embedded in an Excel worksheet as Shape objects.

	OLEObject	Excel
Worksheet .OLEObjects(..) —		.Object ˜ oleprogram

OLE objects are objects of other Windows programs that are embedded within Excel and are displayed there (for example, Corel Draw drawings). OLE objects can be contained in custom dialogs, charts, and worksheets.

OLEObject describes certain external properties of the object that can be changed by Excel (without calling the OLE program), such as position, size, border, and shadow. The property OLEType specifies how the object is attached to Excel: as an embedded, freestanding object or as a linked object from another file. Object refers to the OLE program and enables object automation applications.

The method Activate invokes the subordinate OLE program. Update brings the data to its latest version (this normally occurs automatically at regular intervals). With Verb predefined commands (very few) can be passed to the OLE program.

	OLEObjects	Excel
Worksheet .OLEObjects —		( index or name ) ˜ OLEObject

This enumeration object refers to all object groups in a custom dialog, chart, or worksheet. See OLEObject .

	OptionButton	MS-Forms
UserForm .Controls —

The option button enables a convenient selection of one option from among several. Each option is represented by its own option button. Related option buttons must be identified by a unique GroupName (required only if several option groups appear in the same dialog). For evaluation, the Value property of all option buttons must be tested .

	Outline	Excel
Worksheet .Outline —

The object Outline serves the internal representation of a hierarchical grouping of a table. (Such groupings are formed via DataSubtotals or DataGroup And Out Line.) The method ShowLevels specifies how many row or column levels are to be displayed. The properties SummaryColumn and SummaryRow specify whether result cells are to be located to the right (respectively beneath) the data (default setting) or to the left (respectively above).

The construction of an outline and changes thereto take place via the methods and properties of the Range object ( AutoOutline, ClearOutline, Group , Ungroup ). OutlineLevel specifies or changes the outline level of an individual row or column.

	Page	MS-Forms
MultiPage .Pages(..) —		.Controls(..) ˜ control

This object represents a page of a multipage dialog ( MultiPage ). The controls contained therein are addressed via Controls . The page is labeled with Caption .

	Pages	MS-Forms
MultiPage .Pages —		( index or name ) ˜ Page

This object lists all pages of a multipage dialog.

	PageSetup	Excel
Chart .PageSetup —
Worksheet .PageSetup —

This object describes all printer-specific data for the page setup. It is available for all sheet types as well as for the Window object. The printer setting must be carried out for each sheet individually; the data are not valid for the entire workbook.

Headers and footers are set with the six properties Left -, Center -, and RightHeader and - Footer . Left- , Right -, Top -, and BottomMargin determine the dimensions of the side margins. Orientation determines whether printing is to be in landscape or portrait format. Zoom defines a general scaling factor for printing (10 to 400 percent).

For worksheets PrintArea specifies the cell range to be printed. PrintTitleColumns and PrintTitleLine determine the rows/columns that are to printed on every page. In the case of charts ChartSize determines how the available page area is to be used.

The actual printing is initiated by the method PrintOut , which is available to various objects ( Range , all three page types, Workbook ). In worksheets horizontal and vertical page breaks can be inserted with H/VPageBreaks .

	Pane	Excel
Window .Panes(..) —		.VisibleRange ˜ Range
Window .ActivePane —

The object Pane describes one of at most four panes, which arise through the division (and freezing) of a window. The method Activate selects the currently active pane. The two properties ScrollColumn and ScrollRow specify the number of the first visible row/column in the pane, or else change it. VisibleRange refers to the visible range of cells in the pane.

	Panes	Excel
Window .Panes —		( index ) ˜ Pane

The enumeration object Panes refers to all the panes of a window (see Pane ). If the window is not divided, then the property Count contains the value 1. Undivided windows can be divided at the current cursor position by changing the Window properties Split and FreezePanes .

	Parameter	ADO
Command !name —
Parameters( ) —

This object describes a parameter of a database command. Name and Type specify the parameter name and its data type. Direction determines whether the parameter is an input or output parameter. Value contains the value of the parameter.

	Parameter	Excel
QueryTable .Parameters(..) —		.SourceRange ˜ Range

If an SQL query with parameters is used in a QueryTable object (? in the SQL text), then these parameters can be set with the SetParam method of the Parameter object. Another way of proceeding is to read the parameter via SourceRange from a table field. Finally, to update the data the Refresh method of QueryTable must always be executed.

	Parameters	ADO
Command .Parameters —		( index or name ) ˜ Parameter

This enumeration object refers to all parameters of an SQL command that is addressed via an ADO Command object.

	Parameters	Excel
QueryTable .Parameters —		( index or name ) ˜ Parameter

This enumeration object lists all parameters of an SQL query of a QueryTable object. (A parameter results from each "?" character in the SQL command.)

	Phonetic [s]	Excel
Range .Phonetics —

The Phonetics enumeration object refers to Phonetic objects. These objects contain phonetic information about cells that contain contents in Asian languages.

	Picture	Excel 2002
PageSetup. Right-/Center-/LeftFooterPicture —
PageSetup. Right-/Center-/LeftHeader —

This object describes a graphic that is to be printed in the header or footer of a file. For this to happen, one needs to specify at least the file name ( FileName ) and the desired size of the image. The units of Height and Width are not documented. Apparently, it is points (about 1/72 of an inch). In any case, Width=100 yields a graphic that prints out at about 1.38 inches wide. Moreover, in the Footer or Header property of the PageSetup object, the abbreviation &G must be added. It gives the position of the graphic within the header or footer.

 ActiveSheet.PageSetup.LeftFooterPicture.Filename = "C:\test.bmp" ActiveSheet.PageSetup.LeftFooterPicture.Height = 20 ActiveSheet.PageSetup.LeftFooterPicture.Width = 20 ActiveSheet.PageSetup.LeftFooter = "&G" 

	PictureFormat	Excel
Shape/ShapeRange .PictureFormat —

This object describes features of pictures that are represented in Shape objects ( Type=mso[Linked]Picture or msoXxxOLEObject ). Among the most important properties are Brightness and Contrast . With CropLeft , -Bottom , -Top , and -Right the visible segment of the picture can be set.

Note that the bitmap files files should be inserted with Shapes.AddPicture. Instead of this, the macro recorder chooses the no longer supported Pictures enumeration.

	PivotCache	Excel
Workbook .PivotCaches(..) —
PivotTable .PivotCache —

With the PivotCache object the underlying data for a pivot table as well as (as with QueryTable ) the linkage information are saved. This object plays an especially important role when the basis data are not from an Excel table, but from an external database. (If an Excel file becomes unexpectedly large, printing MemoryUsed for all PivotCache objects often provides a conclusive solution.)

	PivotCaches	Excel
Workbook .PivotCaches —	(index or name) ˜ PivotCache

This enumeration object refers to the PivotCache objects in an Excel workbook.

	PivotCell	Excel 2002
Range. PivotCell —	.Column-/RowItems ˜ PivotItemList
	.Data-/PivotField ˜ PivotField
	.PivotTable ˜ PivotTable

This object describes a single cell of a pivot table. The function of this object consists in creating a connection between a single table cell ( Range ) and a pivot table. Of paramount interest is the property PivotCellType , which gives the type of the cell (e.g., xlPivotCellValue , xlPivotCellSubtotal ). The properties PivotTable , ColumnItems , RowItems , DataField , and Pivotfield refer to various components of the pivot table in which the field is located.

	PivotField	Excel
PivotTable .PivotFields —	.PivotItems ˜ PivotItem

Pivot fields are the structural fields of pivot tables. These are the fields that you move from the pivot table toolbar into the regions "row field," "page field," "column field," and "data field." The properties of pivot fields control the actual content of the pivot table. With the methods Pivot- , Hidden- , Visible -, Parent -, and ChildItems individual pivot elements of a pivot field can be accessed.

	PivotFields	Excel
PivotTable .PivotFields —	(index or name) ˜ PivotField

This enumeration object refers to the PivotField object of a pivot table. This enumeration encompasses only the pivot fields that are directly created from the underlying data. Not considered here are, for example, calculated fields (see CalculatedFields ) and data fields ( PivotTable. DataFields and PivotTable. VisibleFields ) whose composite name differs from the original name (such as "Sum - quantity" instead of "quantity" ).

	PivotFormula	Excel
PivotTable .PivotFormulas(..) —

The PivotFormula object describes a formula field in a pivot table. (In interactive mode formula fields are created with the Formulas submenu.)

	PivotFormulas	Excel
PivotTable .PivotFormulas —		(index) ˜ PivotFormula

This enumeration object lists all formula fields of a pivot table. (Most pivot tables do not have such fields.)

	PivotItem	Excel
PivotField .PivotItems(..) —		.ChildItems ˜ PivotItems

Pivot items contain the groups into which a pivot field is divided. (For a list of articles in which price categories I, II, and III appear, for the pivot field "price" there exist the pivot elements "I," "II," and "III.") Pivot items have hardly any of their own properties that change the structure and content of the pivot table. Only ShowDetail changes the display of subordinate details.

	PivotItemList	Excel 2002
PivotField. PivotItems(..) —		(index or name) ˜ PivotItem

This enumeration object is a variant of PivotItems . The only difference is that there is no Add method.

	PivotItems	Excel
PivotField .PivotItems(..) —		(index or name) ˜ PivotItem

This enumeration object refers to the PivotItem objects of a pivot field. See PivotItem .

	PivotLayout	Excel
Chart .PivotLayout —		.PivotFields ˜ PivotField
		.PivotTable ˜ PivotTable

The object PivotLayout newly introduced in Excel 2000 creates the linkage between a pivot chart ( Chart ) and the associated pivot table. Furthermore, all properties of PivotTable that determine the layout of a pivot table can be accessed directly via PivotLayout . Chart .PivotLayout.PivotFields is a shorthand notation for Chart. PivotLayout.PivotTable.PivotFields . If a chart is not linked to a pivot table (that is, if it is a run-of-the-mill chart), then Chart. PivotLayout contains the value Nothing .

Please note that the object comparison Chart .PivotLayout.PivotTable Is PivotTable can return False under some circumstances, even in dealing with the same pivot table. If you must carry out such a comparison, then compare the properties .Worksheet.Name and .Address of PivotTable.TableRange1 .

	PivotTable	Excel
Worksheet .PivotTables —		.PivotFields ˜ PivotField

Pivot tables are the most important tool that Excel has to offer for the analysis of tabular data (lists). In pivot tables the table entries are grouped according to various criteria and represented as a grid. PivotTable serves the management of pivot tables. The property SourceData refers to the source data of the table. Numerous properties such as DataBodyRange , RowRange , and ColumnRange refer to the ranges in which the results of the pivot table are displayed.

With the method AddFields a pivot table can be enlarged. RefreshTable updates the table. ShowPages creates for a selected page field one or more detailed pivot tables (in new worksheets).

The methods PivotFields , HiddenFields , DataFields , PageFields , ColumnFields , and RowFields refer to the selected PivotField object.

	PivotTables	Excel
Worksheet .PivotTables —		(index or name) ˜ PivotTable

This enumeration object refers to all pivot tables of a worksheet. There is no Add method — new pivot tables are generated with the method PivotTableWizard . See PivotTable .

	PlotArea	Excel
Chart .PlotArea —		.Interior ˜ Interior
		.Border ˜ Border

This object describes the background of the plot area of a chart. The plot area is the region of the chart in which the actual graphic is displayed. This object stands in contrast to ChartArea , through which the entire area of the chart (including the title, legend, etc.) is described. The layout of the plot area is accomplished essentially by the subobjects Interior (color and pattern) and Border (border). See also Floor and Walls for the border surfaces of three-dimensional charts.

	Point	Excel
Series .Points(..) —		.Border ˜ Border
		.DataLabel ˜ DataLabel

The Point object describes a single data point of a chart. Data points are collected within a chart into data series, that is, into groups of associated data. With the Point object the data point itself, the line segment from the previous data point to this data point, and the label of the data point can be visually formatted. For this one has the properties DataLabel , MarkerStyle , MarkerBackgroundColor , and MarkerForegroundColor as well as Border .

	Points	Excel
Series .Points —		(index) ˜ Point

This enumeration object refers to the data points of a data series. As index the number of the data point must be specified (that is, 5 for the fifth data point, for example). See Point .

	Properties/Property	ADO
Connection .Properties —
Recordset .Properties —

This enumeration object lists dynamic properties of various ADO objects ( Connection , Command , Field , Parameter , Recordset ). Which properties an object has depends above all on the database driver ( Provider setting in Connection. ConnectionString) . The most important Property properties are Name , Type , Attributes , and Value .

	Properties/Property	VBE
VBComponent .Properties —

This enumeration object lists all properties ( Name ) and their settings ( Value ) of a VBA component.

	Protection	Excel 2002
Worksheet. Protection —	.AllowEditRanges ˜ AllowEditRanges

This object uses the properties AllowFormattingCells , AllowSorting , etc., to provide information about sheet protection options. These AllowXxx properties can be read, but not changed. Changes must be accomplished with the Protect method.

	PublishObject	Excel
Chart/Range .CreatePublisher —
PublishObjects(..) —

This object manages the settings of an HTML exportation operation for Excel data (for example, for certain table cells, an entire worksheet, or a chart). The exportation can be repeated at any time with the method Publish . Important export properties are FileName (the file name or HTML address), HTMLType (type of HTML file, for example, xlHtmlStatic ), as well as Sheet and Source for describing the data source.

	PublishObjects	Excel
Workbook .PublishObjects —	(index or name) ˜ PublishObject

This enumeration object refers to all components of the file that can be exported in HTML format.

	QueryTable	Excel
Range .QueryTable —
Worksheet .QueryTables(..) —

This object saves all relevant data for carrying out data importation from a text file, a database, an OLAP cube, or a web site. The property QueryType specifies the type of data source. If all further properties are correctly set with respect to the query source, then the importation can be carried out or repeated with Refresh (for updating the data).

If the importation is carried out with MS Query (command DataGet External DataNew Query), then one has QueryType=xlODBCQuery . In this case the property Connection contains the access information to the database file or database server. CommandText contains the query code.

For text importation one has QueryType=xlTextImport . The property Connection in this case contains the character string "Text;" followed by the complete name of the file to be imported. The various parameters of the importation are determined by a host of TextFileXxx properties.

	QueryTables	Excel
Worksheet .QueryTable —		(index or name) ˜ QueryTable

This enumeration object refers to all external data sources in an Excel file.

	Range	Excel
Worksheet .Range(..) —		.Cells ˜ Range
Worksheet .Cells(..) —		.Areas ˜ Range
Application .ActiveCell —		.Font ˜ Font
Application .Selection —		.Border ˜ Border

The object Range can comprise a single cell, an entire range of cells (under certain conditions composed of several partial ranges), or entire rows or columns of a table. There are countless properties and methods that refer to ranges from a wide variety of objects. Conversely, beginning with a Range object almost any number of other (partial) ranges can be addressed (for example, via properties and methods such as CurrentRegion , SpecialCells , End , Dependents , EntireColumn , EntireRow , Precedents ).

Ranges composed of several subranges must in general be processed by evaluation of Areas . In this case the Range object returns the first subrange .

The character string for describing a range of cells can be determined with Address . (When the cells A1:B4 are selected, then Selection.Address returns the character string "$A$1:$B$4".) The desired address format (absolute, relative, A1 or Z1S1, or external) can be selected via several parameters.

The contents of cells can be read or edited with the properties Val and Formula . In the case of ranges with several cells, upon reading, only the contents of the first cell are returned, while writing changes all the cells. The Text property of a cell can only be read; it cannot be altered.

The most important properties and methods for formatting cells are Font , Border , Interior (for the background color), Orientation (of text: horizontal, vertical), HorizontalAlignment (left, centered, right, justified), VerticalAlignment (above, middle, below), NumberFormat , Style , ColumnWidth , RowHeight .

For moving the cell pointer or changing the selected range one has the two methods Select and Offset .

	RecentFile	Excel
Application .RecentFiles(..) —

This object specifies the file name (properties Name and Path ) of a recently used Excel file. With the method Open this file can again be opened.

	RecentFiles	Exc el
Application .RecentFiles —		(index) ˜ RecentFile

This enumeration object lists the most recently opened Excel files.

	Recordset	ADO
Command .Open —	.ActiveConnection ˜ Connection
		!name ˜ Field

This object enables the processing of lists of data records that result from an SQL query. The object thus has central importance in ADO database applications, since it makes possible both reading and editing of data.

The data record list is created with the method Open . The three properties CursorLocation , CursorType , and LockType determine which functions the Recordset object supports and how efficiently internal management proceeds.

With an open Recordset object !name can be used to access the data fields of the currently active data record. To activate another data record one has the methods MoveNext , MovePrevious , etc. The properties EOF and BOF indicate whether navigation led beyond the end, respectively beginning, of the data record list.

	RefEdit	RefEdit
UserForm .Controls(..) —

The RefEdit control (formula field) is not part of the MS Forms library, but rather is made available in the freestanding RefEdit library. It is a variant of the text field and enables convenient input of cell references. Value contains the input text or cell reference as a character string.

	Reference[s]	VBE
VBProject .References(..) —

References lists all object references (libraries) that are used in a VBA project. With the properties of the subordinate Reference object one can determine, among other things, their name, file name, and a short description.

	RoutingSlip	Excel
Workbook .RoutingSlip —

This object controls the transfer of a workbook via e-mail. The most important properties are Recipients (list of persons/computers to whom the workbook is to be sent), Message and Subject (content input), and Delivery . The transfer of the workbook is launched by the Workbook method Route .

	Scenario	Excel
Worksheet .Scenarios(..) —		.ChangingCell ˜ Range

Scenarios are an aid in comparing various changes to source data in a worksheet model. The Scenario object describes which cells of a table are to be changed. The object recognizes two characteristic properties: ChangingCell refers to a (usually composite) range of cells with variable values; Values contains a data field that specifies numerical values that are to be placed in the cells. The method ChangeScenario offers a possibility of changing both properties simultaneously .

	Scenarios	Excel
Worksheet .Scenarios —		(index or name) ˜ Scenario

This enumeration object refers to the scenarios defined in a worksheet. Add creates a new scenario. The method CreateSummary creates, on the basis of all scenarios, a summary report (in a new worksheet) in which all changed cells and the results of those changes are given.

	ScrollBar	MS-Forms
UserForm .Controls —

With a scroll bar a numerical value ( Value ) can be placed within a specified range ( Min to Max ).

	Section	Binder
Binder .ActiveSection —
Binder .Sections(..) —

The Section object describes a section of a binder. The Type property specifies what object type is at issue. This property returns a character string that describes the OLE object type (in the syntax of GetObject or CreateObject ). The Object property enables direct access to the underlying program (Excel for worksheet, Word for a text document, for example). For managing the section one has available the methods Activate , Copy , Delete , Move , PrintOut , SaveAs .

	Sections	Binder
Binder .Sections(..)

This enumeration object lists all sections of a binder. With the Add method new sections can be added.

	Series	Excel
Chart .SeriesCollection(..) —		.Trendlines ˜ Trendline
ChartGroup .SeriesCollection(..) —		.Points ˜ Point
		.ErrorBars ˜ ErrorBars
		.DataLabels ˜ DataLabel

Data series contain groups of associated data points of a chart. A data series can, for example, contain all values for a line in a line chart. If several lines are to be represented, then several data series will be required.

With the object Series various formatting features for the graphical display of the entire data series can be changed. For this there are generally available the same properties and methods as for the formatting of individual Point objects; see above.

Type determines the chart type for the individual data series. (By choosing differing chart types for several data series one ends up with combination charts made up of groups of charts.) With AxisGroup the data series is associated to one of two possible coordinate axes. PlotOrder moves individual data series in a 3-D chart forward or backward. Smooth determines whether the curve of a line chart should be smoothed. Additional effects can be achieved with the subobjects ErrorBars and Trendline .

	SeriesCollection	Excel
Chart .SeriesCollection —		(index or name) ˜ Series
ChartGroup .SeriesCollection —

This enumeration object refers to the data series of a chart or chart group in a combination chart (see Series ).

With the methods Paste and Add the number of data series can be increased. Extend increases the number of data points in the data series.

	SeriesLines	Excel
ChartGroup .SeriesLines —		.Border ˜ Border

Series lines link the bars or columns of a stacked bar or column chart. (" Stacked " means here that several data series are represented not each in its own column, but in segments of a column one above the other.) Series lines thus clarify the development of the individual values. The appearance of series lines is controlled by the Border subobject.

	ShadowFormat	Excel
Shape/ShapeRange .ShadowFormat —		.ForeColor ˜ ColorFormat

This object describes the shadow effects of Shape objects. The most important properties are ForeColor (the color of the shadow), OffsetX , and OffsetY .

	Shape	Excel
Comment .Shape —	ConnectorFormat. ˜ ConnectorFormat
ConnectorFormat. Begin/EndConnectedShape —		Nodes ˜ ShapeNodes
FreeFormBuilder. ConvertToShape(..) —		GroupItems ˜ GroupShapes
Hyperlink.S hape —		ControlFormat ˜ ControlFormat
Shapes. AddShape(..) —	TopLeftCell, BottomRightCell ˜ Range
Worksheet. Shapes(..) —

The Shape object serves primarily to represent AutoForms (lines, rectangles, arrows, stars ”see the drawing toolbar). It thereby replaces the various drawing objects from Excel 5/7. However, this object is also used to manage a completely different collection of objects (for example, MS Forms controls).

There are many related objects: ShapeRange enables the common editing of several Shape objects. Freeforms constitute a particular class of Shape objects. In this case the property ShapeNodes refers to a like-named enumeration of ShapeNode objects. The GroupShape object manages several items that have been collected into a group.

	ShapeRange	Excel
Shapes .Range(Array(..,..,..)) —		(index or name) ˜ Shape
Charts/OLEObjects. ShapeRange —

This object enables simultaneous processing of an entire collection of Shape objects. For this one has most of the properties and methods of the Shape object. Furthermore, an object group can be constructed with Group or ReGroup . (This returns a new Shape object that unifies the collection. The individual objects can now be addressed via Shape. GroupItems . This property again leads to a GroupShape object.)

	ShapeNode	Excel
Shape/ShapeRange .Nodes(..) —

This object describes a segment (that is, a line segment or curve) of a freeform. The coordinate points are read via Points . This property returns a two-dimensional Array . (A one-dimensional field would actually have sufficed.) Access to both coordinates can be accomplished as in the following lines:

 ' first coordinate point of the first Shape object ' assumes Type=msoFreeform pt = Shapes(1).Nodes(1).Points x = pt(1, 1) y = pt(1, 2) 

Other important properties are SegmentType (line or curve) and EditingType (for example, corner point or point of symmetry). All three properties can be read only. To make changes the SetXxx methods of the ShapeNodes object must be used.

	ShapeNodes	Excel
Shape/ShapeRange .Nodes —		(index or name) ˜ ShapeNode

If a Shape object contains a freeform (say, a line segment drawn free-hand, Shape. Type=msoFreeform ), then its property Nodes refers to the enumeration object ShapeNodes , by which the individual corner points of the line segment can be addressed. Moreover, with the methods Insert and Delete additional line segments can be added and then edited with SetEditingType , SetPosition , and SetSegmentType . See also FreeformBuilder .

	Shapes	Excel
Chart .Shapes —		(index or name) ˜ Shape
WorkSheet. Shapes —		.Range ˜ ShapeRange

This enumeration object lists all Shape objects of a worksheet or chart. In contrast to accessing individual Shape objects, with Range several objects can be accessed simultaneously. To create new Shape objects one has countless Add methods available, such as AddShape , AddLine , AddCurve , AddOLEObject . Finally, SelectAll selects all Shape objects.

	Sheets	Excel
Application .Sheets —		(index or name) ˜ Chart
Workbook .Sheets —		(index or name) ˜ Worksheet
Window .SelectedSheets —

This enumeration object refers to all sheets of a workbook or the currently active workbook (if the object Application or no object at all is specified) or to the commonly selected group of sheets in a window.

Please note that there is no Sheet object. Sheets refers, according to the type of sheet, to a Chart or Worksheet object. In Excel 5/7 files Sheets can also refer to objects of type DialogSheet and Module .

With the Sheets object all properties and methods of the relevant XxxSheets objects are accessible. In particular, with Add and Delete sheets can be created and deleted. Select activates a selected sheet. The property Visible specifies whether a sheet is to be displayed in the sheet register. With the method FillAcrossSheets a range of cells in a worksheet can be copied to all sheets encompassed by Sheets .

	SmartTag	Excel 2002
Range. SmartTags(..) —		.Range ˜ Range
Worksheet. SmartTags(..) —		.SmartTagActions ˜ SmartTagActions
SmartTags. Add —

Smart tags are small menus whose content depends on the content of an Excel cell. The menu entries can be used to execute appropriate actions with the cell contents. (For example, a web site with additional information can be displayed.)

The SmartTag object exhibits only a few properties: Name contains the content of an internal name, which, however, gives only the type (e.g., " urn:schemas-microsoft-com:office:smarttags-# stockticker "). Thus several smart tags in a single worksheet can have the same name. SmartTagActions refers to the available actions for the smart tag. XML contains a description of the smart tag in XML format, where there is not much more information than in Name . Range refers to a cell that belongs to the SmartTag .

	SmartTagAction	Excel 2002
SmartTag. SmartTagActions(..) —

This object describes an action that can be executed for a particular SmartTag object. This action can actually be carried out with the method Execute . The property Name gives a short description of the action (though calling this a description is rather an exaggeration, since the text is shorter than what is displayed in the smart tag menu; however, the menu text cannot be ascertained via code).

	SmartTagActions	Excel 2002
SmartTag. SmartTagActions —		(index) ˜ SmartTagAction

Note that this enumeration cannot be run through with a For “Each loop. Instead, you must construct a loop running from 1 to Count .

	SmartTagOptions	Excel 2002
Workbook. SmartTagOptions —

This object uses two properties to determine which smart tag functions are active in the current workbook: DisplaySmartTags specifies whether smart tags are displayed. EmbedSmartTags specifies whether smart tags can be embedded in a workbook. This means that the smart tag information is stored together with the Excel file and after the next launch is displayed even if the smart tag function is deactivated.

	SmartTagRecognizer[s]	Excel 2002
Application. SmartTagRecognizers —

The enumeration SmartTagRecognizers refers to all smart tag recognizer modules installed on a computer (that is, SmartTagRecognizer objects). Normally, this involves only two modules, one for Outlook contacts and another for managing smart tag lists. (However, it is impossible to determine via program code which smart tag lists are installed for this module. By default there is only a list of securities symbols for important American companies.) Lastly, the smart tag modules are displayed in the dialog ToolsAutocorrect OptionsSmart Tags.

The property SmartTagRecognizers.Recognize specifies whether the smart tag function is executed in the background (that is, whether smart tags are automatically inserted for new input or changes in cells).

The SmartTagRecognizer object can be used to determine the file name of the module (property FullName) and to activate/deactivate each module individually (property Enabled).

	SmartTags	Excel 2002
Range. SmartTags —		(index) ˜ SmartTag
Worksheet. SmartTags

This enumeration refers to all smart tags that are available within a range of cells or in the entire worksheet. The method Add can be used to equip a cell with a smart tag. Add must be passed the type name of the smart tag. Add can be used only on Range objects when these encompass exactly a single cell — otherwise , an error occurs.

 ActiveCell.SmartTags.Add( _   "urn:schemas-microsoft-com:office:smarttags#stockticker") 

	SoundNote	Excel
Range .SoundNote —

With the SoundNote object sounds or sequences of notes can be associated with a table cell. The linking of sound information is accomplished with the methods Play and Import . With the method Play the notes are output to the computer's speaker.

	Speech	Excel 2002
Application. Speech —

This object controls the automatic speech output of cells (property SpeakCellOnEnter) and with the Speak method enables the output of English sentences via the sound card: Application.Speech.Speak "I love Excel."

	SpinButton	MS-Forms
UserForm .Controls —

With a spin button a numerical value ( Value ) can be placed within a given range ( Min to Max ).

	StdFont	StdOLE
object .Font —

This object serves the internal representation of fonts ( Font property of many objects). This same function is also filled by the object NewFont of the MS Forms library.

	StdPicture	StdOLE
Image .Picture —

This object serves the internal representation of pictures (bitmaps, Picture property of various MS Forms controls). The StdOLE library makes available the two functions LoadPicture and SavePicture , by means of which bitmap files can be loaded and saved.

	Style	Excel
Workbook .Styles(..) —		.Interior ˜ Interior
Range .Style —		.Borders ˜ Borders
		.Font ˜ Font

Styles are predefined formats that can be used for rapid formatting of cells or ranges of cells. Styles belong to the data of a workbook and are saved with it. To equip a range of cells with a style one must simply assign the corresponding style to the Range property Style .

Styles contain information about font, color, and pattern (property Interior ), cell borders (method Borders for the four lines left, right, above, below), number format ( NumberFormat ), orientation ( Orientation , VerticalAlignment , Horizontal- Alignment , WrapText ), and cell protection ( Locked ). The six properties Include- Alignment , -Pattern , etc. specify which of the six formatting aspects are to be modified by the style. That is, you can define formats that change only the font and number format and leave the rest of the information in the range untouched.

	Styles	Excel
Workbook .Styles —		(index or name) ˜ Style

This enumeration object refers to the styles defined in a workbook. With the methods Add and Merge additional styles can be added to the list.

	Tab	Excel 2002
Worksheet. Tab —

 ActiveSheet.Tab.Color = RGB(255, 0, 0) 

	TextBox	MS-Forms
UserForm .Controls —		.Font ˜ NewFont

A text field enables the input of text (including multiline) in certain dialogs. The most important property is, not surprisingly, Text .

	TextEffectFormat	Excel
Shape/ShapeRange .TextEffect —

This object describes the features of a WordArt object ( Shape object with Type=msoTextEffect ).

	TextFrame	Excel
Shape/ShapeRange .TextEffect —		.Characters ˜ Characters

This object describes the extent of the text in an AutoForm object ( Shape object with Type=msoAutoShape ). Access to the text itself is via the Characters object, which enables separate formatting of each individual letter. The text alignment is set with HorizontalAlignment and VerticalAlignment , the border of the AutoForm object with MarginLeft , -Top , -Right , and -Bottom .

	TextStream	Scripting
File .OpenAsStream —

This object enables the reading and writing of a text file. The most important methods are Read and ReadLine for reading individual characters or entire lines, as well as Write and WriteLine for writing a character string or an entire line.

	ThreeDFormat	Excel
Shape/ShapeRange .ThreeD —	.ExtrusionColor ˜ ColorFormat

This object describes the three-dimensional appearance of Shape objects. With the appropriate setting, inherently flat Shape objects can be extended into the third dimension.

To understand the properties fully you should experiment with the 3-D toolbar (3-D icon on the drawing toolbar).

	TickLabels	Excel
Axis .TickLabels —		.Font ˜ Font

This object describes how the tick marks on a coordinate axis of a chart are to be labeled. Characteristic properties and methods are Orientation , Font , NumberFormat , NumberFormatLinked ( True if the number format should be taken from the table).

This object has influence neither on the location and content of the label nor on the number of label points. These details are handled by Axis properties, in particular, by TickLabelSpacing and -Position as well as by TickMarkSpacing (for X -axes) or MajorUnit (for Y -axes).

	ToggleButton	MS-Forms
UserForm .Controls —		.Picture ˜ StdPicture

The toggle button is a variant of an ordinary button. The special feature is that the toggle button does not spring back automatically after being pressed, but only after being clicked on again.

	TreeviewControl	Excel
CubeField. TreeViewControl —

This object refers to the hierarchical listbox that is used in OLAP pivot fields for selecting an indicated hierarchical level and detail. With the property Drilled one can control the visibility of the pivot data corresponding to the CubeField . For this to occur the property is passed a two-dimensional Array (see the example in the on-line help or use the macro recorder).

	Trendline	Excel
Series .Trendlines(..) —		.Border ˜ Border
		.DataLabel ˜ DataLabel

This object describes trend lines, approximation curves, and smoothed curves in charts. Trend lines are associated to individual data series and can be displayed only in certain two-dimensional chart types. Type determines the type of trend line (for example xlPolynomial , xlLogarithmic ). For approximation curves Period determines the number of data points whose average value is calculated to draw the curve. In the case of polynomial approximation curves the property Order determines the order of the polynomial (2 to 6). Forward and Backward determine how many periods into the future or past beyond the available data range the curve should be drawn (for estimating a trend).

The visual display of a curve is handled with the subobjects DataLabel and Border . DisplayEquation and DisplayRSquared determine whether the formula for the curve and a coefficient for the size of the desired approximation to the data should be displayed in a text field.

	Trendlines	Excel
Series .Trendlines —		(index or name) ˜ Trendline

This enumeration object refers to the trend line, approximation curve, or smoothing curve of a data series. See Trendline .

	UpBars	Excel
ChartGroup .UpBars —		.Interior ˜ Interior
		.Border ˜ Border

This object describes the appearance of positive deviation bars between two data series in a line chart. Details can be found under DownBars (for negative deviation bars).

	UsedObjects	Excel 2002
Application. UsedObjects —		(index) ˜ Object

This enumeration refers to all basis objects being currently managed by Excel. This includes, for example, loaded workbooks ( Workbook objects), worksheets contained within these workbooks ( Worksheet ), diagrams, and ActiveX components. The purpose of this enumeration is not documented (and I have no idea what it is).

	UserAccess	Excel 2002
UserAccessList (n) —
UserAccessList.Add ( ) —
AllowEditRange .Users(n) —

This object contains, in the property Name , the name of a computer user who is permitted to change a range of cells of a protected worksheet without a password. The name must be in the form " computername \ loginname ." Furthermore, AllowEdit=True must hold.

	UserAccessList	Excel 2002
AllowEditRange. Users —		(index) ˜ UserAccess

This enumeration contains all users who are permitted to change a range of cells within a protected worksheet without a password.

	UserForm	MS-Forms
Dialogname —	.ActiveControl ˜ controlelement
		.Controls ˜ Controls
	.controlelementname ˜ controlelement

The UserForm object is the basis object for all MF Forms dialogs (forms). It makes a host of properties available for the structuring of dialogs. StartupPosition has influence over the location at which the dialog appears. Picture can contain a bitmap that is displayed as the dialog background. With Zoom the entire dialog contents can be made larger or smaller.

With the enumeration Controls all controls contained within the dialog can be addressed. (In program code, however, usually the name of the control is used directly.) ActiveControl refers to the currently active control (with keyboard focus).

	Validation	Excel
Range .Validation —

This object describes what input is allowed in a range of cells (interactive setting via DataValidation). The type of number allowed (for example, integer, date) is set with Type . The number range can be restricted to the limit values in Formula1 and Formula2 . For this a comparison operator must be specified with Operator (such as xlBetween , xlGreater ). Formula1 and Formula2 contain either values or cell addresses. (In the second case the boundary values are read from the cells.) InputMessage provides a short infotext for input, and ErrorMessage contains the error message that will be displayed if the rules are not adhered to.

	VBComponent[s]	VBE
VBProject .VBComponents(..) —		.CodeModule ˜ CodeModule
		.Properties(..) ˜ Property

VBComponents contains a listing of all components of a VBA project (that is, modules and dialogs, Type property). The code segment of a component is addressed via CodeModule , the properties via Properties . If the component has a designer (here the dialog editor, for example, is meant ), then it can be determined with HasOpenDesigner whether it is active.

	VBE	VBE
Application .VBE —		.CodePanes(..) ˜ CodePane
	.CommandBars(..) ˜ CommandBars
		.VBProjects(..) ˜ VBProject
		.Window(..) ˜ Window

VBE is the start object for the like-named library for programming of the VBA development environment. (In the object browser this library is denoted by VBIDE.) The principal task of VBE is enabling access to the subordinate objects (see syntax box). Furthermore, the currently active components of the development environment can be addressed via ActiveCodePane , ActiveVBProject , ActiveWindow , and SelectedVBComponent .

	VBProject[s ]	VBE
VBE .ActiveVBProject —		.References(..) ˜ Reference
VBE. VBProjects —	.VBComponents(..) ˜ VBComponent

VBProjects contains references to all currently loaded projects (Excel files and add-ins). The individual components (meaning modules and dialogs) and references to external object libraries can be addressed via the properties VBComponents and References .

	VPageBreak	Excel
Worksheet .VPageBreaks(..) —		.Location ˜ Range

This object denotes a vertical page break in a worksheet.

	VPageBreaks	Excel
Worksheet .VPageBreaks —		(index or name) ˜ VPageBreak

This enumeration object enables access to all manual page breaks in a worksheet.

	Walls	Excel
Chart .Walls —		.Interior ˜ Interior
		.Border ˜ Border

This object describes the two side walls of a 3-D chart. With the subobjects Interior and Border the color and border of these walls can be set, although only for both walls taken together. See also Floor for the floor of a three-dimensional chart.

	Watch	Excel 2002
Application. Watches(n) —		.Source ˜ Range
Watches. Add —

This object describes a watch object. This allows individual cells from various worksheets to be displayed in a watch window (ToolsFormula AuditingShow Watch Window). The property Source tells which cell is being watched. Strangely, though, there is no property with the information as to the worksheet in which the cell is located.

	Watches	Excel 2002
Application. Watches —		(index) ˜ Watch

This enumeration refers to Excel's watch objects. Note that with Watches , in contrast to almost all other Excel enumerations, index begins with 0. Watches(0) refers, then, to the first watch object! Watches also cannot be used for creating For “Each loops ( For Each w In Watches ). Instead, you must create a loop for the index from 0 to Count-1 .

	WebOptions	Excel
Workbook .WebOptions —

The properties of this object control the parameters of the HTML exportation of an Excel file. (For the global export settings of Excel you will have to set the properties of DefaultWebOptions .)

	Window	Excel
Workbook .Windows(..) —		.Panes ˜ Pane
Application .ActiveWindow —		.SelectedSheets ˜ Sheets

This object refers to a window within Excel. Windows are made active with Activate . The methods ActivateNext and ActivatePrevious change to the next or previous window. With NewWindow and Close new windows are generated and closed. With Split and FreezePanes windows can be divided into several panes.

The property Visible determines whether a window is visible. With WindowState the size of the window (icon, normal, maximized) is set. Caption contains the window title. The properties ScrollColumn and ScrollRow determine the number of the first visible column or row. DisplayGridlines specifies whether gridlines are displayed within the window.

The Window properties and methods ActivePane , ActiveSheet , ActiveChart , ActiveCell , Panes , Selection , and SelectedSheets refer to various subordinate objects.

	Windows	Excel
Workbook .Windows —		(index or name) ˜ Window

This enumeration object refers to the windows of a workbook. Access is accomplished via the input of the (internal) window number or the window title ( Caption - property of the Window object).

	Window[s]	VBE
VBE .Windows —

The enumeration object Windows and the derived object Window enable access to the windows of the VBA development environment. In the case of a conflict of names with Excel Window[s] objects you must prefix VBE , for example, Dim w As VBE.Window .

	Workbook	Excel
Application .Workbooks(..) —		.Charts ˜ Chart
Application .ActiveWorkbook —		.DialogSheets ˜ DialogSheet
		.Modules ˜ Module
		.Worksheets ˜ Worksheet
		.Windows ˜ Window

The object Workbook describes an Excel file. With the methods introduced above access can be made to the sheets of the workbook, where a distinction is made among the four types of sheet. The method Sheets enables access to all sheets of the workbook (independent of their type). Windows refers to the windows belonging to the workbook.

The workbook can be saved with the methods Save and SaveAs . Close closes the workbook, and with the provision of an optional parameter the workbook can be saved.

	Workbooks	Excel
Application .Workbooks —		(index or name) ˜ Workbook

Workbooks contains the list of all loaded workbooks (including those that are invisible because their windows are hidden). Reference to individual workbooks can be effected either by the input of the index number or the file names (without path). With Add a new, empty, workbook can be declared. Open loads an existing workbook.

	Worksheet	Excel
Workbook .Worksheets —		.Range ˜ Range
Application .ActiveSheet —		.Cells ˜ Range
		.XxxObjects ˜ XxxObject

Worksheets belong together with dialog, chart, and module sheets to the sheets of a workbook. Countless methods and properties refer on the one hand to ranges of cells ( Range , Rows , Columns , Cells ) and on the other to embedded controls, drawing objects, charts, pivot tables, and so on.

The exchange of data via the clipboard is accomplished with the methods Copy , Paste , and PasteSpecial . Calculate leads to a manual recalculation of a table (required only if the Application property Calculation is set to xlManual ). With the properties ConsolidationFunction , -Options , and -Sources the details of a consolidation process are set, which can then be executed with Range .Consolidate .

	WorksheetFunctions	Excel
Application .WorksheetFunctions —

With this object Excel worksheet functions can be used in VBA program code.

	Worksheets	Excel
Workbook .Worksheets —		(index or name) ˜ Worksheet

This enumeration object refers to the worksheets of a workbook. With the methods Add and Copy new methods can be generated. Select makes a worksheet into the active sheet.

Active Server Pages

error 'ASP 0113'

Script timed out

/viewer_r.asp

The ma