如何用类数组语句优化VBA中IF多AND NOT判断逻辑？

Great question! Those chained And Not conditions get messy fast—let's clean this up with an array and a simple helper function, which will make your code way easier to maintain and read.

Step 1: Group Excluded Worksheets into an Array #

First, we'll gather all the worksheets you want to skip into a single array. This lets you add or remove sheets later without touching the core loop logic:

' Define your excluded worksheets once at the top (after setting your sheet variables)
Dim excludedSheets As Variant
excludedSheets = Array(ws_raw, ws_master_tracker, ws_title_page, ws_sample, ws_closing, ws_ref, ws_pdf_template)

Step 2: Add a Helper Function to Check Membership #

Next, write a small reusable function that checks if the current worksheet is in the excluded array. This keeps your loop code clean and focused:

Function IsExcludedSheet(ws As Worksheet, excludedArray As Variant) As Boolean
    Dim sheetObj As Variant
    For Each sheetObj In excludedArray
        If ws Is sheetObj Then
            IsExcludedSheet = True
            Exit Function ' No need to check further once we find a match
        End If
    Next sheetObj
    IsExcludedSheet = False
End Function

Step 3: Simplify Your Loop's IF Condition #

Now replace all those chained conditions with a single, readable check using our helper function. We'll also clean up the visibility check for clarity:

For Each ws In ThisWorkbook.Worksheets
    ' Clean, concise condition that's easy to understand
    If Not IsExcludedSheet(ws, excludedSheets) And ws.Visible <> xlSheetHidden Then
        project_name = ws.Range("E3").Value
        int_last_row_of_ws = 46
        
        For int_current_row_of_ws = 11 To int_last_row_of_ws
            cell_value = ws.Cells(int_current_row_of_ws, 3).Value
            
            With rng_raw
                .AutoFilter 1, project_name
            End With
            
            ' Add error handling for cases where no filtered cells exist
            On Error Resume Next
            Set rng_filtered_raw = ws_raw.Range("J3", ws_raw.Cells(int_last_row_of_raw, int_last_col_of_raw)).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            
            ' Optional Bonus: Replace long Select Case with a Dictionary
            ' This makes adding/editing module mappings much easier
            Dim moduleMap As Object
            Set moduleMap = CreateObject("Scripting.Dictionary")
            
            ' Populate your mappings here
            moduleMap("Task Creation!") = "Task Creation"
            moduleMap("Another Task Type!") = "Another Module"
            ' Add all your 20+ cases here...
            
            ' Get the module name (default to "MANUAL" if not found)
            If moduleMap.Exists(cell_value) Then
                module_to_look_for = moduleMap(cell_value)
            Else
                module_to_look_for = "MANUAL"
            End If
            
            If Not rng_filtered_raw Is Nothing Then
                If module_to_look_for <> "MANUAL" Then
                    ' Handle VLookup errors to avoid crashes
                    On Error Resume Next
                    look_up_result = Application.WorksheetFunction.VLookup(module_to_look_for, rng_filtered_raw, 3, False)
                    If Err.Number <> 0 Then
                        ws.Cells(int_current_row_of_ws, 56).Value = "Invalid Module!"
                    ElseIf look_up_result = "" Then
                        ws.Cells(int_current_row_of_ws, 56).Value = "Blank Date!"
                    Else
                        ws.Cells(int_current_row_of_ws, 56).Value = look_up_result
                    End If
                    On Error GoTo 0
                Else
                    ' Your manual handling logic (highlight cell, etc.)
                End If
            End If
            
            ' Reset the filtered range reference for the next iteration
            Set rng_filtered_raw = Nothing
        Next int_current_row_of_ws
    End If
Next ws

Key Benefits of This Approach: #

• Maintainability: Adding or removing excluded sheets only requires updating the excludedSheets array, not editing the loop condition.
• Readability: The helper function makes the loop's intent clear at a glance, so anyone reading the code understands what's being skipped.
• Error Resilience: Added basic error handling for SpecialCells and VLookup to prevent runtime crashes when no matches are found.
• Bonus Cleanup: Replaced the long Select Case block with a Dictionary, which is faster to modify and more efficient for large numbers of mappings.

Alternative: Using Sheet Names Instead of Objects #

If you prefer to work with sheet names (e.g., to avoid issues if sheet variables change), you can modify the helper function to check against an array of names:

Dim excludedSheetNames As Variant
excludedSheetNames = Array("Raw", "Master Tracker", "Title Page")

Function IsExcludedSheetByName(ws As Worksheet, excludedNames As Variant) As Boolean
    IsExcludedSheetByName = Not IsError(Application.Match(ws.Name, excludedNames, 0))
End Function

Then use this condition in your loop:

If Not IsExcludedSheetByName(ws, excludedSheetNames) And ws.Visible <> xlSheetHidden Then

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