如何优化VBA代码避免手动声明100个变量 实现按需动态调用

VBA代码优化方案 #

你需要的动态调用变量、避免重复声明100个ans变量的需求可以通过动态数组实现，优化后代码如下：

Sub vba_loop_sheets()
    Dim i As Long '基础工作表索引
    Dim ii As Long '对比工作表索引
    Dim shtCount As Long
    Dim diffArr As Variant '动态存储匹配到的差值
    Dim arrIndex As Long '数组下标
    
    '初始化数组和下标
    ReDim diffArr(0 To 0)
    arrIndex = 0
    shtCount = Sheets.Count - 1
    
    For i = 7 To shtCount
        For ii = i + 1 To shtCount
            '相同判断只执行一次，减少重复计算
            If Sheets(i).Range("B1").Value = Sheets(ii).Range("B1").Value Then
                '计算差值存入数组
                diffArr(arrIndex) = Abs(Sheets(i).Range("B8").Value - Sheets(ii).Range("B8").Value)
                arrIndex = arrIndex + 1
                '扩容数组，准备存下一个值
                ReDim Preserve diffArr(0 To arrIndex)
            End If
        Next ii
    Next i
    
    '所有匹配完成后一次性拼接公式写入
    If arrIndex > 0 Then
        '去掉最后一次多扩容的空位置
        ReDim Preserve diffArr(0 To arrIndex - 1)
        ThisWorkbook.Sheets("calc").Range("F5").Formula = "=" & Join(diffArr, "+")
    End If
End Sub

核心优化点 #

• 完全取消了100个冗余的ans变量声明，改用动态数组按需存储匹配到的差值，后续匹配数量变化也不需要修改变量声明部分的代码
• 将重复的「两张工作表B1值相等」判断合并为一次，减少不必要的重复计算
• 取消了循环中频繁修改单元格公式的操作，全部匹配完成后一次性拼接写入，运行效率更高
• 用VBA内置的Join函数直接拼接求和公式，不需要手动编写多层判断拼接逻辑，代码更简洁易维护

如果你需要和原代码逻辑一致，每匹配到一组符合条件的工作表就更新一次calc表F5的公式，只需要把公式拼接逻辑放到If判断的末尾、数组扩容的位置即可，不需要额外新增变量。

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