Excel Worksheet_Change事件仅手动修改触发 如何适配PowerQuery自动更新

问题原因 #

Worksheet_Change 事件仅响应用户手动编辑单元格的操作，PowerQuery 刷新、公式重算、外部数据导入这类非手动修改的单元格值更新，都不会触发该事件。

方案1：使用 Worksheet_Calculate 事件（简单易实现） #

该方案不需要修改 PowerQuery 配置，通过对比 B2 的历史值判断是否触发存储逻辑，适配绝大多数场景：

1. 首先在对应工作表的代码编辑页，顶部通用声明区（所有 Sub 代码块外部）定义模块级变量，用于存储 B2 的上一次价格：

Dim lastB2Value As Variant

2. 添加工厂初始化逻辑和 Calculate 事件逻辑，直接替换你原来的 Worksheet_Change 代码即可：

Private Sub Worksheet_Activate()
    ' 首次打开工作表时初始化 B2 的基准值
    lastB2Value = Me.Range("B2").Value
End Sub

Private Sub Worksheet_Calculate()
    ' 每次工作表重算时校验 B2 值是否发生更新
    If Me.Range("B2").Value <> lastB2Value Then
        Call Update_Price
        ' 更新存储的基准值
        lastB2Value = Me.Range("B2").Value
    End If
End Sub

3. 优化你原来的 Update_Price 代码，去掉不稳定的 Select 操作，同时解决 G 列为空时定位错误的问题：

Private Sub Update_Price()
    ' 直接定位 G 列最后一个非空单元格的下一个空白位赋值
    Me.Range("G" & Me.Rows.Count).End(xlUp).Offset(1, 0).Value = Me.Range("B2").Value
End Sub

注意：需要确保 Excel 自动重算功能已开启，路径为「文件」-「选项」-「公式」-「计算选项」勾选「自动重算」。

方案2：使用 QueryTable AfterRefresh 事件（更精准） #

如果需要避免重算频繁触发校验逻辑，可以直接绑定 PowerQuery 查询刷新完成的专属事件，触发逻辑更精准：

1. 打开 ThisWorkbook 代码页，在顶部通用声明区添加如下定义：

Dim WithEvents BTCQuery As Excel.QueryTable

2. 添加工作簿初始化和刷新完成事件逻辑：

Private Sub Workbook_Open()
    ' 替换代码中的「工作表名称」「查询名称」为你实际的工作表名、PowerQuery 查询名称
    Set BTCQuery = ThisWorkbook.Worksheets("你的工作表名").ListObjects("查询名称").QueryTable
End Sub

Private Sub BTCQuery_AfterRefresh(ByVal Success As Boolean)
    ' 仅在查询刷新成功后执行存储逻辑
    If Success Then Call Update_Price
End Sub

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