Defrosting your code
One of our team members recently noticed an issue with Excel VBA code running on Windows Vista (it is something that happens on WindowsXP too, it is just more pronounced on Windows Vista), so I thought I would write it up. While it is not the sort of issue that the Excel team can easily address, it is something worth mentioning to all the VBA developers out there, in part to see how people might be handling this situation already.
The behaviour in question is how Excel behaves when VBA code runs for more than ~5 seconds uninterrupted – for example, you are running some sort of loop that takes longer than 5 seconds to finish. On WindowsXP, after 5 seconds or so, if the user starts clicking on the application to see if anything is happening, they will see a “Not Responding” show up in the title bar (which is a subtle enough change that many people do not even notice). (Note – the circumstances around “Not Responding” appearing can be a bit more complicated than that, but I wont go into it, because it doesn’t really matter in the context of this discussion.)
The behaviour on Windows Vista is the same, with one exception – the Windows folks have done work to make it more apparent to the user when an application is “not responding”. Specifically, they have a behaviour called “frosting”, which means that when the user starts clicking around the window, the whole window gets this semi-transparent look (which those of you in northern climes will recognize as frost) to really clue the user into the fact something abnormal is up. (Again, there is more to it than this, and the code controlling when an app is considered not responding has changed between Windows XP and Windows Vista, but I wont go into the details for the purposes of this discussion.)
(Click to enlarge)
While this can be helpful in some cases, it probably isn’t the idea behaviour when it is code executing behind your workbook, since the user may decide to end-task on the code, which isn’t really what you want.
Because this behaviour stems from the fact that Office is not able to send messages to the OS when macros are running, the simplest work around is include a DoEvents call in your code to allow message processing when it is executing loops that may go on for a while – that prevents the frost (and “Not Responding in the title bar).
Assuming that makes sense, let’s have some audience participation time … do people run into this already? If so, what sort of workaround have you used?
