Late bound MSForms.DataObject

Expanding on a previous theme (Transposing arrays using in-memory controls. You remember that, right?), I frequently see posts relating to getting text from the clipboard in VBA – which unfortunately does not have the VB6 Clipboard object to play with. The standard method, rather than messing about with the Windows API if you don’t have to (sometimes there’s no avoiding it), is to use the MSForms.DataObject.

 

For example, to copy the current cell’s contents to the clipboard, you might use this:

Public Sub CopyCellContents()
Dim objData As MSForms.DataObject
On Error Resume Next
Set objData = New MSForms.DataObject

' copy current cell formula to clipboard
With objData
.SetText ActiveCell.Formula
.PutInClipboard
End With
End Sub

Of course, this requires setting a reference to the Microsoft Forms 2.0 Object Library so then you have to explain how to set a reference (or tell the poster to insert a userform and then remove it). Being lazy, I spent a lot of time figuring out how to late bind the code so that I didn’t have to keep explaining the references, so here’s the late bound version:

Public Sub CopyCellContents()
Dim objData As Object
On Error Resume Next
' this is a late bound MSForms.DataObject
Set objData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

' copy current cell formula to clipboard
With objData
.SetText ActiveCell.Formula
.PutInClipboard
End With
End Sub

Enjoy! 🙂

6 thoughts on “Late bound MSForms.DataObject

  1. ZygD says:

    A very nice method of late binding using those curly braces and a code within! Never seen this kind of late bind. I suspect I could use this to set a reference to any library. Could you explain what is it? How to find that code if I know where is the file located?

    • ZygD says:

      Just found the answer myself. The code is called CLSID (class identifier). To find it it’s required to know both, the dll name and the “full name” of the library. It’s needed to search the registry for that particular dll (in our case FM20.DLL) and pay attention to the default value of the key (the “full name” of the library). In our case it should be “Microsoft Forms 2.1 DataObject”. It’s important because there are more CLSIDs connected to the same dll, but the value of those keys (and therefore references) would be different than “Microsoft Forms 2.1 DataObject” (e.g. the value could be “Microsoft Forms 2.0 ListBox” and then it would reference a ListBox object). After finding the correct value, just use the name of the key.

      • Alan Elston says:

        Hi ZygD,
        It sounds very useful what you are describing. But I do not follow your explanation. Could you give a bit more detail into exactly how you found the curly braces text string.
        I can get the info you said was needed from setting a reference to the Library and then running the code below ( ( Code I have from snb ) .
        Thanks
        Alan
        ‘_…..
        Sub NameAndPath() ‘ Note: http://imgur.com/qn1RcYT Options — Trust Center — Trust Center Settings — Macro Settings — Developer Macro Settings — tick Trust access…
        Dim it As Variant, TxtInfo As String
        For Each it In ThisWorkbook.VBProject.References
        TxtInfo = TxtInfo & “Description:” & vbTab & it.Description ‘ “Full Name”
        TxtInfo = TxtInfo & vbLf & “FullPath:” & vbTab & vbTab & it.fullpath ‘Last bit of this gives a “DLL” or similar
        TxtInfo = TxtInfo & vbLf & “GUID:” & vbTab & vbTab & it.GUID & vbLf & vbLf ‘Not to sure what this is, has the form of but is different to what we are looking for
        Next
        Range(“A1”).Value = TxtInfo
        MsgBox TxtInfo
        End Sub

  2. Alan says:

    _ – This was particularly useful as I could not actually find the Microsoft Forms 2.0 Object Library in my VB Editor — Tools – References. ( I am using XL2007 ). So could not do the Early binding anyway!
    (….. BTW, eventually I managed to get it to be displayed in my VB Editor — Tools – References.
    _ – I had to add that manually: VB Editor — Tools — References — Browse — and find FM20.DLL file under C:\WINDOWS\system32 and select it — Open — OK.
    Alan

      • Alan Elston says:

        Thanks Rory, I missed your Response here ( – think the Notification Box is not checked by default, which might be better.. ). But your Blogs are useful enough that one tends to come back often enough anyway.. 😉
        I did not understand what you meant, – so i tried…
        Ahh – I get it – I guess the Ms Forms Library is needed by a UserForm**, so VBA automatically adds the reference, But then just does not bother to take it off. ( **The word “Form” could be a clue there Lol ..Duh!! )
        Neat trick,( but not the quickest way I Think ??)
        But well worth knowing!
        Thanks
        Alan

Leave a Reply

Your email address will not be published. Required fields are marked *