All geek questions in one place

Copy / PasteSpecial vs Range.Value = Range.Value

I read several times on this site (and elsewhere) that it is better to avoid copying / pasting, if possible, in VBA macros. For example, instead ...

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

... maybe a better / faster way 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 on a large table (15 columns, 100 thousand rows), the copy / paste version was much faster (1.9 s versus 2.7 seconds). The difference remains, even if I first declared tbl.DataBodyRange as a range variable.

I thought this might be some weird ListObjects property, but the difference was even greater 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 much faster? And are there any additional reasons to avoid using copy / paste (provided that the clipboard will never be used outside of Excel while the macro is running)?

EDIT: , Copy/PasteValues ​​ / , Mat Mug . 1000 1 , 1000 , 10 . , ( , - ~ 15 . ).

Full test results

10 ( 100 100000 , 100 ), , . "MicroTimer" , , , "Set Array", ".Value =.Value" ( , "", ). , / , 3300 . read/write .Value =.Value, .

Full test 2 results

, :

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
+4
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 , -, , , .

:

  • 100 : /
  • 150 . :
  • , : , ,
  • - .
+5

Source: https://habr.com/ru/post/1681144/

More articles:


All Articles