View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
Posts: 364
Default moved macros to new book now they don't work

This can sometimes happen if you turn it off (set False) in a procedure your
code goes into break mode. It has no effect if running in break mode. So if
you are using F8 to step through your code it will appear to not be working.
Understand that in this case it's been disabled and so is normal behavior.

Some other things to know about using ScreenUpdating:
Besides hiding screen activity while code is running, that in itself makes
your code run faster because screen activity is suspended and so doesn't
require refreshing as code executes. Another setting that commonly
accompanies this is Calculation. Even though screen activity is temporarily
suspended, Excel is still calculating whenever cell values change. It's
better to turn that off until all the work your macro is doing is finished,
then turn it on and force a calculation so values update. (A definite 'plus'
when running long procedures or processing large amounts of data)

Excel always turns ScreenUpdating back on after the procedure that turned it
off ends. If this procedure was called from another procedure that also
turned it off then it may have no effect until that procedure ends or turns
it off, ..and so on up the call stack.

It's good practice to control turning it off/on as a 'wrapper' around lines
of code that you want to hide any screen activity that may occur while it's
executing. It's easy to lose track of when you have it on or off, or if Excel
turned it off when you wanted it on. It can get very confusing and makes
debugging a harder chore than necessary. Even though Excel will turn it on
when procedures end, you should get into the habit of controlling this in
your code before&after those code segments that make changes to the UI that
you want to hide until the task is done.

Example:
Here's a reusable procedure that you can call to toggle the settings if you
want to save the typing in every procedure you need this in:

Public Sub EnableFastCode(Optional SetFast As Boolean = True)
With Application
If SetFast Then
.ScreenUpdating = False
.Calculation = xlCalculationManual
Else
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.Calculate '//update changes
End If
End With
End Sub

To use it in a procedu
'some code...

'Turn off Calculation/ScreenUpdating
EnableFastCode
'do stuff you want to hide screen activity for...
'changes to cells or UI...
'open/close workbooks...
'process lots of data...
'Turn on Calculation/ScreenUpdating, & recalculate
EnableFastCode False

'finish up...

You might want to read up on the various contexts that the Calculate method
can be used so you fully understand what global effect it has, as well as how
to use it on specific worksheets or cells.

HTH
Garry