Async VBA Function

Question

I have a VBA function that is supposed to get some information from the user's cell, make a POST request with that info, then print the response in the output cell.

It's required that the user be able to make about 2000 requests at a time, so I thought to make the requests async to help improve performance.

As it stands right now, I have a function ConnectToAPI that makes the asynchronous request, then passes the response off to a callback function. The problem I'm having is that the data lives in the callback function, but I need it in the query function in order to return it.

Function Query(ID, quote, field)  
  Application.Volatile
  Query = ConnectToAPI(ID)
  Some logic with parsed data from callback
End Function


Function ConnectToAPI(ID)

    Dim Request As New WebRequest
    Dim Client As New WebClient
    Client.BaseUrl = "http://www.endpoint.com"

    Dim Wrapper As New WebAsyncWrapper
    Dim Wrapper.Client = Client
    Dim Body As New Dictionary
    Body.Add "ID", ID
    Set Request.Body = Body
    Request.Method = HttpPost

    ConnectToAPI = Wrapper.ExecuteAsync Request, "CallbackFunction"
End Function

Function CallbackFunction
    Callback = Parsed Data
End function

So ultimately in the query function, I want to write

Query = (Parsed Data From the Callback)

How can I pass the data from the callback back up to query?

It is important that the cell have the Query function in it. The data updates frequently, so we want clients to be able to calculate the workbook to get the newest data.

With what I currently have, my thought process is that the callback will pass the data back to ConnectToAPI, then that will be passed up to Query. However, my function returns 0 and I think this might be that the parsed data is not available once the function tries to return.

For reference, I am using the VBA-Web library https://github.com/VBA-tools/VBA-Web

Some questions, 1) what is a "Post Request"? Be specific or provide a link. 2) A lot of what you are talking about here is very unclear, please provide some code and/or examples. — RBarryYoung
– RBarryYoung, Commented Jul 17, 2018 at 19:38
Are you perhaps talking about a POST request to a web server through HTTP? — RBarryYoung
– RBarryYoung, Commented Jul 17, 2018 at 19:39
You'll have to establish a global "mapping" of the requests (perhaps including a request "ID" that is returned in the response) that indicates the destination cell for the response. — PeterT
– PeterT, Commented Jul 17, 2018 at 19:39
We're going to need some of your code to help you here, but if you want asynchronous VBA code, you'll need to use an asynchronous API, and look into class modules, Event, RaiseEvent, and WithEvents keywords. — Mathieu Guindon
– Mathieu Guindon, Commented Jul 17, 2018 at 19:43

TinMan · Accepted Answer · 2018-07-18 02:02:40Z

3

VBA-Web/src/WebAsyncWrapper.cls

WebAsyncWrapper.ExecuteAsync has an optional parameter: CallbackArgs. Use this parameter to pass back you an ID or a cell address.

ExecuteAsync has an example callback function that receives an Array of arguments.

Here is how you can get the information back to the function for processing.

Sub ConnectToAPI(ID As Variant, quote As Variant, field As Variant, CellAddress As Variant)

    Dim Request As New WebRequest
    Dim Client As New WebClient
    Client.BaseUrl = "http://www.endpoint.com"

    Dim Wrapper As New WebAsyncWrapper

    Dim Body As New Dictionary

    Body.Add "ID", ID
    Set Request.Body = Body
    Request.Method = HttpPost
    Set Wrapper.Client = Client
    Wrapper.ExecuteAsync Request, "Callback", Array(ID, CellAddress)
End Sub

Public Function Callback(Response As WebResponse, Args As Variant)
    Dim ID As Variant, CellAddress As Variant
    ID = Args(0)
    CellAddress = Args(1)
    With Worksheets("Web Requests")
        .Range(CellAddress).Value = Response
        .Range(CellAddress).Offset(0, 1).Value = ID
    End With
End Function

MSDN - Application.Volatile Method (Excel)

Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

I would not recommend trying to have a UDF that can be used as a worksheet function to return the web-requests. Application.Volatile will cause all 2000 queries to refresh every time a value is changed. When the first query updates all the other queries will refresh. This will cause an infinite loop and crash the application.

Function Query(ID, quote, field)  
  Application.Volatile
  Query = ConnectToAPI(ID)
  Some logic with parsed data from callback
End Function

Using the Worksheet_Change event would give the users the ability to update the information without the problems associated with Application.Volatile.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("A")) Is Nothing Then
        If Target.Count = 1 Then
            Debug.Print Target.Value, Target.Address
        End If
    End If
End Sub

answered Jul 18, 2018 at 2:02

TinMan

7,8142 gold badges13 silver badges24 bronze badges

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

7 Comments

K Walsh Over a year ago

Thanks TinMan. Let me just make sure I follow. Are you suggesting that I pass in a cell address, and write the response from the callback to that cell in a separate sheet (Web Requests in your example)? If I'm correct, I'd be able to use that value in the function, but only if it is available in time from the callback, so I think I'd have the same problem. As for Application.Volatile, it's a constraint placed on me by my boss. I agree with you, but she is adamant about having it this way.

AJD Over a year ago

@KWalsh: I second, third and fourth what TinMan says about Application.Volatile. In this instance, I would request in writing from your boss the "guidance" that Application.Volatile is to be used, and that she will take all responsibility for the performance issues. Seriously - Application.Volatile is really bad news - like nitroglycerine, useful in a very small handful of cases otherwise there are many safer and better ways of achieving the result. In addition to the link TinMan provided, look up "Excel Volatile Functions" in your search engine to get a better idea of what this means.

TinMan Over a year ago

You will need to pass a unique identifier to the wrapper class to be passed back to the callback function; so you will know how to process the data.

TinMan Over a year ago

In my pseudocode Web Requests was meant to refer to the same worksheet using the Worksheet_Change event. Because the callback function (which can actually be a Sub) needs to be in a public module, it will need to know what worksheet the address belongs to.

TinMan Over a year ago

If you absolutely must use an UDF with Application.Volatile, you could have the UDF return the value of the Target cell address. I would initially set the Target cell's value to Loading... and in adjacent cells track the status and timestamps of the request. In this way, you will be able to update the UDF's without triggering requeries. You will also need a mechanism to continue any queries that did not return a result before the workbook was closed.

|

K Walsh · Accepted Answer · 2018-07-31 16:04:49Z

0

I ended up populating a dictionary with the response values from the API call, then recursively calling the query function in the callback.

Query checks if the response value is in the dictionary, if it is, then it returns it. If not, it connects to the api, the callback puts the value in the dictionary, and also calls the query function again.

answered Jul 31, 2018 at 16:04

K Walsh

493 silver badges7 bronze badges

Collectives™ on Stack Overflow

Async VBA Function

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related