How to Clear the Windows Clipboard Using VBA in Excel
Question details
The user needs a VBA macro to empty the Windows system clipboard after copying large ranges of data in Excel.
- Product
- Excel
- Device & OS
- Windows
- Scenario
- Freeing up system memory or preventing accidental pasting by clearing the clipboard memory automatically after executing a heavy copy-paste macro.
- Observed behavior
- The clipboard retains large amounts of copied data, requiring a programmatic method using Windows API declarations within a standard VBA module to successfully empty it.
Ensure that you are using a Windows-based system, as Windows API calls are not supported on Mac. Additionally, remember to save your workbook as a Macro-Enabled Workbook (.xlsm) so your VBA code is retained.
Use Windows API to Clear the Clipboard via VBA
Implement standard Windows API declarations in a standard VBA module to interact directly with the system clipboard and empty its contents.
To clear the system clipboard, VBA must call external functions from the 'user32' library. For modern 64-bit versions of Office, these declarations must include the 'PtrSafe' keyword to prevent compatibility errors.
It is crucial to place this code in a Standard Module rather than a worksheet module so that the macro is globally accessible and properly recognized by the Excel macro list.
Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) Editor window.
In the Project Explorer pane on the left side, right-click your workbook name, select 'Insert', and choose 'Module' from the context menu.
Copy and paste the following code into the new blank module window: Option Explicit Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As LongPtr Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr Private Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr Public Sub ClearClipboard() OpenClipboard 0& EmptyClipboard CloseClipboard End Sub
You can now run 'ClearClipboard' directly from the Macro dialog (by pressing Alt + F8) or call it at the end of your existing copy-paste macros to clear the clipboard memory.
Run VBA Macros Smoothly with WPS Office
WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to run complex automation tasks seamlessly, including Windows API calls. Experience high performance and robust compatibility with your existing Excel macros at no cost.
- 1. Download and Install WPS Office: Get WPS Office for free from the official website and install it on your Windows PC.
- 2. Open your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the clipboard clearing macro.
- 3. Enable Macros and Execute: Accept the security prompt to enable macros, press Alt + F8 to open the Macro list, and run the 'ClearClipboard' macro directly.
Frequently Asked Questions
Why isn't my ClearClipboard macro showing up in the macro drop-down list?
If the macro does not appear, it is likely placed in a Sheet module or the ThisWorkbook module instead of a Standard Module. Ensure you right-click your project, select 'Insert', choose 'Module', and paste your code there.
Does this VBA clipboard code work on Mac?
No, this specific code calls the 'user32' library which is exclusive to the Windows API. It will not work on macOS. Mac users need to rely on AppleScript integrations invoked via VBA to clear the system clipboard.
Why is the data still in the clipboard after running the macro?
If data remains, ensure that no other clipboard manager (such as the Windows 10/11 Clipboard History feature triggered by Win+V) is actively holding the copied items. The macro clears the standard system clipboard, but specialized history managers might retain a copy.
What does the 'PtrSafe' keyword do in the macro code?
The 'PtrSafe' keyword is required for declaring API functions in 64-bit versions of Office. It explicitly tells the VBA compiler that the Declare statement is safe to execute in a 64-bit environment, preventing compilation errors.
