To know whether or not it's realistic to pull 10K results with VBA, you need to first split your monolith into smaller chunks, and see where it is exactly that the time is being spent.
Keep in mind that VBA code can only run on a single thread, so all you can do, is make sure your code is optimal for a single request, and then making 10K requests is just going to have to be 10K times that.
Idea: COM-Visible BackgroundWorker
If you have access to Visual Studio (.NET), you could make a COM-visible class library that exposes a
BackgroundWorker implementation that fires up COM-visible events - and then you could reference that library, and create a class to hold the state and results:
Private WithEvents worker As MyCOMVisibleLibrary.MyBackgroundWorker
Private Type TStockPriceResult
StockCode As String
PreviousClosedPrice As Double
DividendRate As Double
IsCompleted As Boolean
Private this As TStockPriceResult
Public Property Get StockCode() As String
StockCode = this.StockCode
Public Property Let StockCode(ByVal value As String)
this.StockCode = value
Public Property Get PreviousClosedPrice() As Double
PreviousClosedPrice = this.PreviousClosedPrice
Public Property Get DividendRate() As Double
DividendRate = this.DividendRate
Public Property Get IsCompleted() As Boolean
IsCompleted = this.IsCompleted
Private Sub worker_DoWork()
' implement background-thread code here
Private Sub worker_RunWorkerCompleted(ByVal HasError As Boolean, ByVal ErrorMessage As String, ByVal IsCancelled As Boolean)
this.IsCompleted = true
Public Sub DoWork()
DoWork handler, you'd set the private field values, and use the
RunWorkerCompleted handler to flag the instance as "completed", meaning the client code can now safely read the encapsulated values using the exposed property getters.
This means the calling code simply needs to create an instance of that class for each
StockCode that needs to be pulled, put them all in a collection and then call the
DoWork method on each instance - then you can loop/wait until all instances have
IsCompleted = True, and write the results to Excel - they should all run asynchronously, and gather their results from a separate background thread that's running in .NET managed code. So 10K requests made that way would probably complete much faster than the same 10K requests made from 100% VBA code.
The actual review
That's all nice, but there are a number of things to say about the code you have here:
- You've coded all the logic inside a button's
Click handler. Extract procedures, move that code elsewhere: there shouldn't be much code in a
- You need a method to enable/disable "wait mode", where Excel is not responsive and waiting for the data.
- You need a method to make the web request
- You need another method to inspect the response
- You need another method to write the results to Excel
- Indentation is lacking - use the Tab key to indent code blocks. A single space isn't indentation. All code blocks should have an indentation level - be it the
For, ... all clode blocks want to be indented.