1

The code below do not work properly. The problem is that it seems that the next line is executed before the command given by the former line completely finish the output to the screen.

So, what command imposes Excel to wait completely for a subprocedure to completely finish before execute next line?

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

Sub SweepLinesAndPaintYellowActiveCell()
lastline= Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastline
      Cells(i, 1).Select
      ActiveCellYellow
      Sleep 25
      ActiveCellWhite
Next i
End Sub

Sub ActiveCellYellow()
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Sub ActiveCellWhite()
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub
Community
  • 1
  • 1
strajano
  • 57
  • 2
  • 7
  • Application.Wait can put a pause in the code. – Cyril Jun 15 '18 at 15:38
  • Thanks for the tip, but Application.Wait has a minimum of 1 second. I need less than that. – strajano Jun 15 '18 at 15:41
  • Then sleep may work, as it allows milliseconds. I'm not that familiar with Sleep function in VBA, thoguh – Cyril Jun 15 '18 at 15:41
  • See: https://stackoverflow.com/questions/18602979/how-to-give-a-time-delay-of-less-than-one-second-in-excel-vba – Cyril Jun 15 '18 at 15:42
  • I am already using Sleep. In fact, the only issue I am facing is a abnormal and undesirable behavior of the worksheet, I mean, it is not a fatal problem. But I would be glad to know that Excel has the same feature of jQuery: $(document).ready(function(){ $("button").click(function(){ $("p").slideToggle(); }); }); – strajano Jun 15 '18 at 15:48
  • Thanks Cyril! From the post you linked I found the P-E-R-F-E-C-T solution: Sub WaitFor(NumOfSeconds As Single) Dim SngSec as Single SngSec=Timer + NumOfSeconds Do while timer < sngsec DoEvents Loop End sub – strajano Jun 15 '18 at 16:01
  • Glad that worked out for you! – Cyril Jun 15 '18 at 16:10
  • Yes, It works perfectly for me! But anyone who needs to make a series of changes to the worksheet in a fast pace so that Excel start to behave abnormaly while executing should use this "pauses" as I am doing now because of you :). Now Excel is behaving beautifully. – strajano Jun 15 '18 at 16:21

1 Answers1

0

Sorry for the low-quality answer, rest of StackOverflow... solved this in the comments, and providing an answer so the question doesn't stay listed as Open.

See: How to give a time delay of less than one second in excel vba? – Cyril 54 mins ago

Cyril
  • 6,448
  • 1
  • 18
  • 31
  • but what if I don't know or don't want to set a time delay, because I can't know how much time it will take? is there no option to wait until previous command finish? like power query etc. – Spon4ik Feb 11 '22 at 00:54
  • @Spon4ik that would need to be its own post, as it is depending on what you're doing, e.g., `do until ie.readystate = 4`. – Cyril Feb 11 '22 at 13:16