如何解决Excel VBA调用API时每4秒才更新响应的问题？

我在Excel VBA中每秒调用一次WorksheetFunction.WebService，但接收的响应每4秒才更新一次。不管是本地服务器还是外部API（比如worldtimeapi）都存在这个问题，测试代码如下：

Sub speedtest()
    
    For f = 1 To 10
    
        my_http = "http://worldtimeapi.org/api/timezone/Europe/Amsterdam"
        my_output = WorksheetFunction.WebService(my_http)
        Debug.Print my_output

        Application.Wait (Now + TimeValue("00:00:01"))
            
    Next f

End Sub

运行后输出的响应重复4次才更新一次：

"datetime":"2023-03-07T18:26:10.288974+01:00"
"datetime":"2023-03-07T18:26:10.288974+01:00"
"datetime":"2023-03-07T18:26:10.288974+01:00"
"datetime":"2023-03-07T18:26:10.288974+01:00"
"datetime":"2023-03-07T18:26:14.205044+01:00"
"datetime":"2023-03-07T18:26:14.205044+01:00"
"datetime":"2023-03-07T18:26:14.205044+01:00"
"datetime":"2023-03-07T18:26:14.205044+01:00"
"datetime":"2023-03-07T18:26:18.227253+01:00"
"datetime":"2023-03-07T18:26:18.227253+01:00"

Chrome里刷新该URL能正常每秒更新，Power Query也能实现每秒刷新，但我需要在VBA里直接获取结果，不想依赖工作表。

问题原因 #

WorksheetFunction.WebService默认带有缓存机制，相同URL的重复调用会直接返回缓存内容，不会发起新的HTTP请求，导致响应无法实时更新。

解决办法 #

改用MSXML2.XMLHTTP对象发送请求，通过两种方式绕过缓存，确保每次请求都获取最新响应：

方案1：设置请求头禁用缓存

Sub speedtest_no_cache()
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
    
    For f = 1 To 10
        Dim url As String
        url = "http://worldtimeapi.org/api/timezone/Europe/Amsterdam"
        
        xmlHttp.Open "GET", url, False
        ' 配置请求头，告知服务器不要返回缓存内容
        xmlHttp.setRequestHeader "Cache-Control", "no-cache"
        xmlHttp.setRequestHeader "Pragma", "no-cache"
        xmlHttp.send
        
        Debug.Print xmlHttp.responseText
        
        Application.Wait (Now + TimeValue("00:00:01"))
    Next f
    
    Set xmlHttp = Nothing
End Sub

方案2：给URL添加随机时间戳参数

通过修改URL添加唯一参数，让缓存机制认为是新请求：

Sub speedtest_timestamp()
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
    
    For f = 1 To 10
        Dim url As String
        ' 追加当前时间戳作为参数，确保每次URL唯一
        url = "http://worldtimeapi.org/api/timezone/Europe/Amsterdam?_=" & Timer
        
        xmlHttp.Open "GET", url, False
        xmlHttp.send
        
        Debug.Print xmlHttp.responseText
        
        Application.Wait (Now + TimeValue("00:00:01"))
    Next f
    
    Set xmlHttp = Nothing
End Sub

两种方案都能实现每秒获取最新的API响应，可根据实际场景选择使用。

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