Nick, or whoever, was correct - it's more efficient and much quicker if you
can do things without either activating or selecting them. And yes, the code
I put up avoids both.
Look in the code that's causing the flashing and see if there isn't a
..Activate or .Select somewhere in it. There may be. Screen updating often
causes a slow down also. And that can occur even when you don't select or
activate anything - Try the following code in a test workbook
Sub FillUpCellsTest()
Dim LoopCounter as Integer
Cells.Clear ' remove any old results
Range("A1").Select
For LoopCounter = 1 to 1000
Activecell = "At Row " & LoopCounter
ActiveCell.Offset(1,0).Activate
Next
MsgBox "All Done Now"
End Sub
All that activating and even just placing the value into the cells will
cause screen scrolling or flashing and a slowdown.
Next lets try it without the flashy stuff. By the way, you might even make
the For Next loop go further say to 5000 just so you can start telling
difference in how long it takes to execute with each of these methods.
We make a simple change to the code, just adding 2 lines
Sub FillUpCellsTest()
Dim LoopCounter as Integer
Cells.Clear ' remove any old results
Range("A1").Select
Application.ScreenUpdating = False
For LoopCounter = 1 to 1000
Activecell = "At Row " & LoopCounter
ActiveCell.Offset(1,0).Activate
Next
Application.ScreenUpdating = True
MsgBox "All Done Now"
End Sub
Technically the Application.ScreenUpdating = True reset at the end isn't
necessary, but I'm kind of retentive about resetting things I've changed to
their original state.
Run it and see how much faster it is.
Next lets see what happens when we do it without activating the cells.
Sub FillUpCellsTest()
Dim LoopCounter as Integer
Cells.Clear ' remove any old results
Range("A1").Select ' becomes the active cell
For LoopCounter = 0 to 999
ActiveCell.Offset(LoopCounter,0) = "Count Is " & LoopCounter
Next
MsgBox "All Done Now"
End Sub
as a final trial, put the Application.ScreenUpdating code into that last
version and (not) watch it just fly!
On my AMD X2 4800+ system the approximate times I got in each of the trials
(best time - it varies depending on what else the system is doing) with a 1
to 10000 or 0 to 9999 loop were:
4.16 Seconds
0.6 (to 1.2) just by using .ScreenUpdating=False
0.4 by going to .Offset()
0.1 by going to .Offset() and turning ScreenUpdating off
The general theory also holds that things already in memory that don't have
to be reinterpreted are worked with faster. This can also make a difference
within loops. Take the literal string in it "At Row " or "Count Is". If
we'd have set those up outside of the loop and written the whole thing like
this (this will give you time to execute also:
Sub FillUpCellsTestReallyFast()
Dim LoopCounter As Integer
Dim myPhrase As String
Dim StartTime As Double
Dim EndTime As Double
Cells.Clear ' remove any old results
Range("A1").Select ' becomes the active cell
myPhrase = "Count Is "
Application.ScreenUpdating = False
StartTime = Timer
For LoopCounter = 0 To 9999
ActiveCell.Offset(LoopCounter, 0) = myPhrase & LoopCounter
Next
EndTime = Timer
Application.ScreenUpdating = True
MsgBox "All Done Now: " & EndTime - StartTime & " Seconds to execute."
End Sub
I was able to get consistent times of approximately 0.4 seconds with that
last routine. But when doing it the 'original' way the time was a consistent
4+ seconds. So there is at least a 10-fold improvement in performance time
using these methods (for this test case). It can go to even larger numbers
if there is a lot of work to be done with cells, such as moving and copying
or filling with custom formulas within VBA code. I've seen cases where the
difference was as much as 30 or more. I have seen 30 second processes
reduced to completion in under a second in the past.
Dean said:
Thanks to all, especially you for explaining all this JL - someday it will
sink in. My understanding, from some other poster (was it Nick) is that
it's more efficient not to activate the worksheets and I think your approach
avoids such, right? In fact, you can probably tell that the macro I sent
you didn't even work with sheets unhidden. I hybridized my existing working
macro which did activate sheets; and one from, I think Nick, which didn't
activate sheets and sent that to you!
The only thing I still find confusing is that I copied the same routine into
my unprotect all sheets macro, when I unprotect, I see the screen flash like
crazy whereas, when I protect, it doesn't move at all. I'm pretty sure the
only difference is one has one row that says unprotect, the other one
protects. No biggie, but I'm curious why. Do you know?
Dean
JLatham said:
Corey's solution will work. Perhaps this will also.
First, there's no .Hidden property for the worksheet object (there is for
rows and columns), so that was a problem.
The second problem was that within your For Each loop your IF test was
always ONLY looking at the current ActiveSheet - so the result of that
test
was always the same (and usually the active sheet is visible <g>). When
you
work through a collection as you were doing with the For Each loop, the
individual members of the collection aren't actually activated, per se, so
that's why ActiveSheet. was always the same sheet.
Sub TestForHiddenSheets()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If wks.Visible = xlSheetVisible Then
'...your code to protect/unprotect here
End If
Next
End Sub
So you test the .Visible property. And since there are 3 possible
conditions or values;
xlSheetVisible (just what it says - the sheet is visible in the workbook)
xlSheetHidden (hidden, but visible in the Format | Sheet [Unhide] list)
xlSheetVeryHidden (hidden from view completely - not even in the list of
hidden sheets)
the best thing to test for is the one condition: Is it Visible and if it
is,
then act on it.
Hope this helps you to some small degree.
Dean said:
I attempted to edit a macro from you folks to protect all sheets, because
I
noticed that, if I hid some sheets, then the macro crashed. I added two
rows, the if statement and the rem statement. This is still crashing
with a
worksheet hidden. I guess it may be that it can't select a sheet if it
is
hidden, so that my if statement is too late, or else my syntax is wrong!
Kindly help.
Thanks!
Dean
Sub ProtectAllWorksheets()
Dim wks As Worksheet
For Each wks In Worksheets
If ActiveSheet.Hidden = True Then GoTo L1
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
L1: Rem
Next wks
End Sub
