In Excel 2007 VBA, if you save a workbook, does the VBA code continue to run while the workbook is being saved?
I have a macro that I wrote that works perfectly in Excel 2010. It is saving about 25 files to a network drive. When someone tries to run it on Excel 2007, it will run for a bit and then freeze. Sometimes it will save 10 files before freezing, sometimes 3, sometimes none. The only thing I can think of that would cause this random problem is that the VBA code continues to run while the workbook is saving; sometimes it gets saved before it gets to whatever line of code is causing it to freeze, while other times maybe the network drive is being accessed by someone else, which delays the save just long enough to run into the problem.
I’ve run into a similar problem in Word VBA, where building in a delay timer for a couple of seconds resolved a problem.
Never saw this one. I’ve converted a few 2007 Workbooks to 2010 and never saw a problem.
A few years ago I had one or two workbooks that acted real flaky and all attempts to find the source of the problem failed.
I finally created a new workbook and carefully copied all of text in the VBA modules over to the new book. Then I copied the workbook pages to the new workbook. It fixed the problem. I did some quick scans to verify that the new book had no links back to the old book.
I read somewhere that when you alter or delete VBA text code from modules it may not necessarily clean out the all of the compiled code from previous VBA activities and that it can corrupt new compiles.
So, I’m not saying that this happened in your case. It may be exactly what you said although ?I’m surprised that it freezes instead of errors out.
I’ve also had some problems kick up from failed save attempts but got around that by adding some on error loops with a counter X it would make X attempts to save before failing out. That usually kept my multi-file macros operating.