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.
Much better than staring at an unchanging screen for 40 seconds (that would be like watching the microwave for 40 seconds -- it takes forever).
Another program for others runs through about 25 steps. Status Bar text can be updated with anything throughout the macro - plain text, calculated completion precentages, elapsed time, etc.
When the user changes the value in cell E1, the external data is automatically refreshed.
Issue: the user has no indication that the program is doing anything.
This tip (9151) applies to Microsoft Excel 2007, 2010, and 2013.
You can find a version of this tip for the older menu interface of Excel here: Turning Off Screen Updating.We are developing a highly customized BPC input template in Excel and have made extensive use of Visual Basic; however it appears that the "application.screenupdating=false" VB function does not work when logged into BPC. I assume somehow the BPC application disables this functionality? For that, I post and update a userform for each step with a text message with the elapsed time, the number and name of each step, and a progress bar. Also, if the program quits, a screen snapshot tells me where the macros quit working on that user's computer. Don't use "Exit Sub", instead use "Go To End Of Macro" and place this code before "End Sub". Screen Updating = True The same goes for all you do with "Application.***" A simple and less involved method of providing progress updates would be to use the following line of code in the macro: Application. Prior to "End Sub", place this last line of code: Application. Screen Updating = False, the statusbar will change.] This does the side effect that it looks as though Excel has "hung" or crashed, especially if the macros is going to take some time to execute.For long macros I would advocate displaying a Userform saying "Please Wait" and for very long macros showing a progress bar or percentage complete, and maybe even a "Cancel" button to exit before completion.Sometimes the query completes quickly; 5-10 seconds.