Drawing vectors in Excel charts?

[Zerex71]

Group,

Is there any way I can draw a vector in 2D or 3D (basically a line
segment with an arrowhead) from one point in a 2D or 3D chart to
another point, driven by a set of (x,y) or (x,y,z) that the user can
modify? The user can change the values in the (x,y) or (x,y,z) cells
and the vector(s) should redraw accordingly.

Mike

 

[F.H. van Zelm]

Hi Mike

Is this what you mean, basically? Probably not.
Create a XY-chart (Scatter) based on these data:

X-value Building
0 0
6 0
6 6
3 10
0 6
0 0
6 6
0 6
6 0 Chimney
4 8.7
4 10
5 10
5 7.3 Door
0.5 0
0.5 2.75
2 2.75
2 0

and you'll have a 'vector driven' dwelling ;-).

Frans

 

[F.H. van Zelm]

(Bit better explanation)

Hi Mike

Is this what you mean, basically? Probably not.
Create a XY-chart (Scatter) based on these data:

#row A B C D
1 X-value Building
2 0 0
3 6 0
4 6 6
5 3 10
6 0 6
7 0 0
(these two reference row 2, same column)
8 6 6
(these two reference row 4, same column)
9 0 6
(these two reference row 6, same column)
10 6 0 Chimney (these two
reference row 3, same column)
11 4 8.7
12 4 10
13 5 10
14 5 7.3 Door
15 0.5 0
16 0.5 2.75
17 2 2.75
18 2 0

and you'll have a 'vector driven' dwelling ;-).

Frans

 

[Ed Ferrero]

Hi Mike,

You can do this with a little VBA.

The following works with Excel 2000/2003 (not 2007) For 2D charts only.

Sub connect_points_with_arrows()
' routine to connect chart points with arrows
' works for series 1 in an embedded chart object
' Ed Ferrero http://www.edferrero.com

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
www.edferrero.com