如何在VBA Excel中使用With语句同时设置多个对象属性？

VBA的With语句本身不支持同时绑定多个对象，你写的With BldOvImgDiv1 And BldOvImgDiv2是语法错误——And是逻辑判断运算符，不能用来连接两个对象作为With的目标。下面提供几种可行的替代方案：

方法1：封装重复操作为子过程（推荐） #

把需要统一设置的属性逻辑封装成独立子过程，然后分别传入两个Shape对象执行，既保证代码简洁，又方便后续扩展：

Sub SetConnectorStyle(targetShape As Shape)
    With targetShape
        .Line.ForeColor.RGB = RGB(0, 0, 0)
        ' 这里可以添加更多需要统一设置的属性
    End With
End Sub

' 原代码修改后：
Dim bow As Worksheet
Set bow = Sheets("Building Overwiew")
Dim BldOvImgDiv1 As Shape, BldOvImgDiv2 As Shape
Set BldOvImgDiv1 = bow.Shapes.AddConnector(msoConnectorStraight, 260, 4, 260, 325)
Set BldOvImgDiv2 = bow.Shapes.AddConnector(msoConnectorStraight, 8, 325, 535, 325)
BldOvImgDiv1.Name = "BuildingOverviewConnector1"
BldOvImgDiv2.Name = "BuildingOverviewConnector2"

' 调用子过程设置样式
Call SetConnectorStyle(BldOvImgDiv1)
Call SetConnectorStyle(BldOvImgDiv2)

方法2：用集合+循环批量处理 #

把需要操作的对象存入集合，通过循环遍历统一设置属性：

Dim bow As Worksheet
Set bow = Sheets("Building Overwiew")
Dim BldOvImgDiv1 As Shape, BldOvImgDiv2 As Shape
Set BldOvImgDiv1 = bow.Shapes.AddConnector(msoConnectorStraight, 260, 4, 260, 325)
Set BldOvImgDiv2 = bow.Shapes.AddConnector(msoConnectorStraight, 8, 325, 535, 325)
BldOvImgDiv1.Name = "BuildingOverviewConnector1"
BldOvImgDiv2.Name = "BuildingOverviewConnector2"

' 创建集合并添加对象
Dim shapeColl As New Collection
shapeColl.Add BldOvImgDiv1
shapeColl.Add BldOvImgDiv2

' 循环设置属性
Dim shp As Shape
For Each shp In shapeColl
    shp.Line.ForeColor.RGB = RGB(0, 0, 0)
Next shp

方法3：连续使用With语句（简洁但复用性差） #

如果只是两个对象，也可以连续写两个With语句，减少重复的属性路径：

Dim bow As Worksheet
Set bow = Sheets("Building Overwiew")
Dim BldOvImgDiv1 As Shape, BldOvImgDiv2 As Shape
Set BldOvImgDiv1 = bow.Shapes.AddConnector(msoConnectorStraight, 260, 4, 260, 325)
Set BldOvImgDiv2 = bow.Shapes.AddConnector(msoConnectorStraight, 8, 325, 535, 325)
BldOvImgDiv1.Name = "BuildingOverviewConnector1"
BldOvImgDiv2.Name = "BuildingOverviewConnector2"

With BldOvImgDiv1
    .Line.ForeColor.RGB = RGB(0, 0, 0)
End With
With BldOvImgDiv2
    .Line.ForeColor.RGB = RGB(0, 0, 0)
End With

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