求助：基于Worksheet.Calculate实现单元格变动时相邻内容自动复制

核心逻辑说明 #

苹果、橙子的成交量为持续动态变动的公式计算值，常规Worksheet_Change事件仅能捕获手动输入的单元格变动，无法识别公式重算带来的数值变化，因此选用Worksheet_Calculate事件做监听。该事件会在工作表所有公式完成重算时触发，通过对比重算前后的成交量差值，即可精准识别变动并抓取相邻单元格的买卖属性文本。

分步实现方案 #

• 模块级缓存旧值
  Worksheet_Calculate事件本身不会传递变动前后的数值差异，需要在对应工作表的代码模块通用声明段，提前定义私有变量用来缓存上一次重算时的成交量基准值：

  ' 工作表代码模块最顶部的通用声明段写入
  Private lastAppleVol As Double, lastOrangeVol As Double
  

• 变动判断与记录逻辑
  在Worksheet_Calculate事件过程中，依次读取当前的成交量数值，和缓存的旧值做比对，一旦识别到差异，就抓取同行动静单元格的买卖文本写入指定记录位置，示例代码框架：

  Private Sub Worksheet_Calculate()
      ' 配置固定单元格位置，可按需调整
      Const APPLE_VOL_RANGE As String = "I2" ' 苹果成交量所在单元格
      Const ORANGE_VOL_RANGE As String = "I3" ' 橙子成交量所在单元格
      Dim currentAppleVol As Double, currentOrangeVol As Double
      
      ' 读取当前重算后的成交量数值
      currentAppleVol = Me.Range(APPLE_VOL_RANGE).Value
      currentOrangeVol = Me.Range(ORANGE_VOL_RANGE).Value
      
      ' 首次触发时初始化缓存值，避免无效记录
      If lastAppleVol = 0 And lastOrangeVol = 0 Then
          lastAppleVol = currentAppleVol
          lastOrangeVol = currentOrangeVol
          Exit Sub
      End If
      
      ' 捕获苹果成交量变动
      If currentAppleVol <> lastAppleVol Then
          Dim nextRecordRow As Long
          nextRecordRow = Me.Cells(Me.Rows.Count, "K").End(xlUp).Row + 1
          ' 抓取相邻J2单元格的买卖属性，写入记录列
          Me.Cells(nextRecordRow, "K").Value = "苹果成交量变动：" & lastAppleVol & "→" & currentAppleVol & "，关联属性：" & Me.Range("J2").Value
          ' 更新缓存的基准值，供下一次重算比对
          lastAppleVol = currentAppleVol
      End If
      
      ' 捕获橙子成交量变动，逻辑与苹果完全一致
      If currentOrangeVol <> lastOrangeVol Then
          Dim nextRecordRowOrange As Long
          nextRecordRowOrange = Me.Cells(Me.Rows.Count, "K").End(xlUp).Row + 1
          Me.Cells(nextRecordRowOrange, "K").Value = "橙子成交量变动：" & lastOrangeVol & "→" & currentOrangeVol & "，关联属性：" & Me.Range("J3").Value
          lastOrangeVol = currentOrangeVol
      End If
  End Sub
  

• 调试注意事项
  • 不要在Calculate事件里不加判断直接写入内容，否则每次全表重算都会重复生成大量无效记录，必须依赖缓存的旧值做差异比对
  • 需补充异常值容错判断，比如成交量单元格为空、返回非数值内容时，跳过本次比对，避免触发VBA类型不匹配的运行时错误
  • 如果存在公式计算带来的浮点精度误差（如0.0000001级别的微小数值波动），可设置合理的比对阈值，仅当差值超过阈值时判定为真实变动，避免误触发无效记录
  • 如果文件存在多工作表、多单元格联动重算的场景，可通过Application.EnableEvents属性临时关闭事件触发，避免记录逻辑嵌套触发死循环

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