Forum Discussion

axuary's avatar
axuary
Copper Contributor
Feb 14, 2021

STOCKHISTORY #BUSY until VBA finishes

I have set the formula2 property of a cell to STOCKHISTORY(... in a VBA macro.  Unfortunately, the cell value is #BUSY until after my macro ends.  I have tried using delays and DoEvents.  Neither see...
Macros and VBA
axuary's avatar
axuary
Copper Contributor
Feb 14, 2021

JoeUser2004  Thanks for the response.  Unfortunately, that doesn't seem to work.

 

JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Feb 14, 2021

axuary 

 

"Doesn't work" is a little unspecific.  But so was my first response.  I took a lot for granted.

 

First, it might help if you attached an Excel file that demonstrates the problem.  If you can take the time, scale the workbook down to just the STOCKHISTORY formula(s).

 

Something like the attached Excel file.  In VBA "module4", I simulate the STOCKHISTORY delay.  Execute VBA procedure "startit".  Procedure "checkit" periodically checks A1 (the simulated STOCKHISTORY formula) for a non-#BUSY result, up to 4 seconds.  You can adjust the frequency; I chose every 1/10 second.  Procedure "mystockhistory" runs in 2 to 3 seconds, simulating the completion of the STOCKHISTORY function.

 

Note: I use #N/A to simulate the #BUSY error, which my version of Excel does not support.

 

I also attach the VBA procedures in an MSWord file, for the faint at heart. (wink)

 

Does this help?