Excell with 100+ sheets keeps freezing

[hodikr]

Hello,
Im facing an issue with excel file when working with 100+ sheets.
Each sheet gets added via macro as a copy of one existing form sheet. Each sheet then uses relative references to one "data" sheet, where all values are stored. However, the excel freezes even when adding empty sheets. Im not sure if I'm facing kind of HW RAM limit. the file size with 176 sheets is about 7,5 MB. RAM usage according to task manager is 230 MB.

Any idea whys this may be happening would help me.

I appreciate any help or idea for improvement.

 

[Fluff]

Cross posted Excel with 100+ sheets keeps crashing after cca 10 clicks at some random cells.

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[Joe4]

Welcome to the Board!

It sounds to me like you are pushing the limits of Excel, and are really using the wrong tool for the job. What you are describing sounds like a "relational database", and as such, a relational database program like Microsoft Access would be better equipped for this type of project.

Note that Excel can only track so many many dependent formulas (it may be something like one million). After that, and change invoking a calculation would require it to recalculate everything single formula in the workbook, instead of just the dependent formulas. This sort of thing just kills performance. You could shut off automatic calculations to see then if that improves performance. You just need to remember to press F9 to manual re-calculate the workbook, as it won't be automated anymore.

 

[hodikr]

Cross posted Excel with 100+ sheets keeps crashing after cca 10 clicks at some random cells.

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

I apologize if I broke the rule. I just tried mine luck on 2 sites. Thanks for the hint, will remember that.

 

[hodikr]

Welcome to the Board!

It sounds to me like you are pushing the limits of Excel, and are really using the wrong tool for the job. What you are describing sounds like a "relational database", and as such, a relational database program like Microsoft Access would be better equipped for this type of project.

Note that Excel can only track so many many dependent formulas (it may be something like one million). After that, and change invoking a calculation would require it to recalculate everything single formula in the workbook, instead of just the dependent formulas. This sort of thing just kills performance. You could shut off automatic calculations to see then if that improves performance. You just need to remember to press F9 to manual re-calculate the workbook, as it won't be automated anymore.

Thanks for the hint Joe. I probably am pushing excel limits. Let me try to switch off the automatic calculations and see if that was the case.

Regarding access. May be a good idea. Let me think about that. The tool is used to create tolerance analysis. So user takes blank form, and puts the data in via userforms. There are several types of data, and depending on the type, its considered in the calculation. If that can be managed in access, I can sure try.

 

[hodikr]

Thanks for the hint Joe. I probably am pushing excel limits. Let me try to switch off the automatic calculations and see if that was the case.

Regarding access. May be a good idea. Let me think about that. The tool is used to create tolerance analysis. So user takes blank form, and puts the data in via userforms. There are several types of data, and depending on the type, its considered in the calculation. If that can be managed in access, I can sure try.

So to switch off the automatic calculations did not help. Still freezes, RAM usage 260MB.

 

[Joe4]

So to switch off the automatic calculations did not help. Still freezes, RAM usage 260MB.

I think that only furthers the argument that this may be too big for Excel.

You should probably at least look at storing the data elsewhere, like in Access, SQL, or MySQL.
Though I do not do this myself, you can store the data in other places, and still query it in Excel.