ID: Q152403
The information in this article applies to:
Microsoft Excel versions 3.0 and 4.0 have three macro functions that allow for the automatic positioning and sizing of chart items. These functions are GET.CHART.ITEM, FORMAT.MOVE, and FORMAT.SIZE.
These functions may assist in positioning a graphical object, such as an arrow, so that the object will be moved if the values in a data series are changed.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/refguide/
The following are example macros which will automatically position an arrow so that it will point from the upper-right corner of the leftmost column, to the upper-left corner of the second column. This principal may be applied to other arrows or graphical objects. There are no Visual Basic for Applications equivalents for the GET.CHART.ITEM commands; therefore, you must use the Visual Basic for Applications method ExecuteExcel4Macro.
The method mentioned above will not work with a 3-D chart. For additional information, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q93730
TITLE : XL5: GET.CHART.ITEM() Returns Unexpected Results with 3-D
Chart.
1. Type the following code into a module sheet:
Sub MoveArrows()
' Activate chart.
ActiveSheet.DrawingObjects("Chart 1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
' Selects Series 1 Point 1.
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
' Gets the upper-right x point of P1.
xs1p1 = ExecuteExcel4Macro("get.chart.item(1,3,""s1p1"")")
' Gets the upper-right y point of P1.
ys1p1 = ExecuteExcel4Macro("get.chart.item(2,3,""s1p1"")")
' Gets the upper-left x point of P2.
xs1p2 = ExecuteExcel4Macro("get.chart.item(1,1,""s1p2"")")
' Gets the upper-left y point of P2.
ys1p2 = ExecuteExcel4Macro("get.chart.item(2,1,""s1p2"")")
' Selects line 1, and then formats and moves the line.
ActiveChart.DrawingObjects("Line 1").Select
With Selection
.Left = xs1p1
.Top = xs1p2
.Width = (xs1p2 - xs1p1)
.Height = (ys1p2 - ys1p1)
End With
' Selects Series 1 Point 2.
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(2).Select
' Gets the upper-right x point of P1.
xs1p1 = ExecuteExcel4Macro("get.chart.item(1,3,""s1p2"")")
' Gets the upper-right y point of P1.
ys1p1 = ExecuteExcel4Macro("get.chart.item(2,3,""s1p2"")")
' Gets the upper-left x point of P2.
xs1p2 = ExecuteExcel4Macro("get.chart.item(1,1,""s1p3"")")
' Gets the upper-left y point of P2.
ys1p2 = ExecuteExcel4Macro("get.chart.item(2,1,""s1p3"")")
' Selects line 2, and then formats and moves the line.
ActiveChart.DrawingObjects("Line 2").Select
With Selection
.Left = xs1p1
.Top = xs1p2
.Width = (xs1p2 - xs1p1)
.Height = (ys1p2 - ys1p1)
End With
End Sub
2. In a spreadsheet, type the following:
A1: JAN B1: 2.5
A2: FEB B2: 6.5
A3: MAR B3: 4.0
3. Select the Range A1:B3.
4. On the Insert menu, click Chart, and then click On This Sheet.
5. While holding down the left mouse button, drag the pointer to form the
chart frame. When you have the frame size you want, release the left
mouse button.
6. Click Next.
7. Click the Finish button.
8. Click the Drawing button on the toolbar, and on the Drawing toolbar,
click the ARROW button. Place the arrow pointing from the top of the
JAN column to the top of the FEB column.
9. On the Drawing toolbar, click the ARROW button. Place the arrow
pointing from the top of the FEB column to the top of the MAR column.
Note that the arrows do not move.
1. Type the following code on a Microsoft Excel 4.0 macro sheet:
A1: MoveArrow
A2: =SELECT("Chart 1")
A3: =ACTIVATE("Sheet1.xls Chart 1")
A4: xs1p1=GET.CHART.ITEM(1,3,"s1p1")
A5: ys1p1=GET.CHART.ITEM(2,3,"s1p1")
A6: xs1p2=GET.CHART.ITEM(1,1,"s1p2")
A7: ys1p2=GET.CHART.ITEM(2,1,"s1p2")
A8: =SELECT("arrow 1")
A9: =FORMAT.MOVE(xs1p1,ys1p1)
A10: =FORMAT.SIZE(xs1p2-xs1p1,ys1p2-ys1p1)
A11: xs1p1=GET.CHART.ITEM(1,3,"s1p2")
A12: ys1p1=GET.CHART.ITEM(2,3,"s1p2")
A13: xs1p2=GET.CHART.ITEM(1,1,"s1p3")
A14: ys1p2=GET.CHART.ITEM(2,1,"s1p3")
A15: =SELECT("arrow 2")
A16: =FORMAT.MOVE(xs1p1,ys1p1)
A17: =FORMAT.SIZE(xs1p2-xs1p1,ys1p2-ys1p1)
A18: =RETURN()
Explanation of macro code:
A1: Macro name
A2: Selects Chart
A3: Activates the chart
A4: Gets upper-right X of P1
A5: Gets upper-right y of P1
A6: Gets upper-Left x of p2
A7: Gets upper-left y of P2
A8: Selects Arrow 1
A9: Moves Arrow 1
A10: Resizes Arrow 1
A11: Gets upper-right X of P2
A12: Gets upper-right y of P2
A13: Gets upper-Left x of p3
A14: Gets upper-left y of P3
A15: Selects Arrow 2
A16: Moves Arrow 2
A17: Resizes Arrow 2
A18: Ends the macro
2. In a spreadsheet, type the following:
A1: JAN B1: 2.5
A2: FEB B2: 6.5
A3: MAR B3: 4.0
3. Save the File as "Sheet1.xls" (without the quotation marks).
4. Select the Range A1:B3.
5. Click the Chart Wizard button.
6. While holding down the left mouse button, drag the pointer to form the
chart frame. When you have the frame size you want, release the mouse
button.
7. Click Next.
8. Click the Finish button.
9. Double-click the chart. On the Chart menu, click Add Arrow.
Place the arrow pointing from the top of the JAN column to the
top of the FEB column.
pointing from the top of the FEB column to the top of the MAR column.
Note that the arrows do not move.
Additional query words: 3.00 3.00a 4.00 4.00a 5.00 5.00a 5.00c 7.00
Keywords : kbcode kbprg PgmHowto
Version : WINDOWS:3.0,4.0,5.0,5.0c,7.0,7.0a; MACINTOSH:3.0,4.0,5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbhowtoLast Reviewed: May 18, 1999