如何防止宏冻结/使Excel窗口变白? [英] How to prevent a macro from freezing / turning white the Excel window?

问题描述

所以在工作中，我正在为某人在Excel中处理宏/用户窗体.它运行良好(我认为)，并且完全可以完成所需的工作，并且运行时间不到1分钟，经过约70k个单元并进行组织.现在，我想知道是否有一种方法可以降低它的运行速度，以使Excel在运行时不会进入无响应"模式.这样会更好，这样需要使用宏的人员在冻结时就不会惊慌失措.最好是在VBA中有一个解决方案，这样人们就不必担心它，并且它在第一次就可以完美运行.

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.

关于宏

数据是一堆数字，需要放在一列中，并且它之前的14列​​(通常为14列)用日期和其他数据标记每个数字.所有尺寸参考和图纸名称都必须来自UserForm，所以我不知道图纸的名称或尺寸是否提前，这会导致在循环开始时出现一些奇怪的代码.

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.

此外，如果您仍然可以提高我的代码效率，将不胜感激！

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

代码

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

推荐答案

正如注释中提到的@stuartd，DoEvents可能会允许用户在宏运行时与Excel进行交互，并防止Excel变得无响应.

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

另一种方法是加快代码，以便在用户有理由认为代码崩溃之前完成代码.因此，这里有一些建议:

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

1. 关闭屏幕更新: Excel渲染屏幕需要很多工作.您可以通过在代码的开头添加Application.ScreenUpdating = False并在代码的末尾添加Application.ScreenUpdating = True来释放这些资源来完成所需的工作.

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 = False to the beginning of your code, and Application.ScreenUpdating = True to the end.

关闭计算:如果您正在运行大量公式，这可能会减慢将值放入工作簿时发生的情况，因为它需要重新计算.解决此问题的首选方法是存储当前的计算设置，关闭计算，然后最后恢复原始设置.

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.

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

1. 使用工作表变量:您将继续按名称访问工作表.对于重复访问，应该更快地将其存储在变量中.与此同时，请勿使用Activate或Select.您呼叫Cells的完整参考，以便它访问正确的工作表.

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 Activate or Select. Fully reference you calls to Cells, so that it accesses the right worksheet.

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

1. 使用变量数组::这应该可以最大程度地提高代码速度，但还可以使用 caveat . Excel和VBA交互时速度很慢.在您的内部循环中，VBA正在访问Excel 7次.通过将这些交互拉出循环，我们可以大大提高性能.问题在于，对Excel Ranges的读/写数组仍受2003大小限制(65536行，...)的约束.如果您希望拥有更多的数据，则需要做一些体操运动才能使它起作用.

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.

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

这篇关于如何防止宏冻结/使Excel窗口变白?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！