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?

 

[Jerry Sullivan]

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.

Hi and Welcome to MrExcel,

That's an interesting problem. You've done some really good steps to try to isolate the problem.

Have you tested what happens when you temporarily remove (comment out) the sorting step from your original code? If it's only the sorting step that is causing a memory leak, then if you run the original code without sorting, the memory use should be stable.

Also, how are you triggering the code to run every 10 seconds? Are you using Application.OnTime or some other method?

 

[siers]

Hi Jerry,

Thanks for the reply, apologies for the delay in responding back.

Have you tested what happens when you temporarily remove (comment out) the sorting step from your original code? If it's only the sorting step that is causing a memory leak, then if you run the original code without sorting, the memory use should be stable.

You know I thought I tried that, and when I comment it out the memory used doesn't get as bad, but still eventually slows down. So I'm thinking there's other things at play now.

Also, how are you triggering the code to run every 10 seconds? Are you using Application.OnTime or some other method?

Yes I was originally using the below to get that sub to run after 10 seconds of it finishing

'Application.OnTime Now + TimeValue("00:00:10"), "Reporting.ContinueSkillReport"

I did read someone that it's possible that 'Application.OnTime' could be the culprit and tried the following two alternatives:

1. Changed the time value to be predefined:

Dim repeatTime
repeatTime = Now() + TimeValue("00:00:10")
Application.OnTime repeatTime, "Reporting.ContinueSkillReport"

2. Used a set of code from Avoid Using DoEvents to Wait in Microsoft Access, VBA, and VB6 for a 'WaitSeconds' function to use the Windows Sleep command instead.

Neither of these changes resulted in improvements either.​

So I've going through a variety of other options, however I still cannot isolate specficially what's causing the issue, nor have any other Google investigations resulted in any fixes. The closest I've ever seen posted elsewhere with a similar issue is https://groups.google.com/forum/#!msg/exceldna/oahQNZtjFOU/9169zU02P_IJ and Excel-DNA - Memory leak? (FYI - I'm not using Excel DNA, but this post has been the closest I've ever come to someone with the same issue).

I'm going to have Excel 2003 installed back onto a test machine (running Windows 7) to see if it's definitely the change in Excel versions.

I'll admit that it's possibly my coding is of 'basic' quality, that it originally did what I needed but with the newer version of Excel it's totally overwhelmed it. And it's overwhelmed me too!

 

[Fazza]

Maybe the query table is the culprit? (I'm not sure how many are used & which files are queried.)

Can you test an alternative file version that uses alternative to query table/s?

 

[siers]

Maybe the query table is the culprit? (I'm not sure how many are used & which files are queried.)

Can you test an alternative file version that uses alternative to query table/s?

Thanks for the reply and suggestion.

We are referencing 3 text files to import the data - and I have tried removing the data connections/query tables entriely (just let it use 'fake data' that wasn't refreshed).

(Just for some further context, I'm using code similar to this post International Avaya Users Group : Forums : Pulling Real-Time CMS report using Excel VBA with the Avaya CMS Supervisor application to export the data to the text files, then using the QueryTables to get it back in. I have also just tried the 'Clipboard' method where it exports to clipboard and just pastes the data in as well, but still the same issue occurs).

 

[Fazza]

I understand from your comment that query tables that pull from three text files are not the problem. Thank you.

(Just for some further context, I'm using code similar to this post International Avaya Users Group : Forums : Pulling Real-Time CMS report using Excel VBA with the Avaya CMS Supervisor application to export the data to the text files, then using the QueryTables to get it back in. I have also just tried the 'Clipboard' method where it exports to clipboard and just pastes the data in as well, but still the same issue occurs).

I don't understand. Is that code (that makes the text files) in the file that has the problem? If so, have you checked if that might be the problem?

I think I can't contribute much more, sorry. Best wishes to sort it out & please post back with your findings. Hope you sort it out soon. regards

 

[siers]

I don't understand. Is that code (that makes the text files) in the file that has the problem? If so, have you checked if that might be the problem?

Sorry for the confusion - yes this is the code that makes the text files. However I have tried leaving that out and just imported 'fake data' and also 'no data' either.

I think I can't contribute much more, sorry. Best wishes to sort it out & please post back with your findings. Hope you sort it out soon. regards

Thanks for the help, I'm almost at the point of going mad! I know just enough of this to get me into trouble, but not enough to fix it!

 

[Fazza]

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.

 

[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. .

An update - I've had another PC on Windows 7 with Excel 2003 installed and ran my last 'XLS' (97-2003) backup version of the same workbook - and it works just with no slow down.

In regards to the memory usage - when the XLS workbook opens in 2003 it's using about 15,000k of memory, and does slowly increase to around 34,000k before stabilising around that figure. However even with this memory usage, the VBA cycles through every 16 seconds (6 seconds to process, then waiting 10 seconds) and stays at this timeframe.

Just to confirm, I then opened the same XLS workbook (on another PC) within Excel 2010 (using compatibility mode) and ran it through. As expected, the first cycle took 16 seconds. Then 17, then 18, then 19 and so on. After about an hour or so, it was already up to 80+ seconds (so taking 70+ seconds to process then waiting 10 seconds). Also, the memory usage which started at around 45,000k was at around 90-100,000k after this time and still increasing.

 

[ttdk1]

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