The information in this article applies to:
- Microsoft Excel for Windows 95, version 7.0
- Microsoft Excel for Windows, versions 3.0, 4.0, 5.0, 5.0c
- Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
SUMMARY
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.
MORE INFORMATION
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 engineers 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/default.asp
In Microsoft Excel, a graphical object, such as an arrow, can be added to a
chart as unattached text. If the values in a data series are changed, when
the chart is updated, the unattached text will not move. This is by design.
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.
Microsoft Excel versions 5.0 and 7.0
- 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
- In a spreadsheet, type the following:
A1: JAN B1: 2.5
A2: FEB B2: 6.5
A3: MAR B3: 4.0
- Select the Range A1:B3.
- On the Insert menu, click Chart, and then click On This Sheet.
- 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.
- Click Next.
- Click the Finish button.
- 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.
- 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.
- Change the value in cell B1 to 5 and the value in cell B2 to 1.
Note that the arrows do not move.
- Run the MoveArrow macro to reposition the arrows.
Microsoft Excel versions 3.0 and 4.0
- 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
- In a spreadsheet, type the following:
A1: JAN B1: 2.5
A2: FEB B2: 6.5
A3: MAR B3: 4.0
- Save the File as "Sheet1.xls" (without the quotation marks).
- Select the Range A1:B3.
- Click the Chart Wizard button.
- 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.
- Click Next.
- Click the Finish button.
- 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.
- On the Chart menu, click Add Arrow. Place the second arrow
pointing from the top of the FEB column to the top of the MAR column.
- Change the value in cell B1 to 5 and the value in cell B2 to 1.
Note that the arrows do not move.
- Run the MoveArrow macro to reposition the arrows.
|