Excel中从API获取BTCUSD动态数据并生成图表的技术求助

我来帮你拆解这两个卡点，一步步搞定数据记录的问题：

一、为什么Worksheet_Change事件没反应？ #

这是Excel的常见坑——外部API/DDE更新单元格时，不会触发Worksheet_Change事件。这个事件只对用户手动编辑、VBA直接修改单元格的操作生效，后台的外部数据源更新属于“静默修改”，会绕过这个事件。

替代方案：用Worksheet_Calculate事件 #

如果你的A1是通过公式引用API数据（比如DDE公式、WEBSERVICE函数），每次API更新都会触发单元格重新计算，这时Worksheet_Calculate会被激活。而且我们可以加个静态变量避免重复记录：

Private Sub Worksheet_Calculate()
    Dim priceCell As Range
    Set priceCell = Me.Range("A1") ' 这里的Me指当前工作表，替换成你的API数据单元格
    
    ' 用静态变量记录上一次价格，只在价格变化时记录
    Static lastPrice As Variant
    If priceCell.Value <> lastPrice Then
        lastPrice = priceCell.Value
        ' 调用统一的记录函数
        LogPrice lastPrice
    End If
End Sub

Private Sub LogPrice(price As Variant)
    Dim logRange As Range
    Set logRange = ThisWorkbook.Names("DDEList").RefersToRange
    
    ' 找到数据区域的最后一行
    Dim lastRow As Long
    lastRow = logRange.CurrentRegion.Rows.Count
    
    ' 写入时间和价格
    logRange.Offset(lastRow, 0).Value = Now
    logRange.Offset(lastRow, 1).Value = price
End Sub

二、修复TextBox_Change的1004错误 #

错误Unable to get the CurrentRegion property of the range class大概率是这两个原因：

• 命名范围DDEList指向的单元格无效（比如是空单元格、被删除的单元格）
• CurrentRegion识别不了有效数据区域（比如数据中间有空行/空列，导致区域判断混乱）

修复后的代码（更健壮） #

Public Sub TextBox1_Change()
    Dim ddeRange As Range
    Dim dataArea As Range
    Dim nextRow As Range
    
    ' 先检查命名范围是否有效，避免直接引用出错
    On Error Resume Next
    Set ddeRange = ThisWorkbook.Names("DDEList").RefersToRange
    On Error GoTo 0
    
    If ddeRange Is Nothing Then
        MsgBox "命名范围DDEList出问题了！请检查它的引用位置。", vbExclamation
        Exit Sub
    End If
    
    ' 安全获取数据区域，处理首次记录的空情况
    Set dataArea = ddeRange.CurrentRegion
    If dataArea.Cells(1, 1).Value = "" Then
        ' 首次记录，先写表头
        ddeRange.Value = "记录时间"
        ddeRange.Offset(0, 1).Value = "BTCUSD价格"
        Set nextRow = ddeRange.Offset(1, 0)
    Else
        Set nextRow = dataArea.Offset(dataArea.Rows.Count, 0).Resize(1, 2)
    End If
    
    ' 写入数据
    nextRow.Cells(1, 1).Value = Now
    nextRow.Cells(1, 2).Value = TextBox1.Value
End Sub

核心修复点： #

• 加了命名范围有效性检查：防止因DDEList引用失效直接报错
• 处理空数据区域的情况：首次记录自动生成表头，避免Offset到错误位置
• 明确指定新行的列数：直接Resize成1行2列，避免后续列被意外占用

额外小提示 #

• 用Worksheet_Calculate的话，要确保Excel事件是开启的（Application.EnableEvents = True，如果之前关过要记得打开）
• 记录数据时可以加个错误捕获，比如价格为空时跳过记录，防止代码崩掉
• 定期清理旧数据，数据量太大的话会影响Excel运行速度

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