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屋!