How to Slow the processing Speed of VBA Code

[vikas_newports]

Hello
I am learning too much from this forum and lot of experts are helping me and others
I wrote a macro which is working fine and speedily , now it is the time to show the video of working to my boss
I observed that the Screen Seems Hang or Still
I used Other workbooks, Other Worksheets and multiple formulas and filtrations while the writing of code
How can I slow down the speed of Processing or Screen Update speed

 

[Anthony47]

Good job, up to now!

I don't know if it will be sufficient to slow down the macro, probably you also have to select the right portion of the workbook for having something that will make your boss happy.

So these are just some guidelines…

1) Add this declaration on top of the vba module with your macro

#If VBA7 Then       '!!! ON  TOP  OF  THE  VBA  MODULE   !!!!
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

This will give you the ability to insert in your macro some "Sleep" statements that will stop the macro for as long as you need, in milliseconds step


Then in your macro add a "demo mode":

Dim DemoFl As Boolean     'Initial declare variable

'Set the flag to False for normal execution of the macro:
DemoFl = False 'Or TRUE, if wish to slowdown

Then, add in your macro blocks like this one to show an updating for a short /long time; this has to be done in every position you wish to stop and show

If DemoFl Then
    Workbooks("NameOfWorkbook").Activate    'Select the workbook to show
    Sheets("XyZ").Select                    'Select the sheet to show
    Range("myCell").Select                  'select the range to show
    Application.ScreenUpdating = True       '
    Application.Calculate                   'update results
    DoEvents
    Sleep 200                               '<<< 200 mSec. Use different delays according your situation
'restore previous environment
    Application.ScreenUpdating = False      '??
    '???
    '???
End If

Beware that these blocks, since they select specific sheets and range, maybe will alter the working scenario that the macro is prepared to deal with (only you know how your macro works), so maybe that after the "Sleep" you'll need to re-set the workbooks, worksheets and range as needed

Finally, when you need to run the demo, set DemoFl = True and start the macro.

When DemoFl is set to False all the activity within the added blocks will be ignored, so you don't need to remove them from the macro.

Bye