Using The Clipboard In VBA
This page describes functions for working with text values and the Windows Clipboard.
The MSForms library contains an object called the DataObject that
provides support for working with text strings on the Windows clipboard. VBA does not support the
data type required for other, non-text, values on the clipboard. To use the DataObject
in your code, you must set a reference to the Microsoft Forms 2.0 Object Library.
ADDING A REFERENCE IN VBA. To add a reference to your VBA project, go to the Tools menu
in the VBA editor and choose the References item. In the dialog that appears, scroll down
the list until you find the appropriate library. Commonly used references are listed at the top
of the list, and after those, the references are listed in alphabetical order. When you find
the reference required by the code, check the checkbox next to the reference title and then click OK.
Putting text data in the clipboard takes two steps. The first step is to put the text in a
DataObject variable and then instru ct the DataObject
to put that text in the clipboard. For example, the code below puts the string Hello World in the
Windows clipboard.
Dim DataObj As New MSForms.DataObject
Dim S As String
S = "Hello World"
DataObj.SetText S
DataObj.PutInClipboard
Once text has been placed in the clipboard using the PutInClipboard method of the
DataObject, you can paste that text with a standard Paste operation in an application.
You can also retrieve the text into a String type variable. Getting text out of the
DataObject takes to steps. The first step is to instruct the DataObject
to get the text from the clipboard. The second step is to get the text out of the DataObject into
the String variable. For example,
Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
Debug.Print S
The Windows clipboard stores at most 1 data element for each format. That is, the clipboard can store simultaneously
a text string, an image, some HTML, and so on. If you store data of a type that already exists in the clipboard,
the existing data for that type is replaced by the new data. While the clipboard can store only one text string
in the default text format, you can specify your own format identifiers to store more than one string.
The SetText and GetText methods of the DataObject
allow you to specify a format identifier. This is a Integer, Long, or
String value used to identify a new or existing clipboard format. By using different format
identifiers, you can store and retrieve multiple text values. For example, the code below places two text strings
in the clipboard each with its own format identifier, and the retrieves that values.
Dim DataObj As New MSForms.DataObject
Dim S1 As String
Dim S2 As String
S1 = "text string one"
S2 = "text string two"
With DataObj
.SetText S1, "FormatId1"
.PutInClipboard
.SetText S2, "FormatId2"
.PutInClipboard
S1 = vbNullString
S2 = vbNullString
.GetFromClipboard
S1 = .GetText("FormatId1")
S2 = .GetText("FormatId2")
End With
Debug.Print S1
Debug.Print S2
You can test whether there is a item with a specific format identifier on the clipboard by
using the GetFormat method of the DataObject.
This method returns True if the format exists on the clipboard or
False if no such format exists. For example,
Dim B As Boolean
B = DataObj.GetFormat("FormatID")
If B = True Then
Debug.Print DataObj.GetText("FormatID")
Else
Debug.Print "Format does not exist on clipboard."
End If
 |
You can download a module file that contains functions for putting data in
the clipboard and retrieving data out of the clipboard. The procedures in this module support format identifiers
for place multiple text string on the clipboard. |
EmptyClipboard
Within Excel, you can clear Excel data from the clipboard with:
Application.CutCopyMode = False. Or to completely clear the clipboard, use code like
the following.
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
Sub ClearClipboard()
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
End Sub
The Public Declare lines of code need to be in the declarations
section of the module, before and outside of any procedure. If you are going to use this code within an
object module (ThisWorkbook, a Sheet module, a UserForm module, or a Class module), change
Public to Private.
PutInClipBoard
Public Function PutInClipboard(S As String, _
Optional FormatID As Variant) As Boolean
This function puts the string S in the clipboard. If FormatID is specified,
the text is put in the clipboard with that format identifier.
GetFromClipboard
Public Function GetFromClipboard(Optional FormatID As Variant) As String
This function get text from the clipboard, using the FormatID if provided.
RangeToClipboardString
Public Function RangeToClipboardString(RR As Range) As String
This function takes an Excel Range and creates and returns a string that can be put in the clipboard. The
string can then be put in the clipboard and pasted into a range of worksheet cells. The format of the
text string is shown below:
Value1 vbTab Value2 vbTab Value3 vbNewLine
Value4 vbTab Value5 vbTab Value6 vbNewLine
Value7 vbTab Value8 vbTab Value9
ArrayToClipboardString
Public Function ArrayToClipboardString(Arr As Variant) As String
This function takes an array (either 1 or 2 dimensions) and creates a string
formated so that it can be pasted into a range of cells. The string is formated
as follows:
Arr(1,1) vbTab Arr(1,2) vbTab Arr(1,3) vbNewLine
Arr(2,1) vbTab Arr(2,2) vbTab Arr(2,3) vbNewLine
Arr(3,1) vbTab Arr(3,2) vbTab Arr(3,3)
The module also contains two support functions, ArrNumDimensions, which returns the
number of dimensions in an array, and IsArrayAllocated, which returns
True or False indicating whether a variable references an
allocated array.
 |
This page last updated: 16-December-2008. |