Very slow macro

[CLR]

Hi All...........

I have this macro repeated 36 times in my workbook, varying the names,
etc..........they work fine, except they take a LONG time to execute, with
the Status Bar indicating "Calculating Cells", and incrementing every
5%.........can anyone please show me how to speed this up?.....the two
Ranges are D8:BC30 and D33:BC46.

Sub UpdateSkeletonCrewX()

Worksheets("SkeletonCrewX").Select
ActiveSheet.Unprotect
Range("FieldSkeletonCrewX1").ClearContents
Range("FieldSkeletonCrewX2").ClearContents

Worksheets("SkeletonCrewX").Range("FieldSkeletonCrewX1").Formula =
"=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE)),
"""",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE))"
Worksheets("SkeletonCrewX").Range("FieldSkeletonCrewX2").Formula =
"=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE))
,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE))"

Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()"
Range("C3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("a5").Select
End Sub

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Every time a cell is changed the entire workbook must recalculate. To get
around this you can turn the caluclations off while the macro runs. The same
as tools->options->Calculation-> manual

sub DoSomeStuff
on error goto ErrorHandler
Application.calculation = xlManual
'Do your stuff
ErrorHandler:
Application.calculation = xlAutomatic
End Sub

As always when you play with application level settings it is a good idea to
use an error handler to rest things in case of (or more accurately when) a
crash.

 

[CLR]

Thanks Jim, but I must have done something wrong.......it takes longer
now........

Sub UpdateSkeletonCrewX()
On Error GoTo ErrorHandler
Application.Calculation = xlManual

Worksheets("SkeletonCrewX").Select
ActiveSheet.Unprotect
Range("FieldSkeletonCrewX1").ClearContents
Range("FieldSkeletonCrewX2").ClearContents

Worksheets("SkeletonCrewX").Range("FieldSkeletonCrewX1").Formula =
"=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE)),
"""",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE))"
Worksheets("SkeletonCrewX").Range("FieldSkeletonCrewX2").Formula =
"=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE))
,"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE))"

Worksheets("SkeletonCrewX").Range("c3").Formula = "=now()"
Range("C3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("a5").Select

ErrorHandler:
Application.Calculation = xlAutomatic

End Sub

Vaya con Dios,
Chuck, CABGx3

 

[Gary Keramidas]

don't know if will work in your case, but i have created the formula in each
cell without the = sign. then i go back and add the = sign when i'm done so
it calculates a lot less times.

so, if the formula in A3 was =A1+A2, i would put A1+A2 in the cell. after i
created all of the text for the formulas, i would go back and add the = sign
then use the code:
Range("A3").Formula = "='" & Range("A3").Formula


like i said, it cut my code run times on one spreadsheet from 3 minutes to
30 seconds. obviously i had more elaborate formulas than this, just giving
an example.

 

[CLR]

Thanks Gary, good idea, I'll give it a ponder...........

Although I'm just using two ranges in the macro to add the formulas to, they
consist of about 1400 cells.......I would have to be able to add the equal
sign to all the cells in a range........
But it actually seems like it's the "Calculation" that's taking all the time
and it only seems to run once at the end......even with my original
code..........

Vaya con Dios,
Chuck, CABGx3

 

[Jim Cone]

Hi Chuck,

I made these changes...
ScreenUpdating = False
Changed the Range names to the range address ...
(so I wouldn't have to create the named ranges).
Added a timer and msgbox in two places
Simplified the Now function code

In xl97, it took about 3.7 seconds to add the formulas and
the same length of time to do the calculation.
Of course, my "MasterNameFile" sheet is blank.
Maybe you can play with it to see how the time is allocated on
your machine. (divide the time by 1000 to get seconds)

Regards,
Jim Cone
San Francisco, USA

'----------------------
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub UpdateSkeletonCrewX()
Dim TimeStart As Long
Dim TimeStop As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("SkeletonCrewX")
.Unprotect
.Range("D8:BC30").ClearContents
.Range("D33:BC46").ClearContents
TimeStart = timeGetTime
.Range("D8:BC30").Formula =
"=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE)),"""",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamefile!$B
:$S,14,FALSE))"
.Range("D33:BC46").Formula =
"=IF(ISNA(VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNamefile!$B:$S,14,FALSE)),"""",VLOOKUP(($C30&""_""&D$1&""_""&D$4),MasterNamefile!
$B:$S,14,FALSE))"
TimeStop = timeGetTime
MsgBox "Formulas took " & TimeStop - TimeStart
.Range("c3").Value = Now
Application.CutCopyMode = False
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
.Range("a5").Select
End With
TimeStart = timeGetTime
Application.Calculation = xlCalculationAutomatic
TimeStop = timeGetTime
Application.ScreenUpdating = True
MsgBox "Calculation took " & TimeStop - TimeStart
End Sub
'-------------------------------



"CLR" <[email protected]>
wrote in message
Thanks Gary, good idea, I'll give it a ponder...........
Although I'm just using two ranges in the macro to add the formulas to, they
consist of about 1400 cells.......I would have to be able to add the equal
sign to all the cells in a range........
But it actually seems like it's the "Calculation" that's taking all the time
and it only seems to run once at the end......even with my original
code..........
Vaya con Dios,
Chuck, CABGx3

 

[Gary Keramidas]

i may be wrong, but i think even though calculation is off, when you enter a
formula, it calculates that cell. so, it is still calculating every formula
creation.

i'd be curious to know if that helped you as much as it helped me.

i have formula that links to 20 sheets. the formula changes to access
different cells in the 20 sheets 9 more times. so there are 10 formulas
accessing 20 linked sheets for each day of the month. that's a lot of
calculating. that's why my code execution time was cut down so much. and
yes, i had calc mode set to manual, but it still had to build each formula
and calc the cell as it built it.

what i ended up doing, was just building the formula for the 1st row, and
autofilling down because every cell in the linked sheet was 1 below the
other just like in my summary sheet. that save time, too, since i didn't
have to build formulas for each day.

 

[Gary Keramidas]

one other thing i do at the end, is do a pastespecial values over the
formulas, so previous months don't have to re-link those long formulas. that
data never changes, so being static is fine. even if something happens, the
macro only takes a few seconds to regenerate the whole month.

 

[CLR]

Hi Jim............thanks for the effort, but no cigar yet............
In my program, your code functions just like you said (the timer thing is
neat),but took nearly 1 minute for each of two cycles. Since I couldn't see
what was going on, I remmed out the screen updating lines and saw nearly the
same thing that I saw when trying Jim Thomlinson's suggestion, ie: as soon
as I fire the macro, the Status line reads "Cell:" with a completely full
progress indicator bar for a full 60 seconds, then it reads "Calculating
Cells: xx%" and cycles through from 5-100% and that takes nearly 60 seconds
as well........

It would appear that my original macro runs quickly enough, it's just the
"Calculating" that is taking all the time. What might speed that up, a
faster computer?, or newer XL maybe?


Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Hi Gary.........
I tried what you said, and it filled the Ranges very quickly using only the
TEXT version, and added the equal signs relatively quickly too, but
unfortunately it put the same formula in every cell in the range instead of
stepping them to be appropriate for each cell location.........maybe I'm
trying too hard, I'm under the gun to get this thing done, so I guess I
better get it done first and worry about the speed thing after the users
start complaining.........<g>

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Yeah Gary, I hear ya, but this book has 36 sheets like this and it gets too
big if I leave all the data there and could present an erroneous impression
of being current data when changes are being made etc etc........so I just
clear the fields with a WorksheetDeactivate macro.........but I DO like your
thinking.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3