VBA 如何发送异步 XMLHTTP 请求? [英] How to VBA sends an Async XMLHTTP request?

问题描述

为了从 Excel 中的 web 服务获取数据，我创建了一个 excel 插件提供函数来获取它；用户只需要输入单元格:=sendRequest("http://webservice.com")

To get data from a webservice in Excel, I have created an excel addin provide function to get it; user just need type in cell: =sendRequest("http://webservice.com")

我有 2 个 excel 文件以两种方法演示 发送请求:1.同步和 2.异步

I have 2 excel files to demostrate the sending request in two methods: 1.synchronous and 2.asynchronous

在sync方法中，函数可以正常发送请求和获取数据.但是，如果我们有 100、200 个单元格调用它，Excel 将花费大量时间等待；这也使 Exel 不响应.

In sync method, the function can send request and get data normaly. But, if we have 100, 200 cells that call it, it will take Excel a huge amount of time waiting; which also makes Exel to be not respoding.

我目前的解决方案是使用 async 方法作为 下面的代码

My current solution for this is to use async method as below code

Public Function sendAsyncRequest(URL)
    'other statement

    ' Get some stuff asynchronously.
    xmlHttpRequest.Open "GET", URL, True
    xmlHttpRequest.send
    sendAsyncRequest = xmlHttpRequest.responseText
End Function

但单元格的值始终为零 0 而不是响应文本.

But the cell's value is alway is zero 0 instead of repsone text.

我必须使用我的处理程序类将其与 xmlHttpRequest object 的 OnReadyStateChange 绑定，以将响应文本设置到单元格中.但是，它也会清除单元格的公式.

I must use my handler class to bind it with OnReadyStateChange of xmlHttpRequest object to set response text into cell. But then, it also clears the cells' formulas.

所以我的问题是如何在不改变公式的情况下改变单元格的显示文本?

我也欢迎在 async 方法下发送请求并获取返回值的另一种解决方案.

I also welcome another solution to send request and get return value under async method.

推荐答案

正如此处所述解决方案的缺点，获取函数异步返回值的正确方法是 1) cache 你的 request 的 url =>返回值 到集合/字典，然后 2) 刷新您的公式

As the downside of the solution stated here, the proper way to get the async return value for the function is to 1) cache your request's url => returned value to a collection/dictionary, and then 2) refresh your formula

这篇关于VBA 如何发送异步 XMLHTTP 请求?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！