There is no built-in functionality in Excel to use the clipboard. We could have implemented the clipboard functionality from scratch using COM DataObject and Win32 API. To save time we will instead show you how to use the Microsoft Forms DataObject class.
There are two different ways to use the Microsoft Forms DataObject:
- Add a reference to the COM library FM20.dll (Microsoft Forms 2.0), or
- Use late binding by calling CreateObject
In most cases you don’t want to add a reference to the Microsoft Forms 2.0 COM library. It is messy to maintain for other developers, and if you re-use your code in another Excel Workbook you would have to remember to add the reference too. In this article we will show you have to use late binding which is enabled through the COM IDispatch interface.
Late binding
The correct way in VBA to create a new instance of a COM Object is to use the CreateObject method. In a perfect world we would have written a statement like
' Instance
Dim objDataObject As Object
' Allocate instance (DOES NOT WORK)
Set objDataObject = CreateObject("Microsoft.Forms.DataObject")
Unfortunately the above statement doesn’t work. This is because when we run Regsvr32 to install the FM20.dll library no named entries are registered in the registry. Instead we need to use the Class ID (CLSID) to find the object definition in the registry. The class ID (CLSID) for the Microsoft Forms 2.0 DataObject is “1C3B4210-F441-11CE-B9EA-00AA006B1A69”. To create a new instance of the DataObject we need to do the following
' Instance
Dim objDataObject As Object
' Allocate new instance of Microsoft Forms 2.0 DataObject
Set objDataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
The object definition of the Microsoft Forms DataObject can be found in the registry (regedit.exe). You can see that the CLSID refers to the FM20.dll.
To Copy any text to the clipboard we need to use the methods SetText and PutInClipboard. Copying text data to the clipboard requires two steps. The first step is to put the text in a DataObject variable, and then copy the entire DataObject to the clipboard. A DataObject can contain many different clipboard formats. Internally the text format is known as CF_TEXT and has the numeric value 1. If you would like to copy a Bitmap or a Metafile to the clipboard it is much more complicated, and it will not be covered in this article.
To Paste existing text from the clipboard, we need to call GetFromClipboard and GetText. First we need to retrieve a reference to the DataObject, and then we need to get the text. When pasting data from the clipboard, the Microsoft Forms DataObject works as a wrapper object. It is not the actual object stored on the clipboard. Instead it calls the methods of the OLE IDataObject interface of the clipboard object.
Code
The complete code for Copy and Paste in Excel can be found below:
' Copy Text To Clipboard
Public Sub SetClipboardText(ByVal strText As String)
' Instance
Dim objDataObject As Object
' Allocate instance of Microsoft Forms DataObject
Set objDataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
' Set the Properties
With objDataObject
' Add text to the DataObject
.SetText strText
' Copy all data in the DataObject to the Clipboard (internally calling OleSetClipboard)
.PutInClipboard
End With
' Free memory
Set objDataObject = Nothing
End Sub
' Paste From Clipboard
Public Sub GetClipboardText() As String
' Instance
Dim objDataObject As Object
' Allocate instance of Microsoft Forms DataObject
Set objDataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
' Set the Properties
With objDataObject
' Get the DataObject from the clipboard (internally calling OleGetClipboard)
.GetFromClipboard
' Get the text and set the return value of the function
GetClipboardText = .GetText
End With
' Free memory
Set objDataObject = Nothing
End Sub
Usage
The Copy and Paste methods can be called the following way:
' Example text Dim strText As String ' Set the text strText = "This is a test" ' Copy the text to the clipboard Call SetClipboardText(strText) ' Get the text from the clipboard strText = GetClipboardText()
