Memory load increases to unsavable during macro use

[Oliver Dewar]

Hi Guys.

I've got a workbook with a complex macro which uses multiple sheets to compile a final results sheet with tens of thousands of rows of data.

My issue is that, while the macro is running, the memory (Private Working Set) for EXCEL.EXE as seen in Task Manager increases until eventually it gets way over a gb and the workbook can no longer be saved and eventually crashes.

I have another script which runs for a similarly long period of time but causes no increase in memory load - I can't see the difference.

My question is if anyone is aware of specific pieces of code or errors that are known to cause this behaviour?

I have Windows 7 and have tested in both excel 2007 and excel 2013 with similar results.

At the moment I'm working around it by setting certain limits so the macro stops and the workbook can be saved... before closing and reopening excel then starting from where I left off.

Cheers all!

Oliver

 

[Oliver Dewar]

Thought I'd come back and post my discoveries and solution - as potentially meager and flawed as they might be.

I did more research and what I'm experiencing is called a 'memory leak' though I can't work out what in particular is causing it in my case.

It seems that some memory leakage is to be expected and my code runs for many hours to completion and is very complex with multiple parts, so perhaps I shouldn't expect it to have no leakage.

Solution?

Because I want this to be able to run overnight and all day without me needing to touch it - until it completes - I needed to come up with a way to 'clear' the memory leak every so often.

What I have decided to do is create a VBscript that, when triggered, waits 5 minutes and then opens the excel workbook that I'm using to process the data.

Then I've broken my processing down to chunks which do not overload the memory. When a chunk is finished, the workbook saves itself and then runs the external VBscript before closing.

The VBscript waits 5 minutes then opens the workbook again.

The workbook opens (with the memory leakage cleared) and runs a macro on open - it discovers that it's mid-process and begins the process again.

This will work for me. I hope it's a useful addition to the forum's workarounds and ideas.