工作表/工作簿后台运行时动态单元格Max/Min值不更新如何解决

原参考代码依赖工作表的SelectionChange/Change事件触发计算，这类事件仅在工作表处于前台激活状态、用户在表内执行选择/编辑操作时才会响应，当工作簿在后台运行时事件不会触发，Max/Min值自然无法自动更新。

采用全局定时轮询+值比对的逻辑替代原有事件触发逻辑，无论工作簿是否在前台激活，都能按固定频率检测动态单元格的值变化并更新最值。

步骤1：写入通用跟踪代码 #

按Alt+F11打开VBA编辑器，右键点击左侧工程栏，选择「插入」-「模块」，在新建的标准模块中粘贴以下代码：

' 按需修改下方常量配置
Public Const TARGET_SHEET As String = "Sheet1" ' 动态单元格所在的工作表名称
Public Const TARGET_CELL As String = "A1"      ' 需要跟踪的动态单元格地址
Public Const MAX_OUTPUT_CELL As String = "C1"  ' Max值存放单元格地址
Public Const MIN_OUTPUT_CELL As String = "C2"  ' Min值存放单元格地址
Public Const CHECK_INTERVAL As String = "00:00:01" ' 检测间隔，当前为1秒，可按需调整

Private lastTrackVal As Variant
Private currentMax As Double
Private currentMin As Double

Sub StartTrack()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(TARGET_SHEET)
    Dim curVal As Variant
    curVal = ws.Range(TARGET_CELL).Value
    
    ' 首次运行初始化最值
    If IsEmpty(lastTrackVal) Then
        If IsNumeric(curVal) Then
            lastTrackVal = CDbl(curVal)
            currentMax = CDbl(curVal)
            currentMin = CDbl(curVal)
        End If
    Else
        ' 检测到值为数值且发生变化时更新最值
        If IsNumeric(curVal) And CDbl(curVal) <> lastTrackVal Then
            lastTrackVal = CDbl(curVal)
            If CDbl(curVal) > currentMax Then currentMax = CDbl(curVal)
            If CDbl(curVal) < currentMin Then currentMin = CDbl(curVal)
            ' 直接写入结果，无需激活工作表
            ws.Range(MAX_OUTPUT_CELL).Value = currentMax
            ws.Range(MIN_OUTPUT_CELL).Value = currentMin
        End If
    End If
    
    ' 注册下一次定时检测
    Application.OnTime Now + TimeValue(CHECK_INTERVAL), "StartTrack"
End Sub

Sub StopTrack()
    On Error Resume Next
    Application.OnTime Now + TimeValue(CHECK_INTERVAL), "StartTrack", , False
    On Error GoTo 0
End Sub

步骤2：配置自动启停 #

双击左侧工程栏中的ThisWorkbook对象，粘贴以下代码，实现打开工作簿自动启动跟踪、关闭工作簿自动停止进程，避免宏残留占用资源：

Private Sub Workbook_Open()
    Call StartTrack
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTrack
End Sub

注意事项 #

• 检测间隔不建议设置小于0.5秒，否则可能占用过多系统资源，常规数据跟踪场景1-2秒的间隔即可满足需求
• 若动态单元格的值由公式计算生成，需将工作簿计算模式设置为自动：点击「文件」-「选项」-「公式」，勾选「自动重算」即可，否则公式不更新的情况下跟踪逻辑无法获取新值
• 若需要跟踪多个动态单元格，只需为每个单元格单独定义存储值、最值的变量，在轮询逻辑中增加对应检测和写入逻辑即可，不要共用变量避免值覆盖

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