Put text into the clipboard / get text from the clipboard

This post is one of a series providing implementation examples of Windows API Functions, Types, Enums and Consts using VBA. The code in this post can be used as-is, however, if you regularly (or even just occasionally) work with Windows API declarations in VBA, you may want to see the posts Automatically add Windows API declaration(s) and Using 'F1' to view Windows API web pages which explain some of the functionality that can be added to the VBE by VBE_Extras.

There's multiple ways of working with clipboard text from VBA.

You can use the MSForms.DataObject (requires your VBA Project to have a reference to the "Microsoft Forms 2.0 Object Library" ... aka just "MSForms" ... type library if using early binding) with which I have had problems before when trying to write text to the clipboard (it appears to be 100% reliable for reading text from the clipboard ... just not 100% reliable when writing text to the clipboard).

You can use the MSHTML.HTMLDocument (requires your VBA Project to have a reference to the "Microsoft HTML Object Library" type library if using early binding).

Or you can use the Windows API ... which requires no references, works with Unicode text (which is what VBA code uses natively) and, for the following code, works in both 32-bit and 64-bit Windows (and Office). The downside is that the code is quite lengthy with lots of Windows API declarations ... the best solution to that, is to put this code in a discrete Module (I use a standard Module named "UtilsClipboard") and then when developing your other code, you only ever see the Public methods.

The following code includes 4 Public methods:

• TryPutTextInClipboard() ... takes a String parameter being the text to put into the clipboard and returns a Boolean True/False for success

• TryGetTextFromClipboard() ... takes a (ByRef) String parameter which will be updated with the text from the clipboard and returns a Boolean True/False for success

• TryEmptyClipboard() ... returns a Boolean True/False for success

• HasClipboardContentsChanged() ... takes a Long parameter being a number that updates when the contents of the clipboard have changed and returns a Boolean True/False for whether the contents have changed

Here's the code ...

And here are examples of using each of the 4 Public methods ... copy this code into a separate Module and then run each Sub to test ...

If you want to understand more about each of the Windows API Functions and Consts used in the code, you can either search online for the relevant Microsoft documentation web pages ... or you can download VBE_Extras (it comes with a free 60 day trial) and use the "F1 to view Windows API web pages" functionality (see the Using 'F1' to view Windows API web pages blog post for more info) to open them automatically for you.