how to determine a freeform shape is an open or closed contour?

Question

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 :)

score 0 · Answer 1 · answered Jul 22 '22 at 06:33

I ended up with this function:

Private Function hasClosedCountour(shp As Shape) As Boolean
    Dim firstPoint As Variant
    Dim lastPoint As Variant
    
    firstPoint = shp.Nodes(1).Points
    lastPoint = shp.Nodes(shp.Nodes.Count).Points
    
    If firstPoint(1, 1) = lastPoint(1, 1) And firstPoint(1, 2) = lastPoint(1, 2) Then
        hasClosedCountour = True
    Else
        hasClosedCountour = False
    End If
End 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."

how to determine a freeform shape is an open or closed contour?

1 Answers1