“Application.Calculation = xlCalculationManual”语句导致VBA复制粘贴过程中的运行时错误1004 [英] "Application.Calculation = xlCalculationManual" statement causing run-time error 1004 in VBA Copy-Paste procedure

问题描述

我有VBA代码复制第一行并将值粘贴到多个行。以下代码运行正常，并按预期粘贴行：

  Sub Macro1（）
 Dim i As Long 
 
 Application.Calculation = xlCalculationManual 
范围（A1：M1）。选择
 Selection.Copy 
 
对于i = 1至50 
 Range（A& i）。选择
 Selection.PasteSpecial粘贴：= xlPasteValues，操作：= xlNone，SkipBlanks _ 
：= False，Transpose：= False 
 Next i 
 End Sub 
  

但是，如果我移动 Application.Calculation = xlCalculationManual 下面两行，那么代码会抛出一个1004运行时错误：

  Sub Macro1 ）
 Dim i As Long 
 
 Range（A1：M1）。选择
 Selection.Copy 
 Application.Calculation = xlCalculationManual 
 For i = 1到50 
范围（A& i）。选择
 Selection.PasteSpecial粘贴：= xlPasteValues，操作：= xlNone，SkipBlanks _ 
：= False，Transpose：= False 
下一个i 
结束Sub 
  

我在这里搜索了有关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 。 p>

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

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

编辑：

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

  Sub MacroFocusTest（）
 Range（A1：M1）。选择
 Selection.Copy 
 Application.CutCopyMode = False输入宏记录。 
 Application.Calculation = xlManual 
 Selection.Cut'Range（A1：M1）被剪切在工作表上，表明焦点没有丢失。 
 End Sub 
  

接下来，我在原始的Macro1中输入了一个变量来捕获应用程序。 CutCopyMode在执行的各个阶段。以下是最终的宏：

  Sub Macro1（）
 Dim i As Long 
 Dim bCCMode as Boolean 
 
 bCCMode = Application.CutCopyMode'False 
范围（A1：M1）。选择
 Selection.Copy 
 bCCMode = Application.CutCopyMode'True 
 Application.EnableEvents = False'包含，因为我在评论中提到没有错误被抛出使用这个
 bCCMode = Application.CutCopyMode'True 
 Application.Calculation = xlCalculationManual 
 bCCMode = Application.CutCopyMode'False 
对于i = 1至50 
范围（A& i）。选择
 Selection.PasteSpecial粘贴：= xlPasteValues，操作：= xlNone，SkipBlanks _ 
：= False，Transpose：= False 
 Next i 
 End Sub 
  

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

解决方案

与您相关的具体问题，答案是： Application.Calculation = xlCalculationManual 语句擦除剪贴板内存，导致随后的运行时你的代码片段中出错。

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

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

 '由于语句
'Application.Calculation = xlCalculationManual 
'或Application.Calculation = xlAutomatic 
'或Application.Calculation = xlManual 
' .Copy`清除剪贴板内存; 
'因此没有任何粘贴和错误1004发生
'，如添加的测试代码块
 Sub YourMacroWithProblem（）
 Dim i As Long 
 
范围（A1：M1）。选择
 
'选择范围内容放置到剪贴板内存
 Selection.Copy 
 
'此语句擦除剪贴板内存
 Application.Calculation = xlCalculationManual 
 
'测试如果剪贴板为空--------------------- 
打开错误Resume Next 
 ActiveSheet.Paste 
如果Err Then MsgBoxClipboard is Empty：Err.Clear 
'------------------- ------------------------------ 
 
对于i = 1至50 
范围（ A& i）。选择
 Selection.PasteSpecial粘贴：= xlPasteValues，操作：= xlNone，SkipBlanks _ 
：= False，Transpose：= False 
 Next i 
 End Sub 
  

此外，还有一个类似主题的旧讨论：从清除剪贴板中停止VB （链接： http：// www。 mrexcel.com/forum/excel-questions/459793-stop-vbulletin-clearing-clipboard-3.html ）。

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

  Sub Macro2（）
 Dim i As Long 
 
 Application.Calculation = xlCalculationManual 
 Application.ScreenUpdating = False 
 
对于i = 1至50 
范围（A1：M1）。复制目标：=范围A& i）
 Next i 
 
 Application.Calculation = xlCalculationAutomatic 
 Application.ScreenUpdating = True 
 
 End Sub 
  

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

希望这可能有所帮助。亲爱的，

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:

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 = xlCalculationManual down two lines as below, then the code throws a 1004 run-time error:

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).aspx and the Excel developer reference site here: 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.

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

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:

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:

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.

解决方案

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

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.

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).

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 Select statement and Clipboard Copy/Paste operations in suggested solution, thus any potential side effects will be minimized, either.

Hope this may help. Kind regards,

这篇关于“Application.Calculation = xlCalculationManual”语句导致VBA复制粘贴过程中的运行时错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！