PDA

View Full Version : Solved: Extremely Slow Performance



bbk5
08-28-2007, 12:55 AM
Hi all,

I've written a series of macros in Excel which write values to a veryhidden tab within a workbook. The only problem is that as the size of the workbook grows, Excel takes longer and longer to do the same operation. I'm not sure why the size of the workbook would affect the performance this much. If I start with a relatively empty workbook, it takes about 10 seconds. With a workbook that's a bit more full, it can take up to 2 minutes to do the same operation. Here's an example of what I'm doing:

.Cells(curRow, nameCol) = "Relay " & i

.Cells(curRow, nameCol).Interior.color = MHEditor.Controls("RelayCheck" & i).BackColor

These lines are within a With that is using the veryhidden worksheet that is being written to. Any idea why such seemingly simple operations would take such a long time? I've stepped through the code to make sure it's not something else hogging up the time and each line, such as those above, takes way too long to complete.

Any help or speculation would be greatly appreciated!

Bob Phillips
08-28-2007, 01:00 AM
Maybe it is calculations.

Try preceding your code with



Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


and afterwards



Application.Calculation = xlCalculationAutomatic
APplication.ScreenUpdating = True

bbk5
08-28-2007, 01:35 AM
Hmm, I already had ScreenUpdating turned off but I tried manual calculations. That didn't seem to have any effect on the runtime. Any other ideas?

Bob Phillips
08-28-2007, 01:55 AM
You need to time your preocedures, and see where the bottlenecks are.

bbk5
08-28-2007, 04:05 PM
That's the thing. The procedure which fills out the worksheet is the bottleneck. It's just a for loop and it has about 40 commands similar to the ones I posted. It takes Excel over 1.5 seconds to just set the values of 40 cells. I've also stepped through the loop and there isn't one instruction that takes longer to compute than another. Just the whole sum takes way too long.

Could it be anything else?

bbk5
08-28-2007, 04:17 PM
Alright, I figured it out. I'm using Excel 2007 and was running in Compatibility Mode. Once I converted the workbook to 2007, it took around 15 seconds, as opposed to 2 minutes.