ThreeWave Using The Clipboard In VBA

This page describes functions for working with text values and the Windows Clipboard.
ShortFadeBar

Accessing 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 In The Clipboard

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

Retrieving Text From The Clipboard

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

Storing Multiple Items In The Clipboard

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

Clipboard Module Functions

download 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.

ShortFadeBar
LastUpdate This page last updated: 16-December-2008.