clipboard in vba

8/10/2019 Clipboard in VBA

1/3

http://www.cpearson.com/excel/Clipboard.aspx

Using The Clipboard In VBA

This page describes functions for woring with text !alues and the "indows Clipboard.

Accessing The "indows Clipboard

The #$%orms librar& contains an ob'ect called the (ata)b'ect that pro!ides support for woring

with text strings on the "indows clipboard. VBA does not support the data t&pe re*uired for other+

non,text+ !alues on the clipboard. To use the (ata)b'ect in &our code+ &ou must set a reference to

theMicrosoft Forms 2.0 Object Library.

A((I- A 0%00-C0 I- VBA. To add a reference to &our VBA pro'ect+ go to the Toolsmenu

in the VBA editor and choose theReferencesitem. In the dialog that appears+ scroll down the list

until &ou find the appropriate librar&. Commonl& used references are listed at the top of the list+ and

after those+ the references are listed in alphabetical order. "hen &ou find the reference re*uired b&

the code+ chec the checbox next to the reference title and then clic )1.

2utting Text In The Clipboard

2utting text data in the clipboard taes two steps. The first step is to put the text in a (ata)b'ect

!ariable and then instru ct the (ata)b'ect to put that text in the clipboard. %or example+ the code

below puts the stringHello Worldin the "indows clipboard.

Dim DataObj As New MSForms.DataObject Dim S As String S = "Hello World" DataObj.SetText S

DataObj.!tn#li$board

etrie!ing Text %rom The Clipboard

)nce text has been placed in the clipboard using the 2utInClipboard method of the (ata)b'ect+ &ou

can paste that text with a standard 2aste operation in an application. 3ou can also retrie!e the text

into a $tring t&pe !ariable. etting text out of the (ata)b'ect taes to steps. The first step is to

instruct the (ata)b'ect to get the text from the clipboard. The second step is to get the text out of

the (ata)b'ect into the $tring !ariable. %or example+

Dim DataObj As New MSForms.DataObject

Dim S As String DataObj.%etFrom#li$board S = DataObj.%etText Deb!g.rint S

$toring #ultiple Items In The Clipboard

The "indows clipboard stores at most 4 data element for each format. That is+ the clipboard can

store simultaneousl& a text string+ an image+ some 5T#6+ and so on. If &ou store data of a t&pe that

alread& exists in the clipboard+ the existing data for that t&pe is replaced b& the new data. "hile the

clipboard can store onl& one text string in the default text format+ &ou can specif& &our own format

identifiers to store more than one string.

The $etText and etText methods of the (ata)b'ect allow &ou to specif& a format identifier. This is


2/3

a Integer+ 6ong+ or $tring !alue used to identif& a new or existing clipboard format. B& using

different format identifiers+ &ou can store and retrie!e multiple text !alues. %or example+ the code

below places two text strings in the clipboard each with its own format identifier+ and the retrie!es

that !alues.

Dim DataObj As New MSForms.DataObject

Dim S& As String Dim S' As String S& = "text string one" S' = "text string two" Wit( DataObj .SetText S&) "Formatd&" .!tn#li$board .SetText S') "Formatd'" .!tn#li$board S& = *bN!llString S' = *bN!llString .%etFrom#li$board S& = .%etText+"Formatd&",

S' = .%etText+"Formatd'", -nd Wit( Deb!g.rint S& Deb!g.rint S'

3ou can test whether there is a item with a specific format identifier on the clipboard b& using the

et%ormat method of the (ata)b'ect. This method returns True if the format exists on the clipboard

or %alse if no such format exists. %or example+

Dim As oolean = DataObj.%etFormat+"FormatD", / = Tr!e T(en

Deb!g.rint DataObj.%etText+"FormatD", -lse Deb!g.rint "Format does not exist on cli$board." -nd /

Clipboard #odule %unctions

3ou can download a module filethat contains functions for putting data in the clipboard

and retrie!ing data out of the clipboard. The procedures in this module support formatidentifiers for place multiple text string on the clipboard.

0mpt&Clipboard

"ithin 0xcel+ &ou can clear 0xcel data from the clipboard with: Application.CutCopode 7 %alse.

)r to completel& clear the clipboard+ use code lie the following.

!blic Declare F!nction O$en#li$board 0ib "!ser1'" +23al (wnd As 0ong, As 0ong!blic Declare F!nction -m$t2#li$board 0ib "!ser1'" +, As 0ong!blic Declare F!nction #lose#li$board 0ib "!ser1'" +, As 0ong

S!b #lear#li$board+, O$en#li$board +45, -m$t2#li$board #lose#li$board-nd S!b

The 2ublic (eclare lines of code need to be in the declarations section of the module+ before and

outside of an& procedure. If &ou are going to use this code within an ob'ect module 8This"orboo+
http://www.cpearson.com/Zips/modClipboard.ziphttp://www.cpearson.com/Zips/modClipboard.zip


3/3

a $heet module+ a User%orm module+ or a Class module9+ change 2ublic to 2ri!ate.

2utInClipBoard

!blic F!nction !tn#li$board+S As String) 6O$tional FormatD As 3ariant, As oolean

This function puts the string $ in the clipboard. If %ormatI( is specified+ the text is put in theclipboard with that format identifier.

et%romClipboard

!blic F!nction %etFrom#li$board+O$tional FormatD As 3ariant, As String

This function get text from the clipboard+ using the %ormatI( if pro!ided.

angeToClipboard$tring

!blic F!nction 7angeTo#li$boardString+77 As 7ange, As String

This function taes an 0xcel ange 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 worsheet cells. The format of

the text string is shown below:

3al!e& *bTab 3al!e' *bTab 3al!e1 *bNew0ine 3al!e8 *bTab 3al!e9 *bTab 3al!e: *bNew0ine 3al!e; *bTab 3al!e< *bTab 3al!e

Arra&ToClipboard$tring

!blic F!nction Arra2To#li$boardString+Arr As 3ariant, As String

This function taes an arra& 8either 4 or dimensions9 and creates a string formated so that it can be

pasted into a range of cells. The string is formated as follows:

Arr+&)&, *bTab Arr+&)', *bTab Arr+&)1, *bNew0ine Arr+')&, *bTab Arr+')', *bTab Arr+')1, *bNew0ine Arr+1)&, *bTab Arr+1)', *bTab Arr+1)1,

The module also contains two support functions+ Arr-um(imensions+ which returns the number of

dimensions in an arra&+ and IsArra&Allocated+ which returns True or %alse indicating whether a

!ariable references an allocated arra&.

clipboard in vba

Documents