Copy and Paste in VBA

March 4, 2013 in VBA

It can be really handy to have simple string Copy and Paste functions in VBA. I use them frequently, as during development on MS Access applications I often use the Immediate window almost like an interactive command prompt. Sometimes I want to copy the output of a function directly to the clipboard, or fetch (Paste) data from the clipboard as input to a function.

Using Microsoft Forms DataObject

The most common way to implement Copy and Paste is to add a reference to Microsoft Forms 2.0 and use the DataObject class. Microsoft Forms often does not show up in the list of available references by default, so you will likely have to manually browse and select FM20.dll.

Once you have a reference to Microsoft Forms 2.0 it is super easy to create simple Copy and Paste methods:

Public Sub Copy(ByVal Expression As String)
    With New DataObject
        .SetText Expression
        .PutInClipboard
    End With
End Sub

Public Function Paste() As String
    With New DataObject
        .GetFromClipboard
        Paste = .GetText
    End With
End Function

Late Binding to DataObject by CLSID

The down side of the Microsoft Forms approach is that it requires you to add a reference to another library just for these two little functions. It’s a moot point if you’re working with UserForms in an Office application like Excel or Word; this already requires a reference to Microsoft Forms. That’s not the case in Access, however, and it’s good to avoid extra references if possible.

With many COM objects, including familiar scripting objects like RegExp, Dictionary, or FileSystemObject, you can use the CreateObject function to create a new object from the class name:

' Late binding a FileSystemObject; no reference to 
' Microsoft Scripting Runtime required:
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

This is called late binding. For a thorough discussion of binding see Using early binding and late binding in Automation. It works because Windows has an entry in the registry that associates the string “Scripting.FileSystemObject” with the dll that implements this object. Unfortunately MSForms does not install named class entries in the registry for its objects, so late binding by name won’t work with DataObject:

' This DOESN'T work:
Dim dObj As Object
Set dObj = CreateObject("MSForms.DataObject")

Thankfully there is a way around this: if you know the CLSID associated with the COM object you can use the CreateObject function directly on the CLSID with the following syntax:

Set myObj = CreateObject("new:{<the CLSID>}")

The CLSID for the Microsoft Forms DataObject is 1C3B4210-F441-11CE-B9EA-00AA006B1A69. So you can remove your reference to Microsoft Forms and keep your Copy and Paste methods by replacing New DataObject with CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"). To make the code clearer I actually first define the CLSID string as a constant with a meaningful name:

Const DATAOBJECT_BINDING As String = "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"

Public Sub Copy(ByVal Expression As String)
    With CreateObject(DATAOBJECT_BINDING)
        .SetText Expression
        .PutInClipboard
    End With
End Sub

Public Function Paste() As String
    With CreateObject(DATAOBJECT_BINDING)
        .GetFromClipboard
        Paste = .GetText
    End With
End Function

Conclusion

You can create simple Copy and Paste methods in VBA using the Microsoft Forms DataObject object. To use the DataObject methods you’ll have to either add a reference to Microsoft Forms (FM20.dll) or use late-binding by CLSID.