I have been struggling for a week or so trying to figure this out and I am having no luck so I am sending it out to you all in hopes of some help/advice.
I have hobbled together a macro that downloads and analyzes stocks. To get the stocks historical prices I use the following code (borrowed from diytraders):
This code works fine...for a while then will hang and force me to close and restart on/around this line:
I have used Fiddler to monitor the traffic in and out of my computer so I know that it is sending the request properly and that the response in coming back on-time and in the correct format. This code typically hangs after 10-40 loops (one loop per stock) but isn't very consistent on where it decides to give up.
I have previous versions of this code in earlier workbooks and they run just fine hundreds of times without fail. The main difference in the code now is that I have added a few subroutines to do more evaluations as well as checking other sheets (a fair amount of Selection.Find) and adding/subtracting data from them depending on the analysis.
After a lot of google searching I have tried many things: Turned off screen updating... no luck, set most of my arrays and ranges to "Nothing" when not in use...no luck, deleted all connections to the workbook...no luck.
Here is where things get very strange.
As I stated earlier, previous version workbooks work just fine. If I have the problematic workbook open along with a previous version workbook I can run the macro on the previous version workbook and it will obviously run fine. Now, if I go ahead and run my macro on the problematic workbook it will now run flawlessly! So by running my previous workbook with the problem one open, something happens that allows the broken code to run, but only if a previous working version runs first. Keep in mind that the XMLHTTP code is not altered in any way in the problematic version, in fact the difference in the macros is only that code has been added. None of the original coding has been modified in any way.
The second strange occurance is that if I run the code in debug mode with a breakpoint set anywhere (doesn't matter where), I can run the code by holding down F5 and the code will run through it's entirety flawlessly. This happens even if I have not run an older version beforehand.
I am completely stumped here as to what is causing this un-ending hang. In the meantime I can run my code but having older workbooks open is not only a pain but a waste of resources, and holding down F5 just hurts my finger . So, has anyone experienced anything like this before? Is there something I that I could be missing that could be causing this issue? Any help/advice would be greatly appreciated!
Thank you in advance!!!
Robert
Bookmarks