Freeze display (2000 SR1)

Topic

New Reply

Some of the macros that I am writing have the display flashing this or that, flipping between windows, etc. When I was writing macros for Lotus, there was a command to freeze the display, not updating anything on screen until the freeze was lifted or the macro finished running. In addition to eliminating unnecessary visual distractions, the macro would also run faster. Is there a similar command that could be added to a VBA macro?

Reply | Quote

Replies

David

Here are some code snippets I use…

Setting calculations to manual will keep Excel from updating any “dependant” cells until after your vba code is finished running… this may or may not have a huge effect on the speed of your vba code.

‘ Set Calculations to Manual
‘ Create a variable to save the user preference setting
Dim saveCalculation As String
‘ Save the users setting for the xlCalculation
saveCalculation = Application.Calculation
‘ Turn off calculations… speeds up VBA code
Application.Calculation = xlCalculationManual
‘ When done… reset back to user preferrence
Application.Calculation = saveCalculation

Turning off screen updating sounds like exactly what you were asking about. Be aware though, ALL Excel screen updates are disabled by the following code, which can make your application appear to freeze. I always use some other activitiy indicator in conjunction with disabling screen updates… like setting a message in the status bar to “Please wait…” and, for long routines, updating the status bar message every so often throughout my code.

‘ Set StatusBar On & send a message
‘ Create a variable to save the user preference setting
Dim saveStatusBar As String
‘ Save the users setting for the StatusBar
saveStatusBar = Application.DisplayStatusBar
‘ Turn on the StatusBar
Application.DisplayStatusBar = True
Application.StatusBar = “This will take some time. Please be patient…”

‘ Turn off screen updates… makes Excel appear to freeze!!!
Application.ScreenUpdating = False

”” Run your time consuming routine here

‘ Turn screen updates back on
Application.ScreenUpdating = True

‘ Send another message
Application.StatusBar = “Processing complete. Thank you for your patience…”

‘ Set the StatusBar back to the user preferrence
Application.DisplayStatusBar = saveStatusBar

BTW… love your signature! (a fellow Canadian)

Hope this helps…

Reply | Quote

The “Application.ScreenUpdating = False” is exactly what I had in mind. For now, the processes are not long or time consuming, just very busy visually. This should make things a little more to my liking. (My opinion of programming is “Let them see the input screen and the final output — hide everything else.”)

Reply | Quote

I have a userform that is shown while macros run. The userform is attached (.bmp file) as an example.

Sub PopulateAvail()
    Application.ScreenUpdating = False
    
    Load frmAvailProg
    frmAvailProg.Show vbModeless
    
    frmAvailProg.lblAvailProg.Caption = "Formatting...."
    frmAvailProg.Repaint
    AvailFormat
    
    frmAvailProg.lblAvailProg.Caption = "Filling in Dates...."
    frmAvailProg.Repaint
    AvailDateFill
    
    frmAvailProg.lblAvailProg.Caption = "Filling in Hours and Minute Intervals...."
    frmAvailProg.Repaint
    AvailHourIntervalFill
    
    frmAvailProg.lblAvailProg.Caption = "Unit Labels are being written...."
    frmAvailProg.Repaint
    FillUnits
    
    frmAvailProg.lblAvailProg.Caption = "Inserting Formulas...."
    frmAvailProg.Repaint
    FillFormulas
    
    frmAvailProg.lblAvailProg.Caption = "Cleaning up...."
    frmAvailProg.Repaint
    CleanUpAvail
    
    Unload frmAvailProg
    Application.ScreenUpdating = True
End Sub

Reply | Quote

Very slick! (and professional)

Thanks for sharing, it’s given me a great idea for my own version.

Reply | Quote

David

I stole your signature…

Reply | Quote