Why is my excel VBA code execution randomly slowing down?
May 21, 2008 12:07 PM Subscribe
Sometimes excel VBA code executes very slow. Sometimes it's really fast. I can't seem to pinpoint why.
posted by johnstein to Computers & Internet (8 answers total) 2 users marked this as a favorite
I'm familiar with application.screenupdating = false and turning off autocalculation.
This happens with a variety of my programs, but most notably with a certain one that simply iterates through an array (i,j) and prints each value (i,j) to a cell on the worksheet. Just your basic nested for loops with a .cells(i,j) = array(i,j) in the middle. (ok, so the real code is more complicated, but as I explain below, this isn't the only code that has the issue, and several coworkers who use different code experience the same thing. I'm not looking for coding help, rather, trying to see if this is a common problem that others experience, or if it's really just a local company phenomena)
Sometimes when I run it, it can whip through a 500x100 array in just a couple seconds, other times, it slugs through it taking almost 10 minutes.
This is always the same type of data, mind you, and there are never any formulas or cell formatting to worry about. Just a blank workbook with one worksheet that needs to be filled with the data from the array.
A few other coworkers notice similar things.
Sometimes restarting excel seems to clear things up. Sometimes restarting the computer helps. Oftentimes it doesn't and seems to clear up randomly on its own later.
Has anyone ever noticed similar issues?
My guess is that perhaps there's a memory lag/leak/etc or something that is degrading performance, but I would assume restarting the app or computer would fix that. We usually attribute it to 'the server being slow' but we notice similar issues even when files are local. It's just really frustrating to be testing a bit of code that is blazingly fast the first few times you test it, then suddenly it screeches to practically a halt seemingly randomly, then later that day or week, it's all back to normal like nothing happened.