过夜运行后，Excel VBA宏不响应 [英] Excel VBA Macro Not Responding After Running Over Night

问题描述

我在excel中构建了一个随机数生成器，该生成器输出到工作簿中另一张纸的另一列.宏在单元格A1,000,000处具有截止值.当我全天工作时运行它时，我可以获得接近200,000行的输出.当我将其运行一整夜并在早晨返回时，它已冻结(无响应)，我认为这意味着它在达到截止值之前就已经自动冻结了.

I built a random number generator in excel which outputs to another column in another sheet in the workbook. The macro has a cut off at cell A1,000,000. When I run it throughout the day while I'm working I can get close to 200,000 rows of output. When I run it over night and come back in the morning, it's frozen (Not Responding) which I think means it just worked itself into freezing before hitting the cut off.

我看了一些其他帖子，但他们并没有完全回答我的问题 ( Excel在宏过程中无响应) ( Excel在运行宏后没有响应.

I took a look at some other posts but they didn't quite answer my questions (Excel Not Responding During Macro) (Excel not responding after running macro).

有什么办法可以使它摆脱无响应"状态，而只是禁用宏并查看输出?

Is there any way I can get it out of Not Responding and just disable the macro and take a look at the output?

为什么会这样?

这是我的代码:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'

Do
    Do

      Do
      Range("H12").Select
      Range("H12").ClearContents

      Loop Until Range("K10") = "MATCH" And Range("K11") = "GOOD"

      Range("H2:H8").Select
      Selection.Copy
      Range("P1").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,    SkipBlanks _
      :=False, Transpose:=False
      Range("P1:P5").Select
      Application.CutCopyMode = False
      ActiveWorkbook.Worksheets("NUMBER GENERATOR").sort.SortFields.Clear
      ActiveWorkbook.Worksheets("NUMBER GENERATOR").sort.SortFields.Add Key:=Range( _
      "P1:P5"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
      xlSortNormal
  With ActiveWorkbook.Worksheets("NUMBER GENERATOR").sort
      .SetRange Range("P1:P5")
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With


    Loop Until Range("P11") = "GOOD" And Range("P12") = 1


 Range("P9").Select
 Selection.Copy


 Sheets("Sheet1").Select
 Sheets("Sheet1").Range("A1").Select
 Range("A" & Rows.Count).End(xlUp).Offset(1).Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,    Transpose:=False


 Sheets("NUMBER GENERATOR").Select
 Range("H12").Select

Loop Until Sheets("Sheet1").Range("A1000000") <> ""

End Sub

推荐答案

Select在VBA中几乎不需要.例如，两行

Select is almost never needed in VBA. For example, the two lines

Range("H12").Select
Range("H12").ClearContents

可以简单地替换为:

Range("H12").ClearContents

更重要的是，这7行

Range("H2:H8").Select
Selection.Copy
Range("P1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,    SkipBlanks _
  :=False, Transpose:=False
Range("P1:P5").Select
Application.CutCopyMode = False

可以用单行代替

Range("P1:P7").Value = Range("H2:H8").Value

类似的说法适用于Select的其他用途.进行这些更改并在宏运行时关闭屏幕更新应该会很有帮助(很难说是否足够有用，因为目前尚不清楚您的实际工作.)

Similar remarks hold for other uses of Select. Making these changes and turning off screen-updating while the macro is running should help substantially (whether or not it will help enough is hard to say since it isn't clear what you are actually doing).

这篇关于过夜运行后，Excel VBA宏不响应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！