Get String from VBA DataObject object after GetText

Question

I'm trying to do some string manipulation (trim and split) with text data received from a TextBox in UserForm Excel Visual Basic VBA. But I can't get the String / text data obtained from user clipboard stored in the Microsoft Form 2.0 DataObject.

I tried Dim s As String = CType(strPaste, String) but it says expected end of statement.

If I only do s = CType(strPaste, String) then it's a syntax error.

I've also poked around the Microsoft Forms documentation but there is no list of methods associated with DataObject.

This is what I have.

Dim dataObj As MSForms.DataObject
Set dataObj = New MSForms.DataObject
dataObj.GetFromClipboard
strPaste = dataObj.GetText()

the Variant Type of strPaste is 0, which is an object. I'd like it to be a String.

Answer 1

If you use early binding, you can see the methods in the Object Browser.

Here are some examples of putting stuff onto the clipboard, and reading from it.

Option Explicit
Sub foo()
    Dim dataObj As MSForms.DataObject
    Dim getStr As String
    Const putStr As String = "This went to the clipboard"

    Set dataObj = New MSForms.DataObject
    dataObj.SetText putStr
    dataObj.PutInClipboard

    dataObj.GetFromClipboard
    getStr = dataObj.GetText

    MsgBox getStr
End Sub

And something placed on the clipboard by an ordinary copy operation:

Sub foo2()
    Dim dataObj As MSForms.DataObject
    Dim R As Range
    Dim getStr As String

Set R = Cells(1, 1)
With R
    .Value = "abcxyz^123"
    .Copy
End With

Set dataObj = New MSForms.DataObject

dataObj.GetFromClipboard
getStr = dataObj.GetText

MsgBox getStr
End Sub

You may also find Microsoft Forms Reference of value, and also the link to the Examples which shows some other examples of using these methods, and interacting with a text box.