Results 1 to 2 of 2

Thread: Asynchronous Function Calls

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2018

    Asynchronous Function Calls


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

  2. #2
    Fanatic Member
    Join Date
    Feb 2013

    Re: Asynchronous Function Calls

    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
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width