When we use a macro to copy and paste data in Excel, it is good practice to clear the clipboard after the operation. If we don’t, an unauthorized user may use the paste command to access the data we copied to the clipboard. This could lead to the misuse of data.
Additionally, when we gather too many items on the clipboard, Excel may generate an error that says that the clipboard is full. This means that we can no longer use the clipboard in our copy-and-paste operations. Clearing the clipboard prevents this occurrence.
Clearing clipboards also frees up system resources leading to optimal system performance.
There are two kinds of clipboards that we can use in Excel: the Excel clipboard and the Windows clipboard.
In this tutorial, we will learn 2 ways to use Excel VBA to clear each clipboard.
Use Excel VBA to clear the Excel clipboard
To clear the Excel clipboard all we need to do is add the following statement to our code:
|
1 |
Application.CutCopyMode = False |
This statement can be placed after the last paste operation in the code or after each paste operation to ensure that the clipboard is cleared between the copy and paste operations.
To demonstrate how effective this statement is, we will first create a subroutine minus the statement to copy and paste data and observe the results. We will then include the statement in the procedure and observe the results.
We will use the following data in our demonstration:
Procedure without the statement – Application.CutCopyMode = False
We use the following steps:
- From the active worksheet that contains our data, Press Alt + F11 to open the Visual Basic Editor (VBE).
- Insert a new module and type in the code below:
|
1 2 3 4 5 6 7 |
Sub copyPaste() Range("A1:A5").Select Selection.Copy Range("C1").Select ActiveSheet.Paste End Sub |
- Place the cursor anywhere in the subroutine and press F5 to run the code.
- Press Alt + F11 to switch back to the active worksheet to observe the results of the code.
We can see that the data has been copied to the destination range and the source data is surrounded by the marching ants border. This means that the data is still on the Excel clipboard and can be pasted again using Ctrl + V or the Paste command on the Excel Ribbon.
Procedure with the statement – Application.CutCopyMode = False
- From the active worksheet that contains our data, Press Alt + F11 to open the Visual Basic Editor (VBE).
- Insert a new module and type in the code below:
|
1 2 3 4 5 6 7 8 |
Sub copyPaste2() Range("A1:A5").Select Selection.Copy Range("C1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub |
- Place the cursor anywhere in the subroutine and press F5 to run the code.
- Press Alt + F11 to switch back to the active worksheet to observe the results of the code.
We can see that the data has been copied to the destination range and the source data is no longer surrounded by the marching ants border. This means that the data has been cleared from the Excel clipboard and therefore it cannot be pasted again using Ctrl + V or the Paste command on the Excel Ribbon.
Use Excel VBA to clear the Windows clipboard
We use the EmptyClipboard function to clear the Windows clipboard using Excel VBA.
We use the following steps to explain how this approach works.
- Press Alt + F11 to open the Visual Basic Editor.
- Insert a module and type in the following procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Public Declare Function CloseClipboard Lib "user32" () As Long Public Function ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Function Sub demoClipboardClear() Call ClearClipboard End Sub |
If you are using a 64-bit version of Excel use the code below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As LongPtr) As LongPtr Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long Declare PtrSafe Function CloseClipboard Lib "User32" () As Long Public Function ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Function Sub demoClipboardClear() Call ClearClipboard End Sub |
The ClearClipboard function procedure is public which means that it is accessible to all the other modules in the project. It utilizes three in-built Excel VBA functions: The OpenClipboard function that opens the Windows clipboard, the Emptyclipboard function that clears the Windows clipboard, and the CloseClipboard function that closes the Windows clipboard.
The demoClipboardClear subroutine calls the ClearClipboard function when it is run.
- Press Alt + F11 to switch back to the active worksheet.
- Copy some data from the Internet and paste it into the worksheet two or three times. This shows that so long as the data is on the Windows clipboard it can be pasted many times.
- Press Alt + F11 to switch back to the Visual Basic Editor. Place the cursor in the demoClipboardClear subroutine and press F5 to run it.
- Press Alt + F11 to switch back to the active worksheet. Select a new cell below the data we had pasted earlier and press Ctrl + V or click Home >> Clipboard >> Paste to try to paste the data again. We notice that nothing happens because the data we copied from the Internet has been cleared from the Windows clipboard.
Conclusion
In Excel VBA it is good practice to always clear the Excel or Windows clipboard after copying and pasting operations. This prevents unauthorized access to data and the clipboards from becoming full and inaccessible.
Clearing clipboards also frees up system resources leading to optimal system performance.
In this tutorial, we explored two ways that we can use Excel VBA to clear the Excel clipboard and the Windows clipboard.
One way is to use a subroutine that has the Application.CutCopyMode = False statement. This statement can be placed after the last paste operation in the code or after each paste operation to ensure that the clipboard is cleared between the copy and paste operations.
The second way is to use a function that utilizes three in-built Excel VBA functions: OpenClipboard function, EmptyClipboard function, and Closeclipboard function.