You can access and retrieve the data in the clipboard into a variable in Excel VBA, using the Microsoft Forms library. On top of copy and paste the code below, you must follow the these steps and activate Microsoft Forms 2.0 Object Library for the code to work. You only need to do it once as long as this macro stays in the same file.
- On the Macro editor menu bar on the top, click on “Tools”
- In the menu, click on “References”
- Click the “Microsoft Forms 2.0 Object Library”
- Press OK
If you don’t follow these steps, you will get a “Compile error: User-defined type not defined” error… Lots of people have been stumped on this part.
Public Clipboard As New MSForms.DataObject Sub readClipboard() 'Tools -> References -> Microsoft Forms 2.0 Object Library 'of you will get a "Compile error: user-defined type not defined" Dim DataObj As New MSForms.DataObject Dim S As String DataObj.GetFromClipboard S = DataObj.GetText Debug.Print S 'print code in the Intermediate box in the Macro editor End Sub
2 thoughts on “How to read or access the clipboard with Excel VBA?”
Despite all the warnings, I still get “Compile error: user-defined type not defined.” When I go to Tools | References, I have the choice of “Microsoft Forms 12.0 Object Library” and not version 2.0. Does that explain why this error arises?