How to copy strings to Clipboard using Excel VBA

The Problem

The Microsoft Forms 2.0 Object library contains an object called DataObject that allows VBA developers to send (put) and read (get) text strings on the Windows clipboard. However, these methods seem to fail, if VBA code is executed under Windows 8 & 10 as of 9/2016. I first spotted the problem, while generating GUID codes using the VBA RC Toolkit add-in.

The VBA Toolkit add-in powered by Ribbon Commander offers a button to generate GUID codes, which are used as globally unique identifiers in computer software. These GUID numbers are so large that the probability of the same number being generated randomly twice is negligible. GUID codes are very useful tool for those who develop ribbon user interfaces in XML, as each control should be unique.

The GUID generator button copies a code to the clipboard, so it can be pasted inside a VBA module.

Here is how a string can be copied in the clipboard using VBA:

Send Information To The Clipboard Using The MS-FORMS Library

Unfortunately, VBA does not offer a clipboard object, although Visual Basic 6 did. Luckily, the MS-Forms 2.0 DataObject object can be used by setting a reference to 'Microsoft Forms 2.0 Object' library. If your VBA project has a userform, then the reference has been already set.

The code snippets shown below work fine in Windows 7 and prior. However, the code copies just two questions marks to the clipboard when used under Windows 8 and 10 (as tested in September 2015). Microsoft recommends using an API workaround.

Option Explicit

Sub CopyTextToClipboardDemo()
    ' Source: www.Spreadsheet1.com
    ' Enable Forms Library: VBE>Tools> References>Microsoft Forms 2.0 Object Library>Check

    Dim oClipboard As MSForms.DataObject
    
    Set oClipboard = New MSForms.DataObject
    oClipboard.SetText Now    'copy current date/time
    oClipboard.PutInClipboard

End Sub

Function CopyToClipboard(sClipText As String) As Boolean

    ' Source: www.Spreadsheet1.com
    ' Late binding, no Forms Library reference required

    Dim MSForms_DataObject As Object

    On Error GoTo ErrorHandler_
    
    Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    MSForms_DataObject.SetText sClipText
    MSForms_DataObject.PutInClipboard
    CopyToClipboard = True
Exit Function
ErrorHandler_:
    CopyToClipboard = False
End Function

Sub Demo()
    Debug.Print CopyToClipboard(Now)
End Sub

Sub GetTextFromClipboardDemo()
    ' Source: www.Spreadsheet1.com
    ' Enable Forms Library: VBE>Tools> References>Microsoft Forms 2.0 Object Library>Check

    Dim oClipboard As MSForms.DataObject
    
    Set oClipboard = New MSForms.DataObject
    oClipboard.GetFromClipboard
    Debug.Print oClipboard.GetText

End Sub

Use An API To Put Text In Windows Clipboard

To use Windows API calls to copy information to the Clipboard read this Microsoft article. The VBA code shown below is a modified version of Microsoft's snippet. The code seems to work just fine in Windows 8 and 10 as tested during September 2015. Error handling has been added to the function in order to return True (text copied) or False (an error occured) to the calling procedure.

The API declarations are compatible with both 32 and 64-bit versions of Office 2010, 2013, 2016.

Option Explicit

#If VBA7 Then
  Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
  Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As Long
  Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, ByVal dwBytes As LongPtr) As Long
  Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
  Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As LongPtr) As Long
  Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
  Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
  Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) As Long
#Else
  Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
  Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
  Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
  Declare Function CloseClipboard Lib "User32" () As Long
  Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
  Declare Function EmptyClipboard Lib "User32" () As Long
  Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
  Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
#End If

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Public Function ClipBoard_SetData(sPutToClip As String) As Boolean

    ' www.msdn.microsoft.com/en-us/library/office/ff192913.aspx

    Dim hGlobalMemory As Long
    Dim lpGlobalMemory As Long
    Dim hClipMemory As Long
    Dim X As Long
    
    On Error GoTo ExitWithError_

    ' Allocate moveable global memory
    hGlobalMemory = GlobalAlloc(GHND, Len(sPutToClip) + 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, sPutToClip)

    ' Unlock the memory
    If GlobalUnlock(hGlobalMemory) <> 0 Then
        MsgBox "Memory location could not be unlocked. Clipboard copy aborted", vbCritical, "API Clipboard Copy"
        GoTo ExitWithError_
    End If

    ' Open the Clipboard to copy data to
    If OpenClipboard(0&) = 0 Then
        MsgBox "Clipboard could not be opened. Copy aborted!", vbCritical, "API Clipboard Copy"
        GoTo ExitWithError_
    End If

    ' Clear the Clipboard
    X = EmptyClipboard()

    ' Copy the data to the Clipboard
    hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
    ClipBoard_SetData = True
    
    If CloseClipboard() = 0 Then
        MsgBox "Clipboard could not be closed!", vbCritical, "API Clipboard Copy"
    End If
    Exit Function
ExitWithError_:
    On Error Resume Next
    If Err.Number > 0 Then MsgBox "Clipboard error: " & Err.Description, vbCritical, "API Clipboard Copy"
    ClipBoard_SetData = False

End Function

Clear the Clipboard

You may need to clear the clipboard, if your program copies sensitive information in it, like passwords, credit card or medical info. Here is how to do it with API calls. Please place the API declarations shown below at the top of a standard VBA module.

#If VBA7 Then
    Private Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
    Private Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
    Private Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
#Else
    Private Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
    Private Declare Function EmptyClipboard Lib "User32" () As Long
    Private Declare Function CloseClipboard Lib "User32" () As Long
#End If

Sub ClearClipboard()

    On Error GoTo ErrorHandler_
    
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
    Exit Sub
ErrorHandler_:
    MsgBox "Error: " & Err.Description, vbCritical, APP_TITLE
End Sub

How To Copy Text To Clipboard Using Excel VBA

The Problem

Send Information To The Clipboard Using The MS-FORMS Library

Use An API To Put Text In Windows Clipboard

Clear the Clipboard