# VBA Object required



## jasonlee91 (Dec 3, 2006)

Hi everyone,
i have been trying to write a VBA script for work and I am running into a weird error:
"Run-time error'424': Object Required"
all i want to do is store my clipboard as an array, insert the exact amount of rows that was in my clipboard in to the selected cell/row, then paste the clipboard.
it doesnt seem like a very difficult script but i have no idea why i am running into this error. idealy this is what i would want:


```
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+y
'
    Dim DataObj As New MSForms.DataObject
    Dim S As Variant
    Dim N As Variant
    DataObj.GetFromClipboard
    S = DataObj.GetText
    N = S.Rows.Count
    ....... REST OF CODE

End Sub
```


Since that hasnt worked for me, i even tried getting rid of N and just Msgboxing the row count like this:




```
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+y
'
    Dim DataObj As New MSForms.DataObject
    Dim S As Variant
    Dim N As Variant
    DataObj.GetFromClipboard
    S = DataObj.GetText
    MsgBox (S.Rows.Count)

End Sub
```
This gave me the same error which leads me to beleive that it is something to do with the Dataobj that is giving me this error?

Any insight would be much appreciated!


----------



## jamiemac2005 (Jul 5, 2007)

It would be good if you could find out which line the error is coming from, if VBA doesn't report this or have a "-debug" mode which can report this then cutting the lines one by one from the bottom might help.

As for the problem itself it could be that either the DataObject which you are trying to read from the clipboard either isn't recognised by VBA or isn't recognised by the method, what's the data object in the form of? If it's just plain text then ignore this.

A good way to check this would be to try copying/pasting directly from notepad/etc...

The error message also leads me to believe that VBA isn't finding an object, so it either thinks the clipboard is empty or it cannot retrieve the kind of object on the clipboard. A way to check this could be to try initializing S as a simple text object.

Sorry I can't be of more help but it's been a while since i've touched VBA and i'm unsure of the datatypes it supports. Post back with more info if possible (a debug log/stack trace would be brilliant because it would narrow down the issue) otherwise let me know where you've gotten to and i'll try and re-create this on my own system to see what the issue is.

Cheers,
Jamey


----------



## jasonlee91 (Dec 3, 2006)

Thanks for the input. I actually did try most of those things you mentioned.
According to the debugger, the line that is giving me trouble is

```
N = S.Rows.Count
```
 for the first one 

```
MsgBox (S.Rows.Count)
```
 for the second one
the issue is in the S.Rows.Count because as soon as i get rid of that line my script runs well. 
I think when you deal with items stored in clipboard, you no longer deal with ranges but an array and thats why im running into issues.
I did try Get.Length instead of Rows.Count but it is the same issue


----------



## AlbertMC2 (Jul 15, 2010)

Hi

What are you trying to achieve with
S.Rows.Count ?

According to the references you are only allowed to store 1 type of data in the clipboard. So you can store one text entry, one integer entry, one picture etc.
However you can create your own type of entries. 
The clipboard also does not support "tables", so if you copy a range of cells it will probably be stored as tab delimited text (could be mistaken).

See:
Is Clipboard entry a Table? - VBA Express Forum

Windows Clipboard


----------



## Jupiter2 (May 16, 2012)

VBA does not have the ClipBoard object. Set reference to 'Microsoft Forms 2.0 Object Library' if you are not using a userform. "MSForms.DataObject" is not registered under the 'Classes' registry key.



```
Sub pfftt()
 
 Dim Clip As Object
    Set Clip = CreateObject( _
     "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    
    Clip.GetFromClipboard
    MsgBox Clip.GetText, vbOKOnly, "What's in the ClipBoard?"
    
    Clip.SetText "Test"
    Clip.PutInClipboard
    
    Set Clip = Nothing
End Sub
```


----------



## jasonlee91 (Dec 3, 2006)

hi jupiter,
As per your suggestion, i changed the code to look liek this


```
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+o
'
    Dim Clip As Object
    Dim S As Variant
    Dim N As Long
    Set Clip = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    Clip.GetFromClipboard
    S = Clip.GetText
    N = S.Rows.Length
    REST OF CODE.....
```

I still get the exact same error though.
I just need to know how many rows are in the clipboard


----------



## jasonlee91 (Dec 3, 2006)

AlbertMC2 said:


> Hi
> 
> What are you trying to achieve with
> S.Rows.Count ?
> ...


sorry i missed this post
i just need to be able to insert the same amount of rows i have in the clip board so that i can paste brand new rows.
The issue is that if i have a selection copied, then insert copied selection, it shifts cells down instead of rows. i need my script to be able to do that automatically so i dont have to stop in the middle and do that part manually


----------



## jasonlee91 (Dec 3, 2006)

If it is delimited by commas, then is there a way to count the commas?


----------



## jasonlee91 (Dec 3, 2006)

Also, if there the windows clipboard is strictly text, how is it that we are able to paste special while in excel? there must be some kind of difference between how excel stores items in the clipboard vs windows no?


----------



## Jupiter2 (May 16, 2012)

I don't think that there are any discernible rows in a clipboard copy object, you would be better to load the data into a textbox (multi-line enabled) and you could set it to be not visible if you don't want it shown. 

The clipboard may hold a minimum of 5 copy items but may have more depending on your preferences (Microsdoft Office Clipboard), so it is uncertain which part of the clipboard array holds your data, it might be the first part of the array or the last part. Clearing the clipboard prior to the copy action might be helpful but I'm unsure where the single copy data is referred as Array(1) or Array(0) where 0 is the first item in the clipboard array.

What you should do is give an example of the final result (captured image of sample data). Where is the copied text coming from and how do you want it inserted into the cells like only one cell or across the row?

There are no delimiters in copied text, it is just identical to the original unless the original text came from a text area that doesn't have embedded character formatting (returns, new line) then your data will copy as all one line and no paragraphing.

If you are copying from another spreadsheet then that is much easier to do.


----------



## jasonlee91 (Dec 3, 2006)

it is from another spread sheets. the only problem is that the colum widths arent equal.
i am fairly new to VBA and quite noob so i really appreciate you guys bearing with me.


----------



## Jupiter2 (May 16, 2012)

Considering that you know where the text is coming from, you are best to just refer to that location. It also seems to be that you know where the said data is to be inserted so before copy of data you count the rows then in the target workbook you insert the rows and then paste the data, basically. And then you foramt the data as to how you want it, copy the format style from the cells above the inserted text.

I'm not sure whether you are aware but you can actually use a macro to find the data to be copied, depending on other factors, of course.


----------

