Excel 2013中的StatusBar和ScreenUpdate [英] StatusBar and ScreenUpdate in Excel 2013

问题描述

我刚刚进入Excel 2013，并注意到宏的工作方式存在一些差异(因为Microsoft无法独自留下美好的事情).

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).

我一直在长宏的开头使用Application.ScreenUdpdating = False来加快运行时间.通常，在这些情况下，我还使用Application.StatusBar = "random text based on macro"让自己(或其他用户)了解正在发生的事情和/或宏需要完成多少工作.

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.

2013年，我从应用程序中了解到.状态栏无法在Excel 2013中正常运行)，现在需要DoEvents.但是，当我在StatusBar更新后添加到DoEvents时，似乎重新打开了屏幕更新，除非将焦点放在Excel外部的窗口(例如，打开的文件资源管理器窗口).

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.

我注意到的另一个问题是，在代码中使用DoEvents似乎无法手动中断宏(即，按住ESC不会停止代码).

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).

以下几个问题: A)确实需要DoEvents吗? B)有没有办法防止ScreenUpdates被DoEvents重新打开? C)您如何手动中断其中运行DoEvents的代码?

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.

提前谢谢！

推荐答案

我今天遇到了类似的问题，所以我想我会分享调试该问题时发现的内容:

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)

看来，调用可执行文件破坏了状态栏的显示，并且excel恢复为显示禁用屏幕更新之前显示的任何状态栏消息.为了解决这个问题，我在运行可执行文件之前重新激活了屏幕更新，然后在运行可执行文件后禁用了屏幕更新:

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.

这篇关于Excel 2013中的StatusBar和ScreenUpdate的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！