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:
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!
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:
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 FunctionBreakdown 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!
