Item Method

Microsoft Excel Visual Basic

previous page next page

Show All

Item Method

Item method as it applies to the Axes object.

Returns a single Axis object from an Axes collection.

expression.Item(Type, AxisGroup)

expression Required. An expression that returns an Axes collection.

Type Required XlAxisType. The axis type.

XlAxisType can be one of these XlAxisType constants.

xlCategory

xlSeriesAxis Valid only for 3-D charts.

xlValue

AxisGroup Optional XlAxisGroup. The axis.

XlAxisGroup can be one of these XlAxisGroup constants.

xlPrimary default

xlSecondary

Item method as it applies to the Names object.

Returns a single Name object from a Names collection.

expression.Item(Index, IndexLocal, RefersTo)

expression Required. An expression that returns a Names collection.

Index Optional Variant. The name or number of the defined name to be returned.

IndexLocal Optional Variant. The name of the defined name, in the language of the user. No names will be translated if you use this argument.

RefersTo Optional Variant. What the name refers to. You use this argument to identify a name by what it refers to.

Remarks

You must specify one, and only one, of these three arguments.

Item method as it applies to the Comments, ODBCErrors, OLEDBErrors and Points objects.

Returns a single object from a collection.

expression.Item(Index)

expression Required. An expression that returns one of the above objects.

Index Required Long. The index number for the object.

Item method as it applies to all other objects in the Applies To list.

Returns a single object from a collection.

expression.Item(Index)

expression Required. An expression that returns all other objects in the Applies To list.

Index Required Variant. The name or index number for the object.

Remarks

The text name of the object is the value of the Name and Value properties. For an Online Analytical Processing (OLAP) data source, the value is equal to the value of the SourceName property, and for other data sources, the value is equal to the value of the Caption property.

Example

As it applies to the Axes object.

This example sets the title text for the category axis on Chart1.

With Charts("chart1").Axes.Item(xlCategory)
    .HasTitle = True
    .AxisTitle.Caption = "1994"
End With

As it applies to the CalculatedFields object.

This example sets the formula for calculated field one.

Worksheets(1).PivotTables(1).CalculatedFields.Item(1) _
    .Formula = "=Revenue - Cost"

As it applies to the CalculatedItems and PivotItemList objects.

This example hides calculated item one.

Worksheets(1).PivotTables(1).PivotFields("year") _
    .CalculatedItems.Item(1).Visible = False

As it applies to the CanvasShapes, GroupShapes, and ShapeRange objects.

This example sets the OnAction property for shape two in a shape range. If the sr variable doesn’t represent a ShapeRange object, this example fails.

Dim sr As Shape
sr.Item(2).OnAction = "ShapeAction"

As it applies to the ChartGroups object.

This example adds drop lines to chart group one on chart sheet one.

Charts(1).ChartGroups.Item(1).HasDropLines = True

As it applies to the ChartObjects object.

This example activates embedded chart one.

Worksheets("sheet1").ChartObjects.Item(1).Activate

As it applies to the Comments object.

This example hides comment two.

Worksheets(1).Comments.Item(2).Visible = False

As it applies to the CustomViews object.

This example includes print settings in the custom view named Current Inventory.

ThisWorkbook.CustomViews.Item("Current Inventory") _
    .PrintSettings = True

As it applies to the DataLabels object.

This example sets the number format for the fifth data label in series one in embedded chart one on worksheet one.

Worksheets(1).ChartObjects(1).Chart _
    .SeriesCollection(1).DataLabels.Item(5).NumberFormat = "0.000"

As it applies to the FormatConditions object.

This example sets format properties for an existing conditional format for cells E1:E10.

With Worksheets(1).Range("e1:e10").FormatConditions.Item(1)
    With .Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 6
    End With
End With

As it applies to the LegendEntries object.

This example changes the font for the text of the legend entry at the top of the legend (this is usually the legend for series one) in embedded chart one on Sheet1.

Worksheets("sheet1").ChartObjects(1).Chart _
    .Legend.LegendEntries.Item(1).Font.Italic = True

As it applies to the Names object.

This example deletes the name mySortRange from the active workbook.

ActiveWorkbook.Names.Item("mySortRange").Delete

As it applies to the ODBCErrors object.

This example displays an ODBC error.

Set er = Application.ODBCErrors.Item(1)
MsgBox "The following error occurred:" &
    er.ErrorString & " : " & er.SqlState

As it applies to the OLEDBErrors object.

This example displays an OLE DB error.

Set objEr = Application.OLEDBErrors.Item(1)
MsgBox "The following error occurred:" & _
    objEr.ErrorString & " : " & objEr.SqlState

As it applies to the OLEObjects object.

This example deletes OLE object one from Sheet1.

Worksheets("sheet1").OLEObjects.Item(1).Delete

As it applies to the Parameters object.

This example modifies the parameter prompt string.

With Worksheets(1).QueryTables(1).Parameters.Item(1)
    .SetParam xlPrompt, "Please " & .PromptString
End With

As it applies to the PivotCaches object.

This example refreshes cache one.

ActiveWorkbook.PivotCaches.Item(1).Refresh

As it applies to the PivotFields object.

This example makes the Year field a row field in the first PivotTable report on Sheet3.

Worksheets("sheet3").PivotTables(1) _
    .PivotFields.Item("year").Orientation = xlRowField

As it applies to the PivotFormulas object.

This example displays the first formula for PivotTable one on worksheet one.

MsgBox Worksheets(1).PivotTables(1).PivotFormulas.Item(1).Formula

As it applies to the PivotItems object.

This example hides the 1998 item in the first PivotTable report on Sheet3.

Worksheets("sheet3").PivotTables(1) _
    .PivotFields("year").PivotItems.Item("1998").Visible = False

As it applies to the PivotTables object.

This example makes the Year field a row field in the first PivotTable report on Sheet3.

Worksheets("sheet3").PivotTables.Item(1) _
    .PivotFields("year").Orientation = xlRowField

As it applies to the Points object.

This example sets the marker style for the third point in series one in embedded chart one on worksheet one. The specified series must be a 2-D line, scatter, or radar series.

Worksheets(1).ChartObjects(1).Chart. _
    SeriesCollection(1).Points.Item(3).MarkerStyle = xlDiamond

As it applies to the QueryTables object.

This example sets a query table so that formulas to the right of the query table are automatically updated whenever it’s refreshed.

Sheets("sheet1").QueryTables.Item(1).FillAdjacentFormulas = True

As it applies to the Scenarios object.

This example shows the scenario named Typical on the worksheet named Options.

Worksheets("options").Scenarios.Item("typical").Show

As it applies to the SeriesCollection object.

This example sets the number of units that the trendline on Chart1 extends forward and backward. The example should be run on a 2-D column chart that contains a single series with a trendline.

With Charts("Chart1").SeriesCollection.Item(1).Trendlines.Item(1)
    .Forward = 5
    .Backward = .5
End With

As it applies to the Shapes object.

This example sets the OnAction property for shape two in a Shapes collection. If the ss variable doesn’t represent a Shapes object, this example fails.

Dim ss As Shape
ss.Item(2).OnAction = "ShapeAction"

As it applies to the Trendlines object.

This example sets the number of units that the trendline on Chart1 extends forward and backward. The example should be run on a 2-D column chart that contains a single series with a trendline.

With Charts("Chart1").SeriesCollection(1).Trendlines.Item(1)
    .Forward = 5
    .Backward = .5
End With

previous page start next page