Should I turn .CutCopyMode back on before exiting my sub procedure?

Question

In order to optimize my code, I turn off several Application Object member properties; one in particular being the .CutCopyMode property.

Sub MyProcedure()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .CutCopyMode = False
    End With

    ' lots of code here

Should I turn .CutCopyMode back on (e.g. True) again with the others before my sub finishes?

    ' lots of code here

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .CutCopyMode = True     '<~~ ???
    End With
End Sub

@pnuts - Beyond [excel-2010] being the *de facto standard* by which I gauge all of my contributions, I wanted to avoid direct association with pre-xl2007 Office clipboard behaviour which I did not test directly. — , Nov 20 '15 at 20:21
@Jeeped I've seen quite a lot of `Application.Calculation = xlCalculationManual`-`Application.Calculation = xlCalculationAutomatic` wraps, is this usefull? And in the case that I add formulas, do I need to set off an event to trigger these? — MGP, Nov 21 '15 at 15:46
@MarcoGetrost - Turning calculation off can certainly help by avoiding reiterative calculation cycles that are subsequently (and redundantly) recalculated. It does depend upon the formula of course. Even a non-volatile formula would suffer if it was usin full column references and values in those references were changed in a loop; e.g. change value in C4 and somewhere else `=SUM(C:C)` recalculates. Next iteration of the loop and C5 gets a new value and another calculation cycle is triggered, etc. — , Nov 22 '15 at 01:03

score 19 · Accepted Answer · answered Nov 20 '15 at 18:08

The short answer is that Application.CutCopyMode = True either does nothing at all or it does the opposite of what you think it does. If the current state of the .CutCopyMode is False then setting it to True does not turn on the 'dancing border' and if the current state is either xlCopy or xlCut (XlCutCopyMode Enumeration constants), setting .CutCopyMode to True effectively turns it off. Additionally, you cannot set the .CutCopyMode to xlCopy or xlCut.

The long story starts with understanding just what purpose the Application.CutCopyMode property performs and how the official documentation wrong implies that you can turn it back 'on'.

When you manually cut (Ctrl+X) or copy (Ctrl+C) one or more cells on the worksheet, the cells will acquire a 'dancing border'¹ that indicates the source of the operation. At this point, .CutCopyMode is non-zero (either xlCopy or xlCut) and a relationship between subsequent paste operations and both the Office clipboard and Windows clipboard exists in that they are retaining the content of the source.

If you elected to cut (aka move) the cells, immediately after pasting (Ctrl+V) the cells to a new location .CutCopyMode becomes false and you lose the 'dancing border' around the source. This is because there is no content left in the source cells. The content remains accessible from the Office clipboard but is removed from the Windows clipboard.

If you elected to copy the cells, you can paste the cells to an additional location and the 'dancing border' remains. The .CutCopyMode property remains non-zero (e.g. xlCopy). You can move to another location and paste the same content; .CutCopyMode remains non-zero and the 'dancing border' around the original content persists as does the relationship to both the Office clipboard and the Windows clipboard.

If you ran VBA code that included Application.CutCopyMode = False at this point, the dancing border would disappear and the connection between any paste operation and the Office clipboard would be eliminated. This is a good state to be in when initiating a VBA sub procedure so that any potential copy/paste operation within the code could not potentially conflict with the .CutCopyMode state. However, this should only be necessary in special circumstances (see next paragraph).

Certain Excel operations are sufficient to break this Office clipboard connection and force .CutCopyMode to False. One of these is manually initiating a macro sub procedure so there is limited benefit to including Application.CutCopyMode = False at the beginning of your code. However, it may be prudent to run within your code if your code has initiated a Range.Copy operation and you have completed any Worksheet.Paste method or Range.PasteSpecial method operations with the contents of the copy.

Examining and reporting the current state of the .CutCopyMode can be done with some worksheet event macro code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Select Case Application.CutCopyMode
        Case True
            Debug.Print "CutCopyMode is ON"
        Case xlCopy
            Debug.Print "CutCopyMode is in Copy mode"
        Case xlCut
            Debug.Print "CutCopyMode is in Cut mode"
        Case False
            Debug.Print "CutCopyMode is OFF"
        Case Else
            Debug.Print "???"
    End Select
End Sub

The results reported to the VBE's Immediate window will be either Copy mode, Cut mode or OFF. Application.CutCopyMode will never directly report its state as True².

While you can effect a change in the operational environment with Application.CutCopyMode = False, I have never been able to turn .CutCopyMode On by setting the property to True. No error is thrown and the official documentation specifically states that setting the property to True "Starts Cut or Copy mode and shows the moving border." but I have found that the only way to get back the 'marching ants' is to initiate another copy operation.

So for all intents and purposes, coding Application.CutCopyMode = True does no harm. However, coding Application.CutCopyMode = False can perform some good by relinquishing clipboard storage.

If anyone can turn the marching ants back on by manipulating the Application.CutCopyMode property, I would dearly love to see an example.

¹ _{The 'dancing border' is also known colloquially as 'marching ants'.}
² _{While a boolean value of True or False is a distinct type, for all intents and purposes False equals zero and anything that is not False is True. If resolving boolean ► number, a VBA False is 0 and True is always equal to (-1) but if resolving the reverse number ► boolean any non-zero number is considered True and a zero is considered False.}

I had no idea I was waiting so long for this question/explanation. Awesome write up! *double thumbs up* — BruceWayne, Nov 20 '15 at 18:14
I think it's just a documentation error. While any of the possible states {-1,0,1,2} can be read, only {0} can be written to the property. Any numerical value is coerced to zero when writing to the property. This seems reasonable, since any time the ants are off, the OCB has been scrubbed. Animating the critters again would leave the user interface in an ambiguous state suggesting that a paste is possible, but isn't, as the OCB is blank. This design makes sense, the documentation does not. — Excel Hero, Nov 20 '15 at 18:38
You have too much time on your hands =P I am curious. How much optimization does this yield? How many copy/pastes does it take to reflect a noticeable difference? — findwindow, Nov 20 '15 at 19:07
@findwindow - tbh, this Q&A was in response to seeing `Application.CutCopyMode = True` in a few code segments published into questions recently. I rarely even use a [Range.Copy method](https://msdn.microsoft.com/en-us/library/office/ff837760.aspx) in VBA, preferring direct value transfer instead. — , Nov 20 '15 at 19:14
lol I did wonder why you were copy/pasting but then you lied. You wrote `In order to optimize my code`... flagging this =P — findwindow, Nov 20 '15 at 19:16
why interop.Excel in c# has values (1 and 2)? Where in VBA has value true and false? Are they referring to the same thing? — liang, Jul 11 '19 at 08:20

Should I turn .CutCopyMode back on before exiting my sub procedure?

1 Answers1

Linked