Aug 06 2021 12:32 PM
Hi, I'm struggling to get a simple VBA macro to get data from a stockhistory spilled array. Please see screenshots. Basically, the stockhistory function runs fine manually outside of a macro. But if the stockhistory function attempts to pull stock data within a macro, it gets stuck on a #Busy! error indefinitely until the macro is aborted. As soon as I end the macro run, the spilled array will automatically finish populating without any additional input on my part. This proves to me that there's nothing wrong with the input. For some reason, Excel does not want to finish performing this stockhistory function while the macro is still running, or while in debugging/step mode. I don't know if this is an issue with stockhistory, with spilled arrays, with pulling info from external sources, etc. I'm hoping there's just a setting or a trick that I don't know about yet.
Thanks for any help!
Aug 06 2021 01:44 PM
What is it that the macro does that STOCKHISTORY by itself won't do in your situation?
I ask as a person who avoids macros unless absolutely necessary (and that condition rarely arises). So you can take my question with a grain of salt if you're a true believe in macros, but please hear me when I say I've often seen the situation where people jump to macros far sooner than they need to. Excel's built-in functions are amazingly powerful.
Aug 06 2021 07:44 PM
Aug 06 2021 08:33 PM
Feb 06 2023 10:35 AM
Feb 06 2023 10:37 AM
your question was asked about a year and a half ago. I am experiencing exactly the same issue. Did you ever get a solution??
Regards
cmsleopoldo