Microsoft's macrofun.hlp file documents the XLM function GET.CHART.ITEM, which is useful for finding the vertical and horizontal positions for selected chart items. This function is further explained in Professional Excel Development.

From Microsoft's macrofun.hlp, below is the documentation for GET.CHART.ITEM:

GET.CHART.ITEM returns the vertical or horizontal position of a point on a chart item. Use these position numbers with FORMAT.MOVE and FORMAT.SIZE to change the position and size of chart items. Position is measured in points; a point is 1/72nd of an inch.

Syntax

GET.CHART.ITEM(x_y_index, point_index, item_text)

X_Y_index is a number specifying which of the coordinates you want returned.

X_Y_index Coordinate returned:

• 1 - Horizontal coordinate.
• 2 - Vertical coordinate.

Point_index is a number specifying the point on the chart item. These indexes are described later. If point_index is omitted, it is assumed to be 1. If the specified item is a point, point_index must be 1. If the specified item is any line other than a data line, use the following values for point_index.

Point_index and Chart item position:

• 1 - Lower or left.
• 2 - Upper or right.

If the selected item is a legend, plot area, chart area, or an area in an area chart, use the following values for point_index.

Point_index and Chart item position:

• 1 - Upper left.
• 2 - Upper middle.
• 3 - Upper right.
• 4 - Right middle.
• 5 - Lower right.
• 6 - Lower middle.
• 7 - Lower left.
• 8 - Left middle.

If the selected item is an arrow in Microsoft Excel 4.0, use the following values for point_index. In Microsoft Excel 5.0, arrows are named lines, and the arrowhead position returned is equivalent to the end of a line where the arrowhead begins.

Point_index and Chart item position:

• 1 - Arrow shaft.
• 2 - Arrowhead.

If the selected item is a pie slice, use the following values for point_index.

Point_index and Chart item position:

• 1 - Outermost counterclockwise point.
• 2 - Outer center point.
• 3 - Outermost clockwise point.
• 4 - Midpoint of the most clockwise radius.
• 5 - Center point.
• 6 - Midpoint of the most counterclockwise radius.

Item_text is a selection code that specifies which item of a chart to select. See the chart form of SELECT for the item_text codes to use for each item of a chart.

If item_text is omitted, it is assumed to be the currently selected item.

If item_text is omitted and no item is selected, GET.CHART.ITEM returns the #VALUE! error value.

Remarks

If the specified item does not exist, or if a chart is not active when the function is carried out, the #VALUE! error value is returned.

Examples

The following macro formulas return the horizontal and vertical locations, respectively, of the top of the main-chart value axis:

GET.CHART.ITEM(1, 2, "Axis 1")

GET.CHART.ITEM(2, 2, "Axis 1")

You could then use FORMAT.MOVE to move a floating text item to the position returned by these two formulas.

Have you tried this in Excel 2007 yet? I haven’t, because I’ve never really used it much.

Hi Jon,

I’ve not tested it in Excel 2007 yet as I’ve really not used it very much either.  I do think it’s an interesting function though.  I think it’s a good charting function to at least be aware of in case the need ever does occur.   

John Mansfield

In Professional Excel Development, Stephen Bullen shows how to use it to determine where an arrow’s endpoint should be to touch the corner of a column in a column chart. I use VBA that calculates the coordinates using the number of categories, the number of series, the gap width and overlap. It feels somehow “nicer” to use VBA, but the XLM is so much faster.