Visual Basic for Applications/Clipboard VBA

Summary

There are three main ways to pass text to and from the clipboard with VBA code.

• The DataObject method:
  • This is perhaps the simplest implementation.
  • Its main restriction is that the contents of the clipboard will be lost when the launching application is closed; generally this is not a problem when running Excel modules, but should be borne in mind.
  • Some users elsewhere report bugs. See DataObject Bugs Forum for details of the bugs and one suggested fix. All of the procedures on this page are tested and work well in both Windows 7 and Windows 8.1 for Excel 2010. The DataObject method has recently been adopted for the VBA Indenter module, in this same series.
  • Other methods avoid these restrictions. In the unlikely event of problems with these procedures, either of the next two methods would suffice.
  • An example of the DataObject method is given in section two of this page.
• User form control methods:
  • When user forms are to be displayed, then the copy and paste methods of the text box can be used. These methods work well and are well tested.
  • When no user form is to be displayed, a hidden form can be used. The form with a text box, is loaded but never displayed. Then, the invisible user form's controls can still then be coded as normal. The text box must have its Multiline property set to true for most useful text transfers. It will be found best, in general,to set the form's ShowModal property to False; this allows for convenient code tracing and avoids many other confusions.
  • An example of the hidden user form method is given in section four. Another example in section three, for a visible user form, shows how to track the active text box prior to copy.
• API methods:
  • These methods make use of Windows libraries, and have copious declarations in their module headings. That said, they work well, and are described by Microsoft documentation as being the most suitable.
  • One example of API use is displayed in section five. See Send-Information-to-the-Clipboard for more details.

DataObject Method

• These methods make used of a DataObject . They are by far the most adaptable, since any text that can be placed in a variable can then be placed onto the clipboard using the PutInClipboard method. Text can also be brought into a VBA string variable with the GetFromClipboard method. The procedures CopyToClip() and GetFromClip() in the example below first send text to the clipboard, then fetch it again, before displaying the text in a message box. Set a reference to Microsoft Forms 2 in the editor options for this; if you cannot find it just add a user form to your project and it will be added to the selections.
• Reports of bugs in DataObject methods are reported elsewhere. These apply to Windows versions beyond Win 7, and are reported to involve an unusual persistence between the object and the clipboard. If difficulty is found with these methods then either the dummy userform method or the API methods could be tried.

Sub testCopyAndPaste()
    'demonstrates copy and paste of text to variables
    'loads clipboard with date-time text then
    'fetches it back for display
    'Only good for text and clipboard content lost
    'when application closes.
        
    Dim sStrOut As String, sStrIn As String
    
    'get the current date-time string
    sStrOut = Now
    
    'copy text to clipboard
    CopyToClip sStrOut

    'retrieve from clipboard
    sStrIn = GetFromClip
    
    'display recovered text
    MsgBox sStrIn

End Sub

Function CopyToClip(sIn As String) As Boolean
    'passes the parameter string to the clipboard
    'set reference to Microsoft Forms 2.0 Object Library.
    'If ref not listed, inserting user form will list it.
    'Clipboard cleared when launch application closes.
    
    Dim DataOut As DataObject
    
    Set DataOut = New DataObject
    
    'first pass textbox text to dataobject
    DataOut.SetText sIn
    
    'then pass dataobject text to clipboard
    DataOut.PutInClipboard
    
    'release object variable
    Set DataOut = Nothing
    
    CopyToClip = True
    
End Function

Function GetFromClip() As String
    'passes clipboard text to function name
    'If clipboard not text, an error results
    'set reference to Microsoft Forms 2.0 Object Library.
    'If ref not listed, inserting user form will list it.
    'Clipboard cleared when launch application closes.
    
    Dim DataIn As DataObject
    
    Set DataIn = New DataObject
    
    'clipboard text to dataobject
    DataIn.GetFromClipboard
    
    'dataobject text to function string
    GetFromClip = DataIn.GetText
    
    'release object variable
    Set DataIn = Nothing
    
End Function

Visible User Form Method

The code module below provides the VBA code for a form module, (shown here as UserForm1). In it there are command button click routines for textbox Copy and Paste. To use the copy procedure the user simply selects some text then presses the button on the user form. To paste the contents of the clipboard into a textbox, the user must first place the insertion point somewhere within a textbox before pressing the requisite button.

In order to clarify which textbox is active, there is a mouse-up event for each, where a number is loaded into a module-level variable whenever a mouse is used in the box. Although this code is made for three textboxes, it can easily be extended to any number.

The code assumes that there is a user form UserForm1, with TextBox1, TextBox2, TextBox3, CommandButton1 and CommandButton2 in it. In addition, note that there is a module level variable in the code. Since the VBA code is fairly generic it applies to most MS Office applications.

Option Explicit
Dim nActTxtBx As Integer

Private Sub CommandButton1_Click()
'this is the "Paste at Cursor" button
'pastes clipboard active textbox's insertion point
'ie; the textbox last clicked with mouse
            
    Dim oTxt1 As Control, oTxt2 As Control, oTxt3 As Control
    Dim oFrm As UserForm, oTxt As Control, s As Long
    
    Set oFrm = UserForm1
    Set oTxt1 = oFrm.TextBox1
    Set oTxt2 = oFrm.TextBox2
    Set oTxt3 = oFrm.TextBox3
    
    'get the textbox with the focus
    Select Case nActTxtBx
    Case 0
        MsgBox "Please place the insertion point."
        Exit Sub
    Case 1
        Set oTxt = oTxt1
    Case 2
        Set oTxt = oTxt2
    Case 3
        Set oTxt = oTxt3
    Case Else
        Exit Sub
    End Select
    
    s = oTxt.SelStart
    With oTxt
        .Paste
        .SetFocus
        .SelStart = s
    End With

    Set oFrm = Nothing: Set oTxt = Nothing
    Set oTxt1 = Nothing: Set oTxt2 = Nothing
    Set oTxt3 = Nothing
End Sub

Private Sub CommandButton2_Click()
'this is the "Copy Selected Text" button
'copies selected text from textbox to clipboard
'ie; the textbox last clicked with mouse

    Dim oTxt1 As Control, oTxt2 As Control, oTxt3 As Control
    Dim oFrm As UserForm, oTxt As Control
    
    Set oFrm = UserForm1
    Set oTxt1 = oFrm.TextBox1
    Set oTxt2 = oFrm.TextBox2
    Set oTxt3 = oFrm.TextBox3
    
    'get reference to active textbox
    Select Case nActTxtBx
    Case 0
        MsgBox "Please make a selection."
        Exit Sub
    Case 1
        Set oTxt = oTxt1
    Case 2
        Set oTxt = oTxt2
    Case 3
        Set oTxt = oTxt3
    Case Else
        Exit Sub
    End Select
    
    'check that a selection was made
    'MsgBox oTxt.SelLength
    If oTxt.SelLength = 0 Then
        MsgBox "No selection found."
        Exit Sub
    End If
    
    With oTxt
        .Copy
        .SetFocus
        .SelStart = 0
    End With

    Set oFrm = Nothing: Set oTxt = Nothing
    Set oTxt1 = Nothing: Set oTxt2 = Nothing
    Set oTxt3 = Nothing

End Sub

Private Sub TextBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
                                ByVal X As Single, ByVal Y As Single)
    'loads an integer to denote active textbox when mouse makes selection
    nActTxtBx = 1
End Sub

Private Sub TextBox2_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
                                ByVal X As Single, ByVal Y As Single)
    'loads an integer to denote active textbox when mouse makes selection
    nActTxtBx = 2
End Sub

Private Sub TextBox3_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
                                ByVal X As Single, ByVal Y As Single)
    'loads an integer to denote active textbox when mouse makes selection
    nActTxtBx = 3
End Sub

Hidden User Form Method

This code should be placed in a standard module. The project needs a user form called Temp, with a single TextBox1 set with MultiLine=true. TextBox contents are always text.

Option Explicit

Sub TestClipboardProcs()
'run this
    
    CopyToClipboard "The string" & vbCrLf & _
                    "to copy..."
    MsgBox GetClipboard2

End Sub

Function GetClipboard2() As String
'PASTES clipboard into function name as a text string
'project needs userform named Temp
'with TextBox1 in it set with property Multiline=true
    
    Dim oTxt1 As Control, oFrm As UserForm
    Dim s As Long
    
    'load the temporary form
    Load Temp
    
    Set oFrm = Temp
    Set oTxt1 = oFrm.TextBox1
        
    s = oTxt1.SelStart
    With oTxt1
        .Paste
        .SetFocus
        .SelStart = s
    End With
    
    GetClipboard2 = oTxt1.Value
        
    Set oTxt1 = Nothing
    Set oFrm = Nothing
    Unload Temp

End Function

Function CopyToClipboard(sStr As String) As Boolean
'COPIES parameter variable text string value to clipboard
'project needs userform named Temp
'with TextBox1 in it set with property Multiline=true
    
    Dim oTxt1 As Control, oFrm As UserForm
    
    If sStr = "" Then
        MsgBox "Clipboard cannot hold an empty string."
        Exit Function
    End If
        
    'load the temporary form
    Load Temp
    
    Set oFrm = Temp
    Set oTxt1 = oFrm.TextBox1
    
    oTxt1.Value = sStr
        
    'copy textbox value to clipboard
    With oTxt1
        .SelStart = 0 'set up the selection
        .SelLength = .TextLength
        .Copy
        .SetFocus
        .SelStart = 0
    End With
        
    Set oTxt1 = Nothing
    Set oFrm = Nothing
    Unload Temp

    CopyToClipboard = True

End Function

API Method

The code below was tested on an Office 2010 version of Excel, 32 bit system, and worked well. Since that time, with 64 bit 2019 Excel, the code will not work in its current state, but needs further changes to the declarations for 64 bit use.

The following VBA code makes use of API calls, and is recommended by Microsoft in their MS Access page Send-Information-to-the-Clipboard. Such methods should overcome the current bugs in the DataObject methods for Windows 8 and 10. The code should be copied into a standard module in its entirety.

Option Explicit
'Declarations for functions SetClipboard() and GetClipboard()
''from https://docs.microsoft.com/en-us/office/vba/access/concepts/windows-api/send-information-to-the-clipboard
Private Declare Function OpenClipboard Lib "user32.dll" (ByVal hWnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32.dll" () As Long
Private Declare Function CloseClipboard Lib "user32.dll" () As Long
Private Declare Function IsClipboardFormatAvailable Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function GetClipboardData Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function SetClipboardData Lib "user32.dll" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Long, ByVal lpString2 As Long) As Long

Sub TestCopyPasteAPI()
    'API methods for clipboard
    Dim sIn As String, sOut As String
    
    sIn = "Sausages"
    SetClipboard sIn
    sOut = GetClipboard
    MsgBox sOut

End Sub

Public Sub SetClipboard(sUniText As String)
    'sets the clipboard with parameter string
      
    Dim iStrPtr As Long, iLen As Long
    Dim iLock As Long
    Const GMEM_MOVEABLE As Long = &H2
    Const GMEM_ZEROINIT As Long = &H40
    Const CF_UNICODETEXT As Long = &HD
    
    OpenClipboard 0&
    EmptyClipboard
    iLen = LenB(sUniText) + 2&
    iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)
    iLock = GlobalLock(iStrPtr)
    lstrcpy iLock, StrPtr(sUniText)
    GlobalUnlock iStrPtr
    SetClipboardData CF_UNICODETEXT, iStrPtr
    CloseClipboard

End Sub

Public Function GetClipboard() As String
    'gets the clipboard text in function name
    
    Dim iStrPtr As Long, iLen As Long
    Dim iLock As Long, sUniText As String
    Const CF_UNICODETEXT As Long = 13&
    
    OpenClipboard 0&
    If IsClipboardFormatAvailable(CF_UNICODETEXT) Then
        iStrPtr = GetClipboardData(CF_UNICODETEXT)
        If iStrPtr Then
            iLock = GlobalLock(iStrPtr)
            iLen = GlobalSize(iStrPtr)
            sUniText = String$(iLen \ 2& - 1&, vbNullChar)
            lstrcpy StrPtr(sUniText), iLock
            GlobalUnlock iStrPtr
        End If
        GetClipboard = sUniText
    End If
    CloseClipboard

End Function

See Also

• Send-Information-to-the-Clipboard: A clearly worded page by Microsoft showing how to use API methods for the clipboard. Although described for MS Access, it works in MS Excel just as well.
• DataObject Bugs Forum: A description of a DataObject bug for Windows versions beyond Win7.