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