0

I posted a question earlier about an error raised by the MSForms DataObject as accessed from Microsoft Office Excel 2013 VBA. As I was writing that post, I discovered other odd behavior that is even more concerning.

Perhaps my perceptions about the DataObject are wrong, but if so, the MS Office documentation is very misleading. My expectations are these:

If I create a DataObject and use the GetFromClipboard method, it should load whatever is on the clipboard into the object. The data stored in the object should NOT change until I perform some other action on the object (such as calling Clear, SetText, etc)

So if I perform the following:

  1. Manually copy some Text onto the windows clipboard.
  2. Create a DataObject and call GetFromClipboard
  3. Perform some VBA operations that change the windows Clipboard (but do NOT access the DataObject)
  4. Call GetText on the DataObject

I would expect that the text I retrieve in step 4 is the same that I placed on it in #2.

However, this IS NOT the case, as my example code below shows.

Instructions for testing:

  1. Copy this code into a standard code Module in an office application.
  2. Copy some text (e.g. from Notepad)
  3. Run the method "TestDataObject"
  4. When prompted, copy some different text.
  5. When prompted a second time, copy some other different text.

(You may need to add a reference to "Microsoft Forms 2.0 Object Library" which you can do quickly by simply adding a UserForm to your VBA project, because this adds the reference automatically)

'Copy some text before running this.
Public Sub TestDataObject()
    Dim oData As DataObject
    Set oData = New DataObject

    'This is BEFORE GetFromClipboard is called, so 
    ' the DataObject currently has NO text in it.
    If oData.GetFormat(1) Then
        Debug.Print "1) Contents: " & oData.GetText(1)
    Else
        'This line will be printed.
        Debug.Print "1) Contents: (NONE)"
    End If

    oData.GetFromClipboard

    'Now the DataObject has some text, and it will be printed below.
    If oData.GetFormat(1) Then Debug.Print "2) Contents: " & oData.GetText(1)

    MsgBox "Copy some Text"

    'If you copied NEW text, then it will be shown below (instead of the original data)
    If oData.GetFormat(1) Then Debug.Print "3) Contents: " & oData.GetText(1)

    MsgBox "Copy some different Text"

    'If you copied other NEW text, then it will be shown below (instead of the original data)    
    If oData.GetFormat(1) Then Debug.Print "4) Contents: " & oData.GetText(1)

End Sub

Assuming the text I copied before running the sub was "Hello", I expect this to print out the following, regardless of what I manually copy while the method is running:

1) Contents: (NONE)
2) Contents: Hello
3) Contents: Hello
4) Contents: Hello

But the actual output is this:

1) Contents: (NONE)
2) Contents: Hello
3) Contents: World
4) Contents: Goodbye

(Assuming I copied "World" when prompted the first time and "Goodbye" when prompted the second time.)

Note that the Msgbox is NOT causing this behavior. You could use a DoEvents-Loop for a couple seconds instead if you like. Or perform Copy/Paste operations with a Range object or other Excel objects as shown below:

Public Sub TestDataObject()
    Dim oData As DataObject: Set oData = New DataObject

    ThisWorkbook.ActiveSheet.Range("A1").Select
    Selection.Value = "Hello"
    Selection.Copy

    If oData.GetFormat(1) Then
        Debug.Print "1) Contents: " & oData.GetText(1)
    Else
        Debug.Print "1) Contents: (NONE)"
    End If

    oData.GetFromClipboard

    If oData.GetFormat(1) Then Debug.Print "2) Contents: " & oData.GetText(1)
    Selection.Value = "World"
    Selection.Copy
    If oData.GetFormat(1) Then Debug.Print "3) Contents: " & oData.GetText(1)
    Selection.Value = "Goodbye"
    Selection.Copy
    If oData.GetFormat(1) Then Debug.Print "4) Contents: " & oData.GetText(1)
End Sub

This is not specific to Excel. The same code work in Word, except that you have to change the Selection/Copy code to this (e.g.):

' Code to copy text in Word
Selection.Text = "World"
Selection.Copy

So my question is: Is this behavior expected or is it a bug? I am using Office 2014 64-bit. Does this also happen in 32-bit Office? Maybe it is just a 64-bit bug.

Thanks!

Improve this question

3 Answers 3

Reset to default
2

I can replicate (32-bit Office 2010, Win7)

Sub Tester()
Dim d As New DataObject, d2 As New DataObject

    d2.SetText "first"
    d2.PutInClipboard

    d.GetFromClipboard
    Debug.Print d.GetText  '--> "first"

    d2.SetText "second"
    d2.PutInClipboard

    Debug.Print d.GetText  '--> "second"

    d2.SetText "third"
    d2.PutInClipboard

    Debug.Print d.GetText  '--> "third"

End Sub

I'd have to guess that GetFromClipboard only establishes a link by reference to the clipboard, not by value. So whenever you call GetText it actually pulls directly from the clipboard, not from a copied cache held in the DataObject.

If you need a stable copy of the clipboard content which would be unaffected by subsequent copy operations then you'd have to store it in a (e.g.) String variable.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for taking the time to run through this, Tim. I think you are right about the link by reference to the clipboard. I had surmised that myself, but it is very unfortunate because it means there is no reliable way to store the contents of the clipboard during an operation and then restore it later. I thought the whole purpose of the DataObject was to do this, but apparently that isn't the case.
Storing the data in a string variable is not an acceptable solution, because the clipboard can contain arbitrary data, including images, OLE objects, binary data and text with different encodings. That's why we need a DataObject to interact with the various formats.
Reliably storting and restoring the clipboard would require enumerating all of the formats and storing each (e.g. in a byte array or string). This isn't possible with the DataObject because it doesn't provide methods to retrieve data stored in a format other than text. And to my knowledge it isn't possible to even enumerate the formats on the DataObject. So one must have a list of expected/possible formats, and this isn't possible because the "Format" parameter is user-defined and can be arbitrary.
I found a solution which basically opens the clipboard during operations that were causing issues for me. Which after stepping through my code was creating/deleting worksheet functions. freesoftwareservers.com/display/FREES/…
1

I encountered a similar problem with MS Access VBA: My routine starts a software which automatically copies text contents from my smartphone's clipboard, then makes a loop until the desired text is available in the pc's clipboard. The text (special bill format) comes from a QR scanning app on my smartphone.

However, I always got errors, sometimes with DataObj.GetFromClipboard and sometimes with ClipText = DataObj.GetText(1) . When I resumed the procedure with <F5> or <F8> everything ran then without any error.

' …
Dim ClipData As New DataObject          'object to use the clipboard
ClipData.SetText Text:="×××"
ClipData.PutInClipboard                 'overwriting old text contents
' …
Application.Echo (False)
On Error Resume Next
For WarteZeit = 1 To 80
    DataObj.GetFromClipboard                ' get data from the clipboard
    If DataObj.GetFormat(1) = True Then     ' Text im Clipboard
        ClipText = DataObj.GetText(1)       ' get clipboard contents
        If Left(ClipText, 3) = "SPC" And ((InStr(ClipText, Chr(10)) = 4) _
            Or (InStr(ClipText, Chr(13)) = 4)) Then
            Exit For
        End If
    End If
    Sleep 750                               ' 80 × 750 ms = 1 Minute
Next WarteZeit
On Error GoTo 0
Application.Echo (True)
' …

The clipboard is obviously quite slow and poorly multitasking. While the clipboard is active with loading new data from one software, it still contains the old data or is not accessible at all leading to errors (no Windows error number). Above code includes my solution to the problem: one just has to be patient and to make sure that the new contents have reached the clipboard before calling them back.

Improve this answer

Comments

0

Gruß Gott :-)

I wonder if the phenomena discussed here could be explained slightly differently:

It seems to me that the data object and the Windows Clipboard are somehow linked quite closely, but in a way that maybe, either, no one knows anymore exactly, or, those that do are not telling as it is propriety information . In addition possibly there are some rules , coding, or similar that govern how the data object and Windows Clipboard copes with the spaghetti of different Clipboards ( Office, Windows, Excel etc… ) and different versions of copied data therein. I doubt anyone in the meantime is capable of unravelling that spaghetti to make any clear sense of it. Part of the monster that is “clipboards” is a true OLE object , the Data object. We have access to that. Our Data object is perhaps more of a hooked Events thing which monitors the Windows Clipboard. We can set things that may then be used. We can influence with the Data object the behaviour of the clipboards

My experimenting suggest to me that there is some registers not directly accessible to us, and which we can influence and which is somehow strongly related to what is in the Window Clipboard. I think we are only privy to some of its behaviour. I am thinking that .GetText() returns the last thing added in to a register.

Some examples to help shed some explanation to the strange behaviour:

Sub Copying()

This routine initially fills Office, Windows, and Excel Clipboards via an Excel range copy .PutInClipboard and .GetText() would fail initially as the registers are not filled to which they refer. .GetFromClipboard somehow now adds something to a register in the data object; I think that is getting data from the Windows Clipboard. I clear the Office and Excel Clipboard to demonstrate they are not used further in the code. However I note that I must do the .GetFromClipboard before clearing those: clearing in this case either of the Office or Excel clipboard seams to make the Windows clipboard empty. I am not sure exactly why this should be the case, other than the spaghetti of dependencies in the clipboards somehow coming into play at this initial point ### I do not think that is generally true that we can empty the Windows clipboard by emptying the Office or Excel Clipboards .GetText() now gives me the value from the copied cell. But I believe this is telling me what I last added to a register in the data object. Now I use .SetText , and I believe I am adding something again to a register in the data object. .GetText() now gives me the text that I added, “New Text” I am proposing that at this point, in this particular code, I still have the value from the cell as coming from a clipboard and this is in a main register. I am not too sure where/ how the “New Text” is at this point. ( There are cases I have seen where this “New Text” would replace the text in the windows clipboard by a .PutInClipboard. This does not happen in this routine, strangely) If I attempt a paste at this point, it would error. I think this is reasonable: At this point I think I have no data in the Windows Clipboard. ### In this particular example the windows clipboard has been emptied by the code lines emptying the Office or Excel clipboard: This is not usually the case ### .PutInClipboard code line does now not error.
Nor does the paste a few lines down.
What one might not have expected is that the value pasted is not the “New Text”, but in fact, the original text from the cell ( And note also that this time we could clear the Office and Excel clipboards – with or without these lines the result is the same - Code line ActiveSheet.Paste Destination:=ActiveSheet.Range("C1") is apparently using the Windows Clipboard )

Option Explicit '   https://stackoverflow.com/questions/25091571/strange-behavior-from-vba-dataobject-gettext-returns-what-is-currently-on-the-c
' YOU NEED routine, ClearOffPainBouton() - get here, or just comment out Call s to it : --- https://pastebin.com/5bhqBAVx , http://www.eileenslounge.com/viewtopic.php?f=30&t=31849&start=20#p246838  http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11019&viewfull=1#post11019   --- it will be best to copy entire coding here  to a seperate code module
Sub Copying()
Range("C1").Clear
Dim DtaObj As Object '  Late Binding equivalent'                                                                                    If you declare a variable as Object, you might be late binding it.  http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/   ... if you can ....  http://www.eileenslounge.com/viewtopic.php?f=30&t=31547&start=40#p246602
 Set DtaObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")                                                             ' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
Let Range("A1").Value = "CellA1": Range("A1").Copy                ' This probably fills the Excel Clipboard, the Window Clipboard and the Office Clipboard
' DtaObj.PutInClipboard '                                         ' This will fail, DtaObj clear
' MsgBox Prompt:="DtaObj.GetText(1) is   " & DtaObj.GetText()     ' This will fail, DtaObj clear
 DtaObj.GetFromClipboard                                          '
 Let Application.CutCopyMode = False ' This clears the  Excel Clipboard
 Call ClearOffPainBouton             ' This clears the Office Clipboard
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText() '  --- "DtaObj.GetText() is  CellA1"
 DtaObj.SetText Text:="New Text" '
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText() '  --- "DtaObj.GetText() is  New Text"
' ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")          ' This would error here
 DtaObj.PutInClipboard
 Let Application.CutCopyMode = False ' This clears the  Excel Clipboard
 Call ClearOffPainBouton             ' This clears the Office Clipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")     '  --- "CellA1"  is pasted in cell C1
End Sub

Sub Copying2() This takes the last Sub further .. here an short walk through the coding

' New bit below In the next 6 lines I have a feeling that the .PutInClipboard and the .GetFromClipboard are not doing much , if anything at all. Possibly the Excel knows that I have not changed any data, so it ignores attempts to do things that usually would be applied to some new data.

' Manual copy You are prompted to copy anything. You should do this Immediately after, .GetText() has not changed, but you get now pasted out the value that you copied. This suggest once again that code line ActiveSheet.Paste Destination:=ActiveSheet.Range("C1") is apparently using the Windows Clipboard After using .GetFromClipboard , you will now find that .GetText() returns the value that you copied

Attempt to use .SetText to add to windows Clipboard We Set the Text (do a .SetText) and do a .PutInClipboard. But as we have seen, this does not change the clipboard on this case, and the last thing we manually copied is still there to be pasted out I try .Clear The next two lines would error. This makes sense: I have emptied the registers. The third line erroring is less obvious. It suggests that .Clear clears the Windows Clipboard. I am not sure if that would always be the case. The final code lines are successful in adding, via .SetText , something to the windows clipboard. My explanation is that as all registers are empty, the value given via .SetText is the only thing there, it is added to an empty thing so that is what is in there and available to be Put in the Clipboard. In this case, now, a second attempt in adding via SetText is also successful. Why it should be in this case is slightly puzzling. 

Sub Copying2()
Range("C1").Clear
Dim DtaObj As Object '
 Set DtaObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Let Range("A1").Value = "cellA1": Range("A1").Copy     '      This fills the Excel Clipboard, the Window Clipboard and the Office Clipboard
' DtaObj.PutInClipboard '
' MsgBox Prompt:="DtaObj.GetText(1) is   " & DtaObj.GetText()
 DtaObj.GetFromClipboard
 Let Application.CutCopyMode = False                        ' This clears the  Excel Clipboard
 Call ClearOffPainBouton                                    ' This clears the Office Clipboard
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText()
 DtaObj.SetText Text:="New Text"
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText()
' ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")    ' This would error here
 DtaObj.PutInClipboard
 Let Application.CutCopyMode = False                        ' This clears the  Excel Clipboard
 Call ClearOffPainBouton                                    ' This clears the Office Clipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")
' New bit below - first 6 lines are not doing much if at all
 Range("C1").Clear
 DtaObj.PutInClipboard
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText()
 DtaObj.GetFromClipboard
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText()
 ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")
' Below we manually copy
 MsgBox prompt:="Please copy anything from anywhere , before hitting  OK  "
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText() ' has not changed
 Range("C1").Clear
 ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")     ' pastes what you copied
 DtaObj.GetFromClipboard
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText() ' now shows what you copied
' Attempt to use  SetText  to add to windows Clipboard
 DtaObj.SetText Text:="New Text To Paste"
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText() ' reflects the added text,  "New Text To Paste"
 DtaObj.PutInClipboard                                      ' This either does nothing or once again puts what you copied there - as it already is, then Excel may know you already did this so does nothing
 ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")     ' pastes what you copied
 DtaObj.Clear
' MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText()' This would error - the registers are empty
' DtaObj.PutInClipboard ' This would also error - there is nothing in the registers to fill the clipboard with
' ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")    ' pastes what you copied
 DtaObj.SetText Text:="New Text To Paste"
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText() ' reflects the added text,  "New Text To Paste"
 DtaObj.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")     ' pastes "New Text To Paste"
 DtaObj.SetText Text:="second Text To Paste"
 MsgBox prompt:="DtaObj.GetText() is   " & DtaObj.GetText() ' reflects the added text,  "New Text To Paste"
 DtaObj.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")     ' pastes "New Text To Paste"
End Sub

_.__________________________________________

Coming back to the original question .. to explain the strange behaviour … just my take…

_ …... Q: Is this behaviour expected or is it a bug?

_ ....A

I think I expect it if my assessment of the situation above is correct: Doing a manual copy, or a copy via coding, makes an entry in a register in the data object. This is what .GetText() appears to get – the last thing entered, be it via a copy or by a .SetText. The .SetText won’t have any effect on a .PutInClipboard unless the register is empty. I guess the reason why it is done like this is somehow related to how you can use the format thing to effectively have multi text. What is required to empty these registers is not clear always, that is to say , it is not clear if .Clear always is necerssary. Clearly, the clipboard remains a mystery to everyone I think.

@ Tim Williams.

Sub Tester()

I think the results from your Sub Tester() are constant with my take on the situation: If you have nothing in the windows clipboard , you will need your code lines of d2.PutInClipboard and d.GetFromClipboard to get d.GetText to not error the first time. This will have a similar effect to doing d.SetText in terms of initialising a register, that is somehow related to d. ( If you have something in the Windows clipboard, then you do not need d2.PutInClipboard to prevent d.GetText from erroring , but it will return you what is in the windows clipboard. ) You do not ever need the second and third d2.PutInClipboard as they never have any effect on your coding: As long as you have your first d2.PutInClipboard, then you will get your shown results. My take on that is that .GetText is indicating the last thing it knows about that was added. You will find however, that if you copy something to the windows clipboard and delete all your 3 d2.PutInClipboard lines, then d.GetText will always tell you what is in the window clipboard. Somehow in this case it has lost its awareness of what d2 is doing. To confirm this you can try copying something to the Windows Clipboard, then run Sub Testies3() , which has the first d2.PutInClipboard removed, but the second and third included. Your routine will in this case tell you all the time what is in the windows clipboard, at least if you copy from Word… . ( …. if you copy something from inside Excel…. Then the first time you run that last routine, Sub Testies3() , you will get some interesting results. Something is managing to make d aware of what d2 is doing, the first time you run the code after copying something from Excel. It also happens like that if you copy something out of a text datei, or a browser, but not if you copy from Word ) If you try to understand fully what is going on in the spaghetti of interdependencies in the Clipboards then you will go mad…

Sub Tester()
Dim d As New DataObject, d2 As New DataObject
 d2.SetText "first": d2.PutInClipboard
 d.GetFromClipboard
 Debug.Print d.GetText  '--> "first"

 d2.SetText "second": 'd2.PutInClipboard
 Debug.Print d.GetText  '--> "second"

 d2.SetText "third" 'd2.PutInClipboard
 Debug.Print d.GetText  '--> "third"
End Sub
Sub Testes2() 'COPY SOMETING before running this
Dim d As New DataObject, d2 As New DataObject
 d2.SetText "first": 'd2.PutInClipboard
 d.GetFromClipboard
 Debug.Print d.GetText  '--> "What you copied"

 d2.SetText "second": 'd2.PutInClipboard
 Debug.Print d.GetText  '--> "What you copied"

 d2.SetText "third" 'd2.PutInClipboard
 Debug.Print d.GetText  '--> "What you copied"
End Sub
Sub Testies3() 'COPY SOMETING before running this
Dim d As New DataObject, d2 As New DataObject
 d2.SetText "first": 'd2.PutInClipboard
 d.GetFromClipboard
 Debug.Print d.GetText  '--> "What you copied"

 d2.SetText "second": d2.PutInClipboard
 Debug.Print d.GetText  '--> "What you copied"

 d2.SetText "third": d2.PutInClipboard
 Debug.Print d.GetText  '--> "What you copied"
End Sub
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.