How to Slow the processing Speed of VBA Code

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,237
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
Code:
#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":

Code:
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
Code:
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
 

Forum statistics

Threads
1,141,413
Messages
5,706,300
Members
421,440
Latest member
cmphares

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top