Excel VBA 正在跳过特定的代码行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23523919/
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 VBA is skipping specific lines of code
提问by T6J2E5
everyone. I have a large VBA module in an Excel workbook that takes data from a bunch of other, remote workbooks and copies it into the current workbook. Then, it formats and analyzes the data. All of that is working fine, except it can take a while, since I'm dealing with over 30,000 lines of data in two worksheets (and growing every day).
每个人。我在 Excel 工作簿中有一个大型 VBA 模块，它从一堆其他远程工作簿中获取数据并将其复制到当前工作簿中。然后，它格式化和分析数据。所有这些都运行良好，但可能需要一段时间，因为我在两个工作表中处理超过 30,000 行数据（并且每天都在增长）。
So, I'm trying to get it to put the progress as a percent in a certain cell on the "Admin" worksheet. This also seems to work OK, but only sometimes. During execution, Excel—seemingly at random—skips the .Valuelines of code that update the progress. Sometimes, it updates about three-quarters of the time, other times, it doesn't update at all until the end of execution. Does anyone know why? Below is a summary of the relevant code.
所以，我试图让它在“管理员”工作表上的某个单元格中以百分比形式显示进度。这似乎也可以正常工作，但只是有时。在执行过程中，Excel（似乎是随机的）会跳过.Value更新进度的代码行。有时，它更新大约四分之三的时间，其他时候，它直到执行结束才更新。有谁知道为什么？下面是相关代码的摘要。
<some code that declares variables and whatnot>
ThisWorkbook.Worksheets("Admin").Range("H18").Value = "Waiting..."
For Each rw In rng.Rows
<code that does the data-gathering and analysis>
progress = <code that returns a Double value>
ThisWorkbook.Worksheets("Admin").Range("H18").Value = progress & "% complete."
Next rw
ThisWorkbook.Worksheets("Admin").Range("H18").Value = "Done!"
<some cleanup code>
Sometimes, "Waiting..." is never displayed. Other times, it is. "Done!" is almost always displayed at the end of execution, but Admin!H18 is not updated for many, many of the loops, and it's never the same set of loops when it's skipped. The calculation is still being done for progress (I think), because when it does update, it's updating to the correct value. But it's not updating the cell during every loop, just some of them.
有时，从不显示“正在等待...”。其他时候，它是。“完毕！” 几乎总是在执行结束时显示，但 Admin!H18 不会在许多循环中更新，并且在跳过时它永远不会是同一组循环。计算仍在进行中（我认为），因为当它更新时，它会更新到正确的值。但它不会在每个循环中更新单元格，而是更新其中的一些。
回答by Tim Williams
Adding DoEventsafter updating the cell may fix this problem. However, your code may run much faster if you disable ScreenUpdating (which would prevent your status updates from updating on the sheet). Try updating the statusbar instead.
DoEvents在更新单元格后添加可能会解决此问题。但是，如果您禁用 ScreenUpdating（这会阻止您的状态更新在工作表上更新），您的代码可能会运行得更快。尝试更新状态栏。
Also - splitting out your Status updates into a separate Sub will simplify your code and allow you to more easily change how you display the updates.
此外 - 将您的状态更新拆分为单独的 Sub 将简化您的代码，并允许您更轻松地更改显示更新的方式。
Sub DoStatus(m)
ThisWorkbook.Worksheets("Admin").Range("H18").Value = m
DoEvents
End Sub
回答by Monty Wild
I believe that if you examined ThisWorkbook.Worksheets("Admin").Range("H18").Valueafter the line setting its value, its value would be exactly as expected.
我相信，如果您ThisWorkbook.Worksheets("Admin").Range("H18").Value在设置其值的行之后检查，其值将完全符合预期。
While VBA that runs with Application.ScreenUpdating=Truegenerallyshows on-screen whatever the code is doing, there is norequirement that Excel mustupdate the screen.
尽管运行的 VBAApplication.ScreenUpdating=True通常会在屏幕上显示代码正在执行的操作，但并不要求 Excel必须更新屏幕。
Conversely, with Application.ScreenUpdating=False, there isa requirement that Excel must notupdate the screen.
相反地，Application.ScreenUpdating=False有是一个要求，即Excel中不能更新屏幕。
If for whatever reason Excel cannot command the system resources necessary to update the screen anddo the other tasks it must perform while Application.ScreenUpdating=True, screen updating will be temporarily turned off, leading to the issue you describe. DoEventsmay well fix this, but can lead to reduced performance, and other problems if you have events elsewhere in the workbook that are being triggered - DoEventsis specifically saying "Handle pending events now".
如果出于某种原因 Excel 无法命令更新屏幕所需的系统资源并执行它必须执行的其他任务Application.ScreenUpdating=True，则屏幕更新将暂时关闭，从而导致您描述的问题。 DoEvents可能会很好地解决这个问题，但如果您在工作簿中的其他地方有正在触发的事件，则可能会导致性能降低和其他问题 -DoEvents特别是说“现在处理挂起的事件”。
You will get better performance if you use Application.ScreenUpdating=Falsewhile your code is running, and use Application.StatusBarto display status.
如果Application.ScreenUpdating=False在代码运行时使用，将获得更好的性能，并用于Application.StatusBar显示状态。
