Why 'Application.Calculation=[value]' statement in Excel VBA erases Clipboard Memory?

Question

PROBLEM DEFINITION: In the following Excel VBA sample code snippet (see Listing 1), which is intended just to Copy-Paste the content of Cell "A1" to cell 'A2", the line:

Application.Calculation = xlCalculationManual

seemingly erases Clipboard Memory, causing a Run-time error. Moreover, any of the following similar statements will produce the same effect:

Application.Calculation = xlManual
Application.Calculation = xlAutomatic

Listing 1.

'demo Sub demonstrating Clipboard Memory flash by the statement
'Application.Calculation = xlCalculationManual
Sub CopyPaste()
    'select cell with some test value
    Range("A1").Select

    'selected value is copied to Clipboard memory
    Selection.Copy

    'This statement seemingly erases Clipboard memory
    'causing Run-Time Error 1004
    'if this line is commented-off, the procedure will run normally
    Application.Calculation = xlCalculationManual

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

    'target cell to copy value from Clipboard
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

If you comment-off the aforementioned statement, procedure will run flawless.

QUESTION: Is it true that Application.Calculation = xlCalculationManual statement flashes Clipboard Memory, and if so, why?

Any reference to a Microsoft Excel documentation would be appreciated.

Jean-François Corbett · Accepted Answer · 2014-12-20T20:05:11.693

Is it true? Apparently so. I had never noticed; but I just tested it and that's what it does. So yes, it's true.

Why? Well, who knows. We'd have to ask the Microsoft developers what was going through their heads, or whether this was at all intended. But I don't see how knowing the "why" would be of any practical use.

The important thing is a proper workaround: move Application.Calculation = xlCalculationManual to the top of your code, or at least before the Copy statement.

Even better, avoid using the clipboard entirely, and certainly avoid using the Select-Selection notation:

Range("A1").Copy Destination:=Range("A2")

or to copy the value only:

Range("A2").Value = Range("A1").Value

or, for full control:

Dim temp as variant
temp = Range("A1").Value
'manipulate temp here if you so wish
Range("A2").Value = temp

Why 'Application.Calculation=[value]' statement in Excel VBA erases Clipboard Memory?

1 Answers1