Macro "Not Responding" - Please help

[jrabenhorst]

Hi Folks,

I'm a VBA newbie and am having trouble with my macro freezing up during execution. The macro is relatively simple, highlighting and revealing certain aspects of the spreadsheet for a presentation (so screen updating needs to be turned on). The delays in the code are intentional. I'm not familiar with a simpler way to write this code so any advice on what may be causing the freezing issue would be very much appreciated. A subset of the code is shown below:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Demo_Wait()
' ROW 1
Range("H5").Select
With Selection.Interior
.Color = 65535
End With
Sleep 1500
Range("G5").Select
With Selection.Interior
.Color = 65535
End With
Sleep 1500
Range("F5").Select
With Selection.Interior
.Color = 65535
End With
Sleep 1500
Range("E5").Select
With Selection.Interior
.Color = 65535
End With
Sleep 1500
Range("D5").Select
With Selection.Font
.Color = -16776961
End With
Sleep 1500
Range("E5:H8").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' Reset code

Thanks in advance for your help!

 

[zelgar]

Is there a reason why your code can't be the following:

Range("E5:H8").Select
With Selection.Interior
     .Color = 65535
     .Pattern = xlNone
     .TintAndShade = 0
     .PatternTintAndShade = 0
End With
Range("D5").Select
With Selection.Font
     .Color = -16776961
End With

I have a feeling your "Sleep 1500" lines are locking up your macro.

 

[Andrzejek]

If you want to make it shorter (zelgar), why not:

With Range("E5:H8").Interior
     .Color = 65535
     .Pattern = xlNone
     .TintAndShade = 0
     .PatternTintAndShade = 0
End With
Range("D5").Font.Color = -16776961

But I guess jrabenhorst wanted the macro to color cells every second and a half...

Have fun.

---- Andy

 

[DaveInIowa]

The code you provided runs as expected on my machine (Excel 2010) without freezing. At what point in your code is it freezing on yours? Have you tried an alternate way to perform a wait in your code, such as...

Private Sub Snooze(seconds As Single)
   Dim t As Single
   t = DateTime.Timer + seconds
   While (DateTime.Timer < t)
   Wend
End Sub

 

[SkipVought]

What is the purpose of Wait in your code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

If it's not a problem to use full seconds and the purpose is to pause a macro, you can use excel's Wait method, see explanation and examples in the help file.


combo