very slow code

[Guest]

I can't figure out why this code takes 9 seconds to run. What it does is
switch the contents of two adjacent cells. It does have to unprotect the
worksheet and also turn off autocalculation, then restore both of those
settings. But the core code doesn't do much that should generate the delay I
am seeing. Any ideas from the experts? TIA

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Unprotect Password:="password"
Selection.Cut
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Insert Shift:=xlToRight
ActiveSheet.Protect Password:="password", DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFiltering:=True
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

 

[Guest]

First off try to eliminate your select statements. In most cases they're
useless. You can also nest your application statements as one, and you can
do that with the active sheet reference.

You can also put your Cut statement on one line.

Here's an example (note I just randomly chose C1, so it probably won't do
what you want, it's more for the examples of what you can do to speed up your
code):

With Application
.Calculation = xlManual

With ActiveSheet
.Unprotect Password:="password"
With Range("C1")
.Cut .Offset(, -1)
.Offset(, -1).Insert Shift:=xlToRight
End With
.Protect Password:="password"
End With

.Calculation = xlAutomatic
End With

HTH,

Smitty

 

[Guest]

Thanks, Smitty. I tried some of the changes but I'm not any better off. The
cells I'm reversing are within a range of 9000 cells that gets celled by a
lot of formulas elsewhere in the workbook (many sumproducts). I have a
feeling that one of my VBA statements - possibly the "Insert
Shift:=xlToRight" - is causing all those formulas to recalculate. A regular
F9 manual recalc takes under 1 second, but I still seeing 9 seconds. Maybe I
need to revise my approach and avoid that statement?

 

[Guest]

Thanks, Smitty. I tried some of the changes but I'm not any better off. The
cells I'm reversing are within a range of 9000 cells that gets celled by a
lot of formulas elsewhere in the workbook (many sumproducts). I have a
feeling that one of my VBA statements - possibly the "Insert
Shift:=xlToRight" - is causing all those formulas to recalculate. A regular
F9 manual recalc takes under 1 second, but I still seeing 9 seconds. Maybe I
need to revise my approach and avoid that statement?

 

[Guest]

Maybe you need to give a deeper explanation of the process, like what else is
happening?

You might also want to look into disabling events if your calculating and
subsequent code changes are causing some recursion like you indicate.

What's calling your code?

Smitty