使用定时器更新Excel单元格值失败，求助排查解决方法

核心错误原因及解决 #

Sheet1中的UpdateTimers是Private子过程，这类过程仅能在其所在的代码模块（Sheet1的代码窗口）内部访问，无法被Module1跨模块调用。

解决方法：将Sheet1中的UpdateTimers改为Public：

' Sheet1代码修改后
Public Sub UpdateTimers()
    Worksheet_Change Range("CountdownTimers")
End Sub

其他需要修复的问题 #

1. 定时器仅执行一次的问题 #

Application.OnTime是一次性触发机制，当前Timer过程执行后不会自动重启定时器，导致仅更新一次。需在Timer过程末尾重新调用Start_Timer实现循环触发：

' Module1中的Timer过程修改后
Private Sub Timer()
    If TimerActive Then
        Sheet1.UpdateTimers
        Start_Timer ' 重启定时器，实现循环更新
    End If
End Sub

2. Worksheet_Change逻辑颠倒 #

当前代码中If Intersect(Target, rng) Is Nothing Then的逻辑是：当目标区域与CountdownTimers不相交时才执行更新，这完全不符合需求（定时调用时传入的就是CountdownTimers，Intersect不会为Nothing，导致更新代码被跳过）。需将条件改为If Not Intersect(Target, rng) Is Nothing Then，同时优化倒计时负数的显示逻辑：

' Sheet1中的Worksheet_Change修改后
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range ' 声明局部变量，避免与Module1的模块级变量冲突
    Dim cell As Range
    Set rng = Range("CountdownTimers")
    
    ' 修正条件：当目标区域与CountdownTimers相交时执行更新
    If Not Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In rng
            If cell.Offset(0, -1).Value <> "" Then
                Dim timeDiff As Variant
                timeDiff = cell.Offset(0, -1).Value - Now()
                ' 处理倒计时为负的情况
                If timeDiff > 0 Then
                    cell.Value = timeDiff
                    cell.NumberFormat = "hh:mm:ss"
                Else
                    cell.Value = "已结束"
                    cell.NumberFormat = "@"
                End If
            Else
                cell.Value = ""
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

3. 变量作用域冲突问题 #

Module1中声明了模块级的rng和cell变量，Sheet1的过程中直接使用会导致作用域混乱。建议在Sheet1的过程中声明局部变量（如上述代码所示），避免变量干扰。

完整修正后代码 #

工作簿代码（不变） #

Private Sub Workbook_Open()
    Module1.StartTimer
End Sub

Module1代码 #

Dim TimerActive As Boolean

Sub StartTimer()
    Start_Timer
End Sub

Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:00:05"), "Timer"
End Sub

Private Sub Stop_Timer()
    TimerActive = False
    ' 取消未触发的定时器（可选，防止关闭文件后仍触发）
    On Error Resume Next
    Application.OnTime Now() + TimeValue("00:00:05"), "Timer", , False
    On Error GoTo 0
End Sub

Private Sub Timer()
    If TimerActive Then
        Sheet1.UpdateTimers
        Start_Timer
    End If
End Sub

Sheet1代码 #

Public Sub UpdateTimers()
    Worksheet_Change Range("CountdownTimers")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("CountdownTimers")
    
    If Not Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In rng
            If cell.Offset(0, -1).Value <> "" Then
                Dim timeDiff As Variant
                timeDiff = cell.Offset(0, -1).Value - Now()
                If timeDiff > 0 Then
                    cell.Value = timeDiff
                    cell.NumberFormat = "hh:mm:ss"
                Else
                    cell.Value = "已结束"
                    cell.NumberFormat = "@"
                End If
            Else
                cell.Value = ""
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

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