Search code examples
excelvbadrawing

How to draw wedge shape with outline in Excel VBA?


I want to draw a sector of a circle (i.e. a wedge shape) using VBA in Excel (the desktop version from Office 365 ProPlus). The code below gives me the right shape, but only has an outline on the curved section:

Dim myArc as Shape
Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100)
With myArc
.Adjustments(1) = 0
.Adjustments(2) = 45
.Fill.Visible = msoTrue
End With

The straight edges don't have an outline, only the curve. How can I get an outline all the way around the shape? Should I be using a different shape to msoShapeArc? I've tried changing the line colour, weight and visibility using the code below, but it only affects the curved edge, not the straight ones.

With myArc
.Line.ForeColor.RGB = RGB(0, 0, 0)
.Line.Weight = 1
.Line.Visible = msoTrue
End With

I've been able to find documentation on general shape properties, but not which properties apply to which type of shape, and how they actually work to control its appearance.


Solution

  • According to https://learn.microsoft.com/en-us/office/vba/api/excel.adjustments:

    Because each adjustable shape has a different set of adjustments, the best way to verify the adjustment behavior for a specific shape is to manually create an instance of the shape, make adjustments with the macro recorder turned on, and then examine the recorded code.

    This worked for me to create a pie wedge:

    Sub WedgeTest()
        Dim ws As Worksheet, shp As Shape
        Set ws = ActiveSheet
        
        Set shp = ws.Shapes.AddShape(msoShapePie, 50, 50, 100, 100)
        With shp
            .Adjustments.Item(1) = -90 'degrees from 3 oclock
            .Adjustments.Item(2) = -45 'degrees from 3 oclock
            .Fill.Visible = msoFalse
            With .Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0
            End With
        End With
    End Sub
    

    Note the degrees from 3 o'clock are positive (clockwise) or negative (anticlockwise) but can't go beyond an absolute value of 180, so a clockwise +190 degrees would be set as -170 (anticlockwise).