Application.Calculation

[krishnak]

Hi All,

What is the basic difference between xlCalculationManual and xlCalculationAutomatic? For my PERSONAL.XLS workbook, the Calculation setting is Automatic. I am trying to understand a macro for a workbook with several worksheets. In this macro , while operating on the data of a particular worksheet, the setting is changed to xlCalculationManual and after the calculation is over the setting is changed to xlCalculationAutomatic.
I am looking at the following macro :

Sub Delete_Data()
StopCharacter = ""
ObjectColumn = "D"
DeleteCriteria = SelectedAccount
RowCounter = 2

Application.Calculation = xlManual

Do While Range(ObjectColumn & RowCounter).Value <> StopCharacter
If Range(ObjectColumn & RowCounter).Value = DeleteCriteria Then
Rows(RowCounter).Delete
Else
RowCounter = RowCounter + 1
End If
Loop
Application.Calculation = xlAutomatic
End Sub

Thanks in advance

- Krishna

[ief]

Hi Krishna,

This relates to the Calculation tab in Tools > Options. Running the Application.Calculation = xlManual switches from the default Automatic setting. Essentially it's used to control when Excel recalculates formulae after a cell is changed.

Hope that helps.

[rory]

The reason it is in the code is that each time a row is deleted Excel might try to recalculate the worksheet. If you turn calculation off at the beginning and then turn it back on at the end, Excel will only recalculate the final results and that should speed up the processing.

[mdmackillop]

You should always build in an error handler to reset to automatic, as this change affects all workbooks
[VBA]
Sub Delete_Data()
On Error GoTo Reset
Application.Calculation = xlManual
'Do Your Stuff
Reset:
Application.Calculation = xlAutomatic
End Sub
[/VBA]

[Bob Phillips]

It's purely about speed. If you set to manual, anything you do in VBA will not cause a recalculation of all formulae. For example, I was handed a workbook recently which had a pivot table, and some code went through and selected every broker from the list and printed it. As handed to me, this took over an hour. I set calculation to manual, and just prior to printing each broker's pivot view I reclaculated just that sheet (there were many others with formulae on), and I reduced the print time down to less than 4 minutes.

[krishnak]

Thanks all of you for the explanation. This forum is awesome; what one can learn from this forum is beyond the scope of any tutorial or book.

- Krishna

[Bob Phillips]

Thanks all of you for the explanation. This forum is awesome; what one can learn from this forum is beyond the scope of any tutorial or book.

- Krishna

That ius because we can tailor the info to YOUR particular problem. Books cannot only really give guidance, the actual problems out there are just too many and too varied. Having said that, your question is one that could (should?) be very easily covered in a tutorial or a book.