How to Use Excel VBA Clear Clipboard [With Easy Steps]

Code Explanation

#If VBA7 Then
Private Declare PtrSafe Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
ByVal iChildStart As Long, ByVal cChildren As Long, _
ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
Public Const myVBA7 As Long = 1
#Else
Private Declare Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
ByVal iChildStart As Long, ByVal cChildren As Long, _
ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long
Public Const myVBA7 As Long = 0
#End If

This part is the conditional compilation section. It checks whether the VBA version is 7 (for 64-bit Excel) or not. It defines two functions AccessibleChildren, which interacts with the Windows Accessibility API (oleacc.dll), and a constant myVBA7, which holds a value depending on the VBA version (1 for 64-bit, 0 for 32-bit).

Public Sub EvRClearOfficeClipBoard()
Dim cmnB, IsVis As Boolean, j As Long, Arr As Variant
Arr = Array(4, 7, 2, 0) '4 and 2 for 32 bit, 7 and 0 for 64 bit
Set cmnB = Application.CommandBars("Office Clipboard")
With Application
.DisplayClipboardWindow = True
End With
IsVis = cmnB.Visible
If Not IsVis Then
cmnB.Visible = True
DoEvents
End If
For j = 1 To Arr(0 + myVBA7)
AccessibleChildren cmnB, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, cmnB, 1
Next
cmnB.accDoDefaultAction CLng(Arr(2 + myVBA7))
Application.CommandBars("Office Clipboard").Visible = IsVis
With Application
.DisplayClipboardWindow = True
End With
End Sub 

1. EvRClearOfficeClipBoard: This is the main subroutine responsible for clearing the Office Clipboard.
2. Variable Declarations:

• cmnB: This variable is declared as a Boolean type and represents the Office Clipboard command bar object (Application.CommandBars(“Office Clipboard”)).
• IsVis: This variable is declared a Boolean type and stores the Office Clipboard’s initial visibility state.
• j: This variable is declared a Long data type and will be used in a loop later in the code.
• Arr: This is a Variant array that holds values used to control the clipboard-clearing process. The values in the array depend on the VBA version and the Office Clipboard accessibility hierarchy.

3. Set cmnB = Application.CommandBars(“Office Clipboard”): This line assigns the Office Clipboard command bar object to the cmnB variable.
4. With Application…End With: This block temporarily changes the DisplayClipboardWindow property of the Excel application to True, ensuring the clipboard window is displayed.
5. IsVis = cmnB.Visible: This line stores the Office Clipboard’s initial visibility state in the IsVis variable.
6. If Not IsVis Then…: This code block checks if the Office Clipboard is not visible (IsVis is False), and if so, it makes it visible using cmnB.Visible = True and waits for events to process with DoEvents.

Code Explanation

Option Explicit
Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As LongPtr
Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
Public Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr

Option Explicit: This statement enforces explicit declaration of all variables in the code, which helps avoid typos and ensures that all variables are declared before use.

Public Declare PtrSafe Function…: These lines declare three Windows API functions, OpenClipboard, EmptyClipboard, and CloseClipboard, which interact with the clipboard. The PtrSafe keyword makes the declarations compatible with 64-bit versions of Excel. 

Public Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
   CloseClipboard
End Function

Public Function ClearClipboard(): This is a public function named ClearClipboard. Public functions can be called from other modules or procedures.

OpenClipboard (0&): This line calls the OpenClipboard Windows API function to open the clipboard for access. The parameter (0&) indicates that the clipboard is opened for all windows (i.e., no specific window handle is provided).

EmptyClipboard: This line calls the EmptyClipboard Windows API function to clear the clipboard’s contents.

CloseClipboard: This line calls the CloseClipboard Windows API function to close the clipboard after the contents have been cleared.

Sub ccc()
    Call ClearClipboard
End Sub

Sub ccc(): This is a Sub procedure named ccc. We use sub-procedures to execute a series of statements or actions.

Call ClearClipboard: This line calls the ClearClipboard function we defined earlier. It clears the clipboard by opening, emptying, and then closing it.