I am using Excel 2007, and need to get the vertices (endpoints) of a line.
Neither the Vertices nor Nodes methods of the Shape object work for a line
(as stated in their Help). In an earlier version, I used a workaround of
inserting a dummy second node in the line, and Excel automatically converted
the line to a freeform polygon, and then the Vertices method worked. That
workaround no longer works. I also tried setting the Type property of the
line to msoFreeform, and get an error. The only information I seem to be
able to get is the Left, Right, Width and Height (bounding rectangle), so I
don't know whether the line has a negative or positive slope (goes from top
left to bottom right, or the reverse). Any suggestions are greatly
appreciated.

This works in XL2002. Can't say about XL2007:

Private Sub CommandButton1_Click()
Dim ShpIt As Shape
Dim i As Long
Dim NodePoint As Variant

Set ShpIt = Worksheets(2).Shapes("Line 3")

With ShpIt
For i = 1 To .Nodes.Count
NodePoint = .Nodes(i).Points
Debug.Print NodePoint(1, 1), NodePoint(1, 2)
Next
End With

End Sub

NickHK

"broderick" wrote in message
...
I am using Excel 2007, and need to get the vertices (endpoints) of a line.
Neither the Vertices nor Nodes methods of the Shape object work for a line
(as stated in their Help). In an earlier version, I used a workaround of
inserting a dummy second node in the line, and Excel automatically
converted
the line to a freeform polygon, and then the Vertices method worked. That
workaround no longer works. I also tried setting the Type property of the
line to msoFreeform, and get an error. The only information I seem to be
able to get is the Left, Right, Width and Height (bounding rectangle), so
I
don't know whether the line has a negative or positive slope (goes from
top
left to bottom right, or the reverse). Any suggestions are greatly
appreciated.

Hi NickHK,

Thanks very much for the post. Unfortunately, this code did work in 2002,
but the Nodes property is no longer valid for a line in 2007. The
Nodes.Count is 0, and if I try to get the .Nodes.Points, it gives the error
"The index into the specfied collection is out of bounds." The Insert and
Item methods of the lines Nodes also don't work. I get a similar error when
trying to access the vertices. And I haven't found a way to convert the
line/connector to a freeform shape so those methods are valid.

Broderick


"NickHK" wrote:

This works in XL2002. Can't say about XL2007:

Private Sub CommandButton1_Click()
Dim ShpIt As Shape
Dim i As Long
Dim NodePoint As Variant

Set ShpIt = Worksheets(2).Shapes("Line 3")

With ShpIt
For i = 1 To .Nodes.Count
NodePoint = .Nodes(i).Points
Debug.Print NodePoint(1, 1), NodePoint(1, 2)
Next
End With

End Sub

NickHK

"broderick" wrote in message
...
I am using Excel 2007, and need to get the vertices (endpoints) of a line.
Neither the Vertices nor Nodes methods of the Shape object work for a line
(as stated in their Help). In an earlier version, I used a workaround of
inserting a dummy second node in the line, and Excel automatically
converted
the line to a freeform polygon, and then the Vertices method worked. That
workaround no longer works. I also tried setting the Type property of the
line to msoFreeform, and get an error. The only information I seem to be
able to get is the Left, Right, Width and Height (bounding rectangle), so
I
don't know whether the line has a negative or positive slope (goes from
top
left to bottom right, or the reverse). Any suggestions are greatly
appreciated.

Ok, here's an update with a partial fix. There is a new Rotation property
for objects. For lines with a gentle slope (low "rise" compared to the
"run"), the Rotation is 0 degrees and the Left, Top, Width and Height are
accurate as is (but I still don't know the direction of the slope or the
endpoints). For those with a steep slope, the Rotation is either 90 degrees
or 270 degrees (depending on whether the line slope is positive or negative).
So, I can determine for some lines where the endpoints are. However, for
those lines, the Left, Top, Width and Height are rotated around the X and Y
center of the object. It just adds an extra calculation, but it can be done.

By the way, it turns out that the Nodes and Vertices of a freeform polygon
are no longer absolute in 2007 -- they are relative. So, in order to get the
exact node positions, you have to loop through all the nodes to find out the
min and max values, then get the Left, Top, Width and Height of the polygon,
and then scale the nodes by the size of the object. Quite a pain, if you ask
me.