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