Working With The Windows Clipboard
This page has been replaced. Click here for the new page.
This page describes various methods in Visual Basic For Applications (VBA) for copying data to and retrieving data from the Windows clipboard. In VBA, you are restricted to setting and retrieving only text data.
To copy data directly from a worksheet cell to the Windows clipboard, you can use the COPY method of the Range object, e.g., Range("A1").Copy. However, copying other data to the clipboard, such as variable, cell comments, sheet names, etc, is not as simple as it might be.
VBA does not give you generic PutOnClipboard or GetOffClipboard procedures, so we'll create them here. Along the way, we'll look at how VBA does interact with the Windows clipboard.
Because these procedures use the DataObject variable type, you must have a reference set in your VBA project to the Microsoft Forms 2.0 object library.
To access the Windows Clipboard from VBA, you must go through an intermediate object of the DataObject type. If your VBA procedure will be working with the clipboard, declare a NEW DataObject object with the following statement.
Dim MyDataObj As New DataObject
The SetText method of the DataObject variable is used to store a text string or numeric value in the variable For example:
MyDataObj.SetText "This Is A Text String"
This sets the contents of MyDataObj to a value. To copy the contents of the variable MyDataObj to the Windows clipboard, use the PutInClipboard method .
To retrieve the contents of the clipboard, use the following statement:
This sets the contents of MyDataObj to the contents of the Windows clipboard.
The counterpart to the SetText method is the GetText method. This method returns the contents of DataObject to another variable. For example,
Dim MyVar As Variant
Public Sub PutOnClipboard(Obj As Variant)
I use these formulas quite often to place the formula of the active cell on to the clipboard, to allow cut and paste operations without Excel changing any cell references. You may find it useful to link them to command items on your right click menu.
To completely clear the clipboard, you need to use a few API calls:
Declare Function CloseClipboard Lib "user32" () As Long