search

VBA-tools / VBA-Web

VBA-Web: Connect VBA, Excel, Access, and Office for Windows and Mac to web services and the web
http://vba-tools.github.io/VBA-Web/
MIT License
2.01k stars 494 forks source link

WebAsyncWrapper available on Mac #371

Closed barnabygordon closed 5 years ago

barnabygordon commented 5 years ago

I'm trying to execute a large number of http requests across many cells using the same function but anything more than 10 function calls is just too slow.

I'm hoping that the WebAsyncWrapper can help me out (from what I've read here https://github.com/VBA-tools/VBA-Web/issues/352) but I need my module to run on Mac's as well as Windows systems.

Is any work being done (or can be done) to adapt the WebAsyncWrapper for use on Mac?

timhall commented 5 years ago

@barnabygordon I've looked really hard at possible options for this and there are some possibilities for adding async support on Mac, but they are all very difficult / may not work. It should be simple to allow Excel / OS a chance to do some work with DoEvents (I'll look into adding this), but processing requests in parallel is difficult. popen for libc on Mac runs cURL asynchronously, but it is synced in VBA by reading from the file (used in ExecuteInShell). If I could open / schedule an event loop to perform these reads it would be async, but I haven't found a good way to do that. Another option would be to defer it so that it runs at a slightly later time (ideally 1ms) so that all the requests can be started at the same time. OnTime seems like a good way to do this, but it only has 1 second resolution, so any async request would take at least a second to resolve.

Another thing to note is that if you are making a web request as part of a worksheet function (e.g. =STOCKVALUE(A1)) then this can't be async since Excel does some low-level work to prevent accessing the cell after the worksheet function has run (at least I've tried and can't find a way, maybe you could with a ton of stored state)

I would try adding a DoEvents before the end of the loop here and see if that speeds it up somewhat. (If it does, please let me know and I'll make the change to WebHelpers)

https://github.com/VBA-tools/VBA-Web/blob/ee6f34de9c534560b145002b5e8a28fdf2eceba7/src/WebHelpers.bas#L1638-L1645

barnabygordon commented 5 years ago

@timhall thank you for the rapid response! I just tried modifying ExecuteInShell by adding a DoEvents but it doesn't seem to be affecting the speed at all unfortunately.

I am executing my function as part of a worksheet (e.g. =STOCKVALUE(A1)) and then copying this across multiple cells (>100). This is probably out of the scope of VBA-Web, but could a the solution to this issue might be to somehow intercept the function calls as they are made in order to group them?

The api that I'm using can accept an array or arguments and return multiple values, so a more efficient way to approach this may be to execute a single http request and then distribute the response amongst each of the cells. Do you know if this is at all possible?