Updating cells on an open Excel sheet while switching application.screenupdating off and on

Question

This is a small issue with a large macro I am making reading through log files and preparing data by writing them into a number of worksheets. I don't want to see all of this writing going on, so I turn screen updating off, however I want to see some progress so every now and then I switch screen updating on I activate the top worksheet, write some progress data in some cells and switch screen updating off again. This worked pretty well in Excel 2010, but last week I upgraded to Excel 2013 and no more updating visible until the macro finishes. I added a 0.3 second pause, but now only the time gets updated and not the number of processed sites. Any ideas?

Application.ScreenUpdating = True  'To write the elapsed time and the number of found sites on the performance monitor sheet
PerfMon.Activate
PerfMon.Range("B4") = Now - StartPoint
PerfMon.Range("D4") = SiteCnt
Application.Wait (Now + (0.3 / 86400))
Application.ScreenUpdating = False

Thanks, Hans

I don't have 2013, but perhaps try instead using the StatusBar to keep track of progress instead. See Chip Pearson's page, or this page for some ideas. (I'm not sure if the StatusBar is affected by ScreenUpdating though, but I don't think it is). — BruceWayne
That was an unexpected end. Anyway, what I wanted to say is: a big thanks to both of you. Both solutions work. I imagine that the StatusBar would be the MS preferred solution, whereas the DoEvents is mine. — HPS_EHV
DoEvents yields execution so that the operating system can process other events. There is a caution in its description but it is the standard way of pausing the main process so background stuff can be actioned. — Tony Dallimore
To show progress I prefer to use a modeless userform. A modeless userform requires no action from the user but the macro can write to it. You could have a progress bar but I usually prefer informative displays. If I am processing 1,000 rows, I will have fixed labels containing "Row", "of" and "1,000" and one to which I write the current row number so the form reads "Record N of 1,000" with N running from 1 to 1,000. With multi-stage macros, I list all the stages and report progress through them. — Tony Dallimore

Better_than_Jeff Better_than_Jeff · Accepted Answer · 2015-11-18T03:55:15

The best solution seems to be enabling the status bar. Without knowing exactly what your macro is, I would suggest you set one of the variables in your workbook as an integer and keep track of the current progress in the status bar. Maybe this is "Now - StartPoint" or "SiteCnt," otherwise you can periodically have the status bar update with new text.

If you go with the integer option, here is an example:

[...]
 Application.ScreenUpdating = False
 Application.DisplayStatusBar = True
 Dim i As Integer
 For i = 2 To Sheets.Count       ' example from my macro but you could use any repeating process
       Worksheets(i).Activate
       Application.StatusBar = "Updating Filings for " & ( i - 1 ) & " of " & (Sheets.Count - 1) & "   |   " & Format (( i - 1 )) / (Sheets.Count - 1), "0%") & "Complete"
 Next i
 Application.StatusBar = ""
 Application.DisplayStatusBar = False
[...]

If you go with the text option, here is an example:

[...]
 Application.ScreenUpdating = False
 Application.DisplayStatusBar = True
 Application.StatusBar = "Reading log files"
 [...]
 Application.StatusBar = "Preparing data"
 [...]
 Application.StatusBar = "Formatting data"
 [...]
 Application.StatusBar = ""
 Application.DisplayStatusBar = False
[...]

Updating cells on an open Excel sheet while switching application.screenupdating off and on

1 Answers