The other answers have merit but perhaps not as much merit as the posters think.
I have had the problem of a routine taking much, much longer than expected for no obvious reason on a number of occasions. I would change things around and the problem would disappear but it was never clear what I had done to fix the problem. My best theory was it was something to do with moving rows from one worksheet to another. I promised myself that one day I would investigate this systematically. Chris's problem seemed relatively clean so perhaps this was the opportunity I was seeking.
With one exception, bad practice in the way worksheets are accessed adds little to the duration of a macro.
Compilation (top option on Debug) has no obvious effect on duration.
Closing and reopening a workbook may reduce the duration of a macro if the workbook has been subject to significant modification since it was last opened.
Even if there are no formulae, having calculation off decreases the duration of a worksheet-accessing macro but not by as much as you would think.
The one bad practice I found that makes a real difference is to switch worksheets and then activate the new worksheet while screen updating is on. By making every other mistake I was able to get the duration of a macro up from 15 to 199 seconds. Adding activate for every access of a new worksheet increased the duration to 9,838 seconds (2:43:58). Adding ScreenUpdating=False brought it down to 466 seconds (7:46)
I suspect that there is a bug in Excel 2003. I have little experience with other versions so it may or may not apply to them. The bug seems to be associated with moving lots of data around and lengthy macro developments with lots of repeated runs to test corrections. I hit this bug once during the experimentation for this answer but as soon as I tried to pin it down it disappeared.
I created a workbook with 26 worksheets. One I named "Alpha" while the rest had numeric names. I coded a macro to fill these sheets with between 900 and 1000 rows each of 25 columns with every cell containing a six character string. Other routines cleared rows with either EntireRow.ClearContents and EntireRow.Delete and used Copy and Paste to move data from the worksheet Alpha to the other worksheets.
I could discover no difference in duration between EntireRow.ClearContents and EntireRow.Delete. However clearing or deleting a selected range as opposed to the entire worksheet increased the total duration of processing the 26 worksheets from .07 seconds to .13 seconds.
By including every bad practice I could think of, I could bring the duration of a copy and paste from worksheet Alpha to each of the other 25 worksheets up to 1.3 seconds.
Copying a row 5 containing formulae down from rows 6 to 905 for each of 25 sheets with both Calculate and ScreenUpdating On took 2.5 seconds.
The above are not exact matches for Chris’s routine but they are broadly similar.
The only routine I could get to take more than a second or two was the fill routine. This routine serves to prepare the worksheets for the real tests but it was the only routine that could be made to exhibit extended durations.
The basic function of the fill routine is to fill 26 worksheets with 6 character strings. Every worksheet had 25 columns but the number of rows varies from 900 to 1000. A total of 613,600 cells are filled. For each cell I take the first six characters of a 62-character string and then moved the first character of the 62-character string to the end. To give a crude indication of progress I display the worksheet number to the immediate window. It took two or three seconds per sheet but I did not care enough to improve it.
Using the fill routine to prepare the workbook, I was able to get timings for moving static data about. It was when I updated the fill routine to output formulae to row 5 that I hit a problem. Previously, as the routine ran the numbers 1 to 26 scrolled quickly up the immediate windows. After the update, 1 was displayed and then nothing. I paused the macro after a while and found it had only reached row 177 of the first sheet.
I added a timer to the fill routine and tried to pin down the cause of the slow running but I was never able get it to repeat. This matches my earlier experience of this problem. My macro would suddenly speed up but I was never able to identify anything I had done that caused it to speed up.
As I created routines that took longer to run, I found timings inconsistent. The second run of a macro would take less time than the first. After saving and reopening, the macro would be even quicker. Normally; sometimes it was the other way round. It is possible that background tasks (for example, software checking for updates or virus checker) were a factor but this seems unlikely. The difference in run times was only 10 to 20 seconds but there was no pattern I could detect. Compilation had no noticable effect. I have no difficulty in believing that I was piling up work for the garbage collector; was this a factor? Was Excel losing memory so the heap was getting smaller and smaller? If this was the cause, how come my recollection is that sometimes the slow running would last for a day or two. I usually hibernate Windows but I close Excel down at least once a day during an extended development. I am sorry if this is a brain dump but I am hoping something will click with someone familiar with Excel's internal workings.
I recoded by fill routine to maximise its sensitivity to poor programming. I changed the sequence of the For-loops to: Row, Column, Worksheet. I prefilled the sheet with 562,224 formulae most of which I overwrote one by one. I alternated between activating or not activating the worksheet over 600,000 times, having calculation on or off and having screen updating on or off. The timings I got were:
Activate Calculation Screen updating Total duration
No Off Off 0:00:20
No On Off 0:01:57
No On On 0:03:19
Yes Off Off 0:05:42
Yes On Off 0:07:46
Yes On On 2:43:58
I ran these tests in background because I could not handle the screen flashing from one sheet to another. I am very impressed at how quickly the fill took without activation, calculation and screen updating since I am generating 613,000 strings and placing them in individual cells. There is clearly an advantage in not activating worksheets and having calculation off if possible but nothing like the advantage I have always assumed.
I should perhaps report that the formulae I used were of the form ‘=Mid($Ax,y,1)’. That is column A contained a string and all the other columns extracted a character from it. I originally set row 4 to numbers and all the other cells to the cell above plus one. I found Mid was noticeable slower. No doubt there are other formulae are even slower.
It is not easy to get extended run times through poor programming; you must make a lot of mistakes at the same time.
Does anyone else recall macros with unexpectedly had extended run times? Can anyone suggest an alternative to my theory that Excel 2003 has a bug that might apply to later versions? I have access to a laptop with Excel 2007 and I with rerun the tests on the laptop when I get the opportunity. However, this is unlikely to reveal the bug, if it exists, because there will be no development which seems to be a requirement.