Vectors on Charts

[Wyvern]

Hi,

I'm trying to figure out how to present information on a line whilst additionally showing the movement in two values over time. Say Income and Growth (on the X and Y axis). Additionally I want to show the direction of the movment that indicates its vector over the two points of observation.

For example on product A, income and growth on the 1/jan/05 was 5% and -1% respectively, and on the 1/jan/06 was 7% and 0% respectively. So I'd like a single line segment and an arrow vector showing the direction of movement from Jan to Dec.

Thanks

[Ed Ferrero]

Hi Wyvern,

Sub connect_points_with_arrows()
' routine to connect chart points with arrows
' works for series 1 in an embedded chart object

Dim i As Integer
Dim Pnt1_x, Pnt1_y, Pnt2_x, Pnt2_y As Long
Dim ch_height As Long

ActiveSheet.ChartObjects(1).Activate

With ActiveChart
ch_height = .ChartArea.Height
For i = 1 To .SeriesCollection(1).Points.Count - 1
' use excel 4 macro to determine chart point coordinates
' notice that the y coordinate axis is reversed in Excel 4
' therefore we need to subtract from the chart height

Pnt1_x = ExecuteExcel4Macro("get.chart.item(1,1, ""S1P" & i & """)")
Pnt1_y = ch_height - ExecuteExcel4Macro("get.chart.item(2,1,""S1P" & i &
""")")
Pnt2_x = ExecuteExcel4Macro("get.chart.item(1,1,""S1P" & i + 1 & """)")
Pnt2_y = ch_height - ExecuteExcel4Macro("get.chart.item(2,1,""S1P" & i +
1 & """)")

' ready to add the arrows now

With ActiveChart.Shapes.AddLine(Pnt1_x, Pnt1_y, Pnt2_x, Pnt2_y).Line
.EndArrowheadStyle = msoArrowheadTriangle
.EndArrowheadLength = msoArrowheadLengthMedium
.EndArrowheadWidth = msoArrowheadWidthMedium
End With

Next
End With

End Sub

Ed Ferrero
http://edferrero.m6.net/