Clipboard Power Tools for Excel VBA

Overview

Excel VBA Clipboard

Standard Ctrl+C copies cell values, but sometimes you need the underlying formulas or the cell addresses themselves. These clipboard utilities use the Windows API to copy formula text and cell references directly to the clipboard, and can even select ranges from clipboard text.

The trio works together: Get_Range_Name copies the selected addresses, Copy_Range_Formulas_To_Clipboard copies formulas as tab-delimited text, and Select_Ranges_From_Text reverses the process by selecting cells from clipboard addresses.

Key Features

Windows API Integration: Uses kernel32 and user32 DLL calls for direct clipboard access, supporting both 32-bit and 64-bit Office via conditional compilation
Get Range Name: Copies the selected cell addresses (e.g., A1, B2:C5) to the clipboard without dollar signs, ready to paste into documentation or other tools
Copy Formulas: Copies all formulas and values from the selection as tab-separated text, preserving the row/column structure for pasting into text editors
Select From Text: Reads comma-separated cell addresses from the clipboard and selects those ranges on the active sheet, bridging clipboard text back to cell selection

The VBA Code

'Handle 64-bit and 32-bit Office
#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 LongPtr
    Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
    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 LongPtr
    Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr
#End If

Private Function ClipBoard_SetData(MyString As String)
    Dim Hnd_Gbl As LongPtr: Hnd_Gbl = GlobalAlloc(&H42, Len(MyString) + 1)
    Dim Ptr_Gbl As LongPtr: Ptr_Gbl = GlobalLock(Hnd_Gbl)
    Ptr_Gbl = lstrcpy(Ptr_Gbl, MyString)
    If GlobalUnlock(Hnd_Gbl) <> 0 Then MsgBox "Could not unlock memory location.": GoTo Lbl_Exit
    If OpenClipboard(0&) = 0 Then MsgBox "Could not open the Clipboard.": Exit Function
    Dim Num_Dum As Long: Num_Dum = EmptyClipboard()
    Dim Hnd_Clip As LongPtr: Hnd_Clip = SetClipboardData(1, Hnd_Gbl)
Lbl_Exit:
    If CloseClipboard() = 0 Then MsgBox "Could not close Clipboard."
End Function

Public Sub Get_Range_Name()
    On Error GoTo Lbl_Skip
    Dim Str_Add As String: Str_Add = Selection.Address
    Str_Add = Replace(Str_Add, "$", "")
    Str_Add = Replace(Str_Add, ",", ", ")
    ClipBoard_SetData Str_Add
Lbl_Skip:
End Sub

Public Sub Copy_Range_Formulas_To_Clipboard()
    Dim Rng_Tgt As Range: Set Rng_Tgt = Selection
    Dim Str_Frm As String
    Dim Idx_Row As Long, Idx_Col As Long
    
    For Idx_Row = 1 To Rng_Tgt.Rows.Count
        Dim Str_Row As String: Str_Row = ""
        For Idx_Col = 1 To Rng_Tgt.Columns.Count
            Dim Rng_Cel As Range: Set Rng_Cel = Rng_Tgt.Cells(Idx_Row, Idx_Col)
            If Rng_Cel.HasFormula Then
                Str_Row = Str_Row & Replace(Rng_Cel.Formula, vbLf, "")
            Else
                Str_Row = Str_Row & Replace(CStr(Rng_Cel.Value), vbLf, "")
            End If
            If Idx_Col < Rng_Tgt.Columns.Count Then Str_Row = Str_Row & vbTab
        Next Idx_Col
        Str_Frm = Str_Frm & Str_Row
        If Idx_Row < Rng_Tgt.Rows.Count Then Str_Frm = Str_Frm & vbCrLf
    Next Idx_Row
    
    ClipBoard_SetData Str_Frm
    MsgBox "Formulas and values copied to clipboard!"
End Sub

Public Sub Select_Ranges_From_Text()
    Dim Wks_Act As Worksheet: Set Wks_Act = ActiveSheet
    Dim Obj_Do As MSForms.DataObject: Set Obj_Do = New MSForms.DataObject
    Obj_Do.GetFromClipboard
    
    On Error GoTo Lbl_ExitErr
    Dim Str_Rng As String: Str_Rng = Obj_Do.GetText
    Str_Rng = Replace(Replace(Replace(Str_Rng, vbCrLf, ""), vbCr, ""), vbLf, "")
    
    Dim Arr_Tok() As String: Arr_Tok = Split(Str_Rng, ", ")
    Dim Rng_Out As Range
    Dim Idx_I As Long
    
    For Idx_I = LBound(Arr_Tok) To UBound(Arr_Tok)
        Dim Str_Tok As String: Str_Tok = Trim$(Arr_Tok(Idx_I))
        If Len(Str_Tok) > 0 Then
            If Rng_Out Is Nothing Then
                Set Rng_Out = Wks_Act.Range(Str_Tok)
            Else
                Set Rng_Out = Union(Rng_Out, Wks_Act.Range(Str_Tok))
            End If
        End If
    Next Idx_I
    
    If Not Rng_Out Is Nothing Then Rng_Out.Select
    Exit Sub
Lbl_ExitErr:
    MsgBox "No data in Clipboard yet."
End Sub

Final Thoughts

These clipboard tools fill a gap that Excel's built-in copy/paste does not cover. Whether you are documenting formulas for review, sharing cell references across applications, or programmatically selecting ranges from a list, this API-driven approach gives you full control over what goes in and out of the clipboard.