While the procedure opens a form and minimizes it, the user only sees an hourglass icon indicating that processing is taking place, and the screen isn't repainted.
When this task is completed, the hourglass changes back to a pointer and screen repainting is turned back on.
Screen Updating = False 'swap between sheets 1/2 (need both sheets or this will crash) For i = 1 To numb Switches Sheets(1 (i Mod 2)). Screen Updating = True 'get results for part two results = results & vb Cr Lf & "Screen Updating IS disabled: " & Format(Time - start Time, "hh:mm:ss") & " seconds" 'show results Msg Box results End Sub Firstly I've been using the script written by Richie (UK) Post #7 Here It simply iterates through a loop changing the value of i in one cell.
I have changed it slightly so it loops 10,000 times and I execute it 10 times for sample size.
The following macro lines will, respectively, turn off screen updating and then turn it back on in a VBA macro.
The idea is to use the first line near the beginning of your macro, and then use the second line near the end.
The grater the amount of screen changes the bigger the impact will be. Other application settings that can make a difference to execution time are Calculation and Event handling. Enable Events = False ' Code here Clean Up: On Error Resume Next Application. Calculation = xl Calculation Automatic Application. Enable Events = True Exit Sub EH: ' Do error handling Go To Clean Up End Sub Sub test Screen Updating() Dim i As Integer Dim numb Switches As Integer Dim results As String 'swap between sheets this number of times numb Switches = 1000 'keep track of time Dim start Time As Double start Time = Time 'swap between sheets 1/2 (need both sheets or this will crash) For i = 1 To numb Switches Sheets(1 (i Mod 2)).
Use this code template as a starting point (the error handler ensures that these properties are turned back on at the end of the sub, even if it errors) Sub Your Sub() On Error Go To EH Application. Select Next i 'get results results = "Screen Updating not disabled: " & Format(Time - start Time, "hh:mm:ss") & " seconds" start Time = Time 'scenario 2 - screenupdating disabled Application.
is your source for cost-effective Microsoft Excel training.This means that the Excel screen can look like it has "gone crazy" while the macro is running.One thing you may want to do with your macro to make it run faster and to prevent distracting flashes on the screen is to turn off screen updating while the macro is running.Keep that on mind if you will execute anything which would take less time.And after all don’t turn screen updating on/off many times in some loop. And one more note (which you probably don’t want to hear) if you want it quick use C .