vba clipboard code example

Example 1: vba windows clipboard

'VBA function to read and write text to the Windows clipboard.

Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
    With .parentWindow.clipboardData
        Select Case True
            Case Len(s): .setData "text", v
            Case Else:   Clipboard = .getData("text")
        End Select
    End With
    End With
End Function

'--------------------------------------------------------------------
'Three examples of copying text to the clipboard:
Clipboard "Excel Hero was here."
Clipboard var1 & vbLF var2
Clipboard 123

'To read text from the clipboard:
MsgBox Clipboard

'Note:	This is a solution that does NOT use MS Forms 
'nor the Win32 API. Instead it uses the Microsoft HTML Object Library 
'which is fast and ubiquitous; and this solution respects line feeds.

Example 2: excel vba copy a string to clipboard

'VBA function to read and write text to the Windows clipboard.

Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
    With .parentWindow.clipboardData
        Select Case True
            Case Len(s): .setData "text", v
            Case Else:   Clipboard = .getData("text")
        End Select
    End With
    End With
End Function

'--------------------------------------------------------------------
'Three examples of copying text to the clipboard:
Clipboard "Excel Hero was here."
Clipboard var1 & vbLF & var2
Clipboard 123

'To read text from the clipboard:
MsgBox Clipboard

'Note:	This is a solution that does NOT use MS Forms 
'nor the Win32 API. Instead it uses the Microsoft HTML Object Library 
'which is fast and ubiquitous; and this solution respects line feeds.