Copy and Paste in VBA

It can be really useful 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. In this article I illustrate how to create simple Copy and Paste functions using the Microsoft Forms DataObject class.

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:

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:

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:

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:

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:

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.