VBA - change calculation setting of workbook to speed up macro

[JEB85]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have a range of cells ( C2, C5, C8:N1007, P8:P1007 …..) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database<o></o>
<o> </o>
I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.<o></o>
<o> </o>
I’ve inserted a module and produced this code;<o></o>
<o> </o>
Sub ClearContents()<o></o>
Application.Calculation = xlCalculationManual<o></o>
Range(“C2, C5, C8:N1007, P8:P1007, ….”).ClearContents<o></o>
Application.Calculation = xlCalculationAutomatic<o></o>
End Sub<o></o>
<o> </o>
Because of all the 3 summary page formulas the code is taking a few seconds to run.<o></o>
<o> </o>
I’m new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.<o></o>
<o> </o>
Thanks<o></o>

 

[jbeaucaire]

Your macro demonstrates the answer already. You are setting the workbook to "manual" before you start making changes, then you turn it back to "automatic" at the end. What else do you need?

 

[JEB85]

Like i say, I'm a newbie to VBA so wasn't sure if I'd done everything right - it's great to be reassured!

Am I right in inserting a module as opposed to putting the code in 'This Workbook'.

Thanks

 

[jbeaucaire]

No, this appears to be a macro you're going to run from the ALT-F8 macro menu?

In the VBEditor, select Insert > Module to add a blank standard code module. Put the macro in there.

Macros in code modules are designed to be called from anywhere and apply to the activesheet, unless the macro targets a specific sheet, which yours does not.

Module1, Module2... - the standard code modules for macros/functions applicable to anywhere in the workbook

Sheet1, Sheet2... - the sheet modules where codes designed specifically for one sheet are found. Also code for Form and Control objects added to that specific sheet.

Sheet-event macros can be found in this module, too, macros that self-trigger based on specific things occurring on the specific sheet.

ThisWorkbook - Holds Workbook-event level macros, things designed to apply to the workbook as a whole or be monitoring the wb as a whole.

 

[JEB85]

Thanks for the great info!

I wasn't sure if the code, as it was an inserted module, recognised the fact that I wanted the automatic calculation turned off for all sheets and not just the 'active sheet' (I run the code from the Database sheet).

 

[jbeaucaire]

The line of code your using is setting the "Application" level calculation, so it applies to the whole instance of Excel, for the moment, so ALL open workbooks would stop calculating, not just the one workbook active nor even the one sheet.