调用Solver的VBA宏运行极慢，能否结合Arrays实现高速运算？

核心思路：避免逐行调用Solver，改用数组+公式逆推（最优） #

逐行调用Solver会产生巨大的性能开销，1000次调用必然卡顿甚至崩溃。如果H列的计算公式可以逆推，直接通过数组在内存中计算B列所需值，再批量写回单元格，速度会提升几个数量级。

步骤1：确定H列的计算逻辑

从数据示例来看，假设H列是类似H = (某列值 - B列值)/另一列值这类线性公式（常见的成本率、利润率计算），直接逆推B列值即可：

• 假设H列公式为H = (D - B)/C，逆推得B = D - H*C
• 目标H是3%（0.03），因此B = D - 0.03*C

步骤2：数组实现代码

Sub CalculateBColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataArr As Variant
    Dim i As Long
    
    Set ws = ActiveSheet ' 可改为指定工作表，比如ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
    
    ' 将数据读入数组（按需调整涉及的列，示例用B、C、D列）
    dataArr = ws.Range("B2:D" & lastRow).Value
    
    ' 在内存中批量计算B列值
    For i = LBound(dataArr, 1) To UBound(dataArr, 1)
        ' 替换为你的实际逆推公式，示例为B = D - 0.03*C
        dataArr(i, 1) = dataArr(i, 3) - 0.03 * dataArr(i, 2)
    Next i
    
    ' 批量写回B列
    ws.Range("B2:B" & lastRow).Value = dataArr
    
    MsgBox "计算完成"
End Sub

如果H列是非线性公式（必须用Solver） #

如果H列公式是非线性的（包含幂次、对数等），无法直接逆推，可以通过以下方式优化Solver调用，减少交互开销：

• 关闭屏幕更新、事件触发
• 取消每次循环的SolverReset（无异常无需重复重置）

优化后的代码：

Sub OptimizedSolverMacro()
    Dim lastRow As Long
    Dim i As Long
    
    ' 关闭界面交互，提升运行速度
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    lastRow = Cells(Rows.Count, "H").End(xlUp).Row
    
    For i = 2 To lastRow
        SolverOk SetCell:="$H$" & i, MaxMinVal:=3, ValueOf:=0.03, ByChange:="$B$" & i, _
                 Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve UserFinish:=True
    Next i
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    MsgBox "求解完成"
End Sub

关键优化点说明 #

• 数组操作：将数据读入内存数组计算，避免频繁读写单元格，这是VBA提速的核心技巧。
• 减少Solver冗余操作：原代码每次循环重置Solver，增加不必要的开销，无异常时无需重复执行。
• 关闭界面交互：关闭ScreenUpdating和EnableEvents后，Excel不会刷新界面、触发事件，大幅降低运行耗时。

内容的提问来源于stack exchange，提问作者Francisco Augusto Varela Aguir