Application.Calculation = xlCalculationManual mystery

I've heard/read that setting calculation to manual can often speed up
macro execution, and I've always seen it placed early in the routine and
reset late, and I've used that technique in other workbooks without
problems, so I put them in the sub below. I've noted that in this
particular workbook, if not reset *before* the sort, it adversely affects
the sort (new student name remains where inserted), but I don't know why.
I've commented the good/bad placements. Can anyone explain why?

Sub AddStudent()
Dim Rng As Range, FirstCell As Range, LastCell As Range
Dim NumRows As Long, NumCols As Long, New_Student As String
Set Rng = Range("Name_Copy")
Set FirstCell = Rng(1)
Set LastCell = Rng(Rng.Count)
NumRows = Rng.Rows.Count
NumCols = Rng.Columns.Count
frmAddStudent.Show
New_Student = UCase$(frmAddStudent.tbNewName.Text)
If New_Student = "" Then Unload frmAddStudent: Exit Sub
Application.ScreenUpdating = False: Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Rng.Copy
Range(FirstCell.Address).Resize(NumRows).Insert shift:=xlDown
Range(FirstCell.Address).Offset(0, 2).Resize(NumRows, NumCols -
4).ClearContents
Range(FirstCell.Address) = New_Student
Range("AD3")(2).Insert shift:=xlDown: Range("AD3")(2) = New_Student
Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp)).Name = "ClassList"
Range(Cells(3, 25), Cells(Rows.Count, 1).End(xlUp)).Offset(0, 2).Name =
"Hours"
Range(Cells(3, 1), Cells(NumRows + 2, NumCols)).Name = "Name_Copy"
Range(Cells(3, 1), Cells(Rows.Count, NumCols).End(xlUp)).Name =
"SortRange"
Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp)).Offset(0, 27).Name =
"Total"
Application.Calculation = xlCalculationAutomatic '<-- If here, good sort
Range("SortRange").Sort key1:=LastCell, Order1:=xlAscending
Range(Range("AD3"), Range("AD65000").End(xlUp)).Sort key1:=Range("AD3"),
Order1:=xlAscending
Insert_PageBreaks
ActiveSheet.UsedRange
Application.Calculation = xlCalculationAutomatic '<-- If here, bad sort
Application.EnableEvents = True: Application.ScreenUpdating = True
Unload frmAddStudent
End Sub

--
David

Depending on exactly what your source data looks like and the formulas in it
you may need to recalculate periodically in order to use the interim values.
Try something like this

Sub test()
On Error GoTo ErrorHandler
Application.Calculation = xlCalculationManual

'A bunch of Stuff
Application.Calculate 'Calculate that stuff

'A bunch more stuff using the calculated values

ErrorHandler:
Application.Calculation = xlCalculationAutomatic
End Sub

***Note: You should always use an error handler whenever you toggle
application level settings in or to reset them in case of a crash.
--
HTH...

Jim Thomlinson


"David" wrote:

> I've heard/read that setting calculation to manual can often speed up
> macro execution, and I've always seen it placed early in the routine and
> reset late, and I've used that technique in other workbooks without
> problems, so I put them in the sub below. I've noted that in this
> particular workbook, if not reset *before* the sort, it adversely affects
> the sort (new student name remains where inserted), but I don't know why.
> I've commented the good/bad placements. Can anyone explain why?
>
> Sub AddStudent()
> Dim Rng As Range, FirstCell As Range, LastCell As Range
> Dim NumRows As Long, NumCols As Long, New_Student As String
> Set Rng = Range("Name_Copy")
> Set FirstCell = Rng(1)
> Set LastCell = Rng(Rng.Count)
> NumRows = Rng.Rows.Count
> NumCols = Rng.Columns.Count
> frmAddStudent.Show
> New_Student = UCase$(frmAddStudent.tbNewName.Text)
> If New_Student = "" Then Unload frmAddStudent: Exit Sub
> Application.ScreenUpdating = False: Application.EnableEvents = False
> Application.Calculation = xlCalculationManual
> Rng.Copy
> Range(FirstCell.Address).Resize(NumRows).Insert shift:=xlDown
> Range(FirstCell.Address).Offset(0, 2).Resize(NumRows, NumCols -
> 4).ClearContents
> Range(FirstCell.Address) = New_Student
> Range("AD3")(2).Insert shift:=xlDown: Range("AD3")(2) = New_Student
> Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp)).Name = "ClassList"
> Range(Cells(3, 25), Cells(Rows.Count, 1).End(xlUp)).Offset(0, 2).Name =
> "Hours"
> Range(Cells(3, 1), Cells(NumRows + 2, NumCols)).Name = "Name_Copy"
> Range(Cells(3, 1), Cells(Rows.Count, NumCols).End(xlUp)).Name =
> "SortRange"
> Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp)).Offset(0, 27).Name =
> "Total"
> Application.Calculation = xlCalculationAutomatic '<-- If here, good sort
> Range("SortRange").Sort key1:=LastCell, Order1:=xlAscending
> Range(Range("AD3"), Range("AD65000").End(xlUp)).Sort key1:=Range("AD3"),
> Order1:=xlAscending
> Insert_PageBreaks
> ActiveSheet.UsedRange
> Application.Calculation = xlCalculationAutomatic '<-- If here, bad sort
> Application.EnableEvents = True: Application.ScreenUpdating = True
> Unload frmAddStudent
> End Sub
>
> --
> David
>

=?Utf-8?B?SmltIFRob21saW5zb24=?= wrote

> Depending on exactly what your source data looks like and the formulas
> in it you may need to recalculate periodically in order to use the
> interim values.

Makes sense, but I can't see that (doesn't mean it isn't there)
circumstance in this file. I've always seen it used early in routines and
reset after routine finishes, so I got curious.

> ***Note: You should always use an error handler whenever you toggle
> application level settings in or to reset them in case of a crash.

Yeah, I sometimes neglect to do that.

--
David