How to VBA callback function when XMLHTTP onTimeOut?

Question

I'm trying get xml data from webserver to excel, then I wrote a sendRequest function to call in excel

=sendRequest("http://abb.com/index.php?id=111")

When web-server having trouble, cannot connect or cannot find, excel is not responding, it was horrible! To avoid it, i think we should set timeOut. These are my function:

Function sendRequest(Url)
    'Call service
    Set XMLHTTP = CreateObject("Msxml2.ServerXMLHTTP.6.0")

    'Timeout values are in milli-seconds
    lResolve = 10 * 1000
    lConnect = 10 * 1000
    lSend = 10 * 1000
    lReceive = 15 * 1000 'waiting time to receive data from server
    XMLHTTP.setTimeOuts lResolve, lConnect, lSend, lReceive

    XMLHTTP.OnTimeOut = OnTimeOutMessage 'callback function

    XMLHTTP.Open "GET", Url, False

    On Error Resume Next
    XMLHTTP.Send
    On Error GoTo 0

    sendRequest = (XMLHTTP.responseText)
End Function

Private Function OnTimeOutMessage()
    'Application.Caller.Value = "Server error: request time-out"
    MsgBox ("Server error: request time-out")
End Function

Normally, when XMLHTTP's timeout occurs, event OnTimeOutMessage will be executed (reference #1, #2). But as in my test, OnTimeOutMessage is executed right at the beginning of sendRequest()

How to use callback function when Msxml2.ServerXMLHTTP.6.0 request is time-out?

Thank for your help!

Possible approach here: http://www.dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/ — Tim Williams, Jul 10 '12 at 06:03
@TimWilliams Thank you! but i don't find Async solution. I'm just want to use Callback function when xmlhttp on timeOut. I've a small reason to do it, because my webserver is weak, it will be `Connection refused` if I send so many request from `sendRequest - Excel` in the same time. — Davuz, Jul 10 '12 at 06:29
If you don't want excel to lock up while your xmlhttp is waiting for a response then you'll have to use asynchronous calls. You can use that to handle the other events. — Tim Williams, Jul 10 '12 at 14:46

score 5 · Accepted Answer · answered Jul 10 '12 at 09:47

5

The line;

XMLHTTP.OnTimeOut = OnTimeOutMessage

Is not a method assignment; rather it immediately executes OnTimeOutMessage() (and assigns its useless return value to OnTimeOut).

The equivalent line in JavaScript as per your example link correctly assigns a Function object to OnTimeOut for subsequent invokation - this is not supported by VBA.

Instead, you could trap the timeout error raised after .send or use early binding, WithEvents, & inline event handlers.

answered Jul 10 '12 at 09:47

Alex K.

171,639
30
264
288

1

Oh, so many solution! Can you clearly infomation? How to trap the timeout error raised after .send`? I've searched `early-bidding` but I found more `late-binding` result :( Can use `withEvents` [as here][1]? What is inline event handler? Thanks a lot! [1]http://www.cpearson.com/excel/Events.aspx – Davuz Jul 11 '12 at 03:17

score 3 · Answer 2 · 2012-07-10T18:54:37.470

Tim is correct in that if you have timeouts then each request will 'hang' Excel/VBA until the timeout duration has elapsed before continuing. Using async will allow you multiple requests without a long request delaying either a response or further requests.

The status property represents the HTTP status code returned by a request. Just place the code below in your existing code for a slower synchronous check or move your response processing to an event handler for async.

XMLHTTP.Send

If XMLHTTP.Status = "200" Then
    '200      OK
    htmlString = XMLHTTP.ResponseText
Elseif XMLHTTP.Status = "408" Then
    '408      Request Timeout
    Call OnTimeOutMessage
else
    'All status return values
    'Number      Description
    '100      Continue
    '101      Switching protocols
    '200      OK
    '201      Created
    '202      Accepted
    '203      Non-Authoritative Information
    '204      No Content
    '205      Reset Content
    '206      Partial Content
    '300      Multiple Choices
    '301      Moved Permanently
    '302      Found
    '303      See Other
    '304      Not Modified
    '305      Use Proxy
    '307      Temporary Redirect
    '400      Bad Request
    '401      Unauthorized
    '402      Payment Required
    '403      Forbidden
    '404      Not Found
    '405      Method Not Allowed
    '406      Not Acceptable
    '407      Proxy Authentication Required
    '408      Request Timeout
    '409      Conflict
    '410      Gone
    '411      Length Required
    '412      Precondition Failed
    '413      Request Entity Too Large
    '414      Request-URI Too Long
    '415      Unsupported Media Type
    '416      Requested Range Not Suitable
    '417      Expectation Failed
    '500      Internal Server Error
    '501      Not Implemented
    '502      Bad Gateway
    '503      Service Unavailable
    '504      Gateway Timeout
    '505      HTTP Version Not Supported

End If

Thank @osknows! How to use your code? When `XMLHTTP.Send` timeout, `XMLHTTP.Status` is empty. — Davuz, Jul 11 '12 at 04:41
If you want to specifically handle the OnTimeOut event then set up a class module similar to the link Tim provided. If the .Status returns an error then 'htmlString = XMLHTTP.ResponseText' will also cause an error; if you handle the error correctly then you can test for htmlString = vbnullstring and know you have an error. Your original link returns 404 status for me btw. — , Jul 11 '12 at 12:38
I've tried with async method and `setTimeouts` but my function exit at anywhere, i cannot catch error 408 - `request timeout` in about code. where to put your code in demo at http://www.dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/ — Davuz, Jul 18 '12 at 09:13

How to VBA callback function when XMLHTTP onTimeOut?

2 Answers2

Linked