VBA 如何发送异步 XMLHTTP 请求？

How to VBA sends an Async XMLHTTP request?

提问by Davuz

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")

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

I have 2 excel files to demostrate the sending requestin two methods: 1.synchronousand 2.asynchronous

我有 2 个 excel 文件来演示sending request两种方法：1.同步和 2.异步

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

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

My current solution for this is to use asyncmethod as below code

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

Public Function sendAsyncRequest(URL)
    'other statement

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

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

但是单元格的值总是零 0 而不是响应文本。

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

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

So my question is How to change the display text of cell without changing its formula?

所以我的问题是如何更改单元格的显示文本而不更改其公式？

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

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

采纳答案by Nam G VU

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

作为此处所述解决方案的缺点，获取函数的异步返回值的正确方法是 1)将您的缓存缓存request's url => returned value到集合/字典，然后 2)刷新您的公式

回答by Nam G VU

This is my solution filefor your question; indeed it is updated from your async test file.

这是我针对您的问题的解决方案文件；实际上它是从您的异步测试文件更新的。

Based on this discusion, you can change the display text of cell without changing its formulaby using Range(yourCellAddress).NumberFormat = "0;0;0;""Value to display"""

基于此讨论，您可以change the display text of cell without changing its formula使用 Range(yourCellAddress).NumberFormat = "0;0;0;""Value to display"""

So to your question, the solution is

所以对于你的问题，解决方案是

1. In your sendAsyncRequestfunction replace the return line as

   sendAsyncRequest = "anything other than numbers"

2. In your ReadyStateChangeHandlersub, replace

   Application.Range(cellAddress).Value = XMLHttpReq.responseText 'return responseText to cell

1. 在您的sendAsyncRequest函数中，将返回行替换为

   sendAsyncRequest = "anything other than numbers"

2. 在你的ReadyStateChangeHandler潜艇中，更换

   Application.Range(cellAddress).Value = XMLHttpReq.responseText 'return responseText to cell

by

经过

cellDisplayText = XMLHttpReq.responseText
Range(cellAddress).NumberFormat = "0;0;0;""" & cellDisplayText & """"