Memory and Macros?

[Fooser]

I have a question that maybe someone can answer. After executing some of my macros, excel tends to run much slower. Then every once in a while I get debug errors for standard short cut keys in excel. The problem is the programs I recieve debug errors work find when I actually execute them.

Question:
Can a macro still reside in memory after it has been executed? If so, is there a way or command I can use in my scripts to completely remove them from memory after executing?

 

[Damon Ostrander]

Hi Fooser,

Very good question, and one I haven't seen before! I do know that Excel does retain macro data in memory after completion because I know that all Public and Static variable values are retained between runs. Upon a little investigation I find that placing an End statement in your code (at the end, of course) will free up all module-level variables and all static local variables in all modules. But if you have any objects that use the Unload, QueryUnload, or Terminate events you should first add the appropriate Unload statements to "gracefully" complete them.

 

[Fooser]

Thanks for the info on how memory is stored.

I think the true problem is how excel is designed. I know when I have excel open too long and have been using some memory intesive spreadsheets along with macros I tend to crash unless I restart excel ocassionally.

I think I might be running into the limits of excel and how it can handle memory. I know from past experience excel is limited to 64mb of memory per occurance, which means any excel occurance over 30000 rows with referene formulas may crash. Since I have been dealing with 25000-45000 row data files, which I'm extracting into excel to work with, I sometimes have problems calculating formulas or running macros smoothly.

More then likely I will have to switch to Acess or some other database program to import these files for manipulation.