VBA / Macro / Spilled Array / StockHistory

%3CLINGO-SUB%20id%3D%22lingo-sub-2621667%22%20slang%3D%22en-US%22%3EVBA%20%2F%20Macro%20%2F%20Spilled%20Array%20%2F%20StockHistory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2621667%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20struggling%20to%20get%20a%20simple%20VBA%20macro%20to%20get%20data%20from%20a%20stockhistory%20spilled%20array.%26nbsp%3B%20Please%20see%20screenshots.%26nbsp%3B%20Basically%2C%20the%20stockhistory%20function%20runs%20fine%20manually%20outside%20of%20a%20macro.%26nbsp%3B%20But%20if%20the%20stockhistory%20function%20attempts%20to%20pull%20stock%20data%20within%20a%20macro%2C%20it%20gets%20stuck%20on%20a%20%23Busy!%20error%20indefinitely%20until%20the%20macro%20is%20aborted.%26nbsp%3B%20As%20soon%20as%20I%20end%20the%20macro%20run%2C%20the%20spilled%20array%20will%20automatically%20finish%20populating%20without%20any%20additional%20input%20on%20my%20part.%26nbsp%3B%20This%20proves%20to%20me%20that%20there's%20nothing%20wrong%20with%20the%20input.%26nbsp%3B%20For%20some%20reason%2C%20Excel%20does%20not%20want%20to%20finish%20performing%20this%20stockhistory%20function%20while%20the%20macro%20is%20still%20running%2C%20or%20while%20in%20debugging%2Fstep%20mode.%26nbsp%3B%20I%20don't%20know%20if%20this%20is%20an%20issue%20with%20stockhistory%2C%20with%20spilled%20arrays%2C%20with%20pulling%20info%20from%20external%20sources%2C%20etc.%26nbsp%3B%20I'm%20hoping%20there's%20just%20a%20setting%20or%20a%20trick%20that%20I%20don't%20know%20about%20yet.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2621667%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2621933%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20%2F%20Macro%20%2F%20Spilled%20Array%20%2F%20StockHistory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2621933%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053078%22%20target%3D%22_blank%22%3E%40djk5214%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20it%20that%20the%20macro%20does%20that%20STOCKHISTORY%20by%20itself%20won't%20do%20in%20your%20situation%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20ask%20as%20a%20person%20who%20avoids%20macros%20unless%20absolutely%20necessary%20(and%20that%20condition%20rarely%20arises).%20So%20you%20can%20take%20my%20question%20with%20a%20grain%20of%20salt%20if%20you're%20a%20true%20believe%20in%20macros%2C%20but%20please%20hear%20me%20when%20I%20say%20I've%20often%20seen%20the%20situation%20where%20people%20jump%20to%20macros%20far%20sooner%20than%20they%20need%20to.%20Excel's%20built-in%20functions%20are%20amazingly%20powerful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2622829%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20%2F%20Macro%20%2F%20Spilled%20Array%20%2F%20StockHistory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2622829%22%20slang%3D%22en-US%22%3EThe%20macro%20in%20the%20screenshot%20is%20just%20a%20basic%20script%20to%20show%20what%20my%20issue%20is.%20In%20actuality%2C%20I%20want%20to%20test%20my%20strategies%20on%20a%20hundred%20different%20stocks%20or%20so.%20So%20instead%20of%20me%20manually%20performing%20a%20stock%20history%20lookup%20on%20every%20single%20stock%2C%20I%20want%20a%20macro%20to%20automatically%20run%20through%20a%20list%20of%20stocks%20and%20collect%20data%20from%20each%20stockhistory%20lookup.%20That%20way%2C%20I%20just%20click%20%22run%22%20once%20and%20I%20get%20my%20list%20of%20data.%20It%20would%20save%20me%20a%20huge%20amount%20of%20time.%3C%2FLINGO-BODY%3E
Occasional Contributor

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! 

3 Replies

@djk5214 

 

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.

The macro in the screenshot is just a basic script to show what my issue is. In actuality, I want to test my strategies on a hundred different stocks or so. So instead of me manually performing a stock history lookup on every single stock, I want a macro to automatically run through a list of stocks and collect data from each stockhistory lookup. That way, I just click "run" once and I get my list of data. It would save me a huge amount of time.
And regardless of what I plan to use the macro for, I still would like to understand why this specific function does not want to work within a macro. This could have implications for a wide variety of uses. If the issue is with spilled arrays itself, then stockhistory isn't the only way to create a spilled array, and I'm sure others would like to know how to fix this issue as well.