Adds a new add-in file to the list of add-ins. Returns an AddIn object.
expression.Add(FileName, CopyFile)
expression Required. An expression that returns an AddIns object.
Filename Required String. The name of the file that contains the add-in you want to add to the list in the add-in manager.
CopyFile Optional Variant. Ignored if the add-in file is on a hard disk. True to copy the add-in to your hard disk, if the add-in is on a removable medium (a floppy disk or compact disc). False to have the add-in remain on the removable medium. If this argument is omitted, Microsoft Excel displays a dialog box and asks you to choose.
Remarks
This method doesn't install the new add-in. You must set the Installed property to install the add-in.
Add method as it applies to the AllowEditRanges object.
Adds a range that can be edited on a protected worksheet. Returns a AllowEditRange object.
expression.Add(Title, Range, Password)
expression Required. An expression that returns an AllowEditRanges object.
Title Required String. The title of range.
Range Required Range object. The range allowed to be edited.
Password Optional Variant. The password for the range.
Add method as it applies to the CalculatedFields object.
Creates a new calculated field. Returns a PivotField object.
expression.Add(Name, Formula, UseStandardFormula)
expression Required. An expression that returns a CalculatedFields object.
Name Required String. The name of the field.
Formula Required String. The formula for the field.
UseStandardFormula Optional Variant. False (default) for upward compatibility. True for strings contained in any arguments that are field names, will be interpreted as having been formatted in standard U.S. English instead of local settings.
Add method as it applies to the CalculatedItems object.
Creates a new calculated item. Returns a PivotItem object.
expression.Add(Name, Formula, UseStandardFormula)
expression Required. An expression that returns a CalculatedItems object.
Name Required String. The name of the item.
Formula Required String. The formula for the item.
UseStandardFormula Optional Variant. False (default) for upward compatibility. True for strings contained in any arguments that are item names, will be interpreted as having been formatted in standard U.S. English instead of local settings.
Add method as it applies to the CalculatedMembers object.
Adds a calculated field or calculated item to a PivotTable. Returns a CalculatedMember object.
expression.Add(Name, Formula, SolveOrder, Type)
expression Required. An expression that returns a CalculatedMembers object.
Name Required String. The name of the calculated member.
Formula Required String. The formula of the calculated member.
SolveOrder Optional Variant. The solve order for the calculated member.
Type Optional Variant. The type of calculated member.
Remarks
The Formula argument must have a valid MDX (Multidimensional Expression) syntax statement. The Name argument has to be acceptable to the Online Analytical Processing (OLAP) provider and the Type argument has to be defined.
If you set the Type argument of this method to xlCalculatedSet, then you must call the AddSet method to make the new field set visible in the PivotTable.
Add method as it applies to the ChartObjects object.
Creates a new embedded chart. Returns a ChartObject object.
expression.Add(Left, Top, Width, Height)
expression Required. An expression that returns a ChartObjects object.
Left ,TopRequired Double. The initial coordinates of the new object (in points), relative to the upper-left corner of cell A1 on a worksheet or to the upper-left corner of a chart.
Width ,HeightRequired Double. The initial size of the new object, in points.
Add method as it applies to the Charts object.
Creates a new chart sheet. Returns a Chart object.
expression.Add(Before, After, Count)
expression Required. An expression that returns a Charts object.
Before Optional Variant. An object that specifies the sheet before which the new sheet is added.
After Optional Variant. An object that specifies the sheet after which the new sheet is added.
Count Optional Variant. The number of sheets to be added. The default value is one.
Remarks
If Before and After are both omitted, the new chart is inserted before the active sheet.Add method as it applies to the CustomProperties object.
Adds custom property information. Returns a CustomProperty object.
expression.Add(Name, Value)
expression Required. An expression that returns a CustomProperties object.
Name Required String. The name of the custom property.
Value Required Variant. The value of the custom property.
Add method as it applies to the CustomViews object.
Creates a new custom view. Returns a CustomView object that represents the new view.
expression.Add(ViewName, PrintSettings, RowColSettings)
expression Required. An expression that returns a CustomViews object.
ViewName Required String. The name of the new view.
PrintSettings Optional Variant. True to include print settings in the custom view.
RowColSettings Optional Variant. True to include settings for hidden rows and columns (including filter information) in the custom view.
Add method as it applies to the FormatConditions object.
Adds a new conditional format. Returns a FormatCondition object that represents the new conditional format.
expression.Add(Type, Operator, Formula1, Formula2)
expression Required. An expression that returns a FormatConditions object.
Type Required XlFormatConditionType. Specifies whether the conditional format is based on a cell value or an expression.
| XlFormatConditionType can be one of these XlFormatConditionType constants. |
| xlCellValue The conditional format is based on a cell value. |
| xlExpression The conditional format is based on an expression. |
Operator Optional Variant. The conditional format operator. Can be one of the following XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual. If Type is xlExpression, the Operator argument is ignored.
Formula1 Optional Variant. The value or expression associated with the conditional format. Can be a constant value, a string value, a cell reference, or a formula.
Formula2 Optional Variant. The value or expression associated with the second part of the conditional format when Operator is xlBetween or xlNotBetween (otherwise, this argument is ignored). Can be a constant value, a string value, a cell reference, or a formula.
Remarks
You cannot define more than three conditional formats for a range. Use the Modify method to modify an existing conditional format, or use the Delete method to delete an existing format before adding a new one.
Add method as it applies to the HPageBreaks object.
Adds a horizontal page break. Returns an HPageBreak object.
expression.Add(Before)
expression Required. An expression that returns an HPageBreaks object.
Before Required Object. A Range object. The range above which the new page break will be added.
Add method as it applies to the Hyperlinks object.
Adds a hyperlink to the specified range or shape. Returns a Hyperlink object.
expression.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
expression Required. An expression that returns a Hyperlinks object.
Anchor Required Object. The anchor for the hyperlink. Can be either a Range or Shape object.
Address Required String. The address of the hyperlink.
SubAddress Optional Variant. The subaddress of the hyperlink.
ScreenTip Optional Variant. The screen tip to be displayed when the mouse pointer is paused over the hyperlink.
TextToDisplay Optional Variant. The text to be displayed for the hyperlink.
Remarks
When you specify the TextToDisplay argument, the text must be a string.
Add method as it applies to the ListColumns collection object.
Adds a new column to the list object. Returns a ListColumn object.
expression.Add(Position)
expression Required. An expression that returns a ListColumns object for the newly created column.
Position Optional Integer. Specifies the relative position of the new column that starts at 1. The previous column at this position is shifted outward.
Remarks
If Position is not specified, a new rightmost column is added. A name for the column is automatically generated. The name of the new column can be changed after the column is added.
Add method as it applies to the ListObjects collection object.
Creates a new list object. Returns a ListObject object.
expression.Add(SourceType, Source, LinkSource, HasHeaders, Destination)
expression Required. An expression that returns a ListObjects object.
SourceType Optional XlListObjectSourceType. Indicates the kind of source for the query. Can be one of the following XlListObjectSourceType constants: xlSrcExternal, or xlSrcRange. If omitted, the SourceType will default to xlSrcRange.
Source Optional when SourceType = xlSrcRange. A Range object representing the data source. If omitted, the Source will default to the range returned by list range detection code. Required when SourceType = xlSrcExternal. An array of String values specifying a connection to the source.
| Element# | Contents |
|---|---|
| 0 | URL to SharePoint Service |
| 1 | ListName |
| 2 | ViewGUID |
LinkSource Optional Boolean. Indicates whether an external data source is to be linked to the ListObject object. If SourceType is xlSrcExternal, default is True. Invalid if SourceType is xlSrcRange, and will return an error if not omitted.
HasHeaders Optional Variant. An XlYesNoGuess constant that indicates whether the data being imported has column labels. If the Source does not contain headers, Excel will automatically generate headers.
| HasHeaders can be one of these XlYesNoGuess constants. |
| xlGuess |
| xlNo |
| xlYes |
Destination Optional Variant. A Range object specifying a single-cell reference as the destination for the top-left corner of the new list object. If the Range object refers to more than one cell, an error is generated. The Destination argument must be specified when SourceType is set to xlSrcExternal. The Destination argument is ignored if SourceType is set to xlSrcRange. The destination range must be on the worksheet that contains the ListObjects collection specified by expression. New columns will be inserted at the Destination to fit the new list. Therefore, existing data will not be overwritten.
Remarks
When the list has headers, the first row of cells will be converted to Text, if not already set to text. The conversion will be based on the visible text for the cell. This means that if there is a date value with a Date format that changes with locale, the conversion to a list might produce different results depending on the current system locale. Moreover, if there are two cells in the header row that have the same visible text, an incremental Integer will be appended to make each column header unique.
Add method as it applies to the ListRows collection object.
Adds a new row to the list object. Returns a ListRow object.
expression.Add(Position)
expression Required. An expression that returns a ListRows object for the newly created row.
Position Optional Integer. Specifies the relative position of the new row.
Remarks
If Position is not specified, a new bottom row is added.
Add method as it applies to the Names object.
Defines a new name. Returns a Name object.
expression.Add(Name, RefersTo, Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1, RefersToR1C1Local)
expression Required. An expression that returns a Names object.
Name Optional Variant. Required if NameLocal isn't specified. The text to use as the name (in the language of the macro). Names cannot include spaces and cannot look like cell references.
RefersTo Optional Variant. Required unless one of the other RefersTo arguments is specified. Describes what the name refers to (in the language of the macro, using A1-style notation). Note Nothing is returned if the reference does not exist.
Visible Optional Variant. True to define the name normally. False to define the name as a hidden name (that is, it doesn't appear in either the Define Name, Paste Name, or Goto dialog box). The default value is True.
MacroType Optional Variant. The macro type, as shown in the following table.
| Value | Meaning |
|---|---|
| 1 | User-defined function (Function procedure) |
| 2 | Macro (also known as Sub procedure) |
| 3 or omitted | None (that is, the name doesn't refer to a user-defined function or macro) |
ShortcutKey Optional Variant. The macro shortcut key. Must be a single letter, such as "z" or "Z". Applies only for command macros.
Category Optional Variant. The category of the macro or function if MacroType is 1 or 2. The category is used in the Function Wizard. Existing categories can be referred to either by number (starting at 1) or by name (in the language of the macro). Microsoft Excel creates a new category if the specified category doesn't already exist.
NameLocal Optional Variant. Required if Name isn't specified. The text to use as the name (in the language of the user). Names cannot include spaces and cannot look like cell references.
RefersToLocal Optional Variant. Required unless one of the other RefersTo arguments is specified. Describes what the name refers to (in the language of the user, using A1-style notation).
CategoryLocal Optional Variant. Required if Category isn't specified. Text identifying the category of a custom function in the language of the user.
RefersToR1C1 Optional Variant. Required unless one of the other RefersTo arguments is specified. Describes what the name refers to (in the language of the macro, using R1C1-style notation).
RefersToR1C1Local Optional Variant. Required unless one of the other RefersTo arguments is specified. Describes what the name refers to (in the language of the user, using R1C1-style notation).
Add method as it applies to the OLEObjects object.
Adds a new OLE object to a sheet. Returns an OLEObject object.
expression.Add(ClassType, FileName, Link, DisplayAsIcon, IconFileName, IconIndex, IconLabel, Left, Top, Width, Height)
expression Required. An expression that returns an OLEObjects object.
ClassType Optional Variant. (you must specify either ClassType or FileName). A string that contains the programmatic identifier for the object to be created. If ClassType is specified, FileName and Link are ignored.
FileName Optional Variant. (you must specify either ClassType or FileName). A string that specifies the file to be used to create the OLE object.
Link Optional Variant. True to have the new OLE object based on FileName be linked to that file. If the object isn't linked, the object is created as a copy of the file. The default value is False.
DisplayAsIcon Optional Variant. True to display the new OLE object either as an icon or as its regular picture. If this argument is True, IconFileName and IconIndex can be used to specify an icon.
IconFileName Optional Variant. A string that specifies the file that contains the icon to be displayed. This argument is used only if DisplayAsIcon is True. If this argument isn't specified or the file contains no icons, the default icon for the OLE class is used.
IconIndex Optional Variant. The number of the icon in the icon file. This is used only if DisplayAsIcon is True and IconFileName refers to a valid file that contains icons. If an icon with the given index number doesn't exist in the file specified by IconFileName, the first icon in the file is used.
IconLabel Optional Variant. A string that specifies a label to display beneath the icon. This is used only if DisplayAsIcon is True. If this argument is omitted or is an empty string (""), no caption is displayed.
Left ,Top Optional Variant. The initial coordinates of the new object, in points, relative to the upper-left corner of cell A1 on a worksheet, or to the upper-left corner of a chart.
Width ,Height Optional Variant. The initial size of the new object, in points.
Add method as it applies to the Parameters object.
Creates a new query parameter. Returns a Parameter object.
expression.Add(Name, iDataType)
expression Required. An expression that returns a Parameters object.
Name Required String. The name of the specified parameter. The parameter name should match the parameter clause in the SQL statement.
iDataType Optional Variant. The data type of the parameter. Can be any XlParameterDataType constant:
| xlParamTypeBigInt
xlParamTypeBinary xlParamTypeBit xlParamTypeChar xlParamTypeDate xlParamTypeDecimal xlParamTypeDouble xlParamTypeFloat xlParamTypeInteger xlParamTypeLongVarBinary xlParamTypeWChar |
xlParamTypeNumeric
xlParamTypeLongVarChar xlParamTypeReal xlParamTypeSmallInt xlParamTypeTime xlParamTypeTimeStamp xlParamTypeTinyInt xlParamTypeUnknown xlParamTypeVarBinary xlParamTypeVarChar |
These values correspond to ODBC data types. They indicate the type of value the ODBC driver is expecting to receive. Microsoft Excel and the ODBC driver manager will coerce the parameter value given in Microsoft Excel into the correct data type for the driver.
Add method as it applies to the Phonetics object.
Adds phonetic text to the specified cellt.
expression.Add(Start, Length, Text)
expression Required. An expression that returns a Phonetics object.
Start Required Long. The position that represents the first character in the specified cell.
Length Required Long. The number of characters from the Start position to the end of the text in the cell.
Text Required String. Collectively, the characters that represent the phonetic text in the cell.
Add method as it applies to the PivotCaches object.
Adds a new PivotTable cache to a PivotCaches collection. Returns a PivotCache object.
expression.Add(SourceType, SourceData)
expression Required. An expression that returns a PivotCaches object.
SourceType Required XlPivotTableSourceType. The source of the PivotTable cache data.
| XlPivotTableSourceType can be one of these XlPivotTableSourceType constants. |
| xlConsolidation |
| xlDatabase |
| xlExternal |
| xlPivotTable |
| xlScenario |
SourceData Optional Variant. The data for the new PivotTable cache. This argument is required if SourceType isn't xlExternal. Can be a Range object, an array of ranges, or a text constant that represents the name of an existing PivotTable report. For an external database, this is a two-element array. The first element is the connection string specifying the provider of the data. The second element is the SQL query string used to get the data. If you specify this argument, you must also specify SourceType.
Remarks
If the PivotTable cache isn't referenced by a PivotTable object, the PivotTable cache is automatically deleted before the workbook is saved.
Add method as it applies to the PivotFormulas object.
Creates a new PivotTable formula. Returns a PivotFormula object.
expression.Add(Formula, UseStandardFormula)
expression Required. An expression that returns one of the above objects.
Formula Required String. The new PivotTable formula.
UseStandardFormula Optional Variant. A standard PivotTable formula.
Add method as it applies to the PivotItems object.
Creates a new PivotTable item.
expression.Add(Name)
expression Required. An expression that returns a PivotItems object.
Name Required String. The name of the new PivotTable item.
Add method as it applies to the PivotTables object.
Adds a new PivotTable report. Returns a PivotTable object.
expression.Add(PivotCache, TableDestination, TableName, ReadData, DefaultVersion)
expression Required. An expression that returns a PivotTables object.
PivotCache Required PivotCache. The PivotTable cache on which the new PivotTable report is based. The cache provides data for the report.
TableDestination Required Variant. The cell in the upper-left corner of the PivotTable report's destination range (the range on the worksheet where the resulting report will be placed). You must specify a destination range on the worksheet that contains the PivotTables object specified by expression .
TableName Optional Variant. The name of the new PivotTable report.
ReadData Optional Variant. True to create a PivotTable cache that contains all records from the external database; this cache can be very large. False to enable setting some of the fields as server-based page fields before the data is actually read.
DefaultVersion Optional Variant. The version of Microsoft Excel the PivotTable was originally created in.
Add method as it applies to the PublishObjects object.
Creates an object that represents an item in a document saved to a Web page. Such objects facilitate subsequent updates to the Web page while automated changes are being made to the document in Microsoft Excel. Returns a PublishObject object.
expression.Add(SourceType, FileName, Sheet, Source, HtmlType, DivID, Title)
expression Required. An expression that returns a PublishObjects object.
SourceType Required XlSourceType. The source type.
| XlSourceType can be one of these XlSourceType constants. Identifies the source object. |
| xlSourceAutoFilter An AutoFilter range. |
| xlSourceChart A chart. |
| xlSourcePivotTable A PivotTable report. |
| xlSourcePrintArea A range of cells selected for printing. |
| xlSourceQuery A query table (external data range). |
| xlSourceRange A range of cells. |
| xlSourceSheet An entire worksheet. |
| xlSourceWorkbook A workbook. |
FileName Required String. The URL (on the intranet or the Web) or path (local or network) to which the source object was saved.
Sheet Optional Variant. The name of the worksheet that was saved as a Web page.
Source Optional Variant. A unique name used to identify items that have one of the following constants as their SourceType argument: xlSourceAutoFilter, xlSourceChart, xlSourcePivotTable, xlSourcePrintArea, xlSourceQuery, or xlSourceRange. If SourceType is xlSourceRange, Source specifies a range, which can be a defined name. If SourceType is xlSourceChart, xlSourcePivotTable, or xlSourceQuery, Source specifies the name of a chart, PivotTable report, or query table.
HtmlType Optional Variant. Specifies whether the item is saved as an interactive Microsoft Office Web component or as static text and images. Can be one of the XlHTMLType constants listed in the following table.
| Constant | Description |
|---|---|
| xlSourceAutoFilter | An AutoFilter range |
| xlSourceChart | A chart |
| xlSourcePivotTable | A PivotTable report |
| xlSourcePrintArea | A range of cells selected for printing |
| xlSourceQuery | A query table (external data range) |
| xlSourceRange | A range of cells |
| xlSourceSheet | An entire worksheet |
DivID Optional Variant. The unique identifier used in the HTML DIV tag to identify the item on the Web page.
Title Optional Variant. The title of the Web page.
Add method as it applies to the QueryTables object.
Creates a new query table. Returns a QueryTable object that represents the new query table.
expression.Add(Connection, Destination, Sql)
expression Required. An expression that returns a QueryTables object.
Connection Required Variant. The data source for the query table. Can be one of the following:
- A string containing an OLE DB or ODBC connection string. The ODBC connection string has the form "ODBC;<connection string>".
- A QueryTable object from which the query information is initially copied, including the connection string and the SQL text, but not including the Destination range. Specifying a QueryTable object causes the Sql argument to be ignored.
- An ADO or DAO Recordset object. Data is read from the ADO or DAO recordset. Microsoft Excel retains the recordset until the query table is deleted or the connection is changed. The resulting query table cannot be edited.
- A Web query. A string in the form "URL;<url>", where "URL;" is required but not localized and the rest of the string is used for the URL of the Web query.
- Data Finder. A string in the form "FINDER;<data finder file path>" where "FINDER;" is required but not localized. The rest of the string is the path and file name of a Data Finder file (*.dqy or *.iqy). The file is read when the Add method is run; subsequent calls to the Connection property of the query table will return strings beginning with "ODBC;" or "URL;" as appropriate.
- A text file. A string in the form "TEXT;<text file path and name>", where TEXT is required but not localized.
Destination Required Range. The cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). The destination range must be on the worksheet that contains the QueryTables object specified by expression .
Sql Optional Variant. The SQL query string to be run on the ODBC data source. This argument is optional when you're using an ODBC data source (if you don't specify it here, you should set it by using the Sql property of the query table before the table is refreshed). You cannot use this argument when a QueryTable object, text file, or ADO or DAO Recordset object is specified as the data source.
Remarks
A query created by this method isn't run until the Refresh method is called.
Add method as it applies to the RecentFiles object.
Adds a file to the list of recently used files. Returns a RecentFile object.
expression.Add(Name)
expression Required. An expression that returns a RecentFiles object.
Name Required String. The file name.
Add method as it applies to the Scenarios object.
Creates a new scenario and adds it to the list of scenarios that are available for the current worksheet. Returns a Scenario object.
expression.Add(Name, ChangingCells, Values, Comment, Locked, Hidden)
expression Required. An expression that returns a Scenarios object.
Name Required String. The scenario name.
ChangingCells Required Variant. A Range object that refers to the changing cells for the scenario.
Values Optional Variant. An array that contains the scenario values for the cells in ChangingCells. If this argument is omitted, the scenario values are assumed to be the current values in the cells inChangingCells.
Comment Optional Variant. A string that specifies comment text for the scenario. When a new scenario is added, the author's name and date are automatically added at the beginning of the comment text.
Locked Optional Variant. True to lock the scenario to prevent changes. The default value is True.
Hidden Optional Variant. True to hide the scenario. The default value is False.
Remarks
A scenario name must be unique; Microsoft Excel generates an error if you try to create a scenario with a name that's already in use.
Add method as it applies to the SeriesCollection object.
Adds one or more new series to the SeriesCollection collection.
expression.Add(Source, Rowcol, SeriesLabels, CategoryLabels, Replace)
expression Required. An expression that returns a SeriesCollection object.
Source Required Variant. The new data, either as a Range object or an array of data points.
Rowcol Optional XlRowCol. Specifies whether the new values are in the rows or columns of the specified range.
| XlRowCol can be one of these XlRowCol constants. |
| xlColumns default |
| xlRows |
SeriesLabels Optional Variant. Ignored if Source is an array. True if the first row or column contains the name of the data series. False if the first row or column contains the first data point of the series. If this argument is omitted, Microsoft Excel attempts to determine the location of the series name from the contents of the first row or column.
CategoryLabels Optional Variant. Ignored if Source is an array. True if the first row or column contains the name of the category labels. False if the first row or column contains the first data point of the series. If this argument is omitted, Microsoft Excel attempts to determine the location of the category label from the contents of the first row or column.
Replace Optional Variant. If CategoryLabels is True and Replace is True, the specified categories replace the categories that currently exist for the series. If Replace is False, the existing categories will not be replaced. The default value is False.
Remarks
This method does not actually return a SeriesCollection object as stated in the Object Browser. This method is not available for PivotChart reports.
Add method as it applies to the Sheets and Worksheets objects.
Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet.
expression.Add(Before, After, Count, Type)
expression Required. An expression that returns one of the above objects.
Before Optional Variant. An object that specifies the sheet before which the new sheet is added.
After Optional Variant. An object that specifies the sheet after which the new sheet is added.
Count Optional Variant. The number of sheets to be added. The default value is one.
Type Optional Variant. Specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template. The default value is xlWorksheet.
Remarks
If Before and After are both omitted, the new sheet is inserted before the active sheet.
Add method as it applies to the SmartTags object.
Adds a smart tag. Returns a SmartTag object.
expression.Add(SmartTagType)
expression Required. An expression that returns a SmartTags object.
SmartTagType Required String. The type of smart tag.
Add method as it applies to the Styles object.
Creates a new style and adds it to the list of styles that are available for the current workbook. Returns a Style object.
expression.Add(Name, BasedOn)
expression Required. An expression that returns a Styles object.
Name Required String. The new style name.
BasedOn Optional Variant. A Range object that refers to a cell that's used as the basis for the new style. If this argument is omitted, the newly created style is based on the Normal style.
Remarks
If a style with the specified name already exists, this method redefines the existing style based on the cell specified in BasedOn. The following example redefines the Normal style based on the active cell.
ActiveWorkbook.Styles.Add Name := "Normal", BasedOn := ActiveCell
Add method as it applies to the Trendlines object.
Creates a new trendline. Returns a Trendline object.
expression.Add(Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)
expression Required. An expression that returns a Trendlines object.
Type Optional XlTrendlineType. The trendline type.
| XlTrendlineType can be one of these XlTrendlineType constants. |
| xlExponential |
| xlLinear default |
| xlLogarithmic |
| xlMovingAvg |
| xlPolynomial |
| xlPower |
Order Optional Variant. Optional Variant. Required if Type is xlPolynomial. The trendline order. Must be an integer from 2 to 6, inclusive.
Period Optional Variant. Required if Type is xlMovingAvg. The trendline period. Must be an integer greater than 1 and less than the number of data points in the series you're adding a trendline to.
Forward Optional Variant. The number of periods (or units on a scatter chart) that the trendline extends forward.
Backward Optional Variant. The number of periods (or units on a scatter chart) that the trendline extends backward.
Intercept Optional Variant. The trendline intercept. If this argument is omitted, the intercept is automatically set by the regression.
DisplayEquation Optional Variant. True to display the equation of the trendline on the chart (in the same data label as the R-squared value). The default value is False.
DisplayRSquared Optional Variant. True to display the R-squared value of the trendline on the chart (in the same data label as the equation). The default value is False.
Name Optional Variant. The name of the trendline as text. If this argument is omitted, Microsoft Excel generates a name.
Add method as it applies to the UserAccessList object.
Adds a user access list. Returns a UserAccess object.
expression.Add(Name, AllowEdit)
expression Required. An expression that returns a UserAccessList object.
Name Required String. The name of the user access list.
AllowEdit Required Boolean. True allows users on the access list to edit the editable ranges on a protected worksheet.
Add method as it applies to the Validation object.
Adds data validation to the specified range.
expression.Add(Type, AlertStyle, Operator, Formula1, Formula2)
expression Required. An expression that returns a Validation object.
Type Required XlDVType. The validation type.
| XlDVType can be one of these XlDVType constants. |
| xlValidateCustom |
| xlValidateDate |
| xlValidateDecimal |
| xlValidateInputOnly |
| xlValidateList |
| xlValidateTextLength |
| xlValidateTime |
| xlValidateWholeNumber |
AlertStyle Optional Variant. The validation alert style. Can be one of the following XlDVAlertStyle constants: xlValidAlertInformation, xlValidAlertStop, or xlValidAlertWarning.
Operator Optional Variant. The data validation operator. Can be one of the following XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual.
Formula1 Optional Variant. The first part of the data validation equation.
Formula2 Optional Variant. The second part of the data validation when Operator is xlBetween or xlNotBetween (otherwise, this argument is ignored).
Remarks
The Add method requires different arguments, depending on the validation type, as shown in the following table.
| Validation type | Arguments |
|---|---|
| xlValidateCustom | Formula1 is required, Formula2 is ignored. Formula1 must contain an expression that evaluates to True when data entry is valid and False when data entry is invalid. |
| xlInputOnly | AlertStyle, Formula1, or Formula2 are used. |
| xlValidateList | Formula1 is required, Formula2 is ignored. Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list. |
| xlValidateWholeNumber, xlValidateDate, xlValidateDecimal, xlValidateTextLength, or xlValidateTime | One of either Formula1 or Formula2 must be specified, or both may be specified. |
Add method as it applies to the VPageBreaks object.
Adds a vertical page break. Returns a VPageBreak object.
expression.Add(Before)
expression Required. An expression that returns a VPageBreaks object.
Before Required Object. A Range object. The range to the left of which the new page break will be added.
Add method as it applies to the Watches object.
Adds a range which is tracked when the worksheet is recalculated. Returns a Watch object.
expression.Add(Source)
expression Required. An expression that returns a Watches object.
Source Required Variant. The source for the range.
Add method as it applies to the Workbooks object.
Creates a new workbook. The new workbook becomes the active workbook. Returns a Workbook object.
expression.Add(Template)
expression Required. An expression that returns a Workbooks object.
Template Optional Variant. Determines how the new workbook is created. If this argument is a string specifying the name of an existing Microsoft Excel file, the new workbook is created with the specified file as a template. If this argument is a constant, the new workbook contains a single sheet of the specified type. Can be one of the following XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorksheet. If this argument is omitted, Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property).
Remarks
If the Template argument specifies a file, the file name can include a path.
Add method as it applies to the XmlMaps collection.
Note XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.
Adds an XML map to the specified workbook. Returns an XmlMap object.
expression.Add(Schema, RootElementName)
expression Required. An expression that returns a Workbook object.
Schema Required String. The schema to be added as an XML map. The string can be a path to a schema file, or the schema itself. The path can be specified in the Universal Naming Convention (UNC) or Uniform Resource Locator (URL) format.
RootElementName Optional String. The name of the root element. This argument can be ignored if the schema contains only one root element.
Example
As it applies to the AddIns object.
This example inserts the add-in Myaddin.xla from drive A. When you run this example, Microsoft Excel copies the file A:\Myaddin.xla to the Library folder on your hard disk and adds the add-in title to the list in the Add-Ins dialog box.
Sub UseAddIn()
Set myAddIn = AddIns.Add(Filename:="A:\MYADDIN.XLA", _
CopyFile:=True)
MsgBox myAddIn.Title & " has been added to the list"
End Sub
As it applies to the AllowEditRanges object.
This example allows edits to range "A1:A4" on the active worksheet, notifies the user, then changes the password for this specified range and notifies the user of this change.
Sub UseChangePassword()
Dim wksOne As Worksheet
Set wksOne = Application.ActiveSheet
' Protect the worksheet.
wksOne.Protect
' Establish a range that can allow edits
' on the protected worksheet.
wksOne.Protection.AllowEditRanges.Add _
Title:="Classified", _
Range:=Range("A1:A4"), _
Password:="secret"
MsgBox "Cells A1 to A4 can be edited on the protected worksheet."
' Change the password.
wksOne.Protection.AllowEditRanges(1).ChangePassword _
Password:="moresecret"
MsgBox "The password for these cells has been changed."
End Sub
As it applies to the CalculatedFields object.
This example adds a calculated field to the first PivotTable report on worksheet one.
Worksheets(1).PivotTables(1).CalculatedFields.Add "PxS", _
"= Product * Sales"
As it applies to the CalculatedMembers object.
The following example adds a set to a PivotTable, assuming a PivotTable exists on the active worksheet.
Sub UseAddSet()
Dim pvtOne As PivotTable
Dim strAdd As String
Dim strFormula As String
Dim cbfOne As CubeField
Set pvtOne = ActiveSheet.PivotTables(1)
strAdd = "[MySet]"
strFormula = "'{[Product].[All Products].[Food].children}'"
' Establish connection with data source if necessary.
If Not pvtOne.PivotCache.IsConnected Then pvtOne.PivotCache.MakeConnection
' Add a calculated member titled "[MySet]"
pvtOne.CalculatedMembers.Add Name:=strAdd, _
Formula:=strFormula, Type:=xlCalculatedSet
' Add a set to the CubeField object.
Set cbfOne = pvtOne.CubeFields.AddSet(Name:="[MySet]", _
Caption:="My Set")
End Sub
As it applies to the ChartObjects object.
This example creates a new embedded chart..
Set co = Sheets("Sheet1").ChartObjects.Add(50, 40, 200, 100)
co.Chart.ChartWizard Source:=Worksheets("Sheet1").Range("A1:B2"), _
Gallery:=xlColumn, Format:=6, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=0, HasLegend:=1
As it applies to the Charts object.
This example creates an empty chart sheet and inserts it before the last worksheet.
ActiveWorkbook.Charts.Add Before:=Worksheets(Worksheets.Count)
As it applies to the CustomProperties object.
This example adds identifier information to the active worksheet and returns the name and value to the user.
Sub CheckCustomProperties()
Dim wksSheet1 As Worksheet
Set wksSheet1 = Application.ActiveSheet
' Add metadata to worksheet.
wksSheet1.CustomProperties.Add _
Name:="Market", Value:="Nasdaq"
' Display metadata.
With wksSheet1.CustomProperties.Item(1)
MsgBox .Name & vbTab & .Value
End With
End Sub
As it applies to the CustomViews object.
This example creates a new custom view named "Summary" in the active workbook.
ActiveWorkbook.CustomViews.Add "Summary", True, True
As it applies to the FormatConditions object.
This example adds a conditional format to cells E1:E10.
With Worksheets(1).Range("e1:e10").FormatConditions _
.Add(xlCellValue, xlGreater, "=$a$1")
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 6
End With
With .Font
.Bold = True
.ColorIndex = 3
End With
End With
As it applies to the HPageBreaks object.
This example adds a horizontal page break above cell F25 and adds a vertical page break to the left of this cell.
With Worksheets(1)
.HPageBreaks.Add .Range("F25")
.VPageBreaks.Add .Range("F25")
End With
As it applies to the Hyperlinks object.
This example adds a hyperlink to cell A5.
With Worksheets(1)
.Hyperlinks.Add Anchor:=.Range("a5"), _
Address:="http://example.microsoft.com", _
ScreenTip:="Microsoft Web Site", _
TextToDisplay:="Microsoft"
End With
This example adds an email hyperlink to cell A5.
With Worksheets(1)
.Hyperlinks.Add Anchor:=.Range("a5"), _
Address:="mailto:[email protected]?subject=hello", _
ScreenTip:="Write us today", _
TextToDisplay:="Support"
End With
As it applies to the ListColumns collection object.
The following example adds a new column to the default ListObject object in the first worksheet of the workbook. Because no position is specified, a new rightmost column is added.
Set myNewColumn = ActiveWorkbook.Worksheets(1).ListObjects(1).ListColumns.AddNote A name for the column is automatically generated. You can choose to change the name after the column has been added.
As it applies to the ListObjects collection object.
The following example adds a new ListObject object based on data from a Microsoft Windows SharePoint Services site to the default ListObjects collection and places the list in cell A1 in the first worksheet of the workbook.
Note The following code example assumes that you will substitute a valid server name and the list guid in the variables strServerName and strListGUID. Additionally, the server name must be followed by "/_vti_bin" or the sample will not work.
Set objListObject = ActiveWorkbook.Worksheets(1).ListObjects.Add(SourceType:= xlSrcExternal, _
Source:= Array(strServerName, StrListGUID), TRUE, XlGuess, Destination:= Range("A1")
Note If there is existing data at cell A1, the existing list data will be moved to the right to accommodate the new list.
As it applies to the ListRows collection object.
The following example adds a new row to the default ListObject object in the first worksheet of the workbook. Because no position is specified, the new row is added to the bottom of the list.
Set myNewColumn = ActiveWorkbook.Worksheets(1).ListObject(1).ListRows.AddAs it applies to the Names object.
This example defines a new name for the range A1:D3 on Sheet1 in the active workbook. Note Nothing is returned if Sheet1 does not exist.
Sub MakeRange()
ActiveWorkbook.Names.Add _
Name:="tempRange", _
RefersTo:="=Sheet1!$A$1:$D$3"
End Sub
As it applies to the OLEObjects object.
This example creates a new Microsoft Word OLE object on Sheet1.
ActiveWorkbook.Worksheets("Sheet1").OLEObjects.Add _
ClassType:="Word.Document"
This example adds a command button to sheet one.
Worksheets(1).OLEObjects.Add ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=40, Top:=40, _
Width:=150, Height:=10
As it applies to the Parameters object.
This example changes the SQL statement for query table one. The clause "(city=?)" indicates that the query is a parameter query, and the value of city is set to the constant "Oakland."
Set qt = Sheets("sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM authors WHERE (city=?)"
Set param1 = qt.Parameters.Add("City Parameter", _
xlParamTypeVarChar)
param1.SetParam xlConstant, "Oakland"
qt.Refresh
As it applies to the Phonetics object.
This example adds three phonetic text strings to the active cell. The example then sets the character type to Hiragana, sets the font color to blue, and sets the text to visible.
ActiveCell.FormulaR1C1 = "
"
ActiveCell.Phonetics.Add Start:=1, Length:=3, Text:="
"
ActiveCell.Phonetics.Add Start:=4, Length:=3, Text:="
"
ActiveCell.Phonetics.CharacterType = xlHiragana
ActiveCell.Phonetics.Font.Color = vbBlue
ActiveCell.Phonetics.Visible = True
As it applies to the PivotCaches object.
This example creates a new PivotTable cache based on an OLAP provider and then it creates a new PivotTable report based on the cache, at cell A3 on the active worksheet.
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\perfdate\record.mdb"
End With
' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Select Speed, Pressure, Time From DynoRun"
.CommandType = adCmdText
.Execute
End With
' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:="Performance"
End With
With ActiveSheet.PivotTables("Performance")
.SmallGrid = False
With .PivotFields("Pressure")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Speed")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Time")
.Orientation = xlDataField
.Position = 1
End With
End With
' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing
As it applies to the PivotFormulas object.
This example creates a new PivotTable formula for the first PivotTable report on worksheet one.
Worksheets(1).PivotTables(1).PivotFormulas _
.Add "Year['1998'] Apples = (Year['1997'] Apples) * 2"
As it applies to the PivotItems object.
This example creates a new PivotTable item in the first PivotTable report on worksheet one.
Worksheets(1).PivotTables(1).PivotItems("Year").Add "1998"
As it applies to the PivotTables object.
This example creates a new PivotTable cache based on an OLAP provider, and then it creates a new PivotTable report based on the cache, at cell A1 on the first worksheet.
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\perfdate\record.mdb"
End With
' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Select Speed, Pressure, Time From DynoRun"
.CommandType = adCmdText
.Execute
End With
' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
' Create PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
ActiveSheet.PivotTables.Add _
PivotCache:=objPivotCache, _
TableDestination:=Range("A3"), _
TableName:="Performance"
With ActiveSheet.PivotTables("Performance")
.SmallGrid = False
With .PivotFields("Pressure")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Speed")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Time")
.Orientation = xlDataField
.Position = 1
End With
End With
' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing
As it applies to the PublishObjects object.
This example saves the range D5:D9 on the First Quarter worksheet in the active workbook to a Web page called Stockreport.htm. You use the Spreadsheet component to add interactivity to the Web page.
ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="\\Server2\Q1\Stockreport.htm", _
Sheet:="First Quarter", _
Source:="D5:D9", _
HTMLType:=xlHTMLCalc).Publish
As it applies to the QueryTables object.
This example creates a query table based on an ADO recordset. The example preserves the existing column sorting and filtering settings and layout information for backward compatibility.
Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Set cnnConnect = New ADODB.Connection
cnnConnect.Open "Provider=SQLOLEDB;" & _
"Data Source=srvdata;" & _
"User ID=testac;Password=4me2no;"
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
Source:="Select Name, Quantity, Price From Products", _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText
With ActiveSheet.QueryTables.Add( _
Connection:=rstRecordset, _
Destination:=Range("A1"))
.Name = "Contact List"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
This example imports a fixed width text file into a new query table. The first column in the text file is five characters wide and is imported as text. The second column is four characters wide and is skipped. The remainder of the text file is imported into the third column and has the General format applied to it.
Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables.Add( _
Connection := "TEXT;C:\My Documents\19980331.txt",
Destination := shFirstQtr.Cells(1,1))
With qtQtrResults
.TextFileParsingType = xlFixedWidth
.TextFileFixedColumnWidths := Array(5,4)
.TextFileColumnDataTypes := _
Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
.Refresh
End With
This example creates a new query table on the active worksheet.
sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
"ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("B1"), Sql:=sqlstring)
.Refresh
End With
As it applies to the RecentFiles object.
This example adds Oscar.xls to the list of recently used files.
Application.RecentFiles.Add Name:="Oscar.xls"
As it applies to the Scenarios object.
This example adds a new scenario to Sheet1.
Worksheets("Sheet1").Scenarios.Add Name:="Best Case", _
ChangingCells:=Worksheets("Sheet1").Range("A1:A4"), _
Values:=Array(23, 5, 6, 21), _
Comment:="Most favorable outcome."
As it applies to the SeriesCollection object.
This example creates a new series in Chart1. The data source for the new series is range B1:B10 on Sheet1.
Charts("Chart1").SeriesCollection.Add _
Source:=ActiveWorkbook.Worksheets("Sheet1").Range("B1:B10")
This example creates a new series on the embedded chart on Sheet1.
Worksheets("Sheet1").ChartObjects(1).Activate
ActiveChart.SeriesCollection.Add _
Source:=Worksheets("Sheet1").Range("B1:B10")
As it applies to the Sheets and WorkSheets objects.
This example inserts a new worksheet before the last worksheet in the active workbook.
ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
As it applies to the SmartTags object.
This example adds a smart tag titled MSFT to cell A1, then adds extra metadata called Market with the value of Nasdaq to the smart tag and then returns the value of the property to the user. This example assumes the host system is connected to the Internet.
Sub UseProperties()
Dim strLink As String
Dim strType As String
' Define smart tag variables.
strLink = "urn:schemas-microsoft-com:smarttags#stocktickerSymbol"
strType = "stockview"
Range("A1").Formula = "MSFT"
' Add a property for MSFT smart tag and define its value.
Range("A1").SmartTags.Add(strLink).Properties.Add _
Name:="Market", Value:="Nasdaq"
' Notify the user of the smart tag's value.
MsgBox Range("A1").SmartTags.Add(strLink).Properties("Market").Value
End Sub
As it applies to the Styles object.
This example defines a new style based on cell A1 on Sheet1.
With ActiveWorkbook.Styles.Add(Name:="theNewStyle")
.IncludeNumber = False
.IncludeFont = True
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = False
.IncludeProtection = False
.Font.Name = "Arial"
.Font.Size = 18
End With
As it applies to the Trendlines object.
This example creates a new linear trendline in Chart1.
ActiveWorkbook.Charts("Chart1").SeriesCollection(1).Trendlines.Add
As it applies to the Validation object.
This example adds data validation to cell E5.
With Range("e5").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:= xlValidAlertStop, _
Operator:=xlBetween, Formula1:="5", Formula2:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With
As it applies to the VPageBreaks object.
This example adds a horizontal page break above cell F25 and adds a vertical page break to the left of this cell.
With Worksheets(1)
.HPageBreaks.Add .Range("F25")
.VPageBreaks.Add .Range("F25")
End With
As it applies to the Watches object.
This example creates a summation formula in cell A3 and then adds this cell to the watch facility.
Sub AddWatch()
With Application
.Range("A1").Formula = 1
.Range("A2").Formula = 2
.Range("A3").Formula = "=Sum(A1:A2)"
.Range("A3").Select
.Watches.Add Source:=ActiveCell
End With
End Sub
As it applies to the WorkBooks object.
This example creates a new workbook.
Workbooks.Add