Copy VBA variable’s numeric value to clipboard

Topic

New Reply

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 ?

Reply | Quote

Replies

Martin,

This looks interesting.

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

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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]

Reply | Quote

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

42971-MartinClip

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Neat, complete and understandable.

Many thanks.

Reply | Quote

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.

Reply | Quote

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 :rolleyes:

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

Perhaps, pasting the value into another application could be one reason.

Reply | Quote

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.

Reply | Quote