为什么用户窗体在 VBA Excel 中运行时“无响应”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13606651/
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
Why UserForm is "Not Responding" During Run Time in VBA Excel?
提问by Timur Aykut YILDIRIM
I'm very new with VBA Excel and i only know the things as far as i need for this report formatting task.
我对 VBA Excel 很陌生，我只知道我需要这个报告格式化任务的东西。
I'm almost done with my task but when i run the program and start the progress, eventhough it works successfully, GUI is not responding for a minute. I share my code here, is something wrong with it? Can you suggest me any best-practice? I don't want it to freeze because it will look bad to my manager.
我几乎完成了我的任务，但是当我运行程序并开始进度时，即使它成功运行，GUI 一分钟也没有响应。我在这里分享我的代码，有什么问题吗？你能建议我任何最佳实践吗？我不希望它冻结，因为它对我的经理来说看起来很糟糕。
Just to make it clear, by "not responding" i mean it freezes on the screen and says "Not Responding" on it's windows frame and when i click on it, it gives a message like this:
为了清楚起见，“无响应”是指它在屏幕上冻结并在其窗口框架上显示“无响应”，当我单击它时，它会给出如下消息：
*ps: the sheet that i get records has 20997 rows and 7 columns and i make some records to another sheet on same file sized and 20997 lines 23 columns. And my GUI is very simple, it has nothing but a CommandButton that starts the progress.
*ps：我得到记录的工作表有 20997 行和 7 列，我在相同文件大小和 20997 行 23 列的另一个工作表上做了一些记录。而且我的 GUI 非常简单，它只有一个启动进度的命令按钮。
How can i fix this?
我怎样才能解决这个问题？
采纳答案by Timur Aykut YILDIRIM
Alright, i believe i found the best solution for this. (a) :)
好吧，我相信我找到了最好的解决方案。（一种） ：）
Instead of using for loop in TheLoop subroutine, i removed the loop and changed it as in below. That makes it incredibly faster when i compare it with my first code eventhough i didn't disable event properties, and now it's not freezing.
我没有在 TheLoop 子例程中使用 for 循环，而是删除了循环并将其更改为如下所示。尽管我没有禁用事件属性，但当我将它与我的第一个代码进行比较时，这使它变得非常快，现在它没有冻结。
Sub TheLoop()
Cells(2, 1).Resize(20996) = "Defect"
Cells(2, 3).Resize(20996) = "New Defect"
Cells(2, 4).Resize(20996) = "3"
Cells(2, 5).Resize(20996) = "2"
Cells(2, 7).Resize(20996) = "Name Surname"
Cells(2, 8).Resize(20996) = "Name Surname"
Cells(2, 9).Resize(20996) = "FALSE"
Cells(2, 10).Resize(20996) = " http://SERVER_NAME:8888/PROJECT_NAME/ "
Cells(2, 12).Resize(20996) = "Software Quality"
Cells(2, 13).Resize(20996) = "Unsigned"
Cells(2, 14).Resize(20996) = "Software Quality"
Cells(2, 15).Resize(20996) = "1"
Cells(2, 16).Resize(20996) = "Name Surname"
Cells(2, 18).Resize(20996) = "Software Quality"
Cells(2, 20).Resize(20996) = "Development"
Cells(2, 22).Resize(20996) = " TYPE YOUR MODULE'S NAME TO HERE"
End Sub
回答by Mroz
You can prevent the freezing of excel window by putting
您可以通过放置来防止excel窗口冻结
DoEvents
inside your loop.
在你的循环内。
回答by CaBieberach
This happens because your procedure is very busy working. For example your Sub TheLoop()is accessing 20995 x 16 times a cell to write on them a string. The interaction VBA with Excel is slow.
发生这种情况是因为您的程序工作非常繁忙。例如，您Sub TheLoop()正在访问一个单元格的 20995 x 16 次以在其上写入一个字符串。VBA 与 Excel 的交互很慢。
There is a couple of things you can do to make the procedure faster.
您可以做一些事情来加快程序。
1.Disable events handlers, screen updating and calculations before you run your procedure. At the end of the procedure restore the settings again.
1.在运行程序之前禁用事件处理程序、屏幕更新和计算。在该过程结束时再次恢复设置。
'Disable'
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'...... Code'
'Enable'
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
2.You can optimize the Sub TheLoop. Instead of writing immediately on the cells, write the values inside an array. After the array is full with the values, assign the values of the array to the range that you need.
for example:
2.您可以优化Sub TheLoop. 不是立即在单元格上写入，而是将值写入数组中。在数组填满值后，将数组的值分配到您需要的范围。例如：
Dim ResultValues() As String
Dim j As Long
ReDim ResultValues(2 To 20997, 1 To 3)
For j = 2 To 20997
ResultValues(j, 1) = "New Defect"
ResultValues(j, 2) = "3"
ResultValues(j, 3) = "2"
Next j
With ThisWorkbook.Worksheets("myWorksheet")
.Range(.Cells(2, 3), .Cells(20997, 5)) = ResultValues
End With
EDIT:
编辑：
Given that the columns between the ones that you modify are only text or empty cells, you can:
鉴于您修改的列之间的列只是文本或空单元格，您可以：
- read the whole range into an array.
- Then modify the array in the same way you are currently modifying cells.
- After the modifications are done, dump the whole matrix in the range again.'
- 将整个范围读入数组。
- 然后以与当前修改单元格相同的方式修改数组。
- 修改完成后，再次将整个矩阵转储到范围内。
For example:
例如：
Sub TheLoop()
Dim arrRangeValues() as Variant
Dim j as Long
arrRangeValues= Range("A2:V20997").Value2
For j = 2 To 20997
arrRangeValues(j, 1) = "Defect" 'Cells(row_index , column_index)'
arrRangeValues(j, 3) = "New Defect"
arrRangeValues(j, 4) = "3" ' this one also might be empty'
arrRangeValues(j, 5) = "2" ' this one also might be empty'
arrRangeValues(j, 7) = "Name Surname"
arrRangeValues(j, 8) = arrRangeValues(j, 7)
arrRangeValues(j, 16) = arrRangeValues(j, 7)
...
arrRangeValues(j, 10) = " http://SERVER_NAME:8888/PROJECT_NAME/ "
Next j
Range("A2:V20997").Value2 = arrRangeValues
End Sub
