STOCKHISTORY #BUSY until VBA finishes

[axuary]

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 seems to help.  It looks like Excel waits until the macro is finished until it evaluates the function.  Is there anything I can do to make it evaluate it while macro is running?

[#2]

I can only make guesses, because my version of Excel does not support STOCKHISTORY.

I suspect that Application.OnTime will work. (Note: __Not__ Application.Wait.)

However, that effectively suspends your macro. It does __not__ permit concurrent Excel and VBA operations. Nothing does, AFAIK. Excel does not permit it, by design (with some exceptions).

Also, it might be difficult to redesign your VBA procedure so that it can be split into two procedures, the second one being the "continuation" when the OnTime event occurs.

I suspect that Application.OnTime Now+TimeSerial(0,0,1) will suffice for your purposes.

But FYI, Now+TimeSerial(0,0,1) does not always wait a full 1 sec before triggering the event. On average, it waits 1/2 second; but it might be less than 1/64 second. The reason is: VBA Now rounds time down to the second; and system time is usually updated every 1/64 second. (But some applications change that frequency.)

Nevertheless, I suspect that once the Excel thread starts the STOCKHISTORY operation, the OnTime event is deferred until at least STOCKHISTORY is done, if it does not execute in background.

[#3]

@Joe User  Thanks for the response.  Unfortunately, that doesn't seem to work.

[#4]

@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?