MSForms DataObject Bugs

Question

This is in reference to the MSForms DataObject object (from the Microsoft Forms 2.0 Object Library) in Office 2013.

The DataObject exhibits a lot of "strange" behavior. I would like to determine whether this behavior is a bug or is by design.

First, the DataObject seems to be unable to Store and later Restore plain text to the clipboard. A "Not Implemented" error occurs when this is attempted.

Steps to reproduce this behavior:

1. Copy the code below and paste it into a new VBA code module.
2. Add a reference to the Microsoft Forms 2.0 Object Library (if it is not already referenced)
3. Copy some plain text (e.g. from Notepad etc)
4. Run the VBA method "StoreClip" 
5. Run the VBA method "RestoreClip"

Option Explicit
Private m_Data As MSForms.DataObject
Private Const FMT_TEXT& = 1

Public Sub StoreClip()
    Set m_Data = New DataObject
    With m_Data
        .GetFromClipboard
        If .GetFormat(FMT_TEXT) Then
            Debug.Print "Stored Text: " & .GetText(FMT_TEXT)
        End If
    End With
End Sub

Public Sub RestoreClip()
    If m_Data Is Nothing Then
        Debug.Print "Nothing Stored"
    Else
        With m_Data
            If .GetFormat(FMT_TEXT) Then
                Debug.Print "DataObject Contents: " & .GetText(FMT_TEXT)
            End If
            .PutInClipboard
        End With
    End If
End Sub

The above steps will produce the following output (assuming you copied the plain text "Hello World!")

Stored Text: Hello World!
DataObject Contents: Hello World!

This output proves that the DataObject was able to retrieve and store the text contents from the clipboard. However, when the call to PutInClipboard is made, a runtime error occurs: (80004001) DataObject:PutInClipboard Not Implemented.

Since the text was obviously stored correctly on the DataObject (as demonstrated by the output "DataObject Contents: Hello World!" in the RestoreClip routine), it is suprising that an error occured when writing this seemingly simple data to the clipboard.

The DataObject also exhibits other behavior that is at best undocumented and possibly the result of a bug. My expectation from reading the MSDN documentation about the DataObject suggests that the GetFromClipboard method retrieves the data from the clipboard (at least the Text-formatted data) and that the GetText function returns the data in string form. This is indeed the observed behavior. However I also expected that AFTER  GetFromClipboard is called, any further changes to the Windows Clipboard would NOT affect the state of the DataObject. However this assumption is NOT correct, as shown by the following procedure:

1. Paste the following code into a new VBA code module
2. Copy some plain text (e.g. "Hello")
3. Run the routine ShowClipText from the immediate window.
4. Copy some different plain text (e.g. "Goodbye")
5. Run the routine ShowDataObjectText from the immediate window.

Option Explicit
Private Const FMT_TEXT& = 1
Private m_Data As MSForms.DataObject

Public Sub ShowClipText()
    Set m_Data = New DataObject
    m_Data.GetFromClipboard
    If m_Data.GetFormat(FMT_TEXT) Then Debug.Print m_Data.GetText(FMT_TEXT)
End Sub

Public Sub ShowDataObjectText()
    If m_Data Is Nothing Then Debug.Print "NOT CREATED YET"
    If m_Data.GetFormat(FMT_TEXT) Then Debug.Print m_Data.GetText(FMT_TEXT)
End Sub

The expected output of these steps is "Hello" for both routines, since the DataObject is NOT directly modified in the second routine. However the actual output of these steps is "Hello" for the first routine and "Goodbye" for the second. This demonstrates that the call to GetFromClipboard establishes a link to the Clipboard and subsequent changes to the Clipboard affect the output of GetText despite the fact that no additional calls to GetFromClipboard are made after the initial one. This behavior is unexpected, but an easy workaround is to change the first routine to:

Public Sub ShowClipText()
    Set m_Data = New DataObject
    m_Data.GetFromClipboard
    m_Data.SetText m_Data.GetText 'Disconnects the Clipboard
    If m_Data.GetFormat(FMT_TEXT) Then Debug.Print m_Data.GetText(FMT_TEXT)
End Sub

The line m_Data.SetText m_Data.GetText serves to sever the connection from the DataObject to the clipboard.

A distinct side-effect of the above work-around is that any other data formats stored on the DataObject are now lost. This makes it impossible to use the DataObject to reliably store the Clipboard state and then restore it at a later time. 

Office Applications (Excel, Word, etc) make heavy use of the windows Clipboard, and simple operations in those programs can cause the contents of the Clipboard to be inadvertently lost. Moreover, it is not possible to accomplish some tasks in these programs using VBA unless methods such as Selection.Copy etc are used, even if the desired action is NOT to affect the clipboard. Therefore a useful paradigm for VBA routines in the applications would be:

1. Backup the Clipboard State
2. Perform VBA processing which may alter the state of the clipboard.
3. Restore the Clipboard to its original State.

However, it appears that this paradigm cannot be achieved using the DataObject because after calling GetFromClipboard, the DataObject remains linked to the Clipboard. So code like this will NOT work:

Public Sub DoSomeWork()
    Dim oData As DataObject
    Set oData = New DataObject
    oData.GetFromClipboard
    'Do some processing that may affect the clipboard
    oData.PutInClipboard
End Sub

The above procedure fails to restore the clipboard because when the line PutInClipboard is executed, the DataObject's state is already identical to the current Clipboard state (NOT the previous state) because it is either (A) updated when the contents of the clipboard are changed or (B) never actually stored the clipboard state in the first place, but rather maintained a link to the clipboard. In either case (A or B), the DataObject cannot restore the original state. Using the above mentioned work-around we can achieve at least the ability to backup & restore text:

Public Sub DoSomeWork()
    Dim oData As DataObject
    Set oData = New DataObject
    oData.GetFromClipboard
    If oData.GetFormat(FMT_TEXT) Then m_Data.SetText m_Data.GetText
    'Do some processing that may affect the clipboard
    oData.PutInClipboard
End Sub

But any other formats are lost (such as other data or formats associated with a copied Selection in Excel, Word etc). So if the user had copied an image from another application, it would NOT be backed up or restored.

Is this the expected behavior of the DataObject?

If so, is it possible to Backup/Restore the clipboard state during VBA processing (e.g. that takes a few milliseconds to excute)?

P.S.

Just for the sake of thoroughness I should point out the corresponding behavior of the DataObject with SetText/PutInClipboard. When PutInClipboard is called, any subsequent modification of the DataObject is propogated to the clipboard, even if PutInClipboard is never called again.

I.e. after running the following code, the clipboard will contain the text "Goodbye" (not "Hello").

Public Sub TestSetText()
    Dim oData As DataObject
    Set oData = New DataObject
    
    oData.SetText "Hello"
    oData.PutInClipboard 'Text is explicitly placed in the clipboard

    oData.SetText "Goodbye" 'DataObject contents are set, but NOT explicitly sent the the clipboard.
End Sub

So if SetText is called after PutInClipboard, the observed behavior is as though PutInClipboard established a link between the DataObject and the clipboard and subsequent modification of the DataObject in turn affects the clipboard, even though this is not explicitly indicated by the code.

Thanks

Answer 1

Hi,

According to your description, I think your issue is more related to the DataObject object of the Microsoft Forms 2.0 Object Library.

Since it is more related to the feature of VBA rather than Office development, I'd like to move it to VBA forum.

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thanks for your understanding.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Simplified but maybe the following answers most of your questions, in particular why PutInClipboard fails without first .Settext and why apparently Settext can be used multiple times without PutInClipboard 

Private mObjData As DataObject

Sub test1()
    Set mObjData = New DataObject

    With Range("a1")
        .Value = "AAA"
        .Copy
    End With

    With mObjData
        .GetFromClipboard
        .SetText .GetText, "copy1"
        .SetText "BBB", "copy2"
         .PutInClipboard ' not necessary to do both here
    End With

End Sub

Sub test2()
' test for mObjData exists and formats ommitted
    With Range("a1")
        .Value = "CCC"
        .Copy ' Range("a2")
    End With

    With mObjData
        Debug.Print .GetText("copy1") ' AAA
        Debug.Print .GetText("copy2") 'BBB
	Debug.Print .GetText ' CCC
    End With
End Sub

 

Be aware the Databoject only supports text. For other formats either use APIs for the Windows clipboard (eg for pictures) or make use of the Office clipboard (copy/paste to a region in a dummy workbook or document).

If the objective is to restore the clipboard with its original text, simply store the contents to a variable and put back when done. 

PS, the code assumes VBA in Excel, if not remove the Range.copy stuff and manually copy AAA & CCC respectively before running the macros

Answer 3

Still the whole DataObject does not do what it's expected to on a Mac.