vba Excel 在执行期间冻结
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15436596/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel freezes during execution
提问by user2156096
When I execute my code (which takes 3-4min. to execute) in Excel, and I click on something in excel or in my userform, it freezes, gets white, almost crashes... When the execution is ready everything is fine again.
当我在 Excel 中执行我的代码（需要 3-4 分钟来执行），并且我在 excel 或我的用户表单中单击某些内容时，它会冻结、变白、几乎崩溃......当执行准备就绪时，一切又好了.
Of course it is normal that I can't work in excel during execution, but how can I avoid the "crashing" of excel?
当然，我在执行过程中不能在excel中工作是正常的，但是如何避免excel的“崩溃”呢？
回答by Charlie Hill
Do you have a Loop in the code?
你的代码中有循环吗？
If so, add the following line just after you initialize the loop and it should help with your problem!
如果是这样，请在初始化循环后添加以下行，它应该有助于解决您的问题！
DoEvents
回答by Jon Crowell
The best way to prevent these kinds of issues is to benchmark your code to determine what is taking so long. If your code is taking over 3 minutes to run, you almost certainly have room for optimization.
防止此类问题的最佳方法是对您的代码进行基准测试，以确定花费这么长时间的原因。如果您的代码运行时间超过 3 分钟，您几乎肯定有优化空间。
Here are the steps you need to take:
以下是您需要采取的步骤：
- Set multiple breakpoints within the code that is executing.
- Time how long it takes to get from one breakpoint to the next.
- Once you've identified the culprit(s), refactor.
- If you aren't able to improve performance, post the inefficient code and ask for help.
- 在正在执行的代码中设置多个断点。
- 计算从一个断点到下一个断点所需的时间。
- 一旦你确定了罪魁祸首，重构。
- 如果您无法提高性能，请发布低效代码并寻求帮助。
A notorious slow-performing anti-pattern to look out for is selecting things during a loop.
要注意的一个臭名昭著的慢速反模式是在循环中选择事物。
回答by StoriKnow
What you need to do is create a custom UserForm that will overlay the Excel UI, like a Progress Bar. You'll also want to look into ways to force your form to stay ontop, much like a modal form. You can't use modal form's because they cease execution.
您需要做的是创建一个自定义用户窗体，它将覆盖 Excel UI，如Progress Bar。您还需要研究强制表单保持在顶部的方法，就像modal form. 您不能使用modal form's 因为它们会停止执行。
回答by pn7a
After you make sure there are no infinite loops making excel unresponsive try optimizing your code.
在确保没有无限循环使 excel 无响应后，请尝试优化您的代码。
I found thisa very interesting read on vba-excel code optimization and some of the practices actually had an impact on a big and time consuming module I wrote. Hope it helps.
我发现这是一篇关于 vba-excel 代码优化的非常有趣的读物，其中一些实践实际上对我编写的一个大而耗时的模块产生了影响。希望能帮助到你。
