excel - 复制/粘贴特殊与 Range.Value = Range.Value

Question

我在本网站(和其他地方)上多次读到，如果可能的话，最好避免在 VBA 宏中进行复制/粘贴。例如，不要这样做......

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

...这样做据说更好/更快:

For i = 1 To tbl.ListColumns.Count
    With tbl.ListColumns(i)
        .DataBodyRange.FormulaR1C1 = "=2*1"
        .DataBodyRange = .DataBodyRange.Value
    End With
Next

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

我认为这可能是 ListObjects 的一些奇怪的属性，但如果没有它们，差异实际上更大:

'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

有谁知道为什么复制/粘贴方法如此快？是否还有其他原因避免使用复制/粘贴(假设在宏运行时永远不会在 Excel 之外使用剪贴板)？

编辑:这是第一组测试结果，将 Copy/PasteValues 与 Mat's Mug 在接受的答案中描述的数组读/写方法进行比较。我测试了从 1000 个单元格到 100 万个单元格的范围大小，每次增加 1000 个，并对每个范围大小进行 10 次测试取平均值。复制粘贴开始较慢，但很快就超过了设定值方法(在图表上很难看到，但盈亏平衡点约为 15k 个单元格)。

我还在范围的下限(范围大小从 100 个单元格到 100000 个单元格，每次递增 100)进行了 10 次进一步测试，试图确定盈亏平衡点发生的位置。这次我用了Charles Williams' "MicroTimer"而不是默认计时器，希望它对于亚秒计时更加准确。我还包括了“Set Array”版本和原始的“.Value = .Value”版本(并且记得将计算切换为手动，这与第一组测试期间不同)。有趣的是，这次阵列读/写方法的表现明显更差，盈亏平衡点约为 3300 个单元，峰值性能更差。数组读/写和 .Value = .Value 之间几乎没有区别，尽管数组版本的性能稍差一些。

这是我用于上一轮测试的代码:

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

这是我使用的 MicroTimer 版本(在单独的模块中):

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

Answer 1

大多数(无论如何，很多)VBA 宏不“使用集合”并迭代某个范围内的单元格。不是因为这是一个好主意(其实不是)，而是因为很多人根本不知道更好的主意。

在处理诸如 Range 之类的对象集合时，最快的循环是 For Each 循环。因此，我接受了您的测试，对其进行了一些重构，添加了迭代解决方案的测试，然后添加了数组读/写测试，因为这也是复制单元格值的常见且良好的方法。

请注意，我从各个测试中提取了公式编写设置步骤。

注意:此代码采用了控制流最佳实践，并将其隐藏起来。 请勿在实际代码中使用GoSub/Return。

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

<小时/>

我必须将范围大小减小一个数量级(否则我仍然会盯着没有响应的 Excel 屏幕)，但这就是输出 - 当然，逐个单元格迭代方法是很多的速度较慢，但​​请注意剪贴板数字与直接 Value 赋值的比较:

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

Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, 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
Pasting from clipboard, iterative:        1691.40625ms
Setting cell values, iterative:           136.71875ms

因此，对于 10 行/150 个单元格，将范围复制到数组/分配 Range.Value 比剪贴板解决方案快得多。

显然，迭代方法要慢得多，但请注意与直接分配范围值相比，剪贴板解决方案慢了多少!

<小时/>

是时候进行另一次测试了。

Testing with 1500 cells (100 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, single operation:    7.8125ms
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
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
Pasting from clipboard, iterative:        10964.84375ms
Setting cell values, iterative:           1062.5ms

现在不太明确，但转储数组似乎仍然是更可靠、更快的解决方案。

<小时/>

让我们看看 1000 行给我们带来了什么:

Testing with 15000 cells (1000 rows)
Pasting from clipboard, single operation: 15.625ms
Setting cell values, single operation:    15.625ms
Pasting from clipboard, iterative:        80324.21875ms
Setting cell values, iterative:           11859.375ms

我没那个耐心。注释掉迭代测试。

Testing with 15000 cells (1000 rows)
Pasting from clipboard, single operation: 19.53125ms
Setting cell values, single operation:    15.625ms

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

<小时/>

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

Testing with 150000 cells (10000 rows)
Pasting from clipboard, single operation: 46.875ms
Setting cell values, single operation:    144.53125ms

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

Testing with 150000 cells (10000 rows)
Pasting from clipboard, single operation: 50.78125ms
Setting cell values, single operation:    144.53125ms

我们在这里 - 剪贴板现在显然获胜了!

<小时/>

底线:如果您有 100K 个单元格可供使用，那么剪贴板可能是一个好主意。如果您有 10K 个单元格可供使用(或更少)，Value 赋值 数组转储可能是更快的方法。介于两者之间的任何事情都可能需要基准测试和测试来找出更快的方法。

TL;DR:没有万能的解决方案。

当您使用相对较少的单元格和/或迭代单个单元格时，您需要避免复制/粘贴。对于涉及大量数据的大型批量操作，剪贴板并不是一个疯狂的主意。

为了完成:

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

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

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

对于巨大的YUGE范围，直接设置单元格值似乎始终优于数组转储，但剪贴板的性能优于两者，而且幅度很大。

所以:

• 单元格少于 100K:数组转储/值分配
• 超过 15 万个单元格:剪贴板
• 介于两者之间的任何内容:数组转储或剪贴板，通过测试找出答案
• 在任何情况下，更快的方法都不是迭代解决方案，快几个数量级。