I have an Excel Addin. The addin retrieves data from XML Soap calls and places data on spreadsheets.

After opening an Excel spreadsheet the first time it runs it takes 7 min 30 sec. Leaving Excel open and running the second time it runs (same functions) in 4 mins 20 secs. Close Excel and open again back to 7 min 30 secs. Tested numerous times and on two different PC's.

Screenupdating and calc have been verified to be correctly turned off during all runs. The application status bar is continually updated during run time. The time difference does not occur in one chunk in any one place. The whole process runs faster second time.

Obviously, I would like to capture faster run time, but suspect it is due to Excel memory management or incoming data remaining in cache after first run. At a minimum I would like to understand what's happening.

Any thoughts on this appreciated?



Steve

I have figured out what caused this and how to solve it - but not why it happens.

The Excel addin has a loop that was creating and deleting the same temporary output spreadsheet - approx 500 times. Removing the add and delete and replacing it with clearcontents and reusing the same spreadsheet solved the problem.

However, I still have no clue on why Excel when running the same code a second time would perform this add and delete so much faster then the first time.

Steve

Steve,

As you've already alluded this is likely to be driven by how Excel manages its memory and, in particular, how it "remembers" objects that that have been deleted.

My guess is that the second time round Excel reuses the ghost memory location of the initial temporary sheets and, in so doing, can operate much quicker. Its worth noting that closing and re-opening Excel will flush most of its temporary memory hang-overs (I say most because the remainder explains how big spreadsheets can get corrupted over time). This is a useful trick to remember if Excel starts slowing up on you with a big spreadsheet.

However since the program code is proprietary all this is just a guess. Unless you can find a suitably motivated Microsoft Excel developer with the time to debug this I reckon we'll just have to speculate...