Excel VBA代码执行无报错但未生效，请求排查解决

核心问题与修复点 #

1. 文件夹路径拼接错误 #

原代码中folderPath = mainFolder & subfolder & "\"，如果mainFolder末尾没有反斜杠（比如C:\MainFolder），会导致路径变成C:\MainFolderEx1\，系统无法识别该文件夹。
修复方法：确保路径拼接正确，先判断mainFolder末尾是否有反斜杠，再拼接：

folderPath = IIf(Right(mainFolder, 1) = "\", mainFolder, mainFolder & "\") & subfolder & "\"

2. 文件夹匹配逻辑存在漏洞 #

原IsInArray函数使用Filter函数，存在两个问题：

• 区分大小写（比如文件夹是EX1，数组里是Ex1会匹配失败）
• 部分匹配（比如数组里有Ex1，文件夹是Ex12会被误匹配）
  修复方法：替换为精确匹配的函数：

Function IsInArray(value As Variant, arr As Variant) As Boolean
    Dim element As Variant
    IsInArray = False
    For Each element In arr
        ' 不区分大小写的精确匹配
        If StrComp(CStr(element), CStr(value), vbTextCompare) = 0 Then
            IsInArray = True
            Exit For
        End If
    Next element
End Function

3. H15图标集条件格式操作错误 #

原代码直接调用ws.Range("H15").FormatConditions(1)，假设第一个条件格式就是图标集，但如果单元格没有条件格式或顺序不对，会直接报错（但因错误隐藏机制被掩盖），导致后续代码无法执行。
修复方法：先检查是否存在图标集，不存在则创建，存在则修改：

' 处理H15的图标集条件格式
Dim h15Format As FormatCondition
Set h15Format = Nothing
On Error Resume Next
Set h15Format = ws.Range("H15").FormatConditions(xlIconSetCondition)
On Error GoTo 0

If h15Format Is Nothing Then
    Set h15Format = ws.Range("H15").FormatConditions.AddIconSetCondition
    h15Format.IconSet = ws.Parent.IconSets(xl3TrafficLights1)
End If
With h15Format.IconCriteria(2)
    .Type = xlConditionValueNumber
    .Value = 0.75
    .Operator = xlGreaterEqual
End With

4. J15公式硬编码错误 #

原公式ws.Range("J15").Formula = "=""A""& Text(" & ws.Range("H15").value & ",""0%""") & """B"""会把H15的当前值硬编码进去，而非动态引用单元格。
修复方法：改为直接引用单元格的公式：

ws.Range("J15").Formula = "=""A""&TEXT(H15,""0%"")&""B"""

5. 条件格式清除范围错误 #

原代码ws.Cells.FormatConditions.Delete会清除整个工作表的所有条件格式，而非仅J15的，这会破坏原有格式且可能导致后续规则失效。
修复方法：仅清除J15的条件格式：

ws.Range("J15").FormatConditions.Delete

6. 计算模式设置不合理 #

原代码在工作表循环中反复设置计算模式，且最后直接设为手动，会影响用户后续操作。
修复方法：保存原计算模式，处理完后恢复：

' 开头保存原计算模式
Dim originalCalcMode As XlCalculation
originalCalcMode = Application.Calculation
Application.Calculation = xlCalculationAutomatic

' 结尾恢复原模式
Application.Calculation = originalCalcMode

修复后的完整代码 #

Function IsInArray(value As Variant, arr As Variant) As Boolean
    Dim element As Variant
    IsInArray = False
    For Each element In arr
        ' 不区分大小写的精确匹配
        If StrComp(CStr(element), CStr(value), vbTextCompare) = 0 Then
            IsInArray = True
            Exit For
        End If
    Next element
End Function

Sub UpdateFormulasAndFormattingInFolders()
    Dim mainFolder As String
    Dim filePath As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim originalCalcMode As XlCalculation
    Dim folderPath As String
    Dim subfolder As String
    Dim selectedFolders() As Variant
    Dim h15Format As FormatCondition
    Dim j15Condition As FormatCondition
    
    ' 保存原计算模式
    originalCalcMode = Application.Calculation
    Application.Calculation = xlCalculationAutomatic
    
    ' 主文件夹路径，请确保末尾可带或不带反斜杠
    mainFolder = "Mainfolder path"
    selectedFolders = Array("Ex1", "Ex2", "Ex3", "Ex4", "Ex5", "Ex6", "Ex7")
    
    ' 遍历主文件夹下的子文件夹
    subfolder = Dir(mainFolder, vbDirectory)
    Do While subfolder <> ""
        If subfolder <> "." And subfolder <> ".." Then
            If IsInArray(subfolder, selectedFolders) Then
                ' 正确拼接文件夹路径
                folderPath = IIf(Right(mainFolder, 1) = "\", mainFolder, mainFolder & "\") & subfolder & "\"
                
                ' 遍历文件夹下的Excel文件
                filePath = Dir(folderPath & "*.xlsx")
                Do While filePath <> ""
                    ' 错误处理：避免文件打开失败导致程序中断
                    On Error Resume Next
                    Set wb = Workbooks.Open(folderPath & filePath)
                    On Error GoTo 0
                    
                    If Not wb Is Nothing Then
                        For Each ws In wb.Worksheets
                            Select Case ws.Name
                                Case "S1", "S2"
                                    ' 更新H15公式与格式
                                    ws.Range("H15").Formula = "=H33/G33"
                                    ws.Range("H15").NumberFormat = "0%"
                                    
                                    ' 处理H15的图标集条件格式
                                    Set h15Format = Nothing
                                    On Error Resume Next
                                    Set h15Format = ws.Range("H15").FormatConditions(xlIconSetCondition)
                                    On Error GoTo 0
                                    
                                    If h15Format Is Nothing Then
                                        Set h15Format = ws.Range("H15").FormatConditions.AddIconSetCondition
                                        h15Format.IconSet = ws.Parent.IconSets(xl3TrafficLights1)
                                    End If
                                    With h15Format.IconCriteria(2)
                                        .Type = xlConditionValueNumber
                                        .Value = 0.75
                                        .Operator = xlGreaterEqual
                                    End With
                                    
                                    ' 更新J15公式
                                    ws.Range("J15").Formula = "=""A""&TEXT(H15,""0%"")&""B"""
                                    
                                    ' 清除J15原有条件格式
                                    ws.Range("J15").FormatConditions.Delete
                                    
                                    ' 添加J15条件格式
                                    Set j15Condition = ws.Range("J15").FormatConditions.Add(Type:=xlExpression, Formula1:="=H15<0.75")
                                    j15Condition.Interior.Color = RGB(255, 0, 0)
                                    j15Condition.StopIfTrue = False
                                    
                                    Set j15Condition = ws.Range("J15").FormatConditions.Add(Type:=xlExpression, Formula1:="=H15>=0.75")
                                    j15Condition.Interior.Color = RGB(0, 176, 80)
                                    j15Condition.StopIfTrue = False
                            End Select
                        Next ws
                        
                        ' 保存并关闭文件
                        wb.Close SaveChanges:=True
                        Set wb = Nothing
                    End If
                    
                    filePath = Dir
                Loop
            End If
        End If
        subfolder = Dir
    Loop
    
    ' 恢复原计算模式
    Application.Calculation = originalCalcMode
    MsgBox "公式与条件格式已完成更新。", vbInformation
End Sub

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