Excel memory issues

Hello everyone,

I have been involved in the development in a graphical user interface which is used to sort large amounts of data, (thousands of rows, 30 columns). After sorting the data according to required ranges on column data, the columns can be plotted with any column against any other column. The number of plots can reach the hundreds and are displayed in a half-sqaure fashion.

Anyway, I originally needed only 10,000 or so rows of data and everything worked well. To sort the data, I wrote a macro to place an equation in the first column after the data with the end result being either true or false. The data is then sorted in this way. When the required amount of data grew to 50,000 rows I started to run into memory errors in Excel. When I copy 50,000 rows to another sheet for sorting, I get the 'Not enough memory' error within excel. As far as I know, the machine I am using should be up to the task; it is a P4 3.0 GHz machine w/ 1 gigabyte of RAM.

My main question is how can I make Excel more efficient aside from increasing cache size or buying more RAM. Also, since Excel has a limit of 65k rows of data and 30,000 data points per plot series and I will most likely have to increase my data size to over 65k points what other options do I have beyond Excel I was reading on some other postings about databases, would it be viable to have a database containing the data to interface with my GUI in Excel

Thanks!

Tanner

Microsoft ISV Community Center Forums2

Hi Tanner,

The not enough memory error I don't think relates to the amount of memory on your machine more to do to with memory limits in maybe the clipboard or in Excel itself. 50,000 rows or 30 columns shouldn't use up all your memory.

Database would be better as it's designed to work with large amounts of data especially with the sorting aspect, and you'll be able to go over 65,000 rows. If you read more about databases you'll read about normalisation and database design, you wouldn't need this, just put all your data in one table.

Excel has some shortcut functions to help you read from databases. If you go Data->Import External Data from the menu then you can import information from a database. You'd need to determine how importing data from a database works and how it affects what code you have at the moment.

I've occassionally needed to go over the 65,000 row limit, I did by using paging which is an approach where you spread your data over multiple sheets but your code accesses it as though it's one. You do this by working in blocks of say 10,000 records, 6 blocks to a sheet. Whenever you process the data you do it according to the current block. No idea if that helps you but could be a smoother transition introducing that than going for the database.

Mr. Smyth,

Thank you for your response. Your description of paging seems promising. As for the most efficient way of doing this, I am thinking of having a loop setup which looks at the first data sheet, sorts the data, then copies the true data to a new sheet. After this, it will go to the next sheet and do the same, once the true data is collected I can plot this and have some means of plotting all the other data. Did you do something different Since I'm relatively new to this and this code needs to be fast I definitely want to implement this change in the most efficient manner.

Regards,

Tanner

Mr T,

I have a really efficient way for you to copy filtered information between you sheets but you'll need to wait until I post it on to my blog, as it will take a bit of explaining. Check my blog tomorrow.

Regards,

Mr Smyth.

Mr. Smyth,

I checked out your blog and it looks like your procedure will be much more efficient than what I was doing. Each time I change a constraint (meaning change my bounds on a value) then all of the data points have to be reevaluated against the new constraints. Because of this I adapted a macro I wrote which pastes in the constraint equation to each row of data.

This is an example of the constraint. Since as far as I know the limit on number of arguments in an AND statement is 30, I actually have another column with additional constraints. I have 40 total...

=IF(AND($O147<=TopDown!$F$7,$O147>=TopDown!$G$7,$P147<=TopDown!$F$9,$P147>=TopDown!$G$9,$Q147<=TopDown!$F$11,$Q147>=TopDown!$G$11,$R147<=TopDown!$F$13,$R147>=TopDown!$G$13,$S147<=TopDown!$F$15,$S147>=TopDown!$G$15,$T147<=TopDown!$F$17,$T147>=TopDown!$G$17,$U147<=TopDown!$F$19,$U147>=TopDown!$G$19,$V147<=TopDown!$F$21,$V147>=TopDown!$G$21,$W147<=TopDown!$F$23,$W147>=TopDown!$G$23,$X147<=TopDown!$F$25,$X147>=TopDown!$G$25,$Y147<=TopDown!$F$27,$Y147>=TopDown!$G$27,$Z147<=TopDown!$F$29,$Z147>=TopDown!$G$29,$AA147<=TopDown!$F$31,$AA147>=TopDown!$G$31,$AB147<=TopDown!$F$33,$AB147>=TopDown!$G$33,$AC147<=TopDown!$F$35,$AC147>=TopDown!$G$35),1,0)

My problem arises when I try to apply this equation to all of my data sheets consecutively. Since I have 2 columns of equations over 6 data sheets I get the 'Not enough memory' error discussed previously. Do you know of any way to get around this I do think your method will be quicker than what I did so if I can just figure out how to get past this all will be well.

Tanner

Yo Mr T,

That is one major league constraint to put in a IF statement. There could be a better way.

You could handle the Workbook_SheetChange event and programmatically apply your check using VBA... You can replace every IF statement in every cell with one function thats called when a cell value changes.

Seeing how I am such a nice guy I've created an example of how to go about doing this...

Mr T's.

Have a look at the macro and see if you understand it ok. It replicates what your IF statement does above and is commented, if you need more info let me know.

Regards,

Mr. Smyth

Mr. Smyth,

First of all, sorry for the late response. Office duties pulled me away. Anyways, thank you so much for all of your help. After some adjustment, I believe I have everythign working correctly. You are a life-saver that's for sure. Thanks again.

"I pity the fool!"

-Mr. T

No worries Mr T glad I could help, see you around.