编写重复条件的最佳方法

Question

我有六个位置a，b，c，d，e，f在我的枢轴切割机和我有五个盒子的形状是灰色的颜色。根据切割机中的选择，箱体颜色将变为绿色。当所有位置都被选中时，所有的框都将变成绿色。我通过在VBA中的if条件实现了这一点。但是，我对如何满足用户只选择三到两个位置的条件感到困惑。满足此条件的最佳代码方式是什么？

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    If Target.Name = "PivotTable4" Then
        If ActiveWorkbook.SlicerCaches("Slicer_Site_work_being_carried_out").SlicerItems("a").Selected = True Then
            With ActiveSheet.Shapes("Freeform: Shape 6").Fill.ForeColor
                .RGB = vbGreen
            End With
            With ActiveSheet.Shapes("Freeform: Shape 15").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 11").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 12").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 7").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 9").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
        ElseIf ActiveWorkbook.SlicerCaches("Slicer_Site_work_being_carried_out").SlicerItems("b").Selected = True Then
            With ActiveSheet.Shapes("Freeform: Shape 15").Fill.ForeColor
                .RGB = vbGreen
            End With
            With ActiveSheet.Shapes("Freeform: Shape 6").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 11").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 12").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 7").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 9").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
        ElseIf ActiveWorkbook.SlicerCaches("Slicer_Site_work_being_carried_out").SlicerItems("c").Selected = True Then
            With ActiveSheet.Shapes("Freeform: Shape 11").Fill.ForeColor
                .RGB = vbGreen
            End With
            With ActiveSheet.Shapes("Freeform: Shape 15").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 6").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 12").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 7").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 9").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
        ElseIf ActiveWorkbook.SlicerCaches("Slicer_Site_work_being_carried_out").SlicerItems("d").Selected = True Then
            With ActiveSheet.Shapes("Freeform: Shape 12").Fill.ForeColor
                .RGB = vbGreen
            End With
            With ActiveSheet.Shapes("Freeform: Shape 11").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 15").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 6").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 7").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 9").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
        ElseIf ActiveWorkbook.SlicerCaches("Slicer_Site_work_being_carried_out").SlicerItems("e").Selected = True Then
            With ActiveSheet.Shapes("Freeform: Shape 7").Fill.ForeColor
                .RGB = vbGreen
            End With
            With ActiveSheet.Shapes("Freeform: Shape 12").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 11").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 15").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 6").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 9").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
        ElseIf ActiveWorkbook.SlicerCaches("Slicer_Site_work_being_carried_out").SlicerItems("f").Selected = True Then
            With ActiveSheet.Shapes("Freeform: Shape 9").Fill.ForeColor
                .RGB = vbGreen
            End With
            With ActiveSheet.Shapes("Freeform: Shape 7").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 12").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 11").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 15").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
            With ActiveSheet.Shapes("Freeform: Shape 6").Fill.ForeColor
                .RGB = RGB(205, 192, 176)
            End With
        End If
    End If
End Sub

Answer 1

您可以使用字典存储形状名称和相应的切片程序名称，并根据所选状态设置形状颜色：

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sShape
    If Target.Name = "PivotTable4" Then
        ' instantiate dictionary
        With CreateObject("Scripting.Dictionary")
            ' fill the dict with shape names as keys and corresponding slicer names as values
            .Item("Freeform: Shape 6") = "a"
            .Item("Freeform: Shape 15") = "b"
            .Item("Freeform: Shape 11") = "c"
            .Item("Freeform: Shape 12") = "d"
            .Item("Freeform: Shape 7") = "e"
            .Item("Freeform: Shape 9") = "f"
            ' set forecolor for each shape depending on corresponding slicer actual selected state
            For Each sShape In .Keys
                Target.Parent.Shapes(sShape).Fill.ForeColor.RGB = IIf( _
                    Target.Parent.Parent.SlicerCaches("Slicer_Site_work_being_carried_out").SlicerItems(.Item(sShape)).Selected, _
                    vbGreen, _
                    RGB(205, 192, 176) _
                )
            Next
        End With
    End If
End Sub

甚至可以使用嵌套数组：

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim aShape
    If Target.Name = "PivotTable4" Then
        ' loop through shapes using array populated by nested arrays with shape/slicer name pairs
        For Each aShape In Array( _
            Array("Freeform: Shape 6", "a"), _
            Array("Freeform: Shape 15", "b"), _
            Array("Freeform: Shape 11", "c"), _
            Array("Freeform: Shape 12", "d"), _
            Array("Freeform: Shape 7", "e"), _
            Array("Freeform: Shape 9", "f") _
        )
            ' set forecolor for the shape depending on the slicer actual selected state
            Target.Parent.Shapes(aShape(0)).Fill.ForeColor.RGB = IIf( _
                Target.Parent.Parent.SlicerCaches("Slicer_Site_work_being_carried_out").SlicerItems(aShape(1)).Selected, _
                vbGreen, _
                RGB(205, 192, 176) _
            )
        Next
    End If
End Sub

没有经过测试，因为我没有这样的数据结构，如果我正确理解您的意图，这应该是可行的。

请注意，这不是依赖ActiveWorkbook和ActiveSheet全局属性的最佳方法。我用Target.Parent.Parent换了ActiveSheet，用Target.Parent换了ActiveSheet。

Answer 2

谢谢您@omegastripe，如果添加了var d，.item是不允许进入的，它起作用了

  Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sShape
    Dim d
    If Target.Name = "PivotTable4" Then
        ' instantiate dictionary
        Set d = CreateObject("Scripting.Dictionary")
        With d
            ' fill the dict with shape names as keys and corresponding slicer names as values
            .Item("Freeform: Shape 6") = "a"
            .Item("Freeform: Shape 15") = "b"
            .Item("Freeform: Shape 11") = "c"
            .Item("Freeform: Shape 12") = "d"
            .Item("Freeform: Shape 7") = "e"
            .Item("Freeform: Shape 9") = "f"
            ' replace each slicer name with it's actual selected state
            For Each sShape In .Keys
                d.Item(sShape) = ActiveWorkbook.SlicerCaches("Slicer_Site_work_being_carried_out").SlicerItems(.Item(sShape)).Selected
            Next
            ' set forecolor for each shape individually
            For Each sShape In .Keys
                With ActiveSheet.Shapes(sShape).Fill.ForeColor
                    If d.Item(sShape) Then
                        .RGB = vbGreen
                    Else
                        .RGB = RGB(205, 192, 176)
                    End If
                End With
            Next
        End With
    End If
End Sub