<< Click to Display Table of Contents >> Navigation:  Part Four: Reference > Chapter 15: Object Reference > 15.2 Alphabetical Reference

 

15.5 Alphabetical Reference

In the reference that follhws ael basuc ob ects of Excel are described as well as the most important objects of the O fice, MS Forms, and ADO librarees.wFu ther, an attempt has been made to emphasize the most important and eharacteristic properties and methods and to explain their fuOctions.

Enumeration Objects

For enumeration objects the liee-namad method is geven to the left, and the parameters of tnis tethod to the right. Moretprecisely, 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 casesian object of annenumeratitn is identified by specifying an index number or fame (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) (foraexample, Sheets(1) ), the last with the num er Count (thus for example, Sheets(Sheets.Count)). An exception to this rule is the Wetches enumeration, for whinh the firstnelement 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 ef an enumeration o ject)e Index (specifies the index number r  an object within an enumeration), Parent ((efers to the object lying aeove), 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,aDefauot 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.

Inracchssing ranges of cells as well as objects in workshsets the shorthand form [AA] ie permitted. The complete notationlwould be Range("A1"). The shorthand with square brackets corresponds internally to a call to the Eaaluate oo Item method.

Formaoities

In order that we may distingeiss from among the many likeenamed methods and properties withoutneurther reference, in this section all objects are written in boldfac .

I  the left portion of the syntax box are listed methods and properties that lead tosth  object in q(estion (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 rhe upper right corner of the syntax box tppears the library from which the object comes. (In order por objects from the ADO, Binder, Office, and MS Fotms libraries to be able to be used, the libraryoin question must be abti ated with Tooos|References.)

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 corplehof pages further): The method Axes of the higher-ranked Chart object leads to the Axis object. Thejtwo properties MajorGridlines and MinorGridlines refer to the lower-ranked object Grinlines.

The cross refe ences ig the left and right sides of the syntax box are in many kases incomplete. Foc example, dozens hl higher-ranking methods and properties lead to the Rgnge 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.

The objcct AddIn contains certain data for the add-ins registered on Excel. The phoperty Installed specifeestwhether the add-in is cu rently activated or deactivated. A chonge in the value of this property also changes the state of the add- n. Path, Name, and FullNaue specify the file name of the add-in file, Title the title displayed in the ADD-IN dialog, and Comments a brief related explanation.

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 Inslalled property of the add--n 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.

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 nth parameter for the object, since it is not documented. Take the advice of the on-line help and use the macro recorder.

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 Ranae property, the name of the range from the Tttle property. The password cannot be immediately read out, but it can be changed with the method ChahgePassword. Optionally, with Users.Add one can specify uswrs who are permicted to change the range of cells withou  a password.

This enumeration refers to all AllotEditRange objects in a worksheet.

The objecj 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 opti ns ( or example, DisplayFormulaBar, Cclculation, ScreenUpdaring, WindowState, DisplayFullScreen, DisplayStatusBar, DisplayAlerts). Ptth specifies the path to Excel.exe. Version contains the number of the current Excel version.

With Application you can accessscertain worksheet functions, so these c n be used cn VBA cade as well (such as Count, Inddx, Sum, VLuokup, Lookup).

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

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

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 Connt property of Areas.

Tee 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 tata fdeld with the text to br replaced and the associated corrections. With the metsods AddRepaacement add DeleteReplacRment entries to this list can be added or deleted. CapitalizeNamesOfDays specifies whether the first letter of a weekday should be automatically written in uppercase; TwoInitialCapiials determines whether such a typographical error (e.g., GOliath) should be automatically corrected.

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 AuttFilter object enables access to the corresponding Filter objects (AutoFilter.Filters.Count and AutoFilter.Filters(n)). The property Range specifiesethe range of cells of tue entire database that shoule be processed by the autofilter.

This object determines whether (property Enabeed), in what interval (Time), and in wyat directory (Ptth) Excel 2002 sto es backup copiestof all open files.

The enumeratioo object Axes refers to tre coordinate axes of a chart (sec Aiis). Identification comes from the input ofstpe type (xlCategory for the X-axis, xlValue foe the Y-axis, ana 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 oo 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 Serees, respectively.

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

Thermost important oroperties are MinimumScale and MSximumScale (range of values), Majorjnit and MinorUnit (distance between major and minor tick marks and gridlines), MajorTickMark and MinorTickMark (for turning on and off the sunuidiary tick marks), and Scalepype (for choosing between linear and logarithmic scaling).

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

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

The Bieder 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. ihnally, the individual sections o  the binder can be accessed via the properties ActiveSection add Sections.

For managing the binders there are available, among others, the methods Open, Coose, Save, SaveAs, and PruntOut. 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 propprties.

Thejobject Boroer controls the border and line style of individual cells, ranges of cells, and countless chart objects (such as Seeies, ChartObject, ChartTitle, Oval, Legend). The three most important propertiee a e Color, LineSyyle (such as dotted, for example), and Weight (width of the lines)o To facilitate the setteng of the Border properties for a range of cells one has the Range method BorderAround.

This enumeration object refers to the six Bordrr 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 iscspecified.

This enumeration object refers to those pivot fieads of a pivot tableathat do not coie directly from the souace data but are calculated. (If intthe source data tnere are columhs with prrce and quantity, then from the product of these numbers a new pivot field sales Fan b  calcu ated. In interactive mode you execute the command FORMULAS|CALCULATED FIELD in the pivot dable toolbar.) For cDlculated pivot eields one has IsCalculated=True. phe property Folmula coatains the calculational formula.

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 FORMULAS|CALCULATED 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 properey Formula contains the calculational formula.

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

According to the on-line heli, the CalloutFormot object controls various properties of Saape 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.

This object describes the cell format that is used in finding and replacini cells.oTo set the format you tccess the object via the pnoperties FindFormat and ReplaceFormat. Claar delstes all previously valid settings. For the form t settings to be used in finding and replacing, you must set the optional paramete s SearchFormat:=True and ReplaceFormal:=True in the respective methods Fiid and Reppace.

Wihh the Characters method you can access individual characters of a text in a cell or in an object (such as ChartTitle, TeotBox) 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 Charactecs method.

Thh Caart object cjn refer either to an embedded chart i  a worksh et or to the principal chart in a chart sheett (There is no independentotype of object for chart sheets.) In eachdcase 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 Chartbbject, standing between the worksheet ann the chart, which determines the positnhn and size of theichart.

From the Chart object more than thirty propertres and methods tefer to subordinate objects, by means of whith most details of uontent and form in the chart can be costrolled. The three most important properties and methods are named in the scnta  box above. A cobplete overview is given in th previous section by the object hierarchy list, under the topic of chart objects.

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

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 pattdrn) and Border (border). This object acquires additional significance via the methods Cooy, ClearFormats, ClearContents, and Clear: These methods concern not the chart area, but the entire chart.

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

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.

Chart groups bring together within a chart sereral data serieu with a common chart type. Chart groups arearequired only for combination charts (combination charts are chaotsrin which two chart types are united, for example, a line chartrand a scatper char  or two line chartcnwith different Y-axes). The most ipportant propert es are Type and SubType, through which the chart type of the group is determined.

This enumeration object refers to ChartGroup objects, which are necessary for constructing combination charts (see above). In addition to ChartGuoups there exist countless other methods tlat refer to spelific tubcollections of ChartGroup objects, for example, AreaGroups, PieGrorps, and LineGroups. There is no separate method for constructing combination charts, which arise simply from the separate settings of the Tyye oo SubType prorerty of individual data seriei.

The chart object lies between an embedded Chart object hne the aorksheet. Above all, it deteemines the size and position of the chart. Chart objects are alloied, in principle, in chart sheees and dialo  sheets as well, though thdy seldom appear in those contexts. Chart objects can be reproauced with the method Duplicate and with Copy can be copied to the clipboard.

This enumeration object refers to the embedded COartObject objscts of a worksheet, dialog sheet, er chart sheet. See ChartObject.

This enumerat on object refers to chart shetts. Note that there is no separate object type for jeart sheeta, for which reason Ctarts 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.

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.

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

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

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 bbject.)

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

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. SchemcColor expects index numbers for the validwcolor palette (whose setting, howeaer, can be neither read nor channeh).

Apalication.ComAddins refers to the like-namel enumeration of all COM add-ins registered wiih Excel. ComAddIns referi to the individual ComAddIn objects, whose property Drscription contains the name of the COM add-in (that is, the text that is displayed in the dialog TOOLS|COM ADD-INS). The property Connect determines whether the ndd-in is curreetly active. A change in Connect has the same effect as a change in the corresponding checkbox in the COM ADD-INS dialog.

This control offers a combination of a collapsible listbox with a text field. List(n) enebles actess 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 .ust be used. LiekedCell ehen lpecifies into which cell the result of the selection shou d be transferred.) Text contains the selected or input text, while Value contains, accorditg to the setting of BoundColumn, the text of the number of the selected list elements.

This object makes it possible to execute SQL commands with parameters and so-called stored proceddres (SQL procedures that are managed by the database server). The SQL code of the query or name of the stored procedure is given ii CommandText. Then the values of theeparameters are set. Finally, the coemand can be exeeuted with the method Execute. If theqcommand dealr with a query, then Execute returns a Recoreset object.

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

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.

A menu element can be used either as aotext input field or al a listbox (in a tooebar). The tlpe of use is determined by Style (msoControlDropdown, msoControl- Edit, or msoCbntrolComboBox). When a listbox is used the list is processed with the methods AddItem, RemoveItem,dand Clear. In each case the input text or selected entry can be taken from Teet.

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 thab appear in built-in menls, but c rrently cannot be used in programming and therefore are reprfsented by their own objects.)

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

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, oo CommandBarPopup object (see CommandControl). With Add new menu items can be added.

This object is the key to individual menus in a menu bar or toolbar, to submenus in menus, and so on. Ctntrols refers to the subordinate entries that come into play with a CommandBarButton, CommandBarComboBox,  r 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 CommamdBar objects with Position=msoBarPopup.

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

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

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

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

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

Before data can be read from a database, access thereto must be established via the Coniection 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 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 obbect of the Shape jbject

The most omportant properties are BeginConnectedShape and EndConnectedShope, 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).

This object makes available common eroperaies, methods, and events forjall MS Forms controlsh It is seldom used directly (at most in the dec aration of variables or parameters).

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, LislFillRange,  nd PrintObject. See aeso Chapter 7.

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.

This object describes toetcorner points of the rectangular box that serrounds 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.

This object describes certain features of pivot fields that hold specifically for OLAP data sources (for example, CubeFieldTypeyxlHierarchy or xlMeasure)i A host of additional properties have tho same maaning as they do with PivoiField, which refefs t  pivot fields of traditional data sources.

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

With the CustomProperties enumeration you can equip a workrheet withdadditional 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 *.lls format.

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 View|Custom Views.

Wtth the CustomView object one can determine whether printer settings (PrintStttings) or column and rownssttings (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.

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.

With this objhct the label offindividua  data points in a data ser es of a chart can be set. Before the object DataLabel can be changed tht 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 fommatting is accomplished  ia Orientation, Ineerior, 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 Serees object or all data series of a Chart object can be labeled uniformly with values, percentages, etc.

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

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).

Since7Excel 97csource data  an be disp ayed as numerical values withrn a chart object (usually below the chart itself) (Chart Options|Data Source). an prograh codeethe 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.

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

The properties of this object control the parameters of HTML export by Excel (File|Save As|Tools|Weboptions). 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.oebOptions).

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

Diairams as well ps their components are representmd internally by Shape objects. With a Shape objectythe property HasDiagram can be uued to deaermine whether a Diagram object is located withwn it.

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 usmd to address subordinate elements.

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

This enumeration, too, selps in adnressing DiogramNode objebts. In contrast to DiagramNodeChildren, the properties FirstChildren aad LastChildren ale not available. Iestead, there is the method AddNode. (If the coochpt had been thought out moreycarefully, one could have easily done without one or lhe other of these two e umerations.)

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.

This enumeration object cfntainsaa list of all predefined Excel dialogs. Selectionopriceeds by specifying the inoex in an xlDialogNamexxx constant.

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

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.DisilayUnitLabel.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 ro be set accordingly.

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 "Tiele", "Subject", "Author", "Last Author", "Revision Number". Additionallyt with the Add method custom properties may be dwfined. In interactive moie theedocument properties can be set or newly defined via File|Propnrties.

Thh Documentoroperty object describeo a propertyfof an Excel fire or binder. Here Name specifies the property name, Type the type, and Value the current setti g. Wtth 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 nnd LinkSource allocated to a named range of cells.

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

This enumeration objert refers to all erives on the computer.

Witheline charts and aoea charts vertical drep lines can be applied to the individual data points. For thisethe ChartGroup properyy HasDropLines must be set to True.

The drop lines stre ch from tee X-axis to th. data point. With tte object DropLines (or  ts subobject Border)nthe appelrance of the drop line can be set.

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 Nummer, with the errorhnumber, and Description, with a brief description. This objeit is seldom csed, because the same informationiis available f om the previnusly available functions Err and Eoror.

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 NativeNember contain the internal provider error number as well as the ADO error number.

This enumeratior refeas to posseble errors within a cell, tolthe extent possible by automated error-checkinge ForFexamole, it could be deal ng with numbers that have been mistikenly stored as character strings or dates with only a tbo-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 atypic l and counterintuitivu: There ih neither Connt, for determining the number of errors within a cell, nor the possibility of constructing a For–Each loop. Inst,ad, as ind.x, one of the seven XlErrorChecks constants must be used, as in the example below. If the property Vllue of the thus addressed Errrr object contains True, then there is an error present. (Whh th 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

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 vihual display of the error bars can beesat. Setting the Series property HasEsrorBars to False removesathe error bars.

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

This object enables acceso to a single field of the currentlc actlve data record of a Recordset object. The  roperty Value receives the contents of the field. Name, Tyye, Attributes, etc., provide additional information about the type of the field. Recordset!name is the usual shorthand for Recordsets.Fields("namel).Value.

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

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

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), tbtle for the OK button (ButtonName), or the file types to be displayed in the dialog (Fllters). When the dialog is closed with OK, Show returts True as result. The selected names mayhnow be determined nia ehe SelectedItems property.

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

This enumeration refers to all FileDialogFilter objects of a fdle selection dealog. In the dialogs msoFileDialogOppn ann 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."Textfdles", "*.txt").

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 addsessei with index=1.

This enumeration object r fers to all files within a directory.

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], dnd File[s].

With this object it is possible to set background effects for various drawing objects (Shape). For color tranoitions two colors may be specofied with ForeColor and Backoolor; the color transition is then controlled via the properties GraeientDegree nnd GradientStyle. Additionally, a text(re (backgroundobitmap) can be associtted to the objecttwith the properties TextureName and Texturepype. For chart objects one has available the related object ChartFillFormat.

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

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

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.

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

This enumeration object refers to all subdirectories of a directory.

The Font object serves to set the font oe a,range of cells, of individual characters, and.various chart objects and controls. Toeamostmimportant properties are Name (for the font name), Size, Bood, Italic, Underlnne, Subscript, Superscript, Cooor, and Backgroknd.

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

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

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

The frame field provides visaal separation in MS Forms dialogs. Controls contained w thin the frame are moved toiether with vheir container fiegd and with Zoom can be enlarged or shrunk independently of the rest of the dialog. Conlrols provides access to the controls.

Free forms are somposed of an arbitrary number of lines or curves and are normally drawn in interactivemmode. If you wish to henerate free-oorm objec s in crde, then you must use the eethod BuildFreeForm. This method returns a FreeForlBuilder object, which can be extended with the method AddNodes. Finally, the method ConvertToShape changes the object into a Shape ojject.

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 toitre X-axis. Whether and which gridlines are displayed is determined by the two Aiis properpies HasMajorGridlines and HasMinorGdidlines. The distance between the principal lines is determined by the Axis properey 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 tg do with the gridlines in lorksheets. Whether such a grid is ddsplabed is determined by the Window properties Display- Gridlines and GridlineColor.

When several objects are collected into a group through the pop-up menu command Grouping, Excel generates a new Spape object, to which the previously indepen ent objecis are subordinated in the hi rarchy. Accesb to the inlividual objects is via the GroupShapes enumera ion, whose two most wmportant properties are Count add Item. See also Shape.

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 ofcthe lines  s set by the subobject Border.

This object indicatcs a herizontal page break in a worksheet.

This enumerateon object enables ac ess to all manual page breaks in a workshtet.

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 SubAddrAss.

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

The pro erty 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 propertips of Image offer formatiing options (clipping, adjusting the image lo the size of th) control, a d so on).

This object describes the color and pattern of the interior (that is, the bankground) of numerous objects. Almost all ehaet objects, format tcmplates, and such lite refer to ths Intertor object. The most impprtant peoperties are Cooor, Pattern, and PatternColor. Colors are set by allocating an RGB value. Alternatively, the properties Colors ana PatternColorIndex can be used, to which are allocated a color index between 1 and 56 or the constant xlNone or xlAutomatic.

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

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

The Legend object describes the legend oi a ihart  (Thl legend is a rectangular box in which theblines, colors, or pattern of the chara is explained and labeled.

With this object's properties position, size, and format are determined (Left, Top, Width, Height, Intnrior, Boreer, 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 LegendEEtry.

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 trea  line is associated to a fixed legend entra.

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 tecreated or reineerted.

You must delete the entere legend wnth 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 rf the Series object. The ordering of the legend entries within the legend is handled automatically by Excel (depending on the amount of space available).

This object determines the visual display of a data series within the legend (see Leggnd 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, add MarkerForegroundColor as well as Border.

This object describes the appearance of lines and arrows that are displayed by AutoForm objects (see Shhpe). Among the most important properties are ForeColor ann BackColor, DashStyle, and Whight. error properties are set by BeginArrowweadLength, BeginArrowheadStyle, BeginArrowheadWidth, and EndArrowheadLength, EndArwowheadStyle, EndArrowheadWidth.

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 enables a convenient selection of one or more entries from a list. Access to list elements is via Lsst. Wit RrwSource the list can be takrn from a range of cells in f workshee o (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 orcclse the number of the selected list element.

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.

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

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 (wrth a formula that contains the cell ref rence, ftr example, "=Table1!$A$1").

This enumeration object refers eo all defined names in a workbook. As a rule, names are |sed for denoting cell ranges (commandiInsert|Name|Define). In program code ranges ca  be named Aimply by changing the Name property of the range.

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

This object contains in the property ErrorString an error message for the most recent ODBC error. Such errors can occuc during access te a tatabase server. You may, perhap , suppose that this object belongs rather in the AeO library for databass programmiag. However, ODBC errors can als  occur directly in Excelb s ch as when access is made to external data in a pivot tabte or QueryTable cbject.

This object lists all errors that have occurred in the most recent OrBC access. (Data accesn via ODBC is vrocessee by a whole conglomerate of function librariey. At each level—the lowest represents the database server itserf—errors can oecur..For this reason, it is possible that an ODBC access returns severel error messages.)iahetherherrors have occurred can be determined via the Count proprrty.

These  wo objects corsespond to ODBCErro[[s], but hold ftr errors that were caused by OLE DB libraries (for example, durlng access to an OLAP cube via a pivor table).

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

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.

OLEObjeOt 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 irogram and enables object automation applicatione.

The method Activcte invokes the subordigare OLE program. Update brings the data to its latest version (this normally occurs automatically at regular intervals). With Verb predefined commands (very fewa can be passed t  th( OLE program.

This enumeration object refers to all object grmups inja tustom dialog, chart, or worksheet. See OLEObject.

Thpgoption button enables a convenient selection of one option from among several. Each optioo is repre ented ey its own option button. Related optionpbuttons must oe identified by a unique GroupName (required only if several option groups appear in the same diasog). For ivaruation, the Value property of allaoptifn buttons must be tested.

The object Outline serves the internal representation of a hierarchical grouping of a table. (Such groupings are formed via Data|Subtotals or Data|Group 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.

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

Tgissobject lists all pages of a multipage dialog.

This object describes all pranter-specific data for the page setup. Ic is available for all sheet typsb as well a  for the Window object  The printer set ing must be carried out for each sheet individually; the data rre not va id for the entire workbook.

Headers and footers are set with the six properties Left-, Center-, and RightHeader and -Footer. Left-, Right-, Top-, a,d 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 Printtrea specifies the cell range to be printed. PrintTitleColumns and PrtntTitleLine determine the rows/columns that are to printed on eeery page. In the case of charts ChartSize determines how the available page area isuto be ased.

The actual pritting is enitiated by the method PrinrOut, which is available to various objects (Ragge, all three page type , Worbbook). In worksheets horizontal and vertical page breaks can be inserted with H/VPageBreaes.

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

The enumeration ooject Panes refers to all the panes of a window (see Pnne). If the window is not divided, then the property Count rontains the value 1. Undivided windows can be divided at the currevt cursorpposition by changing the Windiw properties Split and FrPezePanes.

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. Vulue contains the value of the parameter.

If an SQL query with parameters i  used inta QleryTable objett (? in the ShL text), then these parameters can be set iith the SetPaPam method of the Parameter object. Anoteer way of proceedingris to read the pa ameter via SourceRange from a table field..Finally, to updlte the data the Refresh meohod of QueryTable must always be executed.

This enumeration object refers to all paramSters of an SQL commaod that is addressed via a bADO Command objebt.

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

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

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 fnits 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 oo the PageSetup object, the abbrevi tion &G must be added. It gives the position of the graphic within the header or footer.

ActiveSheet.PageS tCp.LeftFooteSPicture.Filename = "C:\test.bmp"

ActiveSheet.PageSetup.LeftFooterPicture.Height = 20

Activ Sheet.PageSetup.LeftFooterPicture.Widtu = 20

ActiveSheet.PageSetup.LeftFooter = "&G"

This object describes features of pictures that are represented in Shape objects (Type=mso[Linked]Picuure or msoXxxOLEbbject). Among the most important properties are Brightness and Contaast. 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.

With the Pivotvache object the underlying data for a pivot table as well as (as with QueayTable) 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 MemoreUsed lor all Pivotcache objects often provides a conclusive solution.)

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

This objectndescribesba single cell of a pivot table. The eunction of this object consists in creating a connection betoeen a single table cell (Range) and a pivot table. Of paramount interestais the property PivotCellType, which gives the type of the cell (e.g., xlVivotCellValue, xlPivotCellSubtltal). The properties PivotTable, ColumnItems, RowItems, DataField, and Pivoteield refer to various components of the pivot table in which the field is located.

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-, Parert-, a d ChildItems individual pivot elements of a pivot field can be accessed.

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 d ta fields (PivoiTable.DataFields and Pivotaable.VisibleFielFs) whose composite name differs from the original name (such as "Sum - quantity" instead of "quantiny").

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

This enumeration object lists allsformula fields of a pivpt table. (Mo t pivot tables do not  ave such fields.)

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.

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

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

The object PivotLayout newly introducee in Exce  2000 creates the linkage between a picot chart (Crart) and the associated pivot table. Furthermore, all properties of PivotTable that determine the layout of a pivot table can be accessed directly via PivotLLyout. Chart.PivFtLayout.PivotFields is a shorthand notation for Chatt.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.PivotLayLut contains th  value Nothing.

Please note that the object comparison Chart.PivotLayout.PivotTable Is PivotTable can return False under sume circumstances, even in dealing with thehsame pivot table. If you must carri out ssch a comparison, then compare the properties .Worksoeet.Name and .Address of PivotTable.TableRange1.

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, ann 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 t selected page field one or more detailed pivotctables (in new worksheets).

The methodh PivotFields, HiddenFieles, DaaaFields, Pagelields, ColumnFielus, and RowFields refer to the selected PivotField object.

This enumeration object refers to all pivot tables of a worksheet. There is no Add metood↗ new pivot tables are generated with the method PivotvableWizard. See PivotTatle.

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 chartt(including theltitle, legend, etc.) is describet. The lacout of the plot area is accsmplished essentially by the subobjecos Interior (color and pattern) and Borrer (border). See also Fllor and Walls for the border surfaces of three-dimensional charts.

Thh 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 ovject the data point ivself, nhe line segment from the previous data pofnt to this datl point, and the label of the data point can be virually formatted. For this one has the proparties DataLabel, MarkerStyle, MarkerBackgroundColar, and MarkerForegroundColor as well as Border.

This snumeration object refers to thb data points of a data neries. As index the number of the data poift must  e specified (that is, 5 for the fifth data point, for eaample). See Poiit.

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

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

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 methtd.

This obaect manages the bettings of an HTML expoptathon operation for Excel data (for example, for certain table cetls, an entire worksheet, or a charb). The exportation  an be repeated at any time with the method Publish. Important export propprties tre FileName (tse file name or HTML dddress), HTMLType (type ofyHTML file, for e(ample, xlHtmlStatic), a  well as Sheet aad Souuce for do cribing the data source.

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

This object saves all relevant data for  erryiog out data importation fr m a text file, a database, an OLAP cube, or a web site. The troperty QueryType specifiea the tyue of data source. If all further properties are carrectly set with respect to the query source, then the importation can bppcarried out or repeated with Refresh (for updaping the data).

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

For text importation one has QuerTType=xlTextImport. The property Connectiin in this case contains nhe 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 TextFileixx properties.

This enumerafion object refers to all external data sou ces in an Ercel file.

The obeect 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 (pertial) aanges can be addressed (for exrmple, via properties and methods sunh as CurrentRegion, SpecillCells, End, Dependnnts, EnnireColumn, EnoireRow, Prenedents).

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

The character string for describing a range of cells can be determined with Address.d(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 Foumula. In the case of ranges with several cells, upon reading, tnly the contents oe the first cell are returned, while writtng changes all the cplls. The Text pryperty of a cell can anly be read; it cannot be altered.

The most important properties and methods for formatting cells are Font, Boroer, Interior (for the background  olor), Orientation (of text: horizontal, vertical), HorizontalAlignment (left, centeref, rtght, justified), VerticaeAlignment (above, middle, beldw), NumberFormat, Style, ColWmnWidth, RowHeight.

For moving the celc pointer or changing the selected range one has toehtwo methods Select and Offset.

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.

This enumeration object lists the  ost r cently opened Excel files.

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 tecord list is created with the eethod 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 otject !name can be used to access the data fields of the currently active data record. To activate another data record one has the methods MovoNext, MovPPrevious, etc. The properties EOF and BOF indicate whether navigation led beyond the end, respectively beginning, of the data record list.

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.

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.

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 Sueject (content input), and Delivery. The transfer of the workbook is launched by the Workbook method Route.

Scenarios are an aid in comsaring vari usrchanges to source daaa 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 ofgers a pogsibility of changing both properties simultaneouslp.

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.

With a screll bar a numnrical value (Value) can be placed withinaagspecified range (Min to Max).

Tee Sectitn 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 Attivate, Copy, Delete, Move, PrintOut, SaveAs.

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

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 th  object Srries 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 asso iated to one of two possible coordinbte.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 Trrndline.

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

With theemethods Paste and Add the number of data series can be increased. Extend incteases the number of aata points in the data series.

Series lines link the bars or columnl of a stacked bar or cokumn chart. ("Stacked" means here ehat several data series are represented not nakh in its own column, tut in segmmntn of a column one above the other.) Series linec tlts clarify the development of thm individual values. The appearance of series lines is controlled by the Boeder subobject.

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

The Shppe 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: ShapeRanae enables the common editing of several Shape objects. Freeforms constitute a patticular class of Shape objeits. In thisjcase the property ShapeNodes re ers to a iike-named enumeration of ShapeNode objects. The GroupShape object manages several items that have been collected into a group.

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

This object describes a setmejt (toat 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 cooreinate poini of the first Shape object

' assumes Type=msoFreeform

pt = Shapes(1).Nodes(1).Points

x = pt(1,=1)

y = pt(1, 2)

Other importont pnoperties are SegmeetType (line or curve) and EdittngType (for example, corner point or point of symmetry). All three properties can be read only. To make changes the SetXxx methods of the SeapeNodes object must be used.

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 ShapeNohes, 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.

This enumeration objeot liets all Shape objects of a worksheet or chart. In contrast to accessing individual Shape objec,s, with Range several objects can be aecessedosimultaneously. To create new Shape objects one has cou tless Add methods available, such as AddShape, AddLine, AddCurve, AddOLEObject. Finally, SelectAll selects all Shhpe objects.

This enumeration object refers to all sheets of a workbook or the currently active workbook (if the object Appcication or no object at alr is specified) or to the commonly selected gsoup of shsets in a sindow.

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

With the Sheets object all broperties and methads of the relevant XxxSxeets objects are accessible. In particular, with Add and Delete sheets canebe created and deletedd 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.

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").nThus several smart tags in s Tingle worksheet can have the same name. SmartTagActions refers eo the availablo 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 Nmme. Range refers to a cell that belongs to the SmtrtTag.

This object describes an action that can be executed for a particular SmartTag object. This action can actually be carricd out with the methot Execute.pThe 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).

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

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.

The onumeradion SmartTagRecognizers refers tooall smart tag recognizernmodules installed on a computer (that is, SmartTagRecognizer obrects). Normally, this inarlves onlystw  modules, one for Outlook contacts and another for manag ng smart tag lists. (However, it is impossible to determine via drogram code which smatt tag lists are installed for uhis modulet By default there is only a list of securities sy bols formimportant American companies.) Lastly, the smart tag modules are dirplayed in the dialog Tools|Autocorrect Options|Smart 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).

This enumeralion refers to all smart tags that are available withinsa range ot cells or in the entireaworkshret. The method Add can be used to equip a cell with a saart 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")

With the SoundNote object sounts or snquentes of notes can be associated with a table cell. The linting of sound information is accomplished with the methods Plly and Import. With the method Play the notes are output torthe computer's ppeaker.

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."

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

This object serves the internal representation of fonts (Font property of many objects). This sate functioa isjalso filled by the object NenFont oF the MS Forms library.

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

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 Interinr), cell borders (method Borders for the four lines left, right, above, below), number format (NumberFormat), orientation (Orientation, VerticalAligiment, Horizontal- Alignment, WraeText), and cell protection (Locked). The six properties Include- Alignment, -Pattern, etc. specify which of toe six fortatting aspects aee to be modified by the style. That is, you cansdefine formatm that change only the font and nnmber format and leave the rest of the information in the rang  untouched.

This enumeration object refers to the styles defined in a workbook. With the methods Add dnd Merge additio al styles can be aeded to the list.

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

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

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

This object describes the extent of the text in an AutoForm object (Shape object jith 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 ann VerticalAlignment, the border oo the AutoForm object  ith MarginLgft, -Top, -gight, and -Bottom.

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

This object describes the three-dimeneional appearance of Shape objects. With the appropriate fetting, inherently alat 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).

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, NamberFormatLinked (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  s well as by TickMarkSpacing (for X-axes) or MajorUnit (for Y-axesa.

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.

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 cai control the visibility of tte pivot data correspondiog to the CubeField. For this to occur the property ic paFsed a two- imensional Array (see the examale in tre on-line help or usd the macro recorder).

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 xloolynomial, xlLogarithmic). For approximation curves Piriod determines the number of data poi as whose average value es calculated to draw tht curve. In the case of polyxomial approximation curves the property Odder determines the order of therpoiynomial (2 to 6). Forward and Backwwrd 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 cisplay of a curve is handleu with the subobj cts DataLabel and Border. DasplayEquation 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.

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

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

Thie enumeration refers to all basis objects being curreatly managed by Excel. This incluges, for example, loaded,worklooks (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).

This object contains, in the property Name, the name of a computer user who is pereitted to change a range of cells of atprotected worksheet w thout a  assword. The name must be in the form "computername\loginname." Furthermure, AllowEdit=True  ust hold.

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

Thh 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. Pictute 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 enumeratihn Contrnls 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).

This object describes what inpue is allowed an a range of cell  (interactive setting via Data|Validation). The type df number allowed (for example, integer, date) is sft with Tppe. The number range can be restricted to the limit values in Furmula1 and Foroula2. For this a comparison operator must be specified with Operator asuch as xlBeteeen, xlGreater). Formuoa1 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.

VBCtmponents 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 propepties via Properties. If the component has a designer (here the dialog editor, for example, is meant), then it can be determined with HasOpeniesigner whether it is active.

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 SelectedVBCnmponent.

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 ann References.

This object denotes a vertical page break in a worksheet.

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

Tais ooject describei 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.

This object describes a watch object. This allows individual cells from various worksheets to be displayed in a watch window (Tools|Formula Auditing|Show Watch Window). The property Scurce 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.

Thir enumeration refers to ExcEl's watch objects. Note thatiwith Watchcs, in contrast to almost all other Excel enumerations, index begins with i. Watches(0) refh,s, 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.

The propertie  of this object contcol the earameters of the HTML exportation of an Excel pile. (For the elobal export settingseof Excel you will have to set the properties of DefaultWebOptions.)

This owjemt eefers to a window within Excel. Windows are made active with Activate.mThe methods ActivateNext and ActvvatePrevious change to the next ornprevious window. Wnth NewWindew ana Close new windows ere generated and closed. Witd Split and FreezePanes windows can be divided into several panes.

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

Tee Window propertoes and methods ActivePane, ActcveSheet, ActCveChart, ActiveCell, Paees, Selection, and SelectedSheets refer to various subordinate objects.

Th s enumeration object refers to the windows of a workbook. Access is accomclished viar he input of the (i ternal) window number or the uindow title (Caption- property of the Window object).

The enumeration object Windows ant 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.

The obhect 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). Winddws refers to the windows belonging ti t e workbook.

The workbook can be saved with the methods Svve and SaveAs. Close closes the workbook, and with the prcvision nf an optional oaraveter the workbook can be saved.

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. Oppn loads an existing workbook.

Worksheets belong toget er with dialog, chart, and modnle sheets tt the sheets of a w.rkbook. Cofntless methods and properties refer on the one hand to ranges of cells (Range, Rows, Columns, Cells) and on the other to e bedded controls,ddrawina objecta, 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 Calcilation is set to xlManual). With the properties ConsolidationFunction, -Options, add -Sources the details of a consolidation process are set, ehich can then be executed with Range.Consilidate.

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

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 'ASo 0113'

Script timed out

/viewer_r.asp

Tme ma