Clearing clipboard performance

[Jan Karel Pieterse]

Hi,

Question from me this time.

For years I've been running a small macro that adds a new row to a listobject (table), then copies some information from the currently selected row and pastes that into the new row.

After that, I clear the clipboard using Application.CutCopyMode = False

The last couple of weeks, this routine became sluggish, so I decided to investigate which commands were responsible for the slowness. Lo and behold, this statement alone:

Application.CutCopyMode = False

takes 1.9 seconds and is responsible for almost all of the execution time!

I've replaced it with the silly Sendkeys "{ESC}", which is instantaneous.

Has anyone else seen this happen?

[georgiboy]

This is an interesting question, are you able to create a file that has this issue?

I would be interested to see what was on the clipboard at the point Application.CutCopyMode = False is run.

The only thing i can think of is that the clipboard contains a lot of data when it is run and takes time to empty, have you tried making a small sub to only clear the clipboard, then run the problem code after when the clipboard is empty?

[Jan Karel Pieterse]

I just tried, copying the entire table then using cutcopymode and subsequently copying just a single cell.
There is a slight difference, but "just" 20% on 0.02 seconds.

[Jan Karel Pieterse]

When I copy the table to a fresh workbook, all seems well. But then again, even the original workbook now performs as I expect it to. I have no idea what is going on.

[georgiboy]

I was thinking that the clipbord could have been full from previous work that has been completed, I mean very full. When you run your code, it takes a while to clear the clipboard. When you run it for the second time, the clipboard is fairly empty so runs fast.

This is just my thought process.

Maybe you could remember if you were doing a lot of copy/ pasting before you run the code with the issue, or maybe runing code that does not clear the clipboard?

[Jan Karel Pieterse]

Could be, I was also using a virtual machine at the time, so perhaps VMWare is at play here. Thanks!

[Paul_Hossler]

In

' By Chip Pearson
' chip@cpearson.com
' www.cpearson.com/Excel/Clipboard.aspx
' Date: 15-December-2008


Chip uses


Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long



to clear the Clipboard




I'm not sure if CutCopyMode clears the clipboard also


https://learn.microsoft.com/en-us/office/vba/api/excel.application.cutcopymode?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm133101)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

Application.CutCopyMode property (Excel)
Returns or sets the status of Cut or Copy mode. Can be True, False, or an XLCutCopyMode (https://learn.microsoft.com/en-us/office/vba/api/excel.xlcutcopymode) constant, as shown in the following tables. Read/write Long.

[Jan Karel Pieterse]

Thanks Paul.