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

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

Solution 1:

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