Macro works on one computer, but not the other.

[kingofaces]

I have a large macro that typically takes about 30 minutes to complete on a computer at work with Windows XP and Excel 2007. Everything works fine there, but when I save the workbook and try to run the same macro at home on Windows 7 and Excel 2010, I only get about .29% of the calculations done before Excel is grayed out saying it is not responding, and that status bar showing percent completion no longer increases. Only Excel seems to lock up as I can use any other programs freely. It basically runs by using a loop to take information from one row at a time, do calculations for that row, then move on to the next row. I only get about 18 rows in before freezing, but if I set up a break point and keep pressing the play button it seems like there is no issue. Kind of pointless though considering I have nearly 10,000 rows that need to be looped through.

Are there any common problems or incompatibilities to check between the two versions of Excel? I opened up task manager to check and see if for some reason my home computer just couldn't handle the calculations, but the processor is running at a low percent and I still have plenty of memory, not to mention that my home computer has better specs than my work one. There are a lot of calculations going on, but I never had an issue like this while I was developing the macro between home and work. I recently made some changes at the work computer, but tested it there and everything ran smoothly before bringing it home to try.

Any thoughts on starting to troubleshoot this? It shouldn't be an inherent problem in the code as it worked just fine on one computer already, so I'm guessing it most likely has to be a problem between Excel versions? Any thoughts on things to check would be appreciated.

 

[chuckchuckit]

2007 and 2010 differences I would not know much about. And that would be my first guess. If you can find out somehow where in the code it is locking up, or the process it is doing, that might help.

When I switched from Windows XP to Windows 7 I had some Excel files that crashed and would not respond. In my particular case I (if I remember correctly) it had to do with Windows 7 was running 64 bit and XP used 32 bit. My memory is a bit foggy on what exactly it was, but I run 32 bit on Windows 7 as default. I was told that running 64 bit sometimes can even make things slower. Might be worth trying just to see.

 

[kingofaces]

I can't get it to lock up in break mode, which is what has made this one so tricky. That's why I thought that maybe it was just too much data for the computer to handle, but it didn't even approach the amount of data being processed I had used in the past with this macro. I'll have to look into the 64bit issue though. That seems like the only major difference at the moment. Definitely an odd one to say the least.

 

[chuckchuckit]

An other thought is I still do get Excel files that sometimes do grey out and then that message saying Excel is not responding. But that is due to one of the many Web sites that I query does not always respond. So Excel is just sitting there waiting for the query to return data. In this case it is the Web site's fault. It eventually does respond so it eventually works again.

But if you are doing querying, perhaps on the Menu the "Data, From Web", connection needs to be reset, or the macro in 2010 version handles the Web accesses differently.

 

[chuckchuckit]

Was wondering what you meant by "I can't get it to lock up in break mode"?

Also when it locks up can you use "Ctrl-Break" to get the code to stop running? Then if you click the "Debug" button, it should take you to the line of code that it was last on.

 

[kingofaces]

I set up a break point so that I need to press the play button each time the loop (x = 1 to 10000) that runs basically the whole thing reaches the next line. When I run the macro this way everything runs normally as I have got up to ~50 lines with no issues at all as I keep pressing continue each time the break point is reached. Nothing freezes at all this way. If it were a direct problem with the coding then I should have picked something up around x = 18.

When I don't set up a break point and run the macro, the percentage calculated ends up being somewhere around only 18 rows on the worksheet. At this point Excel has the (not responding) message next to the program title. Ctrl + Break does nothing, which I assume means Excel is frozen rather than stuck in a continuous loop or still doing calculations as I cannot enter the debug menu at this point. All I can do is go to the task manager and force Excel to close.

I'm back at work again and I'm not having any issues with the macro here. Both computers have ~3GHz processors, but the work one has 3GB RAM, while my home one that is having issues has 8GB. With that in mind both should be "capable" of running the macro, so that's why I'm still leaning towards some kind of incompatibility between 32/64 bit or maybe the Excel versions.

 

[chuckchuckit]

That's a tough one. Perhaps if we could see your code it might point one of us in the right direction. If it is not the 32/64 bit thing, then perhaps it would be something with the way the code is being handled in 2010 version.

Off the top of my head I'm am wondering if the code might have something that gets ahead of itself in version 2010, where i would not in 2007 so the code crashes. Because your stepping through sections repeatedly with using your breakpoint gives the code time to catch up to itself before the next loop. Something peculiar like that. Might be something as simple as putting a "DoEvents" line in a couple places so the code is sure to finish before going on through the next loop of that same code etc.

Seeing some code (if that is ok to see), may help expand the ponderings.

Thanks.

Chuck

 

[chuckchuckit]

Side Note: I see you are in Minnesota. I grew up in Minnesota (many moons ago). Small town south of the Twin Cities on the Mississippi river.

People here in California have asked, since winters are so cold there in Minnesota, what did you do like to do during the summers? So the answer is:

"Well, if summer in Minnesota fell on a weekend, we usually went fishing."

I liked living there. Winters are easier here though...

 

[kingofaces]

So I shortened the amount of data used and I still got it to lock up, but this time it said "Runtime Error 71 : Disk not ready". I haven't used DoEvents before (which may explain something), so I'm going to check that out and see if it does anything.

It's a rather long set of a code, and you pretty much need to see the worksheet to understand what's going on in the calculations and why certain loops are happening. I'm not going to ask anyone to sort through all that for something like this.

I'll send word if I get this figured out.

As for Minnesota, I've been thinking the same thing recently. We really only had two months of summer this year at best so far, and that's pushing it. I need my winters though, so I'm not going elsewhere.

 

[kingofaces]

So the last error I mentioned had nothing to do with the problem at hand. I just had the wrong url for where the data was going to be exported to after changing it slightly

However, DoEvents did the trick. I just put it in the main loop and it works like a charm. I still wonder why it was an issue for a newer version of Excel and a newer computer, but at least now it works. Thanks.