Return to VBA Code Examples

VBA Copy to Clipboard

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

There may be a time that we wish 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.

As we are using late binding by declaring the variable objCP as an Object, we do not need to add a reference to Excel to make this procedure work.

If we were now to switch to our Excel worksheet and click Paste, the text “Some copied data” would be inserted into the selected cell.

If we were to change this procedure above into a function, we could pass the text to be copied through as a variable.

We could then call this function multiple times in our VBA code as and when we need to copy text to the clipboard.  The text would not therefore be hard-coded into the VBA code.

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

We can then call this function to return the data stored on the clipboard.

A neat trick would be combining the 2 functions together so that we could use the same function to Copy and to Paste data, depending on whether or not we send data to the clipboard, or if we wish to retrieve data from the clipboard.

In the code above, we can make the strText variable optional – this means that if we wish to Copy data, we will include the text to be copied, but if we wish to Paste data, we will exclude it.

We will 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, we can use this procedure, note we are including the text to be copied.

To paste the data, we can use this procedure. The message box will show the value stored on the clipboard.

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! vba save as

Learn More!