Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculation = xlCalculationManual mystery
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculation = xlCalculationManual mystery
?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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.Calculation Problems | Excel Worksheet Functions | |||
How to set in XLA calculation on xlCalculationManual | Excel Programming | |||
Application.Calculation | Excel Programming | |||
Application.Calculation=xlCalculationManual causing run time error? | Excel Programming | |||
Application.Calculation exception | Excel Programming |