Excel 2010 - Sorting incrementally using more memory whilst slowing down subsequent processing

[siers]

Hi Everyone,

I'm trying to diagnose/troubleshoot an issue I've got in an Excel 2010 spreadsheet that we use to display data on LCD TV's (wallboards).

As a basic overview of the spreadsheet's function:

- the spreadsheet is run in full screen mode and uses VBA macros to perform the below functions automatically behind the scenes
- updated data is available via an external text file every 10 seconds
- therefore every 10 seconds, the macro refreshes the query table on the 'Raw Data' tab with 44 rows and 21 columns of data
- on a second tab called 'Sort Data', the raw data (21 columns) is referenced/linked, and in column 22 a sort value is calculated (by formula) allocating a number from -99 to 999999
- the 22 columns are sorted by the macro, with column 22 used as the key sorting from largest to smallest
- data is displayed on a third tab 'Display Data' (also referenced, this time with formatting to make it present on a TV).

I've had no issues with this setup since we started using it in Excel 2003 & Windows XP for around 4 years now. The spreadsheet has always been responsive, memory allocation always stayed around the same (around 50,000-60,000k in Task Manager), and could run for days (even weeks) without any issue.

The troubles have begun since we moved to Excel 2010 and Windows 7. When the spreadsheet is first started, it works just as well as it always had. But progressively over a couple of hours, the macro cycle begins to slow to a crawl (taking around 1-2 minutes to cycle rather than 10 seconds) and eventually we have to close and restart Excel to get it going for a little while again.

Looking at the memory allocation in task manager, starting the spreadsheet is around 50-60 K and every cycle of the macro seems to add around 10-30k, and we eventually see the slow down when it starts approaching 100,000K. I'm sure it's not a RAM/memory issue as there's plenty of capacity left available - but this slight regular increase must be relating to something staying active or in memory. Just closing the spreadsheet only doesn't clear the memory allocation, only closing Excel does.

Even if I open another spreadsheet (unrelated to the first), that seems to be affected too when sorting or using Excel in general (processing time seems to be longer). The only way to clear is to close Excel completley. The Excel.exe process stays running for another 20-120 seconds before finally ending.

I've spent the better part of this month pulling the entire spreadsheet apart, and believe I have narrowed down the cause to the 'sorting' of a range that includes formulas. I've created a more basic spreadsheet to just test the sorting part only that has 'RawData' on on tab, and 'SortData' on the second tab that includes a basic set of formulas. Then when sorting (using either a macro, auto filter or manual sort), it seems I can make the memory allocation increase by 5-15k after every 1-3 sorts and keeps going up.

Other things I've researched and tried (without sucess) include:
- making sure I'm not reference entire columns or large number ranges in formulas
- enabled 'forcefullcalculation' and performed 'calculatefullrebuild'
- before the macro runs, always disable screen-updating, set calculation to manual, disable alerts & events - run the code, then restore those functions
- attempted disabling 'multi-thread' processing & calcuations
- changed the registry setting to '0' for undo functions
- making sure any object references used in the macro are set to 'nothing' at the end of the cycle
- checked that we're running Office SP1 that has already included the hotfix regarding sorting data causing memory allocation issues.
- making sure any PivotTables don't remember missing items and don't save source data

Has anyone else ever heard of this? Is there some other form of 'Sorting Cache' that is keeping track in Excel 2010 that I need to clear?

 

[siers]

Have you tried your XLS workbook on both 32-bit and 64-bit versions of Excel 2010?

Only the 32-bit version (unfortunately our MOE/SOE is only configured for 32-bit and we don't have any 64-bit systems in operation).

Just to add, I don't believe it's an issue with running out of memory in the 32-bit version, I've never gone over 300,000k of memory allocation and still always had at least 30% free.

 

[ttdk1]

Might be a windows 7 issue. Try to see you have the same problem with Office 2010 on windows XP

 

[siers]

Might be a windows 7 issue. Try to see you have the same problem with Office 2010 on windows XP

Thanks for the suggestions - unfortunately it's not an option as XP has reached end of life and no longer running within our organisation.

However I doubt Windows 7 is the issue if:

- Excel 2003 running on Windows 7 is OK
- Excel 2010 running on Windows 7 is NOT

 

[siers]

It sounds to me like your approach is sensible - take out each step one at a time & see what makes a difference - and will lead to identification of the step that is the problem. No need to go mad! Just keep going.

If you don't know how to fix the problem, then likely someone else can help. It might be just using an alternative approach for the step that is the problem.

Hi again,

Well I believe I've had a breakthrough and found the cause of the slow down - Picture Links (or Camera Snapshots) - of which I use quite a few within the affected workbook.

When I ended up removing all of the linked pictures and ran the macro, and it was just perfect. Each cycle took 11 seconds and stayed consistent at 11 seconds for over an hour.

I then re-opened the workback (back with linked pictures) in Excel 2010 and whilst the first cycle was 11 seconds, it then started creeping up to 12 then 13, etc.

As a final test, whilst the macro was cycling (up around the 30 second mark), I deleted all the linked pictures and immediately the cycle dropped back to 11 seconds and stayed consistent.

So I've finally narrowed down the root cause! Hoo-fword-ray! Given that linked pictures worked just fine in Excel 2003 (when it was known as Camera Snapshots or Camera Tools), it appears something happened in either 2007 or 2010 Excel.

I have since deleted and recreated all of the linked pictures within the workbook (as they were created in 2003 and then converted), and this has provided some improvement where the slowing down of the macro cycle seems to be 1 second every 5 minutes now instead of every 1 minute. But it's still worse than 2003 which stays constant and doesn't slow down.

A quick Google search about linked pictures does seem to mention that in the Excel 2010 version of Picture Link/Camera Tool there was a bug found resulting in distorted images and apparently hasn't been fixed (even in Excel 2013).

Anyway, given that I've identfied the issue, I may finish this thread up (as it was about sorting being the issue, which it isnt) and start a new one about this issue specifically.

Thanks for reading, if I end up creating a new thread ill post it back here.