什么是“正确”？在VBA中复制粘贴数据的方法？ [英] What is the "correct" way to copy-paste data in VBA?

问题描述

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

现在，至少就我所知，这三种是唯一可用的方法：

1. （ Range ） .Copy（[Destination]） 方法


2. （工作表） .Paste（[Destination，Link]） 方法


3. 和（范围） .PasteSpecial（[Paste]，[Operation]，[SkipBlanks]，[Transpose]） 方法

---

现在，我做了回覆搜索，这些是专家& .Copy（[Destination]） 和 .Paste（[Destination，链接]） 方法，至少是我能想到的方法：

• 允许我们将数据粘贴到与复制相同的表达式中（我猜代码可读性..？）

缺点：

• 单元格引用是完全不可行的！


• 您的格式和公式可能会弄乱


• 如果范围重叠，它将显示提示，有效地阻止宏在其轨迹中消失（这是一个


• 更糟糕的是，如果您在提示符下按 Cancel ，它将引发错误 **（除非处理）

---

在硬币的另一侧， .PasteSpecial（）

优点：

• PasteSpecial（）允许我们粘贴范围的特定部分！


• 它允许我们指定要执行的粘贴类型


• 具有内置的 skipBlanks 和转置功能


• 不是如此 容易出错！

，而我想尽办法，但是：

缺点：

• 还要写更多的字符吗？

---

现在，这导致我相信， .Copy（）方法的 Destination 参数实际上应该被忽略，而 PasteSpecial（）应该始终代替使用。

是否有场合使用它可能比 .PasteSpecial（）更可取？还是应该将 PasteSpecial（）作为每个复制粘贴操作的规范？

解决方案

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

换句话说，它绝对不是主观的或基于观点的，并且几乎与编程...

#ItDepends。

如果您使用Excel并在其周围复制粘贴单元格， Ctrl + C 和 Ctrl + V 还是使用 paste special ？ / p>

取决于您需要执行的操作。

• 如果您要复制一个单元格，其值，格式，数据验证，边框等；然后。粘贴[目标] 是您最好的朋友。这是 Ctrl + C / Ctrl + V 的程序化等效项...相当于PasteSpecial / All，后者因为是太过分了。在这种情况下，粘贴[目标] 已经完成了您需要做的所有事情。

  
  

  单元格引用是完全不可能的！

  
  

  绝对。硬编码的单元格引用是不好的。 。粘贴[目标位置] 仍然不会强迫您这样做，所以要点很重要。

  
  

  如果范围重叠，它将显示提示，有效地阻止宏死在其轨迹中

  
  

  复制和粘贴范围不能重叠，期间。您也会通过 .PasteSpecial 看到提示。




• 如果要复制单元格的值，但不是其格式，数据验证，边框等；那么 .PasteSpecial 绝对是一个更好的主意，因为这与 paste special / values 粘贴在程序上是等效的-只是分配单元格的 Value 带有您想要的内容（无需往返于剪贴板）； OTOH如果您这样做是要粘贴格式或数据验证，否则，这可能是最简单的方法。

粘贴不会弄乱格式。它确实完成了它的意思。 粘贴和 PasteSpecial 是不是等效项。使用正确的工具完成工作。从字面上看，它们分别是粘贴和特殊粘贴的程序等效项-如果您使用Excel并系统地进行特殊粘贴，则可以完成工作。但是每次执行此操作以全部粘贴时，您的工作就会比需要做的要辛苦。

PasteSpecial 看起来像是一把漂亮的锤子，但并非所有东西都是钉子。当您可以避免剪贴板书写时，通常最好避免这种情况...但是，如果您要处理大量的内容，请再次数据集（认为有10万个单元格），它的性能可能比仅分配值更好。

说：

@ScottCraner我确实考虑过，但这并不是真正的复制粘贴，而是更多的典型指针引用，因此我决定不在我的问题中包括它。我不想打开一本书来讨论什么才算粘贴粘贴。

这是错误的。 .Range（foo）.Value = .Range（bar）.Value 不是典型指针引用。它实际上是将 foo 的值放入2D变量数组中，并将该2D变量数组转储到 bar 上，以前保存的值。因此，这绝对是敲剪贴板的完全有效的选择-但您需要测试并与 Copy + PasteSpecial 来查看这是否是最适合您的情况的解决方案：

 使用1500000个单元格进行测试（100000行）
从剪贴板粘贴，单个操作：324.21875ms 
设置单元格值，单个操作：1496.09375ms 
 
 
使用150个测试单元格（10行）
从剪贴板粘贴，单个操作：11.71875ms 
设置单元格值，单个操作：3.90625ms 
从剪贴板粘贴，迭代：1773.4375ms 
设置单元格值，迭代：105.46875ms 
  




What is the "correct" way to copy-paste data in VBA in Excel?

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

1. the (Range) .Copy([Destination]) method
2. the (Worksheet) .Paste([Destination, Link]) method
3. and the (Range) .PasteSpecial([Paste], [Operation], [SkipBlanks], [Transpose]) method

---

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:

pros:

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

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()

pros:

• PasteSpecial() allows us to paste a specific part of range!
• it allows us to specify what type of paste we want to do
• has an inbuilt skipBlanks and transpose functionality
• is not "so" Error-prone!

and I struggled to come up with any, but:

cons:

• a 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.

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?

解决方案

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?

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.

  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.

  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.

• 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.

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.

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.

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.

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:

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

这篇关于什么是“正确”？在VBA中复制粘贴数据的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！