StatusBar and ScreenUpdate in Excel 2013

Question

I've just gotten into Excel 2013 and am noticing some differences in how macros work (because Microsoft can't leave a good thing alone).

I've always used Application.ScreenUdpdating = False at the start of long macros to speed run time. Generally in those instances I've also used Application.StatusBar = "random text based on macro" to give myself (or other users) some sense of what's going on and/or how much work is left for the macro to complete.

In 2013, I've learned (from Application.Statusbar not working as expected in Excel 2013) that DoEvents is now required. However, when I added in DoEvents after my StatusBar updates, it seems to turn screen updating back on, unless a window outside Excel (ex. an open File Explorer window) is given the focus.

Another issue I've noticed is that with DoEvents in the code I can't seem to break the macro manually (i.e. holding ESC doesn't stop the code).

So a few questions: A) Is DoEvents really required? B) Is there a way to prevent ScreenUpdates from getting turned back "on" by DoEvents? C) How do you manually break code running with DoEvents in it?

I can provide the current macro I'm working on if it will help, but since I'm more interested in the general concepts I didn't want to provide specifics for any readers to get bogged down in.

Thanks in advance!

Answer 1

I ran into a similar problem today so I thought I would share what I found while debugging the issue:

For me I noticed that the status bar was reverting to a previous status bar method while my macro was running. I found that the old message was the last status bar message printed before I disable screen updating. However, new status bar messages were printing temporarily so screen updating wasn't preventing new messages from displaying. I traced the issue to a spot in the macro where I run an executable using:

Set wsh = VBA.CreateObject("WScript.Shell")
errorCode = wsh.Run(exeCMD, windowStyle, waitOnReturn)

It appears that calling the executable disrupted the status bar display and excel reverted to displaying whatever status bar message was showing before screen updating was disabled. To solve this I reactivated screen updating before running the executable and then disabled screen updating once the executable was run:

Set wsh = VBA.CreateObject("WScript.Shell")
Application.ScreenUpdating = True
errorCode = wsh.Run(exeCMD, windowStyle, waitOnReturn)
Application.ScreenUpdating = False

Hope this helps someone out there.