Calculation order

[Geoff Taylor]

I have a large model that manipulates data from multiple sources using all sorts of SUMPRODUCTS, SUMIFS and V/HLOOKUP formulas. It works just fine.

I've written code to automatically generate all the required formulae, sensitive to the size of the data tables and various other factors. The code's logic was to turn off calculation, produce all the formulae and turn calculation back on again, but Excel repeatedly crashed when attempting to calculate the finished model at the end of the routine. However, when I debugged the code line-by-line and forced a calculation every line there was no crash and everything was just dandy.

I've resorted to inserting calculation instructions at a few points during the code and this now works. It's slower than before but at least it doesn't crash, but I'm struggling to understand why this is necessary. Could someone shed some light?

Here's a snippet of the code...
<code>
*SNIP*

With Worksheets("Costings")
sEndColumn = Left(.Range("D13").End(xlToRight).Address,len(.Range("D13").End(xlToRight).Address) - 3)


.Range("D15:" & sEndColumn & "15").FormulaR1C1 = "=IF(ISNA(VLOOKUP(LEFT(R[-2]C,FIND(""/"",R[-2]C)),'Program Code Rules'!R2C3:R73C4,2,FALSE))=TRUE,""??"",VLOOKUP(LEFT(R[-2]C,FIND(""/"",R[-2]C)),'Program Code Rules'!R2C3:R73C4,2,FALSE))"
.Range("D16:" & sEndColumn & "16").FormulaR1C1 = "=VLOOKUP(RIGHT(R[-3]C,1),'Program Code Rules'!R3C8:R8C9,2,FALSE)"
.Range("D20:" & sEndColumn & "20").FormulaR1C1 = "=SUMIF(Banner!R2C19:R50000C19,R13C,Banner!R2C43:R50000C43)"


*SNIP*
</code>

 

[Soggy]

This could purely be to do with the volume of data it is crunching through. You could be doing 50,000+ vlookups across 6 columns (so 300,000 sums) across massive ranges on different sheets or workbooks. it takes a lot to process that.

I have found I have this issue on a number of my projects where it can take 30min to process a set of calculations.

I have personally found it is quicker to run the first set of calculations then copy and paste special the result before starting the next. I usually do this a column or 2 at a time. You don't have the audit trail but it makes the spreadsheet more manageable.

Another solution which I have seen on here is to use the macro to loop through the cells and and do each cell individually (I haven't applied this to mine yet but it should be even quicker).

 

[Geoff Taylor]

I'm sure that sheer volume has something to do with it, but I just wondered if there was anything else.

I can't use pasting values because part of the model is "what-if" analysis and so I need it to be dynamic. It's a trick to consider on other models, though. Thanks