初始测试步骤 #

• 首先在Excel中运行以下VBA宏：

Sub Macro1()

'Delete all shapes if exists
For i = ActiveSheet.Shapes.Count To 1 Step -1
    ActiveSheet.Shapes(i).Delete
Next i

'Add a Rectangle
With ActiveSheet.Shapes.AddShape(Type:=msoShapeRectangle, Left:=20, Top:=20, Width:=200, Height:=120)
    .Name = "myRectangle"
End With

'Make some formatting to the myRectangle
With ActiveSheet.Shapes("myRectangle")
   .Line.Visible = msoTrue
   .Line.ForeColor.RGB = vbBlue
   .Line.Weight = 5
   .Fill.UserPicture "https://upload.wikimedia.org/wikipedia/en/b/ba/Flag_of_Germany.svg"
End With

End Sub

• 运行后会生成一个蓝色粗边框矩形，德国国旗图片填满整个矩形区域，图片与边框无间距。

需求说明 #

需要修改宏，实现矩形边框与填充的国旗图片之间存在边距——即蓝色边框内侧留有空白区域，国旗图片仅在空白区域内显示。

实现方案 #

由于Shape的Fill.UserPicture无法直接设置内边距，我们采用嵌套形状的方式实现：外层为带边框的大矩形，内层为填充图片的小矩形，通过调整内层矩形的位置和尺寸来控制边距大小。

修改后的宏代码如下：

Sub MacroWithPadding()
    Dim outerShape As Shape
    Dim innerShape As Shape
    Dim padding As Integer
    
    '设置边距大小（可根据需求调整）
    padding = 10
    
    '删除现有所有形状
    For i = ActiveSheet.Shapes.Count To 1 Step -1
        ActiveSheet.Shapes(i).Delete
    Next i
    
    '添加外层带边框的矩形
    Set outerShape = ActiveSheet.Shapes.AddShape( _
        Type:=msoShapeRectangle, Left:=20, Top:=20, Width:=200, Height:=120)
    With outerShape
        .Name = "outerRectangle"
        .Line.Visible = msoTrue
        .Line.ForeColor.RGB = vbBlue
        .Line.Weight = 5
        .Fill.Visible = msoFalse '外层仅保留边框，无填充
    End With
    
    '添加内层填充图片的矩形
    Set innerShape = ActiveSheet.Shapes.AddShape( _
        Type:=msoShapeRectangle, _
        Left:=outerShape.Left + padding, _
        Top:=outerShape.Top + padding, _
        Width:=outerShape.Width - 2 * padding, _
        Height:=outerShape.Height - 2 * padding)
    With innerShape
        .Name = "innerPicture"
        .Line.Visible = msoFalse '内层隐藏边框
        .Fill.UserPicture "https://upload.wikimedia.org/wikipedia/en/b/ba/Flag_of_Germany.svg"
        '将内层形状置于外层下方，确保边框显示在最上层
        .ZOrder msoSendBackward
    End With
End Sub

代码说明 #

1. 定义padding变量控制边距大小，可根据需求修改数值；
2. 外层矩形仅保留蓝色粗边框，取消填充；
3. 内层矩形根据外层尺寸和边距计算位置与大小，填充国旗图片并隐藏边框；
4. 通过ZOrder调整层级，确保外层边框显示在内层图片上方。

内容的提问来源于stack exchange，提问作者Danny Coleiro