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 & """)")

' 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