*Resolved* Excel VBA: Freeze Worksheet Calculation?

Hi,

Is there any way to freeze (prevent) a number of worksheets from recalculating when a VBA macro is running?

I have a spreadsheet with about 10 worksheets, most of which are linked to each other in some way. One of my macros only needs to use the formulas (and related results) from about 7 of these, so the other 3 are updating all the time but since the results from these are not needed then they are slowing down my code.

Is there some VBA function for temporarily disabling a worksheets calculation function? I would need my code to look something like this:

Code:

---

Worksheet("sheet1").Calculate:False
Worksheet("sheet2").Calculate:False
Worksheet("sheet3").Calculate:False

*Execute the macro code*

Worksheet("sheet1").Calculate:True
Worksheet("sheet2").Calculate:True
Worksheet("sheet3").Calculate:True

---

Is something like this possible?

Thanks
-Rob

I think the best you could do is to only calculate a range instead of an entire sheet or workbook.

VB Code:

Worksheets(1).Rows(8).Calculate

Depending on what your needs are, ManualCalculation may help also

VB Code:

With Application
.Calculation = xlCalculationManual
 
    Sheet1.Calculate
    Sheet2.Calculate
'    chain more sheets together as needed
 
    'your macro
    
.Calculation = xlCalculationAutomatic
End With