vba 如何防止宏冻结/使 Excel 窗口变白？

How to prevent a macro from freezing / turning white the Excel window?

提问by Jake Schuurmans

So at work I am working on a macro/UserForm in Excel for someone. It works great (I think) and does exactly what it needs to do and takes under 1 minute to run, going through ~70k cells and organizing them. Now I was wondering if there was a way to slow it down so that Excel does not go into "Not Responding" mode while it runs. It would just be better so people that need to use the macro don't freak out when it freezes. And it would be best if there was a solution in VBA so people don't have to worry about it and it works perfectly the first time.

所以在工作中，我正在为某人处理 Excel 中的宏/用户窗体。它工作得很好（我认为）并且完全符合它需要做的事情，运行时间不到 1 分钟，通过大约 70k 个单元并组织它们。现在我想知道是否有办法减慢它的速度，以便 Excel 在运行时不会进入“无响应”模式。这样做会更好，这样需要使用宏的人就不会在它冻结时惊慌失措。最好是在 VBA 中有解决方案，这样人们就不必担心它并且第一次就可以完美运行。

About the Macro

关于宏

The data is a bunch of numbers that need to be put in one column, and that the 14 (normally 14) columns before it label each number with dates and other data. All size references and sheet names needs to be from a UserForm so I don't know the name of the sheets or size ahead of time this resulted in some weird code at the beginning of my loop.

数据是一堆需要放在一列中的数字，并且在它之前的 14（通常是 14）列用日期和其他数据标记每个数字。所有尺寸参考和工作表名称都需要来自用户窗体，所以我不知道工作表的名称或尺寸提前这导致在我的循环开始时出现一些奇怪的代码。

Also, if you see anyway to make my code more efficient that would be greatly appreciated!

另外，如果您看到无论如何使我的代码更高效，将不胜感激！

The Code

编码

Private Sub UserForm_Initialize()

    'This brings up the data for my dropdown menu to pick a sheet to pull data from
    For i = 1 To Sheets.Count
        combo.AddItem Sheets(i).name
    Next i

End Sub

Private Sub OK_Click()

    Unload AutoPivotusrfrm

    'Declaring All of my Variables that are pulled from Userform

    Dim place As Long

    Dim x1 As Integer
    x1 = value1.Value
    Dim x2 As Integer
    x2 = value2.Value
    Dim x3 As Integer
    x3 = value4.Value
    Dim y1 As Integer
    y1 = value3.Value

    Dim copyRange As Variant

    Dim oldname As String
    oldsheetname = combo.Text

    Dim newname As String
    newname = newsheetname.Text

    Sheets.Add.name = newsheetname

    'Labels for section one
    Worksheets(CStr(oldsheetname)).Activate
    copyRange = Range(Cells(x1, x1), Cells(x1 + 1, x3 - 1)).Value
    Worksheets(CStr(newsheetname)).Activate
    Range(Cells(x1, x1), Cells(x1 + 1, x3 - 1)).Value = copyRange
    place = x1 + 2
    x1 = place


    'Looping through the cells copying data
    For i = x1 To x2
    'This was the only way to copy multiple cells at once other ways it would just error
        Worksheets(CStr(oldsheetname)).Activate
        copyRange = Range(Cells(i + 3 - x1, x1 - 2), Cells(i + 3 - x1, x3 - 1)).Value
        Worksheets(CStr(newsheetname)).Activate
        For j = x3 To y1
            Range(Cells(place, 1), Cells(place, x3 - 1)).Value = copyRange
            Cells(place, x3) = Sheets(CStr(oldsheetname)).Cells(1, j)
            Cells(place, x3 + 1) = Sheets(CStr(oldsheetname)).Cells(2, j)
            Cells(place, x3 + 2) = Sheets(CStr(oldsheetname)).Cells(i + 2, j)
            place = place + 1
        Next j
    Next i

End Sub

Private Sub cancel_Click()

    Unload AutoPivotusrfrm

End Sub

回答by Degustaf

As @stuartd mentioned in the comments, DoEventswill probably allow the user to interact with Excel while the macro is running, and prevent Excel from becoming unresponsive.

正如@stuartd 在评论中提到的那样，DoEvents可能会允许用户在宏运行时与 Excel 交互，并防止 Excel 变得无响应。

An alternative approach is to speed upyour code, so that it finishes before the user has reason to believe that it has crashed. In this vein, here are some suggestions:

另一种方法是加速您的代码，以便它在用户有理由相信它已经崩溃之前完成。在这种情况下，这里有一些建议：

1. Turn off Screen Updating:It is a lot of work for Excel to render the screen. You can free those resources to work on what you need done by adding Application.ScreenUpdating = Falseto the beginning of your code, and Application.ScreenUpdating = Trueto the end.

2. Turn off Calculations:If you have a lot of formulas running, this can slow down what happens when you place a value into the workbook, as it needs to recalculoate. My preferred way of dealing with this is to store the current calculation setting, turn off calculations, and then restore the original setting at the end.

1. 关闭屏幕更新：Excel 渲染屏幕需要大量工作。通过添加Application.ScreenUpdating = False到代码的开头和Application.ScreenUpdating = True结尾，您可以释放这些资源来处理您需要完成的工作。

2. 关闭计算：如果您有很多公式正在运行，这会减慢您将值放入工作簿时发生的事情，因为它需要重新计算。我的首选处理方式是存储当前计算设置，关闭计算，然后在最后恢复原始设置。

Dim Calc_Setting as Long
Calc_Setting = Application.Calculation
Application.Calculation = xlCalculationManual
'Your code here
Application.Calculation = Calc_Setting

1. Use Worksheet Variables:You keep accessing your Worksheets by name. For repeated access, it should be faster to store that in a variable. Along with this, don't use Activateor Select. Fully reference you calls to Cells, so that it accesses the right worksheet.

3. 使用工作表变量：您可以继续按名称访问工作表。对于重复访问，将其存储在变量中应该更快。与此同时，不要使用Activate或Select。完全引用您对 的调用Cells，以便它访问正确的工作表。

Dim oldsheet as Worksheet, newsheet as Worksheet
Set oldsheet =  Worksheets(CStr(oldsheetname))
Set newsheet =  Worksheets(CStr(newsheetname))
oldsheet.Cells(place, x3) = ...

1. Use Variant Arrays:This should speed up your code the most, but also has a caveat. Excel and VBA are slow when they interact. Within your inner loop, VBA is accessing Excel 7 times. By pulling those interactions out of the loops, we can achieve a serious performance boost. The issue is that reading/writing arrays to Excel Ranges is still bound by the 2003 size limits (65536 rows, ...). If you expect to have more data than this, you will need to do some gymnastics to make it work.

4. 使用变体数组：这应该可以最大程度地加速您的代码，但也有一个警告。Excel 和 VBA 交互时速度很慢。在您的内部循环中，VBA 访问 Excel 7 次。通过将这些交互从循环中拉出来，我们可以实现显着的性能提升。问题是读取/写入 Excel 范围的数组仍受 2003 大小限制（65536 行，...）的约束。如果您希望获得比这更多的数据，则需要做一些练习才能使其发挥作用。

Dim inVal as Variant, Output as Variant

inVal = Range(oldsheet.Cells(1,x1-2),oldsheet.Cells(x2+3-x1,y)).Value
redim output(1 to (x2-x1) * (y-x3) + 2, 1 to x3+2)
'These numbers are not tested, you should test.

'Loops to fill output.  This will need to be 1 entry at a time.

newsheet.Cells(x1,x1).Resize(Ubound(output,1), Ubound(output,2)).Value