英文:
how to improve speed in calling an API from Excel VBA
问题
如果我在Excel VBA中每秒调用WorksheetFunction.WebService，接收到的响应只在每4秒更新一次。
最初我注意到这个问题是在我的本地PC上的Excel和本地服务器之间存在的。尽管外部服务器也出现了相同的问题。可以通过调用下面的代码来测试这个问题，该代码每秒调用一个世界时间的API服务器：
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
如果在Chrome中提交并刷新URL，它可以每秒正确更新。
在Excel VBA中是否有一种方法可以每秒接收API响应？
我注意到如果将URL提交给Power Query，它可以每秒刷新一次，尽管我需要在VBA中获取结果，而不是在需要工作表的地方使用Power Query。
非常感谢！
英文:
If I call WorksheetFunction.WebService in Excel VBA once a second, the received response is only updated once every 4 seconds.
Initially I noticed this issue between Excel and a local server on my local pc.
Although an external server gives the same issue. This can be tested with calling the code below which calls an api server for the worldtime once a second:
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
>
"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"
If the url is submitted and refreshed in Chrome, it is updating once a second correctly.
Is there a method in Excel VBA to receive an API-response every second?
I noticed that if I submit the url to Power Query it can be refreshed once a second, although I need the result in VBA and not in a worksheet where Power Query seems to need a worksheet.
Thanks a lot!
答案1
得分: 0
我想你有一个缓存问题。
我编写了一个小测试例程，调用了该服务并暂停了1秒。
Dim i As Long
For i = 1 To 10
Application.WebService ""
Debug.Print i, Second(Now), city, Application.WebService("http://worldtimeapi.org/api/timezone/Europe/Amsterdam")
Sleep 1000
Next
结果看起来像这样：
1 :3 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:04.072029+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211344,"utc_datetime":"2023-03-07T17:49:04.072029+00:00","utc_offset":"+01:00","week_number":10}
2 :5 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:04.072029+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211344,"utc_datetime":"2023-03-07T17:49:04.072029+00:00","utc_offset":"+01:00","week_number":10}
3 :6 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:04.072029+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211344,"utc_datetime":"2023-03-07T17:49:04.072029+00:00","utc_offset":"+01:00","week_number":10}
4 :7 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:07.381876+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211347,"utc_datetime":"2023-03-07T17:49:07.381876+00:00","utc_offset":"+01:00","week_number":10}
5 :8 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:07.381876+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211347,"utc_datetime":"2023-03-07T17:49:07.381876+00:00","utc_offset":"+01:00","week_number":10}
6 :9 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:07.381876+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211347,"utc_datetime":"2023-03-07T17:49:07.381876+00:00","utc_offset":"+01:00","week_number":10}
7 :10 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:10.757293+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211350,"utc_datetime":"2023-03-07T17:49:10.757293+00:00","utc_offset":"+01:00","week_number":10}
8 :12 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:10.757293+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211350,"utc_datetime":"2023-03-07T17:49:10.757293+00:00","utc_offset":"+01:00","week_number":10}
9 :13 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:10.757293+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211350,"utc_datetime":"2023-03-07T17:49:10.757293+00:00","utc_offset":"+01:00","week_number":10}
10 :14 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:14.623928+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime
<details>
<summary>英文:</summary>
I guess you have a caching problem.
I wrote a small test routine that calls the service and pauses for 1s.
Dim i As Long
For i = 1 To 10
Application.WebService ""
Debug.Print i, Second(Now), city, Application.WebService("http://worldtimeapi.org/api/timezone/Europe/Amsterdam")
Sleep 1000
Next
The result looks like
1 :3 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:04.072029+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211344,"utc_datetime":"2023-03-07T17:49:04.072029+00:00","utc_offset":"+01:00","week_number":10}
2 :5 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:04.072029+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211344,"utc_datetime":"2023-03-07T17:49:04.072029+00:00","utc_offset":"+01:00","week_number":10}
3 :6 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:04.072029+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211344,"utc_datetime":"2023-03-07T17:49:04.072029+00:00","utc_offset":"+01:00","week_number":10}
4 :7 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:07.381876+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211347,"utc_datetime":"2023-03-07T17:49:07.381876+00:00","utc_offset":"+01:00","week_number":10}
5 :8 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:07.381876+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211347,"utc_datetime":"2023-03-07T17:49:07.381876+00:00","utc_offset":"+01:00","week_number":10}
6 :9 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:07.381876+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211347,"utc_datetime":"2023-03-07T17:49:07.381876+00:00","utc_offset":"+01:00","week_number":10}
7 :10 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:10.757293+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211350,"utc_datetime":"2023-03-07T17:49:10.757293+00:00","utc_offset":"+01:00","week_number":10}
8 :12 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:10.757293+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211350,"utc_datetime":"2023-03-07T17:49:10.757293+00:00","utc_offset":"+01:00","week_number":10}
9 :13 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:10.757293+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211350,"utc_datetime":"2023-03-07T17:49:10.757293+00:00","utc_offset":"+01:00","week_number":10}
10 :14 {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:49:14.623928+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211354,"utc_datetime":"2023-03-07T17:49:14.623928+00:00","utc_offset":"+01:00","week_number":10}
So I changed the code by introducing a logic that changes the city, resulting in different URLs
Dim cities
cities = Array("Paris", "Amsterdam", "Berlin")
Dim i As Long
For i = 1 To 10
Application.WebService ""
Dim city As String
city = cities(i Mod (UBound(cities) + 1))
Debug.Print i & " :" & Second(Now), city, Application.WebService("http://worldtimeapi.org/api/timezone/Europe/" & city)
Sleep 1000
Next
This gave me
1 :24 Amsterdam {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:25.298976+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211785,"utc_datetime":"2023-03-07T17:56:25.298976+00:00","utc_offset":"+01:00","week_number":10}
2 :26 Berlin {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:27.031239+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Berlin","unixtime":1678211787,"utc_datetime":"2023-03-07T17:56:27.031239+00:00","utc_offset":"+01:00","week_number":10}
3 :28 Paris {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:28.200192+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Paris","unixtime":1678211788,"utc_datetime":"2023-03-07T17:56:28.200192+00:00","utc_offset":"+01:00","week_number":10}
4 :29 Amsterdam {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:30.576139+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211790,"utc_datetime":"2023-03-07T17:56:30.576139+00:00","utc_offset":"+01:00","week_number":10}
5 :31 Berlin {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:31.760924+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Berlin","unixtime":1678211791,"utc_datetime":"2023-03-07T17:56:31.760924+00:00","utc_offset":"+01:00","week_number":10}
6 :32 Paris {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:33.032718+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Paris","unixtime":1678211793,"utc_datetime":"2023-03-07T17:56:33.032718+00:00","utc_offset":"+01:00","week_number":10}
7 :34 Amsterdam {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:35.399453+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211795,"utc_datetime":"2023-03-07T17:56:35.399453+00:00","utc_offset":"+01:00","week_number":10}
8 :36 Berlin {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:37.108448+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Berlin","unixtime":1678211797,"utc_datetime":"2023-03-07T17:56:37.108448+00:00","utc_offset":"+01:00","week_number":10}
9 :38 Paris {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:38.334731+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Paris","unixtime":1678211798,"utc_datetime":"2023-03-07T17:56:38.334731+00:00","utc_offset":"+01:00","week_number":10}
10 :39 Amsterdam {"abbreviation":"CET","client_ip":"2003:f8:af01:9e01:b035:2d9a:577:30e2","datetime":"2023-03-07T18:56:39.638523+01:00","day_of_week":2,"day_of_year":66,"dst":false,"dst_from":null,"dst_offset":0,"dst_until":null,"raw_offset":3600,"timezone":"Europe/Amsterdam","unixtime":1678211799,"utc_datetime":"2023-03-07T17:56:39.638523+00:00","utc_offset":"+01:00","week_number":10}
You see that the time constantly changes for every call.
(for the `Sleep`, see https://stackoverflow.com/a/41638733/7599798)
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论