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:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
1 2 3 4 |
' 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:
1 2 3 |
' 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:
1 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
[…] googletag.cmd.push(function() { googletag.display('div-gpt-ad-1486744346002-0'); }); Good one, Barry… I use your version too. In order for it to work, you'd either have to insert a userform into your VBA Project or add a reference to Microsoft Forms 2.0 Object Library. Andy, here's an article to catch you up: Copy and Paste in VBA | Byte Comb […]