VBA Copy to Clipboard

This article will demonstrate how to use VBA to copy items to the Clipboard.

You may want to copy information in Excel VBA and store it to use within another application or at another time when the Excel macro has stopped running. Once a macro stops running, the information that is stored in a variable or variables stops existing and can no longer be retrieved.  A way to solve this problem would be to copy this information to the clipboard.

Copying to the Clipboard using the HTML Object Library

The simplest way to use the clipboard in Excel VBA is to call the HTML Object Library.

Sub StoreData()
  Dim varText As Variant
  Dim objCP As Object
  varText = "Some copied text"
  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", varText
End Sub

This uses late binding by declaring the variable objCP as an Object, thus you do not need to add a reference first.

If youopen an Excel worksheet and click Paste, the text “Some copied data” would be inserted into the selected cell.

If you change the previous sub procedure into a function, you can pass the text to a variable.

Function StoreData(varText As Variant) as String
  Dim objCP As Object
  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", varText
End Function

It’s then possible to call this function multiple times.

Sub CopyData()
  StoreData "Some copied text"
End Sub

You can also use the HTML Object to return the text from the clipboard – ie Pasting.  For this use the GetData rather than the SetData method.

Function ReturnData()
  Dim objCP As Object
  Set objCP = CreateObject("HtmlFile")
  ReturnData = objCP.parentWindow.clipboardData.GetData("text")
End Function

Then you can call this function to return the data stored on the clipboard.

Sub PasteData()
  MsgBox ReturnData
End Sub

A neat trick is combining the 2 functions together so you can use the same function to Copy and to Paste data, depending on whether or not data is sent to the clipboard, or if you wish to retrieve data from the clipboard.

Function StoreOrReturnData(Optional strText As String) As String
  Dim varText As Variant
  Dim objCP As Object
  Set objCP = CreateObject("HtmlFile")
  varText = strText
  If strText <> "" Then
    objCP.ParentWindow.ClipboardData.SetData "text", varText
  Else
    StoreOrReturnData = objCP.ParentWindow.ClipboardData.GetData("text")
  End If
End Function

In the code above, the strText variable is optional – this means you do not need to enter the variable value if you only wish to paste data.

Then assign the string variable (strText) to a Variant variable in order for it to be stored in the SetData method of the HTML File Object.

To copy the data, you can use this procedure:

Sub CopyData()
  StoreOrReturnData "SomeCopiedText"
End Sub

This procedure shows a message box displaying the clipboard value.

Sub PasteData()
  MsgBox StoreOrReturnData
End Sub

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!