在VBA中复制粘贴数据的“正确”方法是什么？

A question on more of a theoretical end:

关于更多理论结束的问题:

What is the "correct" way to copy-paste data in vba / excel-vba?

在vba / excel-vba中复制粘贴数据的“正确”方法是什么?

Now, at least to my knowledge, these three are the only available methods:

现在,至少据我所知,这三种是唯一可用的方法:

1. the (Range) .Copy([Destination]) method

(范围).Copy([Destination])方法

2. the (Worksheet) .Paste([Destination, Link]) method

(工作表).Paste([Destination,Link])方法

3. and the (Range) .PasteSpecial([Paste], [Operation], [SkipBlanks], [Transpose]) method

和(范围).PasteSpecial([Paste],[Operation],[SkipBlanks],[Transpose])方法

---

Now, I did my research, these are the pros & cons of .Copy([Destination]) and the .Paste([Destination, Link]) method, least those, that I can think of:

现在,我做了我的研究,这些是.Copy([Destination])和.Paste([Destination,Link])方法的优点和缺点,至少那些,我能想到:

+ pros:

• allows us to paste data in the same expression as copying (code-readability I guess..?)

允许我们将数据粘贴到与复制相同的表达式中(代码可读性我猜...?)

and the:

- cons:

• Cell references are a complete no go!

细胞参考是完全没有去!

• Your formatting and formulas might get messed up

你的格式和公式可能搞砸了

• If the range overlaps it will display a prompt, effectively stopping macro dead in its tracks (that's a huge bummer especially if you're trying to automize something)

如果范围重叠,它将显示一个提示,有效地阻止宏死在它的轨道上(这是一个巨大的无赖,特别是如果你试图自动化的东西)

• Worse yet, if you press Cancel on the prompt, it will throw an Error** (unless handled)

更糟糕的是,如果你在提示符上按下取消,它将抛出错误**(除非处理)

---

On the other side of the coin, .PasteSpecial()

在硬币的另一边,.PasteSpecial()

+ pros:

• PasteSpecial() allows us to paste a specific part of range!

PasteSpecial()允许我们粘贴范围的特定部分!

• it allows us to specify what type of paste we want to do

它允许我们指定我们想要做什么类型的粘贴

• has an inbuilt skipBlanks and transpose functionality

有一个内置的skipBlanks和转置功能

• is not "so" Error-prone!!!

不是“如此”容易出错!!!

and I struggled to come up with any, but:

我努力想出任何一个,但是:

- cons:

• bit more characters to write..?

要写更多的字符..?

---

Now, that leads me to believe, that the Destination argument of .Copy() method should essentially be ignored and PasteSpecial() should be always used instead.

现在,这让我相信,.Copy()方法的Destination参数应该基本上被忽略,而且应该总是使用PasteSpecial()。

Is there ever an occassion where usage of it might be preferable over .PasteSpecial()? Or should PasteSpecial() be the norm for every copy-paste operation?

有没有一个场合,使用它可能比.PasteSpecial()更好?或者PasteSpecial()应该是每次复制粘贴操作的标准吗?

1 个解决方案

#1

This isn't about readability or how many characters you're typing. It's about what you need to achieve.

这与您的可读性或您输入的字符数无关。这是你需要实现的目标。

In other words, it's absolutely not subjective or opinion-based, and like pretty much everything else in programming...

换句话说,它绝对不是主观的或基于意见的,并且像编程中的其他所有内容一样......

#ItDepends.

If you're using Excel and copy-pasting cells around, do you Ctrl+C and Ctrl+V or use paste special?

如果你正在使用Excel和复制粘贴单元格,你是Ctrl + C和Ctrl + V还是使用粘贴特殊?

Depends what you need to do.

取决于你需要做什么。

• If you mean to copy a cell, its value, formats, data validations, borders, etc.; then .Paste [Destination] is your best friend. That's the programmatic equivalent of Ctrl+C/Ctrl+V... Equivalent to PasteSpecial/All, which is overkill since .Paste [Destination] already does everything you need it to do in this case.

  如果您要复制单元格,其值,格式,数据验证,边框等;那么.Paste [Destination]是你最好的朋友。这是程序化等效的Ctrl + C / Ctrl + V ...相当于PasteSpecial / All,这是一种过度杀伤,因为.Paste [Destination]已经完成了在这种情况下你需要做的一切。

  Cell references are a complete no go!

  细胞参考是完全没有去!

  Absolutely. Hard-coded cell references are bad. .Paste [Destination] doesn't force you to do that anyway, so the point is moot.

  绝对。硬编码的单元格引用很糟糕。 .Paste [目的地]不会强迫你这样做,所以重点是没有实际意义。

  If the range overlaps it will display a prompt, effectively stopping macro dead in its tracks

  如果范围重叠,它将显示一个提示,有效地停止其轨迹中的宏死亡

  Copy and paste ranges cannot overlap, period. You'll get that prompt through .PasteSpecial too.

  复制和粘贴范围不能重叠,期间。你也可以通过.PasteSpecial获得提示。

• If you mean to copy a cell's Value, but not its formats, data validations, borders, etc.; then .PasteSpecial is definitely a better idea, since that's the programmatic equivalent of going paste special / values - except it's probably more efficient to just assign the cell's Value with what you want (no need to round-trip to/from the clipboard); OTOH if you do mean to paste formats, or data validations, or whatnot, then this is probably the easiest way.

  如果您要复制单元格的值,而不是其格式,数据验证,边框等;然后.PasteSpecial绝对是一个更好的主意,因为这是程序上相当于粘贴特殊值/值 - 除了它可能更有效地分配单元格的值与你想要的(不需要往返剪贴板); OTOH如果您的意思是粘贴格式,数据验证或诸如此类,那么这可能是最简单的方法。

Paste doesn't "mess up" formats. It does exactly what it means to do. Paste and PasteSpecial are not equivalents. Use the right tool for the job. They are literally the programmatic equivalents of "paste" and "paste special", respectively - if you're in Excel and systematically going "paste special", you'll get your stuff done. But every time you do that to "paste all", you're working harder than you need to be.

粘贴不会“搞乱”格式。它完全符合它的意义。 Paste和PasteSpecial不是等价物。使用正确的工具完成工作。它们分别是“粘贴”和“粘贴特殊”的程序化等价物 - 如果你在Excel中并且系统地“粘贴特殊”,你将完成你的工作。但每当你这样做“粘贴所有”时,你就会比你需要的更努力。

PasteSpecial looks like a nice hammer, but not everything is a nail. When you can avoid a clipboard write, it's generally a good idea to avoid it... but then again, if you're dealing with huge data sets (think 100K+ cells), it's possible that it performs better than just assigning the values.

PasteSpecial看起来像一把漂亮的锤子,但不是一切都是钉子。当你可以避免剪贴板写入时,通常最好避免它...但是,如果你正在处理大量数据集(想想100K +单元格),那么它可能比仅仅分配值更好。

That said:

@ScottCraner I did think about it, but that's not really copy-pasting, but more of a typical pointer referencing, hence I decided not to include it in my question. I didn't want to open a book to the "what counts as copy-pasting" discussion.

@ScottCraner我确实考虑过它,但这不是真正的复制粘贴,而是更多典型的指针引用,因此我决定不将它包含在我的问题中。我不想打开一本关于“什么算作复制粘贴”讨论的书。

That is wrong. .Range(foo).Value = .Range(bar).Value isn't "typical pointer referencing". It's literally taking the values of foo into a 2D variant array, and dumping that 2D variant array onto bar, overwriting the previously held values. As such, it absolutely is a completely valid alternative to hitting the clipboard - but you'll need to test and compare against Copy+PasteSpecial to see if that's the best (/most efficient) solution for your situation:

那是错的。 .Range(foo).Value = .Range(bar).Value不是“典型的指针引用”。它实际上将foo的值转换为2D变体数组,并将该2D变体数组转储到bar上,覆盖以前保存的值。因此,它绝对是一个完全有效的替代方法来击中剪贴板 - 但你需要测试并与Copy + PasteSpecial进行比较,以确定这是否是适合您情况的最佳(/最有效)解决方案:

Testing with 1500000 cells (100000 rows)
Pasting from clipboard, single operation: 324.21875ms
Setting cell values, single operation:    1496.09375ms


Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, single operation:    3.90625ms
Pasting from clipboard, iterative:        1773.4375ms
Setting cell values, iterative:           105.46875ms