clearing up memory

[hawley]

Does anyone have an hints on how to clean up memory while running a program. My program works great when I am dealing with under 5000 rows but when I start dealing with more and try to autofill I get an out of memory error. I have tried splitting up the autofill but that does not work either. Any suggestions would be great.

Thanks-

 

[Damon Ostrander]

Hi Hawley,

I'm surprised that you are running into memory problems with only on the order of 5000 rows. Of course, the amount of memory requires depends on a lot of factors, like how complex the formulas are, how many columns are involved, and even whether cell formatting, validation, conditional formatting, etc., are involved.

One way to considerably reduce the memory associated with complex formulas is to convert them to VBA UDFs. Usually, even a very complex formula can be converted to a UDF that has just a few arguments and makes the cell formula much simpler.

The formula memory can be reduced even further by eliminating the cell formulas via the use of a VBA macro that does the calculations and just writes the results into the cells. This way, the cells contain only values, but no formulas. Macros can also replace conditional formatting, validation, etc., if these are contributing to the memory problem.

If you would like an example of converting cell formulas to UDFs and macros, let me know and I will put together a simple one.

 

[wilkisa]

Hawley, is this the same sheet you were working on last week? You were trying to autofill a formula over several thousand cells.

I suggested how to clean up the hard drive to free up resources prior to your autofilling. Have you taken those steps?

 

[hawley]

Yes, this is the sheet I was working on last week. I did clean up my hard drive and it did not help. Thanks for that suggestion though.

Actually the sheet I am working with has 8744 rows and 114 columns. The formulas are complex and vary from cell to cell. I can not set the cell to just the value. I need the formula in there in case something in another cell changes. I'm not sure what a UDF is so if you could send me an example that would be great. Also if you want some examples of the formulas let me know.

Thanks-

 

[Damon Ostrander]

Hi again Hawley,

A UDF is a "user-defined function", or simply a custom function written in VBA.

I will return some simple examples if you send me an email at

VBAexpert@piadamon.com

Also, if you want to send me your workbook or just some examples of the types of formulae you are using, that would help me to provide examples that are apropos.

I am interested in doing this partly because I am curious myself as to just how much memory will be freed up using these methods.

Damon