VBA Clipboard Unstable

The following code (Excel 2003) is unstable. After numerous iterations it crashes with the following errors:
      DataObject:PutInClipboard OpenClipboard Failed
      Paste Method of Worksheet class failed
      Out of Memory

This differs per machine, all running the same build of Excel, and is quite temperamental.
Sometimes it will run 1000 iterations, sometimes only 20.

Any ideas how i can make it more stable?

Thanks, Jackie

Sub ClipPaste(PasteText As String, PasteRange As Range)
    Dim doClip As DataObject
    Dim Wbk As Workbook
    Dim WKS As Worksheet
    Dim Rng As Range
    Dim WbkCur As Workbook
    Dim WksCur As Worksheet
    Dim RngCur As Range
    Set WbkCur = Application.ActiveWorkbook
    Set WksCur = Application.ActiveSheet
    Set doClip = New DataObject
    doClip.SetText PasteText

    Set doClip = Nothing
    Set Wbk = Workbooks(PasteRange.Parent.Parent.Name)
    Set WKS = Wbk.Worksheets(PasteRange.Parent.Name)
    Set Rng = WKS.Cells(PasteRange.Row, PasteRange.Column)
    Application.CutCopyMode = False
End Sub
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
This might work a little better:

Sub ClipPaste(PasteText As String, PasteRange As Range)
   PasteRange.Cells(1, 1) = PasteText
End Sub

zorvek (Kevin Jones)ConsultantCommented:
The above code produces the same result as your code. If you posted a demonstration of the problem versus the actual code causing the problem then please post your real code.

Rory ArchibaldCommented:
What is the point of this routine? As far as I can see from a quick look, it just puts some text into a cell and you don't need the clipboard for that. Am I missing something?

PS It looks to me like equivalent code would be:

Sub ClipPaste(PasteText As String, PasteRange As Range)
    PasteRange.Value = PasteText    
End Sub
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Rory ArchibaldCommented:
Guess I should have refreshed! :)
JackieMawAuthor Commented:
Hi Everybody

Thanks for the response - unfortunately that approach will not work.
The PasteString that I am pasting contains vbTab and vbCrLf characters, which when using the clipboard, will paste the string over multiple rows and columns. So it's pasting an entire range of data, but this data is NOT coming from cells, but from the string which is built up in memory.

Can I attach a file that demo's what i am doing? <i have not used experts exchange before, how do i do this?>

Thanks, Jackie
zorvek (Kevin Jones)ConsultantCommented:
If you are constructing the "table" in memory using a string you might consider using an array. An array can be pushed into cells in one statement without using the clipboard.

Another idea is to use a function to convert the assembled string into an array and then push that into the cell range. Here is the function:

Public Function MultiLineSplit( _
      ByVal Text As String _
   ) As Variant
   Dim Lines As Variant
   Dim Line As Variant
   Dim Fields As Variant
   Dim Field As Variant
   Dim ColumnCount As Long
   Dim RowCount As Long
   Dim Row As Long
   Dim Column As Long
   Dim Result As Variant
   Lines = Split(Text, vbCrLf)
   RowCount = UBound(Lines) + 1
   For Each Line In Lines
      Fields = Split(Line, vbTab)
      ColumnCount = Application.Max(ColumnCount, UBound(Fields) + 1)
   Next Line
   ReDim Result(1 To RowCount, 1 To ColumnCount)
   For Row = 1 To RowCount
      Fields = Split(Lines(Row - 1), vbTab)
      For Column = 1 To UBound(Fields) + 1
         Result(Row, Column) = Fields(Column - 1)
      Next Column
   Next Row
   MultiLineSplit = Result

End Function

Use it like so:

Public Sub Test()

   Dim X As Variant
   X = MultiLineSplit("A" & vbTab & "B" & vbCrLf & "C" & vbCrLf & "D" & vbTab & "E" & vbTab & "F")
   [A1].Resize(UBound(X, 1), UBound(X, 2)).Value = X

End Sub

If these solutions don't work you can post your file.

You can post your files in any of a number of ways.

The prefered method at this time is to use the Experts-Exchange file sharing site. It is available to all registered Experts-Exhange members and it is free. First zip your file or files. Then navigate to http://www.ee-stuff.com, click Login in the upper right corner, enter you Experts-Exhange user name and password, click the Login command button, navigate to Expert Area, and click "Upload a new file". Enter the complete URL of the question, or the Question ID into the text box labeled "Question", and then click "Browse..." to select the desired file to upload. The question ID is the eight-digit number after the "Q_" in the question URL. Type a comment describing the file. Click "Upload" to upload the file. On the next page you will be presented with a URL you can post in the Experts-Exchange question so that others can download your file.

There are other free file sharing services available:

Geocities: http://geocities.yahoo.com/ps/learn2/HowItWorks4_Free.html
AngelFire: http://www.angelfire.lycos.com/doc/subscriptions/index.html
Google: http://base.google.com/base/default 
RapidUpload: http://www.rapidupload.com/

If you do not want to use any of these services you can send your file to me using the email address in my profile (http://www.experts-exchange.com/M_1677072.html) and I will post it on my own site.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
Hi Jackie,
You can log into www.ee-stuff.com (using the same ID and password as you use here). You will need to zip the file and you will need the URL for this question from the address bar of your browser (if the URL ends in #nnnnnnnnnn, strip that bit off so it ends with .html) Once uploaded you will be given two links to your file - copy the address of the shortcut and post it here and we will take a look.
PS If you have any problems with that, you can email it to me and I will upload it for you. My address is in my profile.
JackieMawAuthor Commented:
thanks zorvak, that looks great! I have implemented it and it seems to run faster than using the clipboard
I will test it first thing tomoro morning on my client's "unstable" machine, but I am sure that this will solve my problem :-)
JackieMawAuthor Commented:
BRILLIANT!!! It's wonderfully stable and faster, thanks a LOT!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.