“Application.Calculation = xlCalculationManual”语句导致 VBA 复制粘贴过程中的运行时错误 1004

"Application.Calculation = xlCalculationManual" statement causing run-time error 1004 in VBA Copy-Paste procedure

提问by astidham2003

I have VBA code that copies the first row and pastes the values to multiple rows. The below code runs fine and pastes the rows as expected:

我有复制第一行并将值粘贴到多行的 VBA 代码。下面的代码运行良好并按预期粘贴行：

Sub Macro1()
  Dim i As Long

  Application.Calculation = xlCalculationManual
  Range("A1:M1").Select
  Selection.Copy

  For i = 1 To 50
    Range("A" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Next i
End Sub

However, if I move Application.Calculation = xlCalculationManualdown two lines as below, then the code throws a 1004 run-time error:

但是，如果我Application.Calculation = xlCalculationManual向下移动两行，则代码会抛出 1004 运行时错误：

Sub Macro1()
  Dim i As Long    

  Range("A1:M1").Select
  Selection.Copy
  Application.Calculation = xlCalculationManual
  For i = 1 To 50
    Range("A" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Next i
End Sub

I've searched for information on the VBA language reference site here: http://msdn.microsoft.com/en-us/library/office/jj692818(v=office.15).aspxand the Excel developer reference site here: http://msdn.microsoft.com/en-us/library/office/ff194068(v=office.15).aspx.

我在这里搜索了有关 VBA 语言参考站点的信息：http: //msdn.microsoft.com/en-us/library/office/jj692818(v=office.15).aspx和 Excel 开发人员参考站点：http://msdn.microsoft.com/en-us/library/office/ff194068(v=office.15).aspx。

Further, I've verified this error using both Excel 2010 running on Windows 7 and 2013 running on Windows 8.1.

此外，我已经使用在 Windows 7 上运行的 Excel 2010 和在 Windows 8.1 上运行的 2013 验证了此错误。

Can someone help me understand why the location of Application.Calculation = xlManualCalculationwould affect how the code runs?

有人可以帮助我理解为什么 的位置Application.Calculation = xlManualCalculation会影响代码的运行方式吗？

EDIT:

编辑：

I ran some additional tests to check if focus is lost or the clipboard is cleared. First to see if focus is lost I recorded a Macro that copied the first row with ctrl + x, then I changed the calculation mode of the workbook, then I hit ctrl + x again without re-selecting the cells. This is the resultant Macro:

我运行了一些额外的测试来检查焦点是否丢失或剪贴板是否被清除。首先查看焦点是否丢失，我记录了一个用 ctrl + x 复制第一行的宏，然后我更改了工作簿的计算模式，然后我再次按 ctrl + x 没有重新选择单元格。这是结果宏：

Sub MacroFocusTest()
    Range("A1:M1").Select
    Selection.Copy
    Application.CutCopyMode = False 'Macro recording entered this.
    Application.Calculation = xlManual
    Selection.Cut 'Range("A1:M1") is cut on the worksheet suggesting focus was not lost.
End Sub

Next, I entered a variable into my original Macro1 to capture the Application.CutCopyMode at various stages of execution. Follows is the resultant Macro:

接下来，我将一个变量输入到我原来的 Macro1 中，以在不同的执行阶段捕获 Application.CutCopyMode。以下是结果宏：

Sub Macro1()
  Dim i As Long
  Dim bCCMode as Boolean    

  bCCMode = Application.CutCopyMode ' False
  Range("A1:M1").Select
  Selection.Copy
  bCCMode = Application.CutCopyMode ' True
  Application.EnableEvents = False ' Included because I mention in comments no error is thrown using this
  bCCMode = Application.CutCopyMode ' True
  Application.Calculation = xlCalculationManual
  bCCMode = Application.CutCopyMode ' False
  For i = 1 To 50
    Range("A" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Next i
End Sub  

Based on the results of these two tests I believe that Application.Calculation = xlCalculationManual does not cause the range to lose focus, but does clear the clipboard.

基于这两个测试的结果，我相信 Application.Calculation = xlCalculationManual 不会导致范围失去焦点，但会清除剪贴板。

采纳答案by Alexander Bell

Pertinent to you particular question, the answer is: Application.Calculation = xlCalculationManualstatement erases the Clipboard Memory, which causes the subsequent Run-time error in your code snippet.

与您的特定问题相关，答案是：Application.Calculation = xlCalculationManual语句会擦除剪贴板内存，这会导致代码片段中出现后续的运行时错误。

Note: there is another suggested explanation as 'Excel copy loosing the focus'; it might be just a semantic difference, pointing to the same effect and just worded differently, but for better clarity I prefer this one, i.e. clipboard memory (or whatever you call that temp register) loosing value, or the reference.

注意：还有另一个建议的解释是“Excel 副本失去焦点”；这可能只是语义上的差异，指向相同的效果，只是措辞不同，但为了更清晰，我更喜欢这个，即剪贴板内存（或任何你称之为临时寄存器的东西）丢失值或引用。

The test settings to prove/illustrate the concept and detailed explanation follows:

验证/说明概念的测试设置和详细说明如下：

'Error occured because a statement
'Application.Calculation = xlCalculationManual
'or Application.Calculation = xlAutomatic
'or Application.Calculation = xlManual
'placed after `Selection.Copy` clears the clipboard memory;
'thus there is nothing to paste and Error 1004 occured
'as demonstrated in the added test code block
Sub YourMacroWithProblem()
    Dim i As Long

    Range("A1:M1").Select

    'Selected Range content is placed to Clipboard memory
    Selection.Copy

    'This statement erases Clipboard memory
    Application.Calculation = xlCalculationManual

    ' test if clipboard is empty ---------------------
    On Error Resume Next
    ActiveSheet.Paste
    If Err Then MsgBox "Clipboard is Empty": Err.Clear
    '-------------------------------------------------

  For i = 1 To 50
    Range("A" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Next i
End Sub

Also, there is an old discussion on similar topic: Stop VB from Clearing Clipboard(link: http://www.mrexcel.com/forum/excel-questions/459793-stop-vbulletin-clearing-clipboard-3.html).

此外，还有一个关于类似主题的旧讨论：Stop VB from Clearing Clipboard（链接：http: //www.mrexcel.com/forum/excel-questions/459793-stop-vbulletin-clearing-clipboard-3.html）。

You may consider the following solution to your problem optimized for speed and reliability:

您可以考虑使用以下针对速度和可靠性进行优化的问题的解决方案：

Sub Macro2()
    Dim i As Long

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    For i = 1 To 50
        Range("A1:M1").Copy Destination:=Range("A" & i)
    Next i

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Note: unlike your problematic code snippet, there is no need for the Selectstatement and Clipboard Copy/Paste operations in suggested solution, thus any potential side effects will be minimized, either.

注意：与您有问题的代码片段不同Select，建议解决方案中不需要语句和剪贴板复制/粘贴操作，因此任何潜在的副作用也将被最小化。

Hope this may help. Kind regards,

希望这可能会有所帮助。亲切的问候，

回答by Ron Rosenfeld

You are changing the focus between the copy and the paste. When you do that Excel loses the copied data, giving you the error when you try to Paste. The same thing would occur if you tried to do that from the worksheet, in that order.

您正在更改复制和粘贴之间的焦点。当您这样做时，Excel 会丢失复制的数据，并在您尝试粘贴时出现错误。如果您尝试从工作表中按照该顺序执行此操作，则会发生同样的事情。

Excel doesn't really use the system clipboard, like other programs. I believe this has to do with issues having to do with changing cell references in the copied data.

Excel 并不像其他程序那样真正使用系统剪贴板。我相信这与更改复制数据中的单元格引用有关的问题有关。

You could try using the Office Clipboard if you only want to paste the values, but there's no VBA support for that of which I am aware, in recent versions of Excel.

如果您只想粘贴值，您可以尝试使用 Office 剪贴板，但我知道在最新版本的 Excel 中没有 VBA 支持。

You might find this response of interest. It references a comment by an Excel developer Prevent Excel from clearing copied data for pasting, after certain operations, without Office clipboard

你可能会发现这个回应很有趣。它引用了 Excel 开发人员的评论，在没有 Office 剪贴板的情况下，某些操作后，防止 Excel 清除复制的数据进行粘贴