STOCKHISTORY: Is "#BUSY!" a real error

[mookyon]

Dear Members,

I am using STOCKHISTORY function and VBA (on 365).

When STOCKHISTORY function is operational, Excel shows #BUSY! “error like” message where results should appear later when retrieval is done.

This is an Error-Like message since it is never reflected neither in IFERROR, nor in functions (e.g., ISERROR, or ERROR.TYPE).

While retrieval is active (and #BUSY! is shown on cells) VBA stops. I found no way to check whether the target cell had finished displaying #BUSY! and now displays the required stock price.

Do you think asynchronous processing will work here?

If anyone had resolved this issue, please kindly advise.

1000 THX

 

[smozgur]

I think you can check if the cell value returns an error or not by using the IsError function.

If IsError(TargetCell) then
' Error
end if

However, I believe you'll need to use a loop to continue VBA execution after waiting #BUSY! returns to a value OR another error perhaps.
Maybe something like this (I am not familiar with the STOCKHISTORY function, so you can test with your formula instead):

Sub doIt()
    Range("A1").Formula = "=STOCKHISTORY(""MSFT"", TODAY())"
   
    Do While CStr(Range("A1")) = "Error 2051" ' #BUSY! error value
        DoEvents
        Debug.Print "Still BUSY!"
    Loop
    Debug.Print "Not BUSY anymore"
End Sub

 

[mookyon]

Thank you very much smozgur,

I checked both options, but none worked.

While STOCKHISTORY displays #BUSY!, IsError (VBA function) will be true. However as long as VBA is running #BUSY! will not change. You’ll need to stop VBA in order to finish #BUSY!

Same applies to checking CStr(Range("A1")) = "Error 2051"
(and Debug.Print "Still BUSY!" will become an endless loop)

I am still looking for a working solution…

 

[smozgur]

I actually posted the second code that works as it is expected to demonstrate how it works when VBA is working. This is what I get on my computer:

However, it really depends on your code. Perhaps it is somehow blocking auto-calculation. For me, it is hard to say something without seeing the entire process.

 

[mookyon]

Dear [FONT=arial]smozgur[/FONT],

THX again. I don't know why but, my system keeps printing "Still Busy" endlessly. Never shows "Not BUSY Anymore".

The formula shows #BUSY! until I click the Reset (square) button on the VBA screen. Then immediately the result is properly displayed in A1.

I wonder why we see different results.
(I use Microsoft® Excel® for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20188) 64-bit)

 

[robintong]

Not sure if your issue is resolved. I faced exact same issue and tried above solution. What I see is whenever #BUSY! is triggered, the calculation stops running in the Do While loop. I finally turned on "Enable iterative calculation" in the options page and it works fine. Hope this helps

 

[Ronaldj]

Interesting ! ! !
My experience:
1) Putting the StockHistory function directly into a SpreadSheet cell (no VBA)
Single Stock, about 250 Day date range. No Busy issue.
2) Using VBA and data into a VBA array (StkData) with
StkData = WorksheetFunction.StockHistory(TabName, PeriodStartDate, PeriodEndDate, 0, 1, 0, 5, 2, 3, 4, 1)
No Busy issue
For debug purposes, I then copied the StkData into SpreadSheet cells
It was quick
3) After gaining confidence that things were working Ok, I began to wonder if I was using too much memory
having put the same data into a VBA array and into SpreadSheet cells.
(I experienced difficulty saving the file after the data had been loaded. Also a lot of charts generated.)
So, I began the approach below (DQ = Chr(34) i.e. """")
StkFormula = "=StockHistory(" & DQ & TabName & DQ & "," & DQ & PeriodStartDate & DQ & "," & DQ & PeriodEndDate & DQ & ", 0, 1, 0, 5, 2, 3, 4, 1)"
Cells(1, SheetStkDataCol).Formula2 = StkFormula
Now, this is where I experienced the @Busy message in the cell where the formula was placed.
Eventually (seemed a long time) the data was populated, but the VBA function could not proceed normally.
Seems to me there is something not good about VBA putting the formula into a cell.
Getting the data into a VBA array seems a better approach. Perhaps one could copy from it into the Spreadsheet cells and release the VBA array memory?

Good wishes