J
J Streger
I have a workbook that adds a worksheet to the workbook, and then updates
formulas on that workbook to include the new worksheet. The code takes 4
seconds to run in 2003, and 168 seconds in 2007. So I ddi some testing and in
a blank worksheet I added this code:
Sub junk()
Dim inx As Integer
Dim iny As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For inx = 0 To 299
For iny = 0 To 19
Me.Range("A1").Offset(inx, iny).Formula= "=" &
Me.Range("A1").Offset(inx, iny + 52).Address
Next
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
And tested it in Excel 2003 and 2007, IN 2003, the code barely took a
second. In 2007 it took over a minute. now I know the trick for setting
multiple cell values using arrays, but what can you do if it's the formulas
you want to update. 40x longer for code to run is just unacceptable!
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003
User of MS Office 2003
formulas on that workbook to include the new worksheet. The code takes 4
seconds to run in 2003, and 168 seconds in 2007. So I ddi some testing and in
a blank worksheet I added this code:
Sub junk()
Dim inx As Integer
Dim iny As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For inx = 0 To 299
For iny = 0 To 19
Me.Range("A1").Offset(inx, iny).Formula= "=" &
Me.Range("A1").Offset(inx, iny + 52).Address
Next
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
And tested it in Excel 2003 and 2007, IN 2003, the code barely took a
second. In 2007 it took over a minute. now I know the trick for setting
multiple cell values using arrays, but what can you do if it's the formulas
you want to update. 40x longer for code to run is just unacceptable!
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003
User of MS Office 2003