How To Use VBA Code To Copy Text To The Clipboard
Update! Better Solution from ExcelHero
Excel MVP Daniel Ferry from ExcelHero graciously provided a much more eloquent solution than my original article over on the StackOverflow forum.
In the below solution, he is utilizing the Microsoft HTML Object Library to access the clipboard. This solution will work with 32-bit and 64-bit versions of Excel which has been a pain point for many proposed VBA solutions out there on the web. His VBA function also allows you to both read from the clipboard and write to the clipboard depending on if you pass through a variable. Check out this beautiful piece of code (and if you dare keep scrolling to see my original behemoth of a solution!).
Function Clipboard(Optional StoreText As String) As String
'PURPOSE: Read/Write to Clipboard
'Source: ExcelHero.com (Daniel Ferry)
Dim x As Variant
'Store as variant for 64-bit VBA support
x = StoreText
'Create HTMLFile Object
With CreateObject("htmlfile")
With .parentWindow.clipboardData
Select Case True
Case Len(StoreText)
'Write to the clipboard
.setData "text", x
Case Else
'Read from the clipboard (no variable passed through)
Clipboard = .GetData("text")
End Select
End With
End With
End FunctionHere is a simple example that writes the text “I can copy to the Clipboard!” to the computer’s clipboard and then calls the same text from the clipboard, displaying it in a message box.
Sub ExampeMacro()
'Copy text to the clipboard
Clipboard "I can copy to the Clipboard!"
'To read text from the clipboard:
MsgBox Clipboard
End SubText To The Clipboard (original solution)
There may be instances where you create a macro that reads in text and sticks it in your computer’s clipboard so you can manually paste it somewhere else. The most prominent way to do this, is to use a DataObject variable from the Forms library (language).
You can reference this library by either going to your Visual Basic Editor’s tools menu and enabling the Microsoft Forms 2.0 Object Library reference or by simply adding a userform to your project (the userform doesn’t have to do anything, it’s very existence tells your project that the Forms library is needed).
Below is a very self-explanatory snippet of VBA code that will show you how to copy text straight into your computer’s clipboard.
Sub CopyTextToClipboard()
'PURPOSE: Copy a given text to the clipboard (using DataObject)
'SOURCE: www.TheSpreadsheetGuru.com
'NOTES: Must enable Forms Library: Checkmark Tools > References > Microsoft Forms 2.0 Object Library
Dim obj As New DataObject
Dim txt As String
'Put some text inside a string variable
txt = "This was copied to the clipboard using VBA!"
'Make object's text equal above string variable
obj.SetText txt
'Place DataObject's text into the Clipboard
obj.PutInClipboard
'Notify User
MsgBox "There is now text copied to your clipboard!", vbInformation
End SubThere’s An Excel Office Bug!
I initially started investigating how to copy text to the clipboard while running someone else’s code. This code worked perfectly on my work computer (using Windows 7/Excel 2007), however it kept copying just two question marks to the clipboard while executing the VBA code on my home computer (using Windows 8.1/Excel 2013). It took me forever to narrow down what was causing this to happen (the source code was very long) and of course, the problem ended up being a bug on the part of Microsoft!
Luckily, there were many others who were running into this very issue on the forums. Someone posted that they were actually able to troubleshoot this problem with a Microsoft support member a couple years ago (maybe in 2010) and that person determined it to be a bug. The support member pointed to a solution using a Windows API as a workaround (shown in the next section).
Copy To Clipboard With Windows API
Below is the API workaround suggested by Microsoft to get around the “SetText” bug. It has three parts: an API declaration section, a Function routine, and then I used a similar subroutine macro to place the desired text into the Clipboard.
UPDATE: I have modified the API declarations to work with both 64-bit and 32-bit versions of Microsoft Office
'Handle 64-bit and 32-bit Office
#If VBA7 Then
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, _
ByVal dwBytes As LongPtr) As LongPtr
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As LongPtr
Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As LongPtr, _
ByVal hMem As LongPtr) As LongPtr
#Else
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
ByVal dwBytes As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _
As Long, ByVal hMem As Long) As Long
#End If
Const GHND = &H42
Const CF_TEXT = 1
Const MAXSIZE = 4096
Function ClipBoard_SetData(MyString As String)
'PURPOSE: API function to copy text to clipboard
'SOURCE: www.msdn.microsoft.com/en-us/library/office/ff192913.aspx
#If VBA7 Then
Dim hGlobalMemory As LongPtr, lpGlobalMemory As LongPtr
Dim hClipMemory As LongPtr, x As LongPtr
#Else
Dim hGlobalMemory As Long, lpGlobalMemory As Long
Dim hClipMemory As Long, x As Long
#End If
'Allocate moveable global memory
hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)
'Lock the block to get a far pointer to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)
'Copy the string to this global memory.
lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)
'Unlock the memory.
If GlobalUnlock(hGlobalMemory) <> 0 Then
MsgBox "Could not unlock memory location. Copy aborted."
GoTo OutOfHere2
End If
'Open the Clipboard to copy data to.
If OpenClipboard(0&) = 0 Then
MsgBox "Could not open the Clipboard. Copy aborted."
Exit Function
End If
'Clear the Clipboard.
x = EmptyClipboard()
'Copy the data to the Clipboard.
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
OutOfHere2:
If CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard."
End If
End Function
Sub CopyTextToClipboard()
'PURPOSE: Copy a given text to the clipboard (using Windows API)
'SOURCE: www.TheSpreadsheetGuru.com
'NOTES: Must have above API declaration and ClipBoard_SetData function in your code
Dim txt As String
'Put some text inside a string variable
txt = "This was copied to the clipboard using VBA!"
'Place text into the Clipboard
ClipBoard_SetData txt
'Notify User
MsgBox "There is now text copied to your clipboard!", vbInformation
End SubOther Resources
Here are a few articles that helped me during my research for this article and may build on what I discussed in this article.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.