Mac端Excel VBA获取币安加密货币价格报429错误解决咨询

错误原因 #

运行时错误429触发的核心是Mac版Office VBA不支持Windows专属的ActiveX组件，原有代码中两处CreateObject调用的MSXML2.XMLHTTP（发起HTTP请求）和VBScript.RegExp（正则解析）均为Windows系统独有依赖，Mac环境不存在对应组件，因此直接抛出创建失败错误。

修复思路 #

完全抛弃ActiveX依赖，全部改用Excel跨平台内置功能实现原有逻辑，无需引入任何第三方.cls类文件，适配你的使用场景：

• 用Excel内置WebService工作表函数替代MSXML2.XMLHTTP发起GET请求，直接获取API返回的JSON内容
• 用原生VBA的Split函数替代正则表达式解析JSON返回值，逻辑更简单，兼容性更强

完整修改后代码 #

Sub GetData()
    Dim strURL As String, HTMLcode As String
    Dim arrProperties()
    Dim arrCoinItems() As String, singleCoinStr As String
    Dim r As Integer, c As Byte, i As Integer
    Dim strResponse As String, intStatus As Integer, strStatus As String
    
    ' 清空原有内容
    Range("A1:U" & Rows.Count) = ""
    
    ' 表头配置，和原有逻辑完全一致
    arrProperties = Array("symbol", "priceChange", "priceChangePercent", "weightedAvgPrice", "prevClosePrice", _
                          "lastPrice", "lastQty", "bidPrice", "bidQty", "askPrice", "askQty", "openPrice", _
                          "highPrice", "lowPrice", "volume", "quoteVolume", "openTime", "closeTime", _
                          "firstId", "lastId", "count")
                          
    Range("A1:U1") = arrProperties
    Range("A1:U1").Font.Bold = True
    Range("A1:U1").Font.Color = vbRed
    
    ' 替换原有ActiveX HTTP请求，用内置WebService函数
    strURL = "https://api.binance.com/api/v3/ticker/24hr"
    On Error Resume Next
    HTMLcode = WorksheetFunction.WebService(strURL)
    On Error GoTo 0
    
    ' 判断请求是否成功
    If HTMLcode <> "" And InStr(HTMLcode, """symbol"":") > 0 Then
        ' 拆解JSON数组，拆分出每个币种的行情字符串
        HTMLcode = Mid(HTMLcode, 2, Len(HTMLcode) - 2) ' 去掉首尾的[]
        arrCoinItems = Split(HTMLcode, "},{")
        
        r = 1
        ' 遍历每个币种的行情
        For Each singleCoinStr In arrCoinItems
            r = r + 1
            singleCoinStr = Replace(Replace(singleCoinStr, "{", ""), "}", "") ' 去掉剩余的大括号
            ' 按顺序提取21个字段
            For c = 0 To 20
                Dim fieldName As String, fieldStart As Integer, fieldEnd As Integer
                fieldName = """" & arrProperties(c) & """:"
                fieldStart = InStr(singleCoinStr, fieldName) + Len(fieldName)
                ' 判断字段值是否带引号
                If Mid(singleCoinStr, fieldStart, 1) = """" Then
                    fieldStart = fieldStart + 1
                    fieldEnd = InStr(fieldStart, singleCoinStr, """")
                Else
                    fieldEnd = InStr(fieldStart, singleCoinStr, ",")
                    If fieldEnd = 0 Then fieldEnd = Len(singleCoinStr) + 1
                End If
                Cells(r, c + 1) = Mid(singleCoinStr, fieldStart, fieldEnd - fieldStart)
            Next c
        Next singleCoinStr
        
        Columns("A:U").AutoFit
        MsgBox "İşlem tamam...", vbInformation
    Else
        ' 请求失败处理
        If InStr(HTMLcode, """status"":") > 0 Then
            strResponse = Split(HTMLcode, "status"":")(1)
            intStatus = Split(strResponse, "}")(0) + 0
            
            strResponse = Split(HTMLcode, "error"":""")(1)
            strStatus = Split(strResponse, """")(0)
            
            MsgBox "Durum: " & intStatus & vbCrLf & vbCrLf & "Hata mesajı: " & strStatus
        Else
            MsgBox "API请求失败，请检查网络连接或Binance API访问权限", vbCritical
        End If
    End If
    
    Erase arrProperties
    Erase arrCoinItems
End Sub

注意事项 #

• 需使用2016及以上版本的Mac版Excel，确保内置WebService函数可用
• 运行前请确认Mac网络可正常访问Binance API地址
• 如使用的Excel版本过旧不支持WebService，可将HTTP请求部分替换为调用Mac系统curl命令实现，逻辑同样不需要ActiveX支持

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