Excel - Drawing vectors in Excel charts?

Asked By Zerex71 on 01-May-07 03:20 PM
```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 replied on 01-May-07 05:38 PM
```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 replied on 01-May-07 05:49 PM
```(Bit better explanation)

Hi Mike

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

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 replied on 01-May-07 06:20 PM
```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 & """)")