View original (French)

Clear cache and variables at the beginning of the macro

Solved
Sormick Posted messages 163 Status Membre -  
 sormick -

Hello,

I notice that after several executions of my macros, at some point my spreadsheet crashes because too much memory is being used (probably due to unemptied variables including Global variables).

My problem is that I have an impressive number of modules and macros to check. The list of variables to verify would be so long that it would take me several weeks to check everything.

Is there a process that really allows me to free up Excel's cache and the variables at the beginning of my macro execution without having to check all my variables?


4 réponses

Réponse 1 / 4
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   Ambassadeur 1 587
 

Hello,

What do you do to fix the situation? Do you save your file, then reopen it?
0
Réponse 2 / 4
Sormick Posted messages 163 Status Membre
 

My file becomes slow after several executions and eventually crashes at some point.

Then I restart it and inevitably lose data.

I don't want to recover the data after the bug because it automatically renames my files during the recovery, which I don't want.
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 587
 

What do these variables contain? Complex objects?

What does this code do, does it create Excel objects (pivot tables, ...) that accumulate from one execution to another?

If not, perhaps restart Excel regularly, or close the file regularly.

0
Réponse 3 / 4
Sormick Posted messages 163 Status Membre
 

I have too many variables and objects to check. There are hundreds of modules and probably several hundred variables and objects to check. Restarting Excel resolves the issue, but I would like to clear the cache without having to restart the workbook.

My question is: is it possible to clear all variables and objects without having to check them one by one? A sort of shortcut, in a way; if that is possible.
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 587
 

Apart from automating the regular closing and opening of the file, I don't see how it would be possible.

And it could very well be that the issue is caused by permanent objects, which survive the closing and opening.

0
Réponse 4 / 4
sormick
 

Thank you. There doesn’t seem to be a miracle solution. I will take the time to check what is not clearing the cache.
0