New Members and Constants
| Excel Developer Reference |
New Members
The following properties, methods, and events have been added to existing objects in Microsoft Office Excel 2007.
Application
| Properties | Description |
|---|---|
| ShowDevTools | Returns or sets a Boolean that represents whether the Developer tab is displayed in the Ribbon. Read/write Boolean. |
| ShowMenuFloaties | Returns or sets a Boolean that represents whether to display Mini toolbars when the user right-clicks in the workbook window. Read/write Boolean. |
| ShowSelectionFloaties | Returns or sets a Boolean that represents whether Mini toolbars displays when a user selects text. Read/write Boolean. |
| WarnOnFunctionNameConflict | The WarnOnFunctionNameConflict property, when set to True, raises an alert if a developer tries to create a new function using an existing function name. Read/write Boolean. |
| LargeOperationCellThousandCount | Returns or sets the maximum number of cells needed in an operation beyond which an alert is triggered. Read/write Long. |
| MeasurementUnit | Specifies the measurement unit used in the application. Read/write xlMeasurementUnit. |
| MultiThreadedCalculation | Returns a MultiThreadedCalculation object that controls the multi-threaded recalculation settings that are new in Excel 2007. Read-only. |
| DeferAsyncQueries | Gets or sets whether asychronous queries to OLAP data sources are executed when a worksheet is calculated by VBA code. Read/write Boolean. |
| DisplayDocumentInformationPanel | Returns or sets a Boolean that represents whether the document properties panel is displayed. Read/write Boolean. |
| DisplayFormulaAutoComplete | Gets or sets whether to show a list of relevant functions and defined names when building cell formulas. Read/write Boolean. |
| EnableLargeOperationAlert | Sets or returns a Boolean that represents whether to display an alert message when a user attempts to perform an operation that affects a larger number of cells than is specified in the Office center UI. Read/write Boolean. |
| EnableLivePreview | Sets or returns a Boolean that represents whether to show or hide gallery previews that appear when using galleries that support previewing. Setting this property to True shows a preview of your workbook before applying the command. Read/write Boolean. |
| FileExportConverters | Returns a FileExportConverters collection that represents all the file converters for saving files available to Microsoft Office Excel. Read-only. |
| FormulaBarHeight | Allows the user to specify the height of the formula bar in lines. Read/write Long. |
| GenerateTableRefs | The GenerateTableRefs property determines whether the traditional notation method or the new structured referencing notation method is used for referencing tables in formulas. Read/write. |
| ActiveEncryptionSession | Read-only |
| AlwaysUseClearType | Returns or sets a Boolean that represents whether to use ClearType to display fonts in the menu, Ribbon, and dialog box text. Read/write Boolean. |
| Assistance | Returns an IAssistance object for Excel 2007 that represents the Microsoft Office Help Viewer. Read-only. |
| Methods | Description |
|---|---|
| SharePointVersion | |
| CalculateUntilAsyncQueriesDone | Runs all pending queries to OLEDB and OLAP data sources. |
| Events | Description |
|---|---|
| WorkbookRowsetComplete | The WorkbookRowsetComplete event occurs when the user either drills through the recordset or invokes the rowset action on an OLAP PivotTable. |
| AfterCalculate | The AfterCalculate event occurs when all pending refresh activity (both synchronous and asynchronous) and all of the resultant calculation activities have been completed. |
AutoCorrect
| Properties | Description |
|---|---|
| AutoFillFormulasInLists | Affects the creation of calculated columns created by automatic fill-down lists. Read/write Boolean. |
AutoFilter
| Properties | Description |
|---|---|
| Sort | Gets the sort column or columns, and sort order for the AutoFilter collection. |
| FilterMode | Returns True if the worksheet is in the AutoFilter filter mode. Read-only Boolean. |
| Methods | Description |
|---|---|
| ShowAllData | Displays all the data returned by the AutoFilter object. |
| ApplyFilter | Applies the specified Autofilter object. |
Axis
| Properties | Description |
|---|---|
| LogBase | Returns or sets the base of the logarithm when you are using log scales. Read/write Double. |
| TickLabelSpacingIsAuto | Returns or sets whether or not the tick label spacing is automatic. Read/write Boolean. |
| Format | Returns the ChartFormat object. Read-only. |
AxisTitle
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
| IncludeInLayout | True if an axis title will occupy the chart layout space when a chart layout is being determined. The default value is True. Read/write Boolean. |
| Position | Returns or sets the position of the axis title on the chart. Read/write XlChartElementPosition. |
Border
| Properties | Description |
|---|---|
| ThemeColor | Returns or sets the theme color in the applied color scheme that is associated with the specified object. Read/write Variant. |
| TintAndShade | Returns or sets a Single that lightens or darkens a color. |
Borders
| Properties | Description |
|---|---|
| ThemeColor | Returns or sets the theme color in the applied color scheme that is associated with the specified object. Read/write Variant. |
| TintAndShade | Returns or sets a Single that lightens or darkens a color. |
Chart
| Properties | Description |
|---|---|
| ShowDataLabelsOverMaximum | Returns or sets whether to show the data labels when the value is greater than the maximum value on the value axis. Read/write Boolean. |
| SideWall | Returns a Walls object that allows the user to individually format the side wall of a 3-D chart. Read-only. |
| BackWall | Returns a Walls object that allows the user to individually format the back wall of a 3-D chart. Read-only. |
| ChartStyle | Returns or sets the chart style for the chart. Read/write Variant. |
| Methods | Description |
|---|---|
| ApplyChartTemplate | Applies a standard or custom chart type to a chart. |
| ApplyLayout | Applies the layouts shown in the ribbon. |
| ExportAsFixedFormat | Exports to a file of the specified format. |
| SaveChartTemplate | Saves a custom chart template to the list of available chart templates. |
| SetDefaultChart | Specifies the name of the chart template that Microsoft Excel uses when creating new charts. |
| SetElement | Sets chart elements on a chart. Read/write MsoChartElementType. |
| ClearToMatchStyle | Clears the chart elements formatting to automatic. |
ChartArea
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
ChartObjects
| Properties | Description |
|---|---|
| ProtectChartObject | True if the embedded chart frame cannot be moved, resized, or deleted through the user interface. Read/write Boolean. |
ChartTitle
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
| Position | Returns or sets the position of the chart title on the chart. Read/write XlChartElementPosition. |
| IncludeInLayout | True if a chart title will occupy the chart layout space when a chart layout is being determined. The default value is True. Read/write Boolean. |
ColorFormat
| Properties | Description |
|---|---|
| ObjectThemeColor | Returns or sets a color that is mapped to the theme color scheme. Read/write MsoThemeColorIndex. |
CubeField
| Properties | Description |
|---|---|
| AllItemsVisible | The AllItemsVisible property checks whether manual filtering is applied to a PivotField or CubeField. Read-only Boolean. |
| CubeFieldSubType | Specifies the type of a CubeField. Read-only. |
| CurrentPageName | Returns or sets the page name for a CubeField. Read/write String. |
| IncludeNewItemsInFilter | The IncludeNewItemsInFilter property is used to track included/excluded items in OLAP PivotTables. Read/write. |
| IsDate | Returns True if the CubeField is a date. Read-only Boolean. |
| Methods | Description |
|---|---|
| ClearManualFilter | The ClearManualFilter method provides an easy way to set the Visible property to True for all items of a PivotField in PivotTables, and to empty the HiddenItemsList/VisibleItemsList collections in OLAP PivotTables. |
| CreatePivotFields | The CreatePivotFields method is new in Microsoft Office Excel 2007. It enables users to apply a filter to PivotFields not yet added to the PivotTable by creating the corresponding PivotField object. |
DataLabel
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
DataLabels
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
DataTable
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
DisplayUnitLabel
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
| Position | Returns or sets the position of the unit label on an axis in the chart. Read/write XlChartElementPosition. |
DownBars
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
DropLines
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
ErrorBars
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
ErrorCheckingOptions
| Properties | Description |
|---|---|
| InconsistentTableFormula | Returns True if the table formula is inconsistent. Read/write Boolean. |
FillFormat
| Properties | Description |
|---|---|
| GradientStops | Returns the end point for the gradient fill. Read-only. |
| TextureOffsetX | Returns the offset X value for the specified fill. Read/write Single. |
| TextureOffsetY | Returns the offset Y value for the specified fill. Read/write Single. |
| TextureTile | Returns the texture tile style for the specified fill. Read/write MsoTriState. |
| RotateWithObject | Returns or sets if the fill style should rotate with the object. Read/write MsoTriState. |
| TextureAlignment | Returns or sets the text alignment for the specified FillFormat object. Read/write. |
| TextureHorizontalScale | Returns or sets the value for horizontally scaling the text for the FillFormat object. Read/write Single. |
| TextureVerticalScale | Returns the texture vertical scale for the specified fill. Read/write Single. |
Filter
| Properties | Description |
|---|---|
| Count | Returns the number of objects in the collection. Read-only Long. |
Floor
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
| Thickness | Returns or sets a Long, specifying the thickness of the floor. Read/write. |
Font
| Properties | Description |
|---|---|
| ThemeColor | Returns or sets the theme color in the applied color scheme that is associated with the specified object. Read/write Variant. |
| ThemeFont | Returns or sets the theme font in the applied font scheme that is associated with the specified object. Read/write XlThemeFont. |
| TintAndShade | Returns or sets a Single that lightens or darkens a color. |
FormatCondition
| Properties | Description |
|---|---|
| NumberFormat | Returns or sets the number format applied to a cell if the conditional formatting rule evaluates to True. Read/write Variant. |
| Priority | Returns or sets the priority value of the conditional formatting rule. The priority determines the order of evaluation when multiple conditional formatting rules exist in a worksheet. |
| PTCondition | Returns a Boolean value indicating if the conditional format is being applied to a PivotTable chart. Read-only. |
| ScopeType | Returns or sets one of the constants of the XlPivotConditionScope enumeration, which determines the scope of the conditional format when it is applied to a PivotTable chart. |
| StopIfTrue | Returns or sets a Boolean value that determines if additional formatting rules on the cell should be evaluated if the current rule evaluates to True. |
| Text | Returns or sets a String value specifying the text string used by the conditional formatting rule. |
| TextOperator | Returns or sets one of the constants of the XlContainsOperator enumeration, specifying the text search performed by the conditional formatting rule. |
| AppliesTo | Returns a Range object specifying the cell range to which the formatting rule is applied. |
| DateOperator | Specifies the Date operator used in the format condition. Read/write. |
| Methods | Description |
|---|---|
| ModifyAppliesToRange | Sets the cell range to which this formatting rule applies. |
| SetFirstPriority | Sets the priority value for this conditional formatting rule to "1" so that it will be evaluated before all other rules on the worksheet. |
| SetLastPriority | Sets the evaluation order for this conditional formatting rule so it is evaluated after all other rules on the worksheet. |
FormatConditions
| Methods | Description |
|---|---|
| AddAboveAverage | Returns a new AboveAverage object representing a conditional formatting rule for the specified range. |
| AddColorScale | Returns a new ColorScale object representing a conditional formatting rule that uses gradations in cell colors to indicate relative differences in the values of cells included in a selected range. |
| AddDatabar | Returns a Databar object representing a data bar conditional formatting rule for the specified range. |
| AddIconSetCondition | Returns a new IconSetCondition object which represents an icon set conditional formatting rule for the specified range. |
| AddTop10 | Returns a Top10 object representing a conditional formatting rule for the specified range. |
| AddUniqueValues | Returns a new UniqueValues object representing a conditional formatting rule for the specified range. |
Gridlines
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
HiLoLines
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
Interior
| Properties | Description |
|---|---|
| Gradient | Returns or sets the Gradient property of an Interior object of a selection. Read-only |
| PatternThemeColor | Returns or sets a theme color pattern for an Interior object. Read/write Variant. |
| PatternTintAndShade | Returns or sets a tint and shade pattern for an Interior object. Read/write Variant. |
| ThemeColor | Returns or sets the theme color in the applied color scheme that is associated with the specified object. Read/write Variant. |
| TintAndShade | Returns or sets a Single that lightens or darkens a color. |
LeaderLines
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
Legend
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
| IncludeInLayout | True if a legend will occupy the chart layout space when a chart layout is being determined. The default value is True. Read/write Boolean. |
LegendEntry
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
LegendKey
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
| PictureUnit2 | Returns or sets the unit for each picture on the chart if the PictureType property is set to xlStackScale (if not, this property is ignored). Read/write Double. |
ListColumn
| Properties | Description |
|---|---|
| DataBodyRange | Returns a Range object that is the size of the data portion of a column. Read-only. |
| Total | Returns the Total row for a ListColumn object. Read-only. |
ListObject
| Properties | Description |
|---|---|
| ShowHeaders | Returns or sets if the header information should be displayed for the specified ListObject object. Read/write Boolean. |
| ShowTableStyleColumnStripes | Returns or sets if the Column Stripes table style is used for the specified ListObject object. Read/write Boolean. |
| ShowTableStyleFirstColumn | Returns or sets if the first column is displayed for the specified ListObject object. Read/write Boolean. |
| ShowTableStyleLastColumn | Returns or sets if the last column is displayed for the specified ListObject object. Read/write Boolean. |
| ShowTableStyleRowStripes | Returns or sets if the Row Stripes table style is used for the specified ListObject object. Read/write Boolean. |
| AutoFilter | Filters a list using the AutoFilter. Read-only. |
| Comment | Returns or sets the comment associated with the list object. Read/write String. |
| DisplayName | Returns or sets the display name for the specified ListObject object. Read/write String. |
| TableStyle | Gets or sets the table style for the specified ListObject object. Read/write Variant. |
| Sort | Gets or sets the sort column or columns, and sort order for the ListObject collection. |
| Methods | Description |
|---|---|
| ExportToVisio | Exports a ListObject object to Visio. |
Name
| Properties | Description |
|---|---|
| ValidWorkbookParameter | Returns True if the specified Name object is a valid workbook parameter. Read-only Boolen. |
| Comment | Returns or sets the comment associated with the name. Read/write String. |
| WorkbookParameter |
PageSetup
| Properties | Description |
|---|---|
| AlignMarginsHeaderFooter | Returns True for Excel to align the header and the footer with the margins set in the page setup options. Read/write Boolean. |
| ScaleWithDocHeaderFooter | Returns or sets if the header and footer should be scaled with the document when the size of the document changes. Read/write Boolean. |
| DifferentFirstPageHeaderFooter | True if a different header or footer is used on the first page. Read/write Boolean. |
| EvenPage | Returns or sets the alignment of text on the even page of a workbook or section. |
| FirstPage | Returns or sets the alignment of text on the first page of a workbook or section. |
| OddAndEvenPagesHeaderFooter | True if the specified PageSetup object has different headers and footers for odd-numbered and even-numbered pages. Read/write Boolean. |
| Pages | Returns or sets the the count or item number of the pages in Pages collection. |
Pane
| Methods | Description |
|---|---|
| PointsToScreenPixelsX | Returns or sets a pixel point on the screen. |
| PointsToScreenPixelsY | Returns or sets the location of the pixel on the screen. |
PivotCache
| Properties | Description |
|---|---|
| UpgradeOnRefresh | Contains information on whether to upgrade the PivotCache and all connected PivotTables on the next refresh. Read/write Boolean. |
| Version | Returns the version of Microsoft Excel in which the PivotCache was created. Read-only. |
| WorkbookConnection | Establishes a connection between the current workbook and the PivotCache object. Read-only. |
PivotCaches
| Methods | Description |
|---|---|
| Create | Creates a new PivotCache. |
PivotCell
| Properties | Description |
|---|---|
| PivotColumnLine | Returns the PivotLine on a column for a specific PivotCell object. Read-only PivotLine. |
| PivotRowLine | Returns the PivotLine on a row for a specific PivotCell object. Read-only PivotLine. |
PivotField
| Properties | Description |
|---|---|
| AllItemsVisible | Used to retrieve a Boolean value that indicates whether or not any manual filtering is applied to the PivotField. Read-only. |
| LayoutCompactRow | Specifies whether or not a PivotField is compacted (items of multiple PivotFields are displayed in a single column) when rows are selected. Read/write Boolean. |
| UseMemberPropertyAsCaption | This property is used to control whether member property captions are used for PivotItem captions of the PivotField. Read/write Boolean. |
| AutoSortCustomSubtotal | Returns the name of the custom subtotal used to sort the specified PivotTable field automatically. Read-only. |
| AutoSortPivotLine | Returns the name of the PivotLine used to sort the specified PivotTable field automatically. Read-only. |
| DisplayAsCaption | This property is used to display member properties of PivotFields as captions. Read-only. |
| DisplayAsTooltip | This property is used to specify whether or not a specific member property PivotField is displayed in tooltips. Read/write Boolean. |
| DisplayInReport | This property is used to specify whether the specified member property PivotField is displayed in the PivotTable or not. Read/write Boolean. |
| EnableMultiplePageItems | Used for specifying whether or not check boxes are present in the filter drop-down list for fields in the page area. Read/write Boolean. |
| Hidden | This property is used to hide the individual levels of an OLAP hierarchy. Read/write Boolean. |
| IncludeNewItemsInFilter | This property allows developers to specify whether excluded or included items should be tracked when manual filtering is applied to the PivotField. Read/write Boolean. |
| MemberPropertyCaption | Setting the MemberPropertyCaption property controls which member property is used as caption for a given level. Read/write Boolean. |
| PivotFilters | Returns or sets the PivotFilters for the specified PivotField object. Read-only. |
| ShowDetail | Gets or sets whether the specified PivotField is showing detail. Read/write Boolean. |
| ShowingInAxis | Indicates if the PivotField is currently visible in the PivotTable or not. Read-only. |
| SourceCaption | The SourceCaption property is applicable only for OLAP PivotTables, and returns the original caption from the OLAP server for a PivotField. Read-only. |
| VisibleItemsList | Returns or sets a Variant specifying an array of strings that represent included items in a manual filter applied to a PivotField. Read/write. |
| Methods | Description |
|---|---|
| ClearAllFilters | Calling this method deletes all filters currently applied to the PivotField. This includes deleting all filters from the PivotFilters collection of the PivotField and removing any manual filtering applied to the PivotField as well. If the PivotField is in the Report Filter area, the item selected will be set to the default item. |
| ClearLabelFilters | This method deletes all label filters or all date filters in the PivotFilters collection of the PivotField. |
| ClearManualFilter | Provides an easy way to set the Visible property to True for all items of a PivotField in PivotTables, and to empty the HiddenItemsList and VisibleItemsList collections in OLAP PivotTables. |
| ClearValueFilters | Calling this method deletes all value filters in the PivotFilters collection of the PivotField. |
| DrillTo | The DrillTo method supports drilling to a specified PivotField from another PivotField. |
PivotItem
| Methods | Description |
|---|---|
| DrillTo | The DrillTo method supports drilling to a specified PivotField from a PivotItem. |
PivotTable
| Properties | Description |
|---|---|
| ShowTableStyleRowStripes | The ShowTableStyleRowStripes property displays banded rows in which even rows are formatted differently from odd rows. This makes PivotTables easier to read. Read/write Boolean. |
| SortUsingCustomLists | The SortUsingCustomLists property controls whether custom lists are used for sorting items of fields, both initially when the PivotField is initialized and the PivotItems are ordered by their captions; and later when the user applies a sort. Read/write Boolean. |
| ShowDrillIndicators | The ShowDrillIndicators property is used for toggling the display of drill indicators in the PivotTable. Read/write Boolean. |
| ShowTableStyleColumnHeaders | The ShowTableStyleColumnHeaders property is set to True if the coulmn headers should be displayed in the PivotTable. Read/write Boolean. |
| ShowTableStyleColumnStripes | The ShowTableStyleColumnStripes property displays banded columns in which even columns are formatted differently from odd columns. This makes PivotTables easier to read. Read/write Boolean. |
| ShowTableStyleLastColumn | |
| ShowTableStyleRowHeaders | The ShowTableStyleRowHeaders property is set to True if the row headers should be displayed in the PivotTable. Read/write Boolean. |
| TableStyle2 | The TableStyle2 property specifies the PivotTable style currently applied to the PivotTable. Read/write. |
| ActiveFilters | Indicates the currently active filter in the specified PivotTable. Read-only. |
| AllowMultipleFilters | Sets or retrieves a value that indicates whether a PivotField can have multiple filters applied to it at the same time. Read/write Boolean. |
| CompactLayoutColumnHeader | Specifies the caption that is displayed in the column header of a PivotTable when in compact row layout form. Read/write String. |
| CompactLayoutRowHeader | Specifies the caption that is displayed in the row header of a PivotTable when in compact row layout form. Read/write String. |
| CompactRowIndent | Returns or sets the indent increment for PivotItems when compact row layout form is turned on. Read/write. |
| DisplayContextTooltips | Controls whether or not tooltips are displayed for PivotTable cells. Read/write Boolean. |
| DisplayFieldCaptions | Controls whether or not filter buttons and PivotField captions for rows and columns are displayed in the grid. Read/write. |
| DisplayMemberPropertyTooltips | Controls whether or not to display member properties in tooltips. Read/write Boolean. |
| FieldListSortAscending | Controls the sort order of fields in the PivotTable Field List. When this property is set to True, the fields are sorted in ascending order. When it is set to False, the fields are sorted in data source order. Read/write. |
| InGridDropZones | This property is used to toggle in-grid drop zones for a PivotTable object. In some cases, it also affects the layout of the PivotTable. Read/write Boolean. |
| LayoutRowDefault | This property specifies the layout settings for PivotFields when they are added to the PivotTable for the first time. Read/write xlLayoutRowType. |
| Location | Gets or sets a String that represents the top-left cell in the body of the specified PivotTable. Read/write. |
| PivotColumnAxis | Returns a PivotAxis object representing the entire column axis. Read-only PivotAxis. |
| PivotRowAxis | Returns a PivotAxis object representing the entire row axis. Read-only PivotAxis. |
| PrintDrillIndicators | Specifies whether or not drill indicators are printed with the PivotTable. Read/write Boolean. |
| Methods | Description |
|---|---|
| RowAxisLayout | This method is used for simultaneously setting layout options for all existing PivotFields. |
| SubtotalLocation | This method changes the subtotal location for all existing PivotFields. Changing the subtotal location has an immediate visual effect only for fields in outline form, but it will be set for fields in tabular form as well. |
| ChangeConnection | Changes the connection of the specified PivotTable. |
| ChangePivotCache | Changes the PivotCache of the specified PivotTable. |
| ClearAllFilters | The ClearAllFilters method deletes all filters currently applied to the PivotTable. This includes deleting all filters in the PivotFilters collection of the PivotTable object, removing any manual filtering applied and setting all PivotFields in the Report Filter area to the default item. |
| ClearTable | The ClearTable method is used for clearing a PivotTable. Clearing PivotTables includes removing all the fields and deleting all filtering and sorting applied to the PivotTables. This method resets the PivotTable to the state it had right after it was created, before any fields were added to it. |
| ConvertToFormulas | The ConvertToFormulas method is new in Microsoft Office Excel 2007 and is used for converting a PivotTable to cube formulas. Read/write Boolean. |
PlotArea
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
| Position | Returns or sets the position of the plot area on the chart. Read/write XlChartElementPosition. |
Point
| Properties | Description |
|---|---|
| Has3DEffect | True if a point has a three-dimensional appearance. Read/write Boolean. |
| PictureUnit2 | Returns or sets the unit for each picture on the chart if the PictureType property is set to xlStackScale (if not, this property is ignored). Read/write Double. |
| Format | Returns the ChartFormat object. Read-only. |
QueryTable
| Properties | Description |
|---|---|
| Sort | Returns the sort criteria for the query table range. Read-only. |
| WorkbookConnection | Returns the WorkbookConnection object that the query table uses. Read-only. |
Range
| Properties | Description |
|---|---|
| MDX | Returns the MDX name for the specified Range object. Read-only String. |
| ServerActions | Specifies the actions that can be performed on the SharePoint server for a Range object. |
| CountLarge | Counts the largest value in a given range of values. Read-only Variant. |
| Methods | Description |
|---|---|
| RemoveDuplicates | Removes duplicate values from a range of values. |
| CalculateRowMajorOrder | Calculates a specfied range of cells. |
| ExportAsFixedFormat | Exports to a file of the specified format. |
Series
| Properties | Description |
|---|---|
| PictureUnit2 | Returns or sets the unit for each picture on the chart if the PictureType property is set to xlStackScale (if not, this property is ignored). Read/write Double. |
| Format | Returns the ChartFormat object. Read-only. |
SeriesLines
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
ShadowFormat
| Properties | Description |
|---|---|
| Blur | Returns or sets the degree of blurriness of the specified shadow. Read/write Single. |
| RotateWithShape | Returns or sets an MsoTriState that represents whether to rotate the shadow when rotating the shape. Read/write. |
| Size | Returns or sets the size of the specified shadow. Read/write Single. |
| Style | Returns or sets the style of the specified shadow. Read/write MsoShadowStyle. |
Shape
| Properties | Description |
|---|---|
| Chart | Returns a Chart object that represents the chart contained in the shape. Read-only. |
| Glow | Returns a GlowFormat object for a specified shape that contains glow formatting properties for the shape. Read-only. |
| HasChart | Returns whether a shape contains a chart. Read-only MsoTriState. |
| Reflection | Returns a ReflectionFormat object for a specified shape that contains reflection formatting properties for the shape. Read-only. |
| ShapeStyle | Returns or sets an MsoShapeStyleIndex that represents the shape style of shape range. Read/write. |
| SoftEdge | Returns a SoftEdgeFormat object for a specified shape that contains soft edge formatting properties for the shape. Read-only. |
| TextFrame2 | Returns a TextFrame2 object that contains text formatting for the specified shape. Read-only. |
| BackgroundStyle | Returns or sets the background style. Read/write MsoBackgroundStyleIndex. |
ShapeRange
| Properties | Description |
|---|---|
| BackgroundStyle | Returns or sets the background style. Read/write MsoBackgroundStyleIndex. |
| Glow | Returns a GlowFormat object for a specified shape range that contains glow formatting properties for the shape range. Read-only. |
| ShapeStyle | Returns or sets an MsoShapeStyleIndex that represents shape style of shape range. Read/write. |
| HasChart | Returns whether a shape range contains a chart. Read-only MsoTriState. |
| Reflection | Returns a ReflectionFormat object for a specified shape range that contains reflection formatting properties for the shape range. Read-only. |
| TextFrame2 | Returns a TextFrame2 object that contains text formatting for the specified shape range. Read-only. |
| Chart | Returns a Chart object that represents the chart contained in the shape range. Read-only. |
| SoftEdge | Returns a SoftEdgeFormat object for a specified shape range that contains soft edge formatting properties for the shape range. Read-only. |
Shapes
| Methods | Description |
|---|---|
| AddChart | Creates a chart at the specified location on the active sheet. |
Tab
| Properties | Description |
|---|---|
| ThemeColor | Returns or sets the theme color in the applied color scheme that is associated with the specified object. Read/write XlThemeColor. |
| TintAndShade | Returns or sets a Single that lightens or darkens a color. |
ThreeDFormat
| Properties | Description |
|---|---|
| BevelBottomDepth | Returns or sets the bottom depth when using the bevel effect on a ThreeDFormat object. Read/write Single. |
| BevelBottomInset | Returns or sets a value indicating whether the bottom insert bevel should be raised for a ThreeDFormat object. Read/write Single. |
| BevelBottomType | Returns or sets the bottom bevel type for a ThreeDFormat object. Read/write MsoBevelType. |
| BevelTopDepth | Returns or sets the top depth when using the bevel effect on a ThreeDFormat object. Read/write Single. |
| BevelTopInset | Returns or sets a value indicating whether the top insert bevel should be raised for a ThreeDFormat object. Read/write Single. |
| BevelTopType | Returns or sets the top Bevel type for a ThreeDFormat object. Read/write MsoBevelType. |
| ContourColor | Returns the contour color for a ThreeDFormat object. Read-only ColorFormat. |
| ContourWidth | Returns or sets the contour width for a ThreeDFormat object. Read/write Single. |
| ProjectText | Returns or sets the project text state for the specified ThreeDFormat object. Read/write MsoTriState. |
| FieldOfView | Returns or sets the angle at which a ThreeDFormat object can be viewed. Read/write Single. |
| LightAngle | Returns or sets the angel of the extrusion lights set on a ThreeDFormat object. Read/write Single. |
| PresetCamera | Returns or sets the extrusion preset camera for a ThreeDFormat object. Read-only MsoPresetCamera. |
| PresetLighting | Returns or sets the extrusion preset lighting for a ThreeDFormat object. Read-only MsoLightRigType. |
| RotationZ | Returns or sets the rotation of the extruded shape around the z-axis in degrees. Read/write Single. |
| Z | Returns the Z order of the specified ThreeDFormat object. Read/write Single. |
| Methods | Description |
|---|---|
| IncrementRotationHorizontal | Changes the rotation of the specified shape horizontally by the specified number of degrees. |
| IncrementRotationVertical | Changes the rotation of the specified shape vertically by the specified number of degrees. |
| IncrementRotationZ | Changes the rotation of the specified shape around the z-axis by the specified number of degrees. |
| SetPresetCamera | Sets the camera for the specified ThreeDFormat object. |
TickLabels
| Properties | Description |
|---|---|
| MultiLevel | Sets whether an axis is multilevel or not. Read/write Boolean. |
| Format | Returns the ChartFormat object. Read-only. |
Trendline
| Properties | Description |
|---|---|
| Backward2 | Returns or sets the number of periods (or units on a scatter chart) that the trendline extends backward. Read/write Double. |
| Format | Returns the ChartFormat object. Read-only. |
| Forward2 | Returns or sets the number of periods (or units on a scatter chart) that the trendline extends forward. Read/write Double. |
UpBars
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
Walls
| Properties | Description |
|---|---|
| Format | Returns the ChartFormat object. Read-only. |
| Thickness | Returns or sets a Long specifying the thickness of the wall. Read/write. |
Window
| Properties | Description |
|---|---|
| ActiveSheetView | Returns an object that represents the view of the active sheet in the specified window. Read-only. |
| AutoFilterDateGrouping | True if the auto filter for date grouping is currently displayed in the specified window. Read/write Boolean. |
| DisplayRuler | True if a ruler is displayed for the specified window. Read/write Boolean. |
| DisplayWhitespace | True if whitespace is displayed. Read/write Boolean. |
| SheetViews | Returns the SheetViews object for the specified window. Read-only. |
Workbook
| Properties | Description |
|---|---|
| ShowPivotChartActiveFields | This property controls the visibility of the PivotChart Filter Pane. Read/write Boolean. |
| Signatures | Returns the digital signatures for a workbook. Read-only. |
| TableStyles | Returns a TableStyles collection object for the current workbook that refers to the styles used in the current workbook. Read-only. |
| Theme | Returns the theme applied to the current workbook. Read-only. |
| Research | Returns a Research object that represents the research service for a workbook. Read-only. |
| ServerPolicy | Returns a ServerPolicy object that represents a policy specified for a workbook stored on a server running Office SharePoint Server 2007. Read-only. |
| ServerViewableItems | Allows a developer to interact with the list of published objects in the workbook that are shown on the server. Read-only. |
| HasVBProject | Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Read-only Boolean. |
| IconSets | This property is used to filter data in a workbook based on a cell icon from the IconSet collection. Read-only. |
| EncryptionProvider | Returns a String specifying the name of the algorithm encryption provider that Microsoft Office Excel 2007 uses when encrypting documents. Read/write. |
| CheckCompatibility | Controls whether or not the compatibility checker is run automatically when the workbook is saved. Read/write Boolean. |
| Connections | The Connections property establishes a connection between the workbook and an ODBC or an OLEDB data source and refreshes the data without prompting the user. Read-only. |
| ConnectionsDisabled | |
| ContentTypeProperties | Returns a MetaProperties collection that describes the metadata stored in the workbook. Read-only. |
| CustomXMLParts | Returns a CustomXMLParts collection that represents the custom XML in the XML data store. Read-only. |
| DefaultPivotTableStyle | Specifies the table style from the TableStyles collection that is used as the default style for PivotTables. Read/write. |
| DefaultTableStyle | Specifies the table style from the TableStyles collection that is used as the default TableStyle. Read/write Variant. |
| DocumentInspectors | Returns a DocumentInspectors collection that represents the Document Inspector modules for the specified workbook. Read-only. |
| DoNotPromptForConvert | Returns or sets if the user should be prompted to convert the workbook if the workbook contains features that are not supported by versions of Excel earlier than Excel 2007. Read/write Boolean. |
| Excel8CompatibilityMode | The Excel8CompatibilityMode property provides developers with a way to check if the workbook is in compatibility mode. Read-only Boolean. |
| Final | Returns or sets a Boolean that indicates whether a workbook is final. Read/write Boolean. |
| ForceFullCalculation | Forces something. Read/write. |
| Methods | Description |
|---|---|
| RemoveDocumentInformation | Removes all information of the specified type from the workbook. |
| LockServerFile | Locks the workbook on the server to prevent modification. |
| ApplyTheme | Applies the specified theme to the current workbook. |
| EnableConnections | The EnableConnections method allows developers to programmatically enable data connections within the workbook for the user. |
| ExportAsFixedFormat | The ExportAsFixedFormat method is used to publish a workbook to either the PDF or XPS format. |
| GetWorkflowTasks | Returns the collection of WorkflowTask objects for the specified workbook. |
| GetWorkflowTemplates | Returns the collection of WorkflowTemplate objects for the specified workbook. |
| Events | Description |
|---|---|
| RowsetComplete | The event is raised when the user either drills through the recordset or invokes the rowset action on an OLAP PivotTable. |
Worksheet
| Properties | Description |
|---|---|
| Sort | Returns the sorted values in the current worksheet. Read-only. |
| EnableFormatConditionsCalculation | Returms or sets if conditional formats will will occur automatically as needed. Read/write Boolean. |
| Methods | Description |
|---|---|
| ExportAsFixedFormat | Exports to a file of the specified format. |
WorksheetFunction
| Methods | Description |
|---|---|
| Oct2Hex | Converts an octal number to hexadecimal. |
| OddFYield | Returns the yield of a security that has an odd (short or long) first period. |
| DollarDe | Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number. Use DOLLARDE to convert fractional dollar numbers, such as securities prices, to decimal numbers. |
| DollarFr | Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction. Use DOLLARFR to convert decimal numbers to fractional dollar numbers, such as securities prices. |
| BesselK | Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments. |
| BesselY | Returns the Bessel function, which is also called the Weber function or the Neumann function. |
| Duration | Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of the cash flows and is used as a measure of a bond price's response to changes in yield. |
| Oct2Bin | Converts an octal number to binary. |
| Oct2Dec | Converts an octal number to decimal. |
| WeekNum | Returns a number that indicates where the week falls numerically within a year. |
| WorkDay | Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed. |
| Xirr | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function. |
| Xnpv | Returns the net present value for a schedule of cash flows that is not necessarily periodic. Read/write Double. |
| YearFrac | Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. |
| YieldDisc | Returns the annual yield for a discounted security. |
| YieldMat | Returns the annual yield of a security that pays interest at maturity. |
| Received | Returns the amount received at maturity for a fully invested security. |
| SeriesSum | Returns the sum of a power series based on the formula:.gif "Equation") |
| SqrtPi | Returns the square root of (number * pi). |
| SumIfs | Adds the cells in a range that meet multiple criteria. |
| TBillEq | Returns the bond-equivalent yield for a Treasury bill. |
| TBillPrice | Returns the price per $100 face value for a Treasury bill. |
| TBillYield | Returns the yield for a Treasury bill. |
| MultiNomial | Returns the ratio of the factorial of a sum of values to the product of factorials. |
| NetworkDays | Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term. |
| Nominal | Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year. |
| MRound | Returns a number rounded to the desired multiple. |
| OddLPrice | Returns the price per $100 face value of a security having an odd (short or long) last coupon period. |
| OddLYield | Returns the yield of a security that has an odd (short or long) last period. |
| Price | Returns the price per $100 face value of a security that pays periodic interest. |
| PriceDisc | Returns the price per $100 face value of a discounted security. |
| PriceMat | Returns the price per $100 face value of a security that pays interest at maturity. |
| Quotient | Returns the integer portion of a division. Use this function when you want to discard the remainder of a division. |
| RandBetween | Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated. |
| Hex2Bin | Converts a hexadecimal number to binary. |
| Hex2Dec | Converts a hexadecimal number to decimal. |
| ImProduct | Returns the product of 2 to 29 complex numbers in x + yi or x + yj text format. |
| ImReal | Returns the real coefficient of a complex number in x + yi or x + yj text format. |
| ImSin | Returns the sine of a complex number in x + yi or x + yj text format. |
| ImSqrt | Returns the square root of a complex number in x + yi or x + yj text format. |
| ImSub | Returns the difference of two complex numbers in x + yi or x + yj text format. |
| ImSum | Returns the sum of two or more complex numbers in x + yi or x + yj text format. |
| IntRate | Returns the interest rate for a fully invested security. |
| IsEven | Checks the type of value and returns TRUE or FALSE depending if the value is even. |
| IsOdd | Checks the type of value and returns TRUE or FALSE depending if the value is odd. |
| Lcm | Returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments number1, number2, and so on. Use LCM to add fractions with different denominators. |
| Dec2Bin | Converts a decimal number to binary. |
| Dec2Hex | Converts a decimal number to hexadecimal. |
| Dec2Oct | Converts a decimal number to octal. |
| Delta | Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise. |
| EDate | Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. |
| Effect | Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. |
| EoMonth | Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. |
| FactDouble | Returns the double factorial of a number. |
| FVSchedule | Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate. |
| Gcd | Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder. |
| GeStep | Returns 1 if number ≥ step; returns 0 (zero) otherwise. Use this function to filter a set of values. For example, by summing several GESTEP functions you calculate the count of values that exceed a threshold. |
| Hex2Oct | Converts a hexadecimal number to octal. |
| IfError | Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula. |
| ImAbs | Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format. |
| Imaginary | Returns the imaginary coefficient of a complex number in x + yi or x + yj text format. |
| ImArgument | Returns the argument .gif "Theta") (theta), an angle expressed in radians, such that: .gif "Equation") |
| ImConjugate | Returns the complex conjugate of a complex number in x + yi or x + yj text format. |
| ImCos | Returns the cosine of a complex number in x + yi or x + yj text format. |
| ImDiv | Returns the quotient of two complex numbers in x + yi or x + yj text format. |
| ImExp | Returns the exponential of a complex number in x + yi or x + yj text format. |
| ImLn | Returns the natural logarithm of a complex number in x + yi or x + yj text format. |
| ImLog10 | Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format. |
| ImLog2 | Returns the base-2 logarithm of a complex number in x + yi or x + yj text format. |
| ImPower | Returns a complex number in x + yi or x + yj text format raised to a power. |
| MDuration | Returns the modified Macauley duration for a security with an assumed par value of $100. |
| OddFPrice | Returns the price per $100 face value of a security having an odd (short or long) first period. |
| AccrInt | Returns the accrued interest for a security that pays periodic interest. |
| Convert | Converts a number from one measurement system to another. For example, Convert can translate a table of distances in miles to a table of distances in kilometers. |
| Disc | Returns the discount rate for a security. |
| AverageIf | Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. |
| Bin2Dec | Converts a binary number to decimal. |
| Bin2Hex | Converts a binary number to hexadecimal. |
| Bin2Oct | Converts a binary number to octal. |
| Complex | Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj. |
| CountIfs | Counts the number of cells within a range that meet multiple criteria. |
| CoupDayBs | Returns the number of days from the beginning of the coupon period to the settlement date. |
| CoupDays | Returns the number of days in the coupon period that contains the settlement date. |
| CoupDaysNc | Returns the number of days from the settlement date to the next coupon date. |
| CoupNcd | Returns a number that represents the next coupon date after the settlement date. |
| CoupNum | Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon. |
| CoupPcd | |
| CumIPmt | Returns the cumulative interest paid on a loan between start_period and end_period. |
| CumPrinc | Returns the cumulative principal paid on a loan between start_period and end_period. |
| AmorDegrc | Returns the depreciation for each accounting period. This function is provided for the French accounting system. |
| AmorLinc | Returns the depreciation for each accounting period. This function is provided for the French accounting system. |
| AverageIfs | Returns the average (arithmetic mean) of all cells that meet multiple criteria. |
| BesselI | Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments. |
| BesselJ | Returns the Bessel function. |
| AccrIntM | Returns the accrued interest for a security that pays interest at maturity. |
| Erf | Returns the error function integrated between lower_limit and upper_limit. |
| ErfC | Returns the complementary ERF function integrated between the specified parameter and infinity. |
XmlMap
| Properties | Description |
|---|---|
| WorkbookConnection | Retuns a new connection for the specified XMLMap object. Read-only. |
New Constants
The following constants have been added to existing enumerations in Microsoft Office Excel 2007.
| Enumeration | New Constants |
|---|---|
| XlAutoFilterOperator | xlFilterAutomaticFontColor xlFilterCellColor xlFilterDynamic xlFilterFontColor xlFilterIcon xlFilterNoFill xlFilterNoIcon xlFilterValues |
| XlBuiltInDialog | xlDialogDocumentInspector xlDialogNameManager xlDialogNewName |
| XlClipboardFormat | xlClipboardFormatBIFF12 |
| XlErrorChecks | xlInconsistentListFormula |
| XlFileFormat | xlAddIn8 xlExcel12 xlExcel8 xlOpenXMLAddIn xlOpenXMLTemplate xlOpenXMLTemplateMacroEnabled xlOpenXMLWorkbook xlOpenXMLWorkbookMacroEnabled xlTemplate8 xlWorkbookDefault |
| XlFormatConditionType | xlAboveAverageCondition xlBlanksCondition xlColorScale xlDatabar xlErrorsCondition xlIconSets xlNoBlanksCondition xlNoErrorsCondition xlTextString xlTimePeriod xlTop10 xlUniqueValues |
| XlImportDataAs | xlTable |
| XlLegendPosition | xlLegendPositionCustom |
| XlListObjectSourceType | xlSrcQuery |
| XlPasteType | xlPasteAllUsingSourceTheme |
| XlPattern | xlPatternLinearGradient xlPatternRectangularGradient |
| XlPivotTableMissingItems | xlMissingItemsMax2 |
| XlPivotTableVersionList | xlPivotTableVersion11 xlPivotTableVersion12 |
| XlTotalsCalculation | xlTotalsCalculationCustom |
| XlWindowView | xlPageLayoutView |