Asynchronous Function Calls

[kd13]

Greetings,

I'm in a bit of a dilemma and hope that someone can help

For reference, the module I am using can be found here: https://github.com/VBA-tools/VBA-Web



I need to develop an excel add-in that plugs some data from a cell into a function, sends that data to the backend of a web request, then prints the data out in a response. This isn't the difficult part, the hard part is making it faster. My boss requires that I be able to make about 2000 queries in as little time as possible.


To make it faster, I decided to use the Asynchronous wrapper class module in the reference I posted above. Basically, the requests can be fired off rapid fire (as opposed to the back and forth nature that I originally implemented) our backend processes them as they are received, then returns the value.

My code:

Public AsyncResultsDictionary As New Dictionary

Function Query(ByVal securityID, ByVal quote_type, ByVal field)
  Dim dict_key = securityID & quote_type & field
  If AsyncResultsDictionary.Exists(dict_key) Then 
    Query = AsyncResultsDictionary(dict_key)
    AsyncResultsDictionary.Remove(dict_key)
    Exit Function
  End If

  Args = Array(quote_type, field, Application.Caller.Address, securityID, dict_key)
  Call ConnectToBackEnd(Args)
  Query = "Loading..."

End Function

Function ConnectToBackEnd(callbackArgs)
  Dim Request As New WebRequest
  Dim Client As New WebClient
  Client.BaseUrl = "http://backend.com"
  Dim Wrapper As New WebAsyncWrapper
  Set Wrapper.Client = Client

  Dim Body As New.Dictionary
  Body.Add "securityID", callbackArgs(3)
  Body.Add "quoteType", callbackArgs(0)
  Body.Add "field", callbackArgs(1)
  
 Set Request.Body = Body
 Request.Method = HttpPost

  Wrapper.ExecuteAsync Request, "QueryHandler", callbackArgs

End Function

Public Function QueryHandler(Response As WebResponse, callbackArgs)
  AsyncResultsDictionary(callbackArgs(4)) = Response.Content
  Range(callbackArgs(2)).Value = "=Query(~~~)" ' all the arguments are passed in, I'm just too lazy to type it out
End Function

Breakdown of my logic:
I'm using an async call to make this faster. The problem however is that VBA doesn't have any way to alert the original Query method when the results of the asynchronous call are done(async await, promises etc), so I cannot pass the value from my QueryHandler function back up to my Query function. Therefore I decided to define the AsyncResultsDictionary. When the function is first called, it gets the data from our backend, creates a key and stores the data in the dictionary to that key. In the QueryHandler function, I invoke the Query function a second time. The query function checks the dictionary to see if the key is there. When it finds the key, it returns the value to the function. The reason I need to do it like this is so I can retain the formula. The user will need to refresh, so to just set Range(callbackArgs(2)).Value equal to the value from our backend, the cell loses the formula and the user needs to retype everything in order to get new values.

With this current implementation, I am getting an out of stack space error. My guess is that it's because each function essentially has to call itself twice. Given the 2000 constraint, that's about 4000 function calls at once.

I think I see some room for improvement, I can change the ConnectToBackend and QueryHandler functions to Subs. I'm wondering if because I have them as functions, but they do not return anything, if they remain on the stack. Would a sub immediately be removed?

Ultimately what I am looking for:
1) A way to speed up web requests between vba excel and a website or
2) A way to return the value from QueryHandler back up to Query or
3) A way to free up stack space so I don't get the out of stack space error or
4) Any suggestions in general

Thanks for your time!

[GBeats]

I came across some code on excel/vba a year ago and i have never used it myself but i downloaded it because it was very interesting and was a possible fix for me at the time where i 'needed to speed things up' i never used it but it may or may not help u.

it is a multi threading hack for excel, it basically opens up multiple excel applications and runs any code you want and returns all the data into the parent, i have no idea how buggy this maybe but it maybe worth a look.VBA Multithreading Tool_20141221.zip