Unlike VB6, VBA does not have the ClipBoard object.
Instead of VB6's ClipBoard object, you can use MSForms 2.0's DataObject object.
To use the object , you should set reference to 'Microsoft Forms 2.0 Object Library'.
If your VBA project has a UserForm, the reference is automatically set.
Then you can use the following code:

 Dim Clip as MSForms.DataObject
 Set Clip = New MSForms.DataObject

You may think that you can write the following code to use the object without the reference.

 Dim Clip as Object
 Set Clip = CreateObject("MSForms.DataObject")

Unfortunately the code does not run 
because "MSForms.DataObject" is not registered under the 'Classes' registry key.
Here's a workaround snippet.



'You can use a sample code for this site freely.
'Though this is not a duty, I am grateful that you describe that you reffered this site(http://akihitoyamashiro.com/en/VBA/), 
'when you present this sample code in your web site.

Sub Main()
    'This is ok when you use a UserForm.
    'Dim Clip As MSForms.DataObject
    'Set Clip = New MSForms.DataObject

    'You cannot write a code like this.
    'Dim Clip As Object
    'Set Clip = CreateObject("MSForms.DataObject")

    'This is a workaround when you do not use a UserForm.
    Dim Clip As Object
    Set Clip = CreateObject( _
     "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    
    Clip.GetFromClipboard
    MsgBox Clip.GetText, vbOKOnly, "What's in the ClipBoard?"
    
    Clip.SetText "Test"
    Clip.PutInClipboard
    
    Set Clip = Nothing
End Sub


Top Page