复制/ PasteSpecial vs Range.Value = Range.Value [英] Copy/PasteSpecial vs Range.Value = Range.Value

问题描述

我已经在这个网站（和其他地方）阅读了很多次，最好避免在VBA宏中复制/粘贴。例如，而不是这样做...

  For i = 1 To tbl.ListColumns.Count 
 With tbl .ListColumns（i）.DataBodyRange 
 .FormulaR1C1 == 2 * 1
 .Copy 
 .PasteSpecial粘贴：= xlPasteValues 
 Application.CutCopyMode = False 
结束使用
下一个
  

...这应该是更好/更快：

 对于i = 1 To tbl.ListColumns.Count 
带有tbl.ListColumns（i）
 .DataBodyRange.FormulaR1C1 == 2 * 1
 .DataBodyRange = .DataBodyRange.Value 
结束
下一个
  

但是在大​​型表（15列，100k行）上进行测试，复制/粘贴版本明显更快（1.9秒vs 2.7秒）。即使我首先将tbl.DataBodyRange声明为一个Range变量，差异仍然存在。

我以为这可能是ListObjects的一些奇怪的属性，但是区别实际上更大，没有他们：

 '以1.1秒运行
使用Sheet1.Range（A1：O100000）
 .FormulaR1C1 == 2 * 1
 .Copy 
 .PasteSpecial粘贴：= xlPasteValues 
 Application.CutCopyMode = False 
结束
 
'运行在2.1秒
 With Sheet1.Range（A1：O100000）
 .FormulaR1C1 == 2 * 1
 .Value = .Value 
结束
有没有人知道为什么复制/粘贴方法要快得多？ 

还有其他原因避免使用复制/粘贴（假设在宏运行时剪贴板永远不会在Excel之外使用）？

编辑：这是第一个一组测试结果将复制/粘贴值与Mat的Mug在接受的答案中描述的数组读/写方法进行比较。我测试的范围从1000个单元格到100万个单元格，一次增加1000个，每个范围的平均值为10次。复制粘贴开始速度较慢，但​​很快超过了设定值的方法（在图表上很难看出，但平均点为〜15k个单元格）。

我还在下端进行了10个进一步的测试范围（范围从100个单元格到100000个单元格，一次递增100个），以试图将该平均值出现在哪里。这次我使用 Charles WilliamsMicroTimer而不是默认计时器，希望对次秒的时间更准确。我还包括Set Array版本和原始的.Value = .Value版本（并记住将计算转换为Manual，与第一组测试不同）。有趣的是，阵列读/写方法在这个时候显得更糟糕，平均点大约在3300个单元和更差的峰值性能。数组读/写和.Value = .Value之间几乎没有区别，尽管阵列版本执行得更差。

这是我用于最后一轮测试的代码：

  Sub speedTest（）
 Dim copyPasteRNG（1 To 10，1 To 1000）
 Dim setValueRNG（1 To 10，1到1000）
 Dim setValueArrRNG（1到10，1到1000）
 
 Dim i As Long 
 Dim j As Long 
 Dim numRows As Long 
 Dim rng As Range 
 
 Application.ScreenUpdating = False 
 Application.Calculation = xlCalculationManual 
 Application.DisplayStatusBar = False 
 
对于i = 1至10 
 numRows = 100 
对于j = 1至1000 
设置rng = Sheet3.Range（A1：A& numRows）
 setValueRNG（i，j）= getTime（False ，rng，False）
 setValueArrRNG（i，j）= getTime（False， rng，True）
 numRows = numRows + 100 
下一个
下一个
 
对于i = 1到10 
 numRows = 100 
对于j = 1到1000 
设置rng = Sheet3.Range（A1：A& numRows）
 copyPasteRNG（i，j）= getTime（True，rng）
 numRows = numRows + 100 
下一个
下一个
 
 Sheet4.Range A1：J1000）Value2 = Application.Transpose（copyPasteRNG）
 Sheet5.Range（A1：J1000）Value2 = Application.Transpose（setValueRNG）
 
 Application.DisplayStatusBar = True 
 Application.Calculation = xlCalculationAutomatic 
 Application.ScreenUpdating = True 
 
 End Sub 
 
函数getTime（copyPaste As Boolean，rng As Range，Optional arrB As Boolean）As Double 
 Dim startTime As Double 
 Dim endTime As Double 
 
 startTime = MicroTimer 
 
带有rng 
 .FormulaR1C1 = = 1
如果copyPaste = True然后
 .Copy 
 .PasteSpecial粘贴：= xlPasteValues 
 Application.CutCopyMode = False 
 ElseIf arrB = True然后
 Dim arr As Variant 
 arr = .Value2 
 .Value2 = arr 
 Else 
 .Value2 = .Value2 
如果
结束
 
 endTime = MicroTimer  -  startTime 
 
 getTime = endTime 
 
结束函数
  

这里是我使用的MicroTimer（在单独的模块中）：

 私有声明PtrSafe函数getFrequency Libkernel32别名QueryPerformanceFrequency（cyFrequency As Currency ）As Long 
私有声明PtrSafe函数getTickCount Libkernel32别名QueryPerformanceCounter（cyTickCount as Currency）As Long 
 
 Private Const sCPURegKey =HARDWARE\DESCRIPTION\System\CentralProcessor \0
 Private Const HKEY_LOCAL_MACHINE As Long =& H80000002 
私有声明PtrSafe函数RegCloseKey Libadvapi32.dll（ByVal hKey As Long）As Long 
私有声明PtrSafe函数RegOpenKey Lib advapi32.dll别名RegOpenKeyA（ByVal hKey As Long，ByVal lp SubKey As String，phkResult As Long）As Long 
私有声明PtrSafe函数RegQueryValueEx Libadvapi32.dll别名RegQueryValueExA（ByVal hKey As Long，ByVal lpValueName As String，ByVal lpReserved As Long，lpType As Long，lpData As Any，lpcbData As Long）As Long 
 
功能MicroTimer（）As Double 
 
 Dim cyTicks1作为货币
静态cyFrequency作为货币
'
 MicroTimer = 0 
如果cyFrequency = 0然后getFrequency cyFrequency 
 getTickCount cyTicks1 
如果cyFrequency然后MicroTimer = cyTicks1 / cyFrequency 
 
结束函数
  




解决方案

大多数（无论如何）无论如何，VBA宏不与集合并在一个范围内迭代单元格。不是因为这是一个好主意（不是这样），而是因为很多东西根本就不了解。

使用对象集合（如一个范围，是一个 For Each 循环。所以我进行了测试，重构了一下，添加了迭代解决方案的测试，然后我添加了一个数组读/写测试，因为这也是一个常见的好的方法来复制单元格值。

请注意，我从个别测试中拉出了公式写作设置步骤。

注意：此代码控制流程最佳实践和把它们推到地毯下面。 不要使用 GoSub / 以实际代码返回

  Sub Test（）
 
 Const TEST_ROWCOUNT As Long = 10 
 
 Const RANGE_ADDRESS As String =A1：O& TEST_ROWCOUNT 
 Const RANGE_FORMULA As String == 2 * 1
 
 Dim startTime As Double 
 
 Application.ScreenUpdating = False 
 Application.Calculation = xlCalculationManual 
 
 Debug.PrintTesting with& Sheet1.Range（RANGE_ADDRESS）.Count& cell（& TEST_ROWCOUNT&rows）
 
 GoSub InitTimer 
 TestPasteFromClipboard Sheet1.Range（RANGE_ADDRESS）
 Debug.Print从剪贴板粘贴，单次操作： ，
 GoSub ReportTime 
 
 GoSub InitTimer 
 TestSetRangeValue Sheet1.Range（RANGE_ADDRESS）
 Debug.Print设置单元格值，单次操作：，
 GoSub ReportTime 
 
 GoSub InitTimer 
 TestArrayDump Sheet1.Range（RANGE_ADDRESS）
 Debug.PrintArray read + write，single operation：，
 GoSub ReportTime 
 
 GoSub InitTimer 
 TestIteratePaste Sheet1.Range（RANGE_ADDRESS）
 Debug.Print从剪贴板粘贴，迭代：，
 GoSub ReportTime 
 
 GoSub InitTimer 
 TestIterateSetValue Sheet1.Range（RANGE_ADDRESS）
 Debug.Print设置单元格值，迭代：，
 GoSub ReportTime 
 
 Application.ScreenUpdating = True 
应用程序计算= xlCa lculationAutomatic 
 
退出Sub 
 
 InitTimer：
 Sheet1.Range（RANGE_ADDRESS）.Formula = RANGE_FORMULA 
 startTime = Timer 
返回
 ReportTime：
 Debug.Print（Timer  -  startTime）* 1000& ms
返回
结束Sub 
 
私有子TestPasteFromClipboard（ByVal withRange As Range）
 With withRange 
 .Copy 
 .PasteSpecial Paste ：= xlPasteValues 
 End with 
 Application.CutCopyMode = False 
 End Sub 
 
 Private Sub TestSetRangeValue（ByVal withRange As Range）
 withRange.Value = withRange .Value 
 End Sub 
 
 Private Sub TestIteratePaste（ByVal withRange As Range）
 Dim cell As Range 
对于每个单元格在withRange.Cells 
单元格中。复制
 cell.PasteSpecial粘贴：= xlPasteValues 
下一个
 Application.CutCopyMode = False 
 End Sub 
 
 Private Sub TestIterateSetValue（ByVal withRange As Range）
 Dim cell As Range 
对于每个单元格在withRange.Cells 
 cell.Value = cell.Value 
下一个
 Application.CutCopyMode = False 
 End Sub 
 
 Private Sub TestArrayDump（ByVal withRange As Range）
 Dim arr As Vari ant 
 arr = withRange.Value 
 withRange.Value = arr 
 End Sub 
  

---

我不得不将范围大小减小一个数量级（否则我仍然会盯着我不响应的Excel屏幕），但这是输出 - 当然，逐个细胞迭代方法的速度要慢一点，但要注意剪贴板数据如何与直线比较直接值赋值：

 使用150个单元格（10行）测试
从剪贴板粘贴，单次操作：11.71875ms 
设置单元格值，单个操作：3.90625ms 
数组读+写，单操作：7.8125ms 
从剪贴板粘贴迭代：1773.4375ms 
设置单元格值，迭代：105.46875ms 
 
使用150个单元格（10行）
从剪贴板粘贴，单次操作：11.71875ms 
设置单元格值，单次操作：3.90625ms 
数组读取+写入，单操作：3.90625ms 
从剪贴板粘贴，迭代：1718.75ms 
设置单元格值，迭代：109.375ms 
 
测试150个单元格（10行）
粘贴从剪贴板，单操作：15.625ms 
设置单元格值，单操作：3.90625ms 
数组读+写，单操作：3.90625ms 
从剪贴板粘贴，迭代：1691.40625ms 
设置单元格值，迭代：136.71875ms 
  

所以对于10行/ 150个单元格，将范围复制到一个数组，或分配 Range.Value ，没有任何区别：两者都比剪贴板解决方案快。

显然，迭代方法要慢得多，但是与直接分配范围值相比，注意剪贴板解决方案的速度要慢些。

---

另一个测试运行的时间。

 测试w第一个1500个单元格（100行）
从剪贴板粘贴，单次操作：11.71875ms 
设置单元格值，单次操作：7.8125ms 
数组读取+写入，单次操作：3.90625ms 
从剪贴板粘贴，迭代：10480.46875ms 
设置单元格值，迭代：1125ms 
 
测试1500个单元格（100行）
从剪贴板粘贴，单次操作：19.53125ms 
设置单元格值，单次操作：3.90625ms 
数组读取+写入，单次操作：3.90625ms 
从剪贴板粘贴迭代：10859.375ms 
设置单元格值，迭代：2390.625ms 
 
使用1500个单元格（100行）测试
从剪贴板粘贴，单次操作：15.625ms 
设置单元格值，单次操作：3.90625ms 
数组读取+写入，单操作：3.90625ms 
从剪贴板粘贴，迭代：10964.84375ms 
设置单元格值，迭代：1062.5ms 
  

现在不太清楚，但倾销阵列似乎还是更可靠的解决方案。

---

我们来看看有1000行给我们：

 用15000个单元格1000行）
从剪贴板粘贴，单次操作：15.625ms 
设置单元格值，单次操作：15.625ms 
数组读取+写入，单次操作：15.625ms 
从剪贴板粘贴，迭代：80324.21875ms 
设置单元格值，迭代：11859.375ms 
  

我不有耐心评论迭代测试

 使用15000个单元格（1000行）测试
从剪贴板粘贴，单次操作：19.53125ms 
设置单元格值，单次操作：15.625ms 
数组读取+写入，单次操作：15.625ms 
 
使用15000个单元格（1000行）
从剪贴板粘贴，单次操作：23.4375ms 
设置单元格值，单次操作：15.625ms 
数组读取+写入，单次操作：15.625ms 
  

---

相当一致;再次，剪贴板丢失。但是10K行怎么样？

 使用150000个单元格（10000行）进行测试
粘贴单个操作：46.875ms 
设置单元格值，单次操作：144.53125ms 
数组读取+写入，单次操作：152.34375ms 
 
测试150000个单元格（10000行）
从剪贴板粘贴，单次操作：46.875ms 
设置单元格值，单次操作：148.4375ms 
数组读取+写入，单次操作：148.4375ms 
 
测试具有150000个单元格（10000行）
从剪贴板粘贴，单次操作：50.78125ms 
设置单元格值，单次操作：144.53125ms 
数组读取+写入，单次操作：152.34375ms 
  

这里我们是 - 剪贴板清楚地赢了！

---

底线：如果您有100K单元格可以使用，剪贴板可能是一个好主意。如果您有10K个单元格可以使用（或更少），则 值赋值 数组转储可能是更快的方法。中间的任何事情都可能需要基准测试和测试，以找出更快的方法。

TL; DR：没有任何银弹子一刀切的解决方案。

当您使用相对较少数量的单元格时，或者如果您正在迭代，您将避免复制/粘贴单个细胞。对于涉及数据的大批量操作而言，剪贴板不是一个疯狂的想法。

为了完成：

 使用1500000个单元格（100000行）进行测试
从剪贴板粘贴，单次操作：324.21875ms 
设置单元格值，单次操作：1496.09375ms 
数组读取+写入，单次操作：1578.125ms 
 
使用1500000个单元格（100000行）测试
从剪贴板粘贴，单次操作：324.21875ms 
设置单元格值，单次操作：1445.3125ms 
数组读取+写入，单次操作：1515.625ms 
 
测试与1500000个单元格（100000行）
从剪贴板粘贴，单次操作：367.1875ms 
设置单元格值，单次操作：1562.5ms 
数组读取+写入，单次操作：1574.21875ms 
  / pre> 
 
 
对于巨大的 YUGE范围，直接设置单元格值似乎始终优于大写数组转储，但剪贴板优于两者，并且相当有利。
 
 
 
所以：
 
 
 
 
 
小于1K单元格：值赋值或数组转储，测试找出
 
 
小于100K单元格：数组转储
 
 
多于150K单元格：剪贴板
 
 
中间的任何内容：数组转储或剪贴板，测试找出
 
 
在任何情况下，更快的方法是一个迭代的解决方案，几个数量级。
 
 
 
I've read a number of times on this site (and elsewhere) that it's best to avoid copy/paste if possible in VBA macros. For example, instead of doing this...
For i = 1 To tbl.ListColumns.Count
    With tbl.ListColumns(i).DataBodyRange
        .FormulaR1C1 = "=2*1"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
Next
...it's supposedly better/faster to do this:
For i = 1 To tbl.ListColumns.Count
    With tbl.ListColumns(i)
        .DataBodyRange.FormulaR1C1 = "=2*1"
        .DataBodyRange = .DataBodyRange.Value
    End With
Next
But testing this out on a large table (15 columns, 100k rows), the copy/paste version was significantly faster (1.9 sec vs 2.7 seconds). The difference remained even if I declared tbl.DataBodyRange as a Range variable first.

I thought this might be some weird property of ListObjects, but the difference was actually even bigger without them:
'Runs in 1.1 seconds
With Sheet1.Range("A1:O100000")
    .FormulaR1C1 = "=2*1"
    .Copy
    .PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End With

'Runs in 2.1 seconds
With Sheet1.Range("A1:O100000")
    .FormulaR1C1 = "=2*1"
    .Value = .Value
End With
Does anyone know why the copy/paste method is so much faster? And are there additional reasons to avoid using copy/paste (assuming that the clipboard will never be used outside of Excel while the macro is running)?

EDIT: Here's the first set of test results comparing Copy/PasteValues to the array read/write method described by Mat's Mug in the accepted answer. I tested range sizes from 1000 cells to 1 million cells, incrementing by 1000 at a time, and took the average of 10 tests for each range size. Copy paste started slower, but quickly overtook the set value method (it's hard to see on the chart, but the break even point is ~15k cells).



I also ran 10 further tests at the lower end of the range (range sizes from 100 cells to 100000 cells, incrementing by 100 at a time) to try to pin down where the break even point was occurring. This time I used Charles Williams' "MicroTimer" instead of the default timer, hoping that it'd be more accurate for the sub-second timing. I also included both the "Set Array" version and the original ".Value = .Value" version (and remembered to switch calculation to Manual, unlike during the first set of tests). Interestingly, the array read/write method fared significantly worse this time around, with a break even point of around 3300 cells and worse peak performance. There was virtually no difference between array read/write and .Value = .Value, though the array version performed slightly worse.



Here's the code I used for the last round of tests:
Sub speedTest()
    Dim copyPasteRNG(1 To 10, 1 To 1000)
    Dim setValueRNG(1 To 10, 1 To 1000)
    Dim setValueArrRNG(1 To 10, 1 To 1000)

    Dim i As Long
    Dim j As Long
    Dim numRows As Long
    Dim rng As Range

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False

    For i = 1 To 10
        numRows = 100
        For j = 1 To 1000
            Set rng = Sheet3.Range("A1:A" & numRows)
            setValueRNG(i, j) = getTime(False, rng, False)
            setValueArrRNG(i, j) = getTime(False, rng, True)
            numRows = numRows + 100
        Next
    Next

    For i = 1 To 10
        numRows = 100
        For j = 1 To 1000
            Set rng = Sheet3.Range("A1:A" & numRows)
            copyPasteRNG(i, j) = getTime(True, rng)
            numRows = numRows + 100
        Next
    Next

    Sheet4.Range("A1:J1000").Value2 = Application.Transpose(copyPasteRNG)
    Sheet5.Range("A1:J1000").Value2 = Application.Transpose(setValueRNG)

    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Function getTime(copyPaste As Boolean, rng As Range, Optional arrB As Boolean) As Double
    Dim startTime As Double
    Dim endTime As Double

    startTime = MicroTimer

    With rng
        .FormulaR1C1 = "=1"
        If copyPaste = True Then
            .Copy
            .PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        ElseIf arrB = True Then
            Dim arr As Variant
            arr = .Value2
            .Value2 = arr
        Else
            .Value2 = .Value2
        End If
    End With

    endTime = MicroTimer - startTime

    getTime = endTime

End Function
And here's the version of MicroTimer I used (in separate module):
Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

Private Const sCPURegKey = "HARDWARE\DESCRIPTION\System\CentralProcessor\0"
Private Const HKEY_LOCAL_MACHINE As Long = &H80000002
Private Declare PtrSafe Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Private Declare PtrSafe Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare PtrSafe Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long

Function MicroTimer() As Double

    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
     '
    MicroTimer = 0
    If cyFrequency = 0 Then getFrequency cyFrequency
    getTickCount cyTicks1
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency

End Function

解决方案
Most (a lot, anyway) VBA macros don't "work with sets" and iterate the cells in a range. Not because it's a good idea (it's not), but because a lot simply don't know any better.

The fastest loop, when working with an object collection such as a Range, is a For Each loop. So I took your test, refactored it a bit, added tests for iterative solutions, and then I added an array read/write test, because that's also a common, good way to copy cell values.

Note that I pulled the formula-writing setup step out of the individual tests.

NOTE: This code takes control flow best practices and shoves them under the carpet. DO NOT use GoSub/Return in real code.
Sub Test()

    Const TEST_ROWCOUNT As Long = 10

    Const RANGE_ADDRESS As String = "A1:O" & TEST_ROWCOUNT
    Const RANGE_FORMULA As String = "=2*1"

    Dim startTime As Double

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Debug.Print "Testing with " & Sheet1.Range(RANGE_ADDRESS).Count & " cells (" & TEST_ROWCOUNT & " rows)"

    GoSub InitTimer
    TestPasteFromClipboard Sheet1.Range(RANGE_ADDRESS)
    Debug.Print "Pasting from clipboard, single operation:",
    GoSub ReportTime

    GoSub InitTimer
    TestSetRangeValue Sheet1.Range(RANGE_ADDRESS)
    Debug.Print "Setting cell values, single operation:",
    GoSub ReportTime

    GoSub InitTimer
    TestArrayDump Sheet1.Range(RANGE_ADDRESS)
    Debug.Print "Array read+write, single operation:",
    GoSub ReportTime

    GoSub InitTimer
    TestIteratePaste Sheet1.Range(RANGE_ADDRESS)
    Debug.Print "Pasting from clipboard, iterative:",
    GoSub ReportTime

    GoSub InitTimer
    TestIterateSetValue Sheet1.Range(RANGE_ADDRESS)
    Debug.Print "Setting cell values, iterative:",
    GoSub ReportTime

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    Exit Sub

InitTimer:
    Sheet1.Range(RANGE_ADDRESS).Formula = RANGE_FORMULA
    startTime = Timer
    Return
ReportTime:
    Debug.Print (Timer - startTime) * 1000 & "ms"
    Return
End Sub

Private Sub TestPasteFromClipboard(ByVal withRange As Range)
    With withRange
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

Private Sub TestSetRangeValue(ByVal withRange As Range)
    withRange.Value = withRange.Value
End Sub

Private Sub TestIteratePaste(ByVal withRange As Range)
    Dim cell As Range
    For Each cell In withRange.Cells
        cell.Copy
        cell.PasteSpecial Paste:=xlPasteValues
    Next
    Application.CutCopyMode = False
End Sub

Private Sub TestIterateSetValue(ByVal withRange As Range)
    Dim cell As Range
    For Each cell In withRange.Cells
        cell.Value = cell.Value
    Next
    Application.CutCopyMode = False
End Sub

Private Sub TestArrayDump(ByVal withRange As Range)
    Dim arr As Variant
    arr = withRange.Value
    withRange.Value = arr
End Sub




I had to reduce the range size by an order of magnitude (otherwise I'd still be staring at my non-responding Excel screen), but this was the output - of course the cell-by-cell iterative approach is MUCH slower, but notice how the clipboard figures compare to straight Value assignment:
Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, single operation:    3.90625ms
Array read+write, single operation:       7.8125ms
Pasting from clipboard, iterative:        1773.4375ms
Setting cell values, iterative:           105.46875ms

Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, single operation:    3.90625ms
Array read+write, single operation:       3.90625ms
Pasting from clipboard, iterative:        1718.75ms
Setting cell values, iterative:           109.375ms

Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 15.625ms
Setting cell values, single operation:    3.90625ms
Array read+write, single operation:       3.90625ms
Pasting from clipboard, iterative:        1691.40625ms
Setting cell values, iterative:           136.71875ms
So with 10 rows / 150 cells, copying the range into an array, or assigning Range.Value, makes no difference: both are MUCH faster than the clipboard solution.

Obviously the iterative approaches are much slower, but notice how much slower the clipboard solution is, compared to directly assigning range values!



Time for another test run.
Testing with 1500 cells (100 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, single operation:    7.8125ms
Array read+write, single operation:       3.90625ms
Pasting from clipboard, iterative:        10480.46875ms
Setting cell values, iterative:           1125ms

Testing with 1500 cells (100 rows)
Pasting from clipboard, single operation: 19.53125ms
Setting cell values, single operation:    3.90625ms
Array read+write, single operation:       3.90625ms
Pasting from clipboard, iterative:        10859.375ms
Setting cell values, iterative:           2390.625ms

Testing with 1500 cells (100 rows)
Pasting from clipboard, single operation: 15.625ms
Setting cell values, single operation:    3.90625ms
Array read+write, single operation:       3.90625ms
Pasting from clipboard, iterative:        10964.84375ms
Setting cell values, iterative:           1062.5ms
Much less clear-cut now, but dumping an array still seems the more reliably faster solution.



Let's see what 1000 rows gives us:
Testing with 15000 cells (1000 rows)
Pasting from clipboard, single operation: 15.625ms
Setting cell values, single operation:    15.625ms
Array read+write, single operation:       15.625ms
Pasting from clipboard, iterative:        80324.21875ms
Setting cell values, iterative:           11859.375ms
I don't have the patience. Commenting-out the iterative tests.
Testing with 15000 cells (1000 rows)
Pasting from clipboard, single operation: 19.53125ms
Setting cell values, single operation:    15.625ms
Array read+write, single operation:       15.625ms

Testing with 15000 cells (1000 rows)
Pasting from clipboard, single operation: 23.4375ms
Setting cell values, single operation:    15.625ms
Array read+write, single operation:       15.625ms




Quite consistent; again, clipboard loses. But how about 10K rows?
Testing with 150000 cells (10000 rows)
Pasting from clipboard, single operation: 46.875ms
Setting cell values, single operation:    144.53125ms
Array read+write, single operation:       152.34375ms

Testing with 150000 cells (10000 rows)
Pasting from clipboard, single operation: 46.875ms
Setting cell values, single operation:    148.4375ms
Array read+write, single operation:       148.4375ms

Testing with 150000 cells (10000 rows)
Pasting from clipboard, single operation: 50.78125ms
Setting cell values, single operation:    144.53125ms
Array read+write, single operation:       152.34375ms
And here we are - clipboard clearly wins now!



Bottom line: if you have 100K cells to work with, clipboard is probably a good idea. If you have 10K cells to work with (or less), Value assignment array dump is probably the faster approach. Anything in-between probably requires benchmarking and testing to figure out the faster approach.

TL;DR: There's no silver bullet one-size-fits-all solution.

You'll want to avoid copy/pasting when you're working with a relatively small number of cells, and/or if you're iterating individual cells. For large, bulk operations involving a lot of data, clipboard isn't a crazy idea.

For the sake of completion:
Testing with 1500000 cells (100000 rows)
Pasting from clipboard, single operation: 324.21875ms
Setting cell values, single operation:    1496.09375ms
Array read+write, single operation:       1578.125ms

Testing with 1500000 cells (100000 rows)
Pasting from clipboard, single operation: 324.21875ms
Setting cell values, single operation:    1445.3125ms
Array read+write, single operation:       1515.625ms

Testing with 1500000 cells (100000 rows)
Pasting from clipboard, single operation: 367.1875ms
Setting cell values, single operation:    1562.5ms
Array read+write, single operation:       1574.21875ms
For huge YUGE ranges, directly setting cell values seems to consistently outperform array dump, but the clipboard outperforms both, and by quite a margin.

So:


Less than 1K cells: value assignment or array dump, test to find out
Less than 100K cells: array dump
More than 150K cells: clipboard
Anything in-between: array dump or clipboard, test to find out
In no circumstance the faster approach is an iterative solution, by several orders of magnitude.


                        
这篇关于复制/ PasteSpecial vs Range.Value = Range.Value的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！