It is possible to create shapes with open or closed contour by using freefrom tools in excel (“Freeform: Shape” or “Freeform: Scribble”). “Open” or “Closed”, their Type is “msoFreefrom” and I could not find any property to distinguish between them by reading the value.
Excel somehow distinguish them. When I click one of the shape “Format” tab appear and its content changes according to shape’s contour. (open contour shape like line, closed contour shape like square).
I want to distinguish them in vba. I manage this by causing error in vba. For close contour shape if you try to set line arrow it throws an error. (Please see code below) Is there a better way?
Sub Try01()
ActiveWindow.DisplayGridlines = False
Dim ws As Worksheet
Set ws = ActiveSheet
Dim ds As Shape
For Each ds In ws.Shapes
ds.Delete
Next ds
Dim myShape As Shape, myShape2 As Shape
' Open contour shape
With ws.Shapes.BuildFreeform(msoEditingAuto, 100, 100)
.AddNodes msoSegmentLine, msoEditingAuto, 200, 100
.AddNodes msoSegmentLine, msoEditingAuto, 200, 200
.AddNodes msoSegmentLine, msoEditingAuto, 100, 200
'.AddNodes msoSegmentLine, msoEditingAuto, 100, 100
Set myShape = .ConvertToShape
End With
myShape.Name = "MyL"
'Closed contoru shape
With ws.Shapes.BuildFreeform(msoEditingAuto, 300, 100)
.AddNodes msoSegmentLine, msoEditingAuto, 400, 100
.AddNodes msoSegmentLine, msoEditingAuto, 400, 200
.AddNodes msoSegmentLine, msoEditingAuto, 300, 200
.AddNodes msoSegmentLine, msoEditingAuto, 300, 100
Set myShape2 = .ConvertToShape
End With
myShape2.Name = "MyS"
'You can set styles no error
myShape.ShapeStyle = myShape2.ShapeStyle
Debug.Print myShape.Line.BeginArrowheadStyle
'Open contour you can change line arrow type
myShape.Line.BeginArrowheadStyle = msoArrowheadDiamond
Debug.Print myShape2.Line.BeginArrowheadStyle
'But for closed contour you can not
myShape2.Line.BeginArrowheadStyle = msoArrowheadDiamond
End Sub
Thank you for your help :)
I ended up with this function:
Its first and last nodes have the same coordinates if a shape has a closed contour. But be careful. If you have a freeForm shape composed of merging two closed, not interacting shapes, it will be detected as "not closed."