基于Excel单元格值控制IFRS/IAS工作表显示隐藏的VBA宏需求

需求说明 #

Excel表格区域B10:G40中，B11及以下单元格存储工作表名称（如IFRS 1、IAS 1等），C11:G40单元格为Yes/No数据验证项。需实现：当某行C11:G40中任意单元格为Yes时，显示该行B列对应的工作表；当该行C11:G40全为No时，隐藏对应工作表。

初始冗余代码 #

以下是最初的硬编码实现，重复编写大量判断逻辑，维护性差：

Sub ControlSheets_Original()
    If [C11] = "Yes" Then
        Sheets("IFRS 1").Visible = True
    Else
        Sheets("IFRS 1").Visible = False
    End If

    If [C12] = "Yes" Then
        Sheets("IFRS 2").Visible = True
    Else
        Sheets("IFRS 2").Visible = False
    End If

    If [C13] = "Yes" Then
        Sheets("IFRS 3").Visible = True
    Else
        Sheets("IFRS 3").Visible = False
    End If

    If [C14] = "Yes" Then
        Sheets("IFRS 5").Visible = True
    Else
        Sheets("IFRS 5").Visible = False
    End If

    If [C15] = "Yes" Then
        Sheets("IFRS 6").Visible = True
    Else
        Sheets("IFRS 6").Visible = False
    End If

    If [C16] = "Yes" Then
        Sheets("IFRS 7").Visible = True
    Else
        Sheets("IFRS 7").Visible = False
    End If

    If [C17] = "Yes" Then
        Sheets("IFRS 13").Visible = True
    Else
        Sheets("IFRS 13").Visible = False
    End If

    If [C18] = "Yes" Then
        Sheets("IFRS 14").Visible = True
    Else
        Sheets("IFRS 14").Visible = False
    End If

    If [C19] = "Yes" Then
        Sheets("IFRS 15").Visible = True
    Else
        Sheets("IFRS 15").Visible = False
    End If

    If [C20] = "Yes" Then
        Sheets("IFRS 16").Visible = True
    Else
        Sheets("IFRS 16").Visible = False
    End If


    If [C21] = "Yes" Then
        Sheets("IAS 1").Visible = True
    Else
        Sheets("IAS 1").Visible = False
    End If

    If [C22] = "Yes" Then
        Sheets("IAS 2").Visible = True
    Else
        Sheets("IAS 2").Visible = False
    End If

    If [C23] = "Yes" Then
        Sheets("IAS 7").Visible = True
    Else
        Sheets("IAS 7").Visible = False
    End If

    If [C24] = "Yes" Then
        Sheets("IAS 8").Visible = True
    Else
        Sheets("IAS 8").Visible = False
    End If

    If [C25] = "Yes" Then
        Sheets("IAS 10").Visible = True
    Else
        Sheets("IAS 10").Visible = False
    End If

    If [C26] = "Yes" Then
        Sheets("IAS 12").Visible = True
    Else
        Sheets("IAS 12").Visible = False
    End If


    If [C27] = "Yes" Then
        Sheets("IAS 16").Visible = True
    Else
        Sheets("IAS 16").Visible = False
    End If

    If [C28] = "Yes" Then
        Sheets("IAS 19").Visible = True
    Else
        Sheets("IAS 19").Visible = False
    End If


    If [C29] = "Yes" Then
        Sheets("IAS 20").Visible = True
    Else
        Sheets("IAS 20").Visible = False
    End If

    If [C30] = "Yes" Then
        Sheets("IAS 21").Visible = True
    Else
        Sheets("IAS 21").Visible = False
    End If

    If [C31] = "Yes" Then
        Sheets("IAS 23").Visible = True
    Else
        Sheets("IAS 23").Visible = False
    End If

    If [C32] = "Yes" Then
        Sheets("IAS 24").Visible = True
    Else
        Sheets("IAS 24").Visible = False
    End If


    If [C33] = "Yes" Then
        Sheets("IAS 27").Visible = True
    Else
        Sheets("IAS 27").Visible = False
    End If


    If [C34] = "Yes" Then
        Sheets("IAS 29").Visible = True
    Else
        Sheets("IAS 29").Visible = False
    End If

    If [C35] = "Yes" Then
        Sheets("IAS 32").Visible = True
    Else
        Sheets("IAS 32").Visible = False
    End If

    If [C36] = "Yes" Then
        Sheets("IAS 34").Visible = True
    Else
        Sheets("IAS 34").Visible = False
    End If

    If [C37] = "Yes" Then
        Sheets("IAS 36").Visible = True
    Else
        Sheets("IAS 36").Visible = False
    End If


    If [C38] = "Yes" Then
        Sheets("IAS 38").Visible = True
    Else
        Sheets("IAS 38").Visible = False
    End If


    If [C39] = "Yes" Then
        Sheets("IAS 40").Visible = True
    Else
        Sheets("IAS 40").Visible = False
    End If


    If [C40] = "Yes" Then
        Sheets("IAS 41").Visible = True
    Else
        Sheets("IAS 41").Visible = False
    End If
End Sub

优化后的高效代码 #

通过循环遍历目标区域，避免重复代码，同时支持行范围扩展，加入错误处理防止工作表不存在导致报错：

Sub ControlSheets_Optimized()
    Dim ws As Worksheet
    Dim targetSheet As Worksheet
    Dim rowNum As Integer
    Dim sheetName As String
    Dim hasYes As Boolean
    
    ' 设置当前操作的工作表（假设为包含控制区域的工作表，可根据实际修改）
    Set ws = ThisWorkbook.ActiveSheet
    
    ' 遍历B11到B40的每一行
    For rowNum = 11 To 40
        sheetName = ws.Cells(rowNum, "B").Value
        ' 跳过空单元格
        If sheetName = "" Then GoTo NextRow
        
        ' 检查当前行C到G列是否存在"Yes"
        hasYes = False
        On Error Resume Next
        hasYes = (Application.WorksheetFunction.CountIf(ws.Range(ws.Cells(rowNum, "C"), ws.Cells(rowNum, "G")), "Yes") > 0)
        On Error GoTo 0
        
        ' 控制工作表可见性
        On Error Resume Next
        Set targetSheet = ThisWorkbook.Sheets(sheetName)
        If Err.Number = 0 Then
            targetSheet.Visible = hasYes
        Else
            ' 可选：工作表不存在时的提示
            ' MsgBox "工作表 '" & sheetName & "' 不存在", vbExclamation
        End If
        On Error GoTo 0
        
NextRow:
    Next rowNum
End Sub

代码说明 #

• 循环遍历：自动处理B11到B40的所有行，无需逐个硬编码判断
• 存在性检查：通过CountIf函数判断当前行C-G列是否有"Yes"值，满足"任意为Yes则显示"的需求
• 错误处理：避免因工作表名称错误或不存在导致宏中断
• 扩展性：后续新增行只需调整循环范围，无需修改大量重复代码

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