Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Copy VBA variable's numeric value to clipboard

    I can do this easily enough by writing the variable to a cell and then using Range.copy (and then erasing the cell contents), but I am surprised there doesn't seem to be a simple direct way of doing this.
    Or am I wrong ?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    This looks interesting.

    Googling: vba copy to clipboard
    returns quite a few items including one using API calls.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Martin,

    If RG's link doesn't provide what you need, the following code works. Run the SAMPLECALL routine to call the function that loads the passed variable to the clipboard. Change the line a="test" to the value you want. After the code is run, Ctrl-V will paste the variables value.

    HTH,
    Maud

    Code:
    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
    Public Const GHND = &H42
    Public Const CF_TEXT = 1
    Public Const MAXSIZE = 4096
    
    
    Public Sub SAMPLECALL()
    Dim a As Variant
    a = "test"
    VarCopy a
    End Sub
    
    
    Public Function VarCopy(Variable As Variant)
       Dim MemLoc1 As Long, MemLoc2 As Long
       Dim MemLoc3 As Long, X As Long
       Dim strng As String, MyString As String
       MyString = Variable
       MemLoc1 = GlobalAlloc(GHND, Len(MyString) + 1)
       MemLoc2 = GlobalLock(MemLoc1)
       MemLoc2 = lstrcpy(MemLoc2, MyString)
       If GlobalUnlock(MemLoc1) <> 0 Then
          MsgBox "Could not unlock memory location. Copy aborted."
          GoTo Continue
       End If
       If OpenClipboard(0&) = 0 Then
          MsgBox "Could not open the Clipboard. Copy aborted."
          Exit Function
       End If
          X = EmptyClipboard()
       MemLoc3 = SetClipboardData(CF_TEXT, MemLoc1)
    Continue:
       If CloseClipboard() = 0 Then
          MsgBox "Could not close Clipboard."
       End If
    End Function

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks RG - I had done that and found reams of code which persuaded me that the workaround I had was in truth easier (or that I wasn't searching properly) !

    And thanks Maud - lots of food for thought there !!

    I remain amazed that so simple a task is so complex to undertake in VBA.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Inspired by RG and Maud, I think this is as simple as it gets:

    Code:
    Dim DataObj As New MSForms.DataObject
    Dim Number As String
    Number = [VBA variable or Expression]
    DataObj.SetText Number
    DataObj.PutInClipboard
    It hadn't really occurred to me that the Clipboard only contains strings rather than numbers.

    For this code to work you have to reference the Microsoft Forms 2.0 Object Library [VBA . . .Tools . . . References]

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Nice Job.

    Here's a function with some improvements.
    1. Doesn't need reference due to late binding
    2. Use of Variant as argument type allows both numbers (yes when pasted they can be operated on) and strings.

    Code:
    Option Explicit
    
    Function WriteToClipBoard(vValue As Variant)
    
       Dim DataObj As Object
     
       '*** Using Late Binding to MSForms.DataObject
     
      Set DataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
      
       With DataObj
           .SetText vValue
           .PutInClipboard
       End With
       
    End Function   'WriteToClipBoard
    
    Sub Test()
    
      WriteToClipBoard "Hello"  'Also test just 5 and it came in as number!
      
    End Sub
    MartinClip.JPG

    HTH
    Last edited by RetiredGeek; 2015-12-18 at 18:28.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Neat, complete and understandable.

    Many thanks.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just be aware that there is a bug in Windows 8 (I am not sure if it persists in 10) whereby sometimes using the DataObject results in just two odd Unicode characters being put on the clipboard rather than the data you intended. The API is more complicated but safer.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks again Rory.

    I'm sticking with Windows 7 forever - Windows 8 is still in beta and Windows 10 is just an experiment, not for serious users in my anticipated lifetime

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    RG probably knows this method, but perhaps, like me, forgot about the good old DOS pipe method to send stuff to the clipboard.
    Here's the method:
    Code:
    Sub test()
    
    zVar = 123456.78966666      '<<any vba variable value you want
    
    Set zShell = CreateObject("WScript.Shell")
    zShell.Run ("%comspec% /c echo " & zVar & " | clip"), vbHide
    
    End Sub
    ..if you run this code, you can then use [Ctrl][v] to paste the clipboard value wherever you want.

    zeddy
    Last edited by zeddy; 2015-12-23 at 15:53.

  11. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2015-12-23)

  12. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by MartinM View Post
    I can do this easily enough by writing the variable to a cell and then using Range.copy (and then erasing the cell contents), but I am surprised there doesn't seem to be a simple direct way of doing this.
    Or am I wrong ?
    Perhaps you could explain why you want to put the variable's value into the clipboard?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Perhaps, pasting the value into another application could be one reason.

  14. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Good question Paul, good answer Maud !

    I have to enter data into a website from time to time, from various places in a spreadsheet - most of the cells in question are protected (albeit by me).

    So I've created a button with an assigned macro (which has a keyboard shortcut) which copies the value of any selected cell to the clipboard. To help me avoid errors, the macro displays the value about to be pasted to a message box before it is copied to the clipboard.

    As ever, an extraordinary number of different solutions have been offered here - my on-going surprise is that none of them, including mine, is particularly memorable when coding. I think I'll have to create a UDF to get around that as I'm finding it quite a useful function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •