How to VBA sends an Async XMLHTTP request?

Question

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

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

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.

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

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

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.

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 async method.

Answer 1

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

Answer 2

This is my solution file for 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 formula by using Range(yourCellAddress).NumberFormat = "0;0;0;""Value to display"""

So to your question, the solution is

1. In your sendAsyncRequest function replace the return line as

   sendAsyncRequest = "anything other than numbers"

2. In your ReadyStateChangeHandler sub, replace

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

by

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