This article shows how to copy a string to clipboard using VBA code. Those who are new to VBA programming language, VBA (Visual Basic for Application) is mainly used to automate repetitive tasks using MS Office applications (MS Excel, Word, Powerpoint etc). There are many times you need to copy some text to clipboard without storing it in a cell or variable.
How to use VBA CodeOpen Excel WorkbookPress ALT + F11 shortcut key to open visual basic editor (VBE)To insert a module, go to Insert > ModulePaste the complete VBA script below
Many popular blogs show how you can copy content to clipboard using method of Microsoft Forms 2.0 Object Library but it does not work anymore as it returns two question marks. To workaround this issue we need to rely on API by Microsoft. The program below uses the API and code is broken down into two sections – Declaration of API, user-defined function.
MS Excel 2010 or above uses VBA7 version. If you are still using MS Excel 2007 or below, it supports VBA 6.5 or below versions.
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 CopyToClipBoard(mytext As String) As Boolean
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(mytext) + 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, mytext)
‘ 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)
CopyToClipBoard = 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”
CopyToClipBoard = False
End Function
Examples to use the above function
The code below is the most basic example how you can copy the text and paste to cell A1.
Sub Example1()
‘ Copy Content
CopyToClipBoard (“Hello, how are you?”)
‘Paste
Range(“A1”).Select
ActiveSheet.Paste
‘Message when done
MsgBox “Task Accomplished”, vbInformation
End Sub
Read MoreListenData
