Manual Formula Calculation causes file to crash during macro

[mpobrien12]

Hello,
I am trying to run a macro update on a file with a lot of excel formulas that cause the file to act very slowly when set to Automatic update. The macro involves pasting various data sets from other files into the current file, applying various formulas to all rows in those datasets, which are then reported in one master sheet via countifs and sumifs formulas. To help with this, I've set the macro up so that the calculations are set to manual and the code calculates only the cells with formulas in the data sets.

The problem arises when I run all these macros together--in almost all instances, the file crashes unexpectedly while the macros are running without any error message or reason. When I run the macro updates with the formulas set to automatic update, the file never crashes but takes VERY long (almost 30 min in some cases) to update.

Does anyone have an idea of why the file crashes during the macro update when the formula calculation update is set to manual? I've been trying to figure this out for months but still have found no information on this. Any help would be GREATLY appreciated, thanks so much.

 

[StephenCrump]

Welcome to the Forum!

It will be hard to diagnose this without seeing the workbook/code. Some preliminary thoughts ...

What do you mean when you say the file crashes unexpectedly while the macros are running without any error message or reason? Does the macro appear to simply stop running? Do you have any code that might trigger this on error, e.g.

On Error GoTo ErrHandler

'lots of code

ErrHandler: Exit Sub

Or do you mean that Excel itself crashes? Without error message?

Also, what do you mean by run all these macros together. Presumably you're just successively pasting datasets?

If you are only applying Calculation to the dataset, how are you making sure you include all dependencies, especially as these appear to cross at least two sheets?

Instead of switching calculation to manual, have you tried setting Application.ScreenUpdating = False?

The macro runs for nearly 30 mins, but it's not clear how much of this time is iteration, and how much is calculation. How long does a full workbook calculation take for one dataset paste, i.e. is the workbook big and complicated to start with?

How are you pasting datasets in? Cell by cell (which will be slow) or entire range at a time?

Similarly, how are you storing the results for each data set, e.g. line by line, or building a VBA array to paste to the worksheet in one go at the end of the iterations?