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.

My answer has a critical downside and absolutely should NOT be the accepted one. Please uncheck it. — Nam G VU
– Nam G VU, Commented Jul 12, 2012 at 12:23

ashleedawg · Accepted Answer · 2019-01-22 23:16:46Z

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

edited Jan 22, 2019 at 23:16

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

answered Jul 15, 2012 at 11:38

Nam G VU

35.8k79 gold badges248 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:52:44Z

1

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

In your sendAsyncRequest function replace the return line as

sendAsyncRequest = "anything other than numbers"
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 & """"

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Jul 11, 2012 at 14:13

Nam G VU

35.8k79 gold badges248 silver badges399 bronze badges

2 Comments

Nam G VU Over a year ago

I also refactor the solution for a better code here dl.dropbox.com/u/6194904/2012/…

Nam G VU Over a year ago

One downside of this solution is that the value we see as the displayed text is NOT actually the cell value; the cell value is "anything other than numbers". So if the cell A1 's displayed value is 122 and we use it in a formula of cell A2, say =A1+1, then the value is NOT 123 but results as error (due to adding string to a number)

Collectives™ on Stack Overflow

How to VBA sends an Async XMLHTTP request?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related