Improving VBA Efficiency when Copying

[Joe4]

Calling all VBA efficiency experts! Here is a multi-part question:

I have read that in speeding up Excel macros, avoid the use of “Copy” and “Paste” whenever possible.
For example, use:
Sheet2.Range(“B1:B200”).Value = Sheet1.Range(“A1:A100”).Value
Instead of:
Sheet1.Range(“A1:A100”).Copy Destination:= Sheet2.Range(“B1”)

My first question has two parts:
1. Can you do something similar when copying a whole sheet of unknown size to another sheet? If so, how?
2. Can you do something similar when just copying cell FORMATTING, not values? If so, how?

My second question is this:
I also read that putting “Application.EnableEvents=False” at the beginning of your code may speed it up. What does this do? What are “events”?

Thanks.

 

[Dave Hawley]

Hi all

After reading this thread I have realized I had made a mistake on the page:
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

It was suposed to use the PasteSpecial as the one to avoid. The main point I'm trying to make is the Clipboard should be avoided when Copying, something the PasteSpecial cannot do, but the Copy Method with the Destination argument can.

As to whether it's worth it, I still say a big yes. If we are do something it may as well be done efficiently, especially when no extra effort is required. In fact, the question should really be "Why wouldn't we do it efficiently?" A few seconds here and there add up to make a difference.


_________________
Regards
Dave Hawley
Excel Email, Online and Download Training, Excel Add-ins and Business Software. Hundreds of free tips, tricks, code, Add-ins and downloads
This message was edited by Dave Hawley on 2003-02-14 23:24

 

[Legacy 12630]

To Dave Hawley

I've just had a look at your web site, and presume that you have revised it re your post to this thread.


Your site says :-

'Instead of:
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").pasteSpecial
'Use:
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
.........
'Or:
Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula


I think this could be a bit confusing for some readers, because each of these three bits of code does a different thing.

 

[Dave Hawley]

Hi Ponsonby

Thanks for the feedback, much appreciated. Have changed again, feel free to let me know.

 

[NateO]

Hello,

Your site says :-

'Instead of:
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").pasteSpecial
'Use:
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value

What I don't necessarily understand here is that the substitution code is slower regarding a single paste as values operation. The clipboard isn't necessarily the bugger here, it's the fact that the pastespecial method selects cells. This is a major deficiency IMO, hopefully v. 11 will clean this up.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2003-02-16 21:05

 

[Dave Hawley]

Hi NateO

Let's first post my example as a whole, it seems to be constantly taken out of context.

Avoid the use of Copy and Paste whenever Possible:

Sub NoCopyAndPaste()
'Instead of:
 Sheet1.Range("A1:A200").Copy
 Sheet2.Range("B1").pasteSpecial
 Application.CutCopyMode=False'Clear Clipboard
'Use:
 'By-passes the Clipboard
 Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")
'Or, if only values are needed:
 Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
'Or, if only formulae are needed:
 Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula
 'See also FormulaArray and FormulaR1C1 etc
'Instead of:
 Sheet1.Range("A1:A200").Copy
 Sheet1.Range("A1:A200").PasteSpecial xlPasteValues
 Application.CutCopyMode=False'Clear Clipboard
'Use:
 Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").Value
End Sub

What I don't necessarily understand here is that the substitution code is slower regarding a single paste as values operation.

Not so! Using your own timer, from page 2 of this thread, it proves to be OVER 3 times faster. Try it for yourself.

Sub Test1()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency
QueryPerformanceFrequency y
QueryPerformanceCounter str

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

    For i = 1 To 10000
        Sheet1.Range("A1:A200").Copy
        Sheet2.Range("B1").PasteSpecial
        Application.CutCopyMode = False
    Next
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

QueryPerformanceCounter fin
n = (fin - str)
MsgBox Format(n, "##########.############") / y
End Sub


Sub Test2()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency
QueryPerformanceFrequency y
QueryPerformanceCounter str

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

    For i = 1 To 10000
        Sheet2.Range("B1:B200").Value = Sheet1.Range("A1:A200").Value
    Next
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

QueryPerformanceCounter fin
n = (fin - str)
MsgBox Format(n, "##########.############") / y
End Sub

To give this a true comparison you should run both Procedures once to load them into Cache, the run them again and take note of the times. I have switched off ScreenUpdating, Events and Calculations to ensure there are no other determining factors, i.e compare apples with apples. You should really also add the XlValues agument to the PasteSpecial.

The clipboard isn't necessarily the bugger here, it's the fact that the pastespecial method selects cells

While it is true the PasteSpecial does select the destination, I do not believe that to be the major bottlneck. I believe it is the fact that it must first copy to the Clipboard and then paste to the destination. The Copy Method, with the Destination Argument, copies direct to the destination only and hence no Paste is involved.



_________________
Regards
Dave Hawley
Excel Email, Online and Download Training, Excel Add-ins and Business Software. Hundreds of free tips, tricks, code, Add-ins and downloads
This message was edited by Dave Hawley on 2003-02-16 21:37

 

[NateO]

Hi Dave, with all due respect, that's not a single operation, that's 10,000 of them. The selecting is what's slowing this down. I've already agreed with this via test1 vs. test2. On my PC, with this loop, your method is much faster, I came up with a figure like ~17x.

But do it once, no loop. If you reread page 2, you'll notice I did that in test3 and test4, the clipboard/pastespecial method is faster, ~twice as fast, despite the cell selection. I ran them all about 50 times each before posting.

This is the sole reason I attribute the delay to cell selecting. Not trying to be argumentative, just trying to re-articulate what I mentioned on the 2nd page of this thread.

Have a good one.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2003-02-16 22:48

 

[Dave Hawley]

Hi Nate

Yes it is 10, 000 operations, but that is only so we can see a reasonable result. Even using one operation for each, my suggested method is still faster, try it.

Public Declare Function QueryPerformanceFrequency Lib _
    "kernel32" (lpFrequency As Currency) As Long
Public Declare Function QueryPerformanceCounter Lib _
    "kernel32.dll" (lpPerformanceCount As Currency) As Long



Sub Test1()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency
QueryPerformanceFrequency y
QueryPerformanceCounter str

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False


        Sheet1.Range("A1:A200").Copy
        Sheet2.Range("B1").PasteSpecial
        Application.CutCopyMode = False

    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

QueryPerformanceCounter fin
n = (fin - str)
MsgBox Format(n, "##########.############") / y
End Sub


Sub Test2()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency
QueryPerformanceFrequency y
QueryPerformanceCounter str

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

 
        Sheet2.Range("B1:B200").Value = Sheet1.Range("A1:A200").Value
 
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

QueryPerformanceCounter fin
n = (fin - str)
MsgBox Format(n, "##########.############") / y
End Sub

I ran each code ten times alternating between procedures and never once go a faster result from the PasteSpecial. Most of the time my Method was 3 times faster.

 

[NateO]

Hello Dave,

I ran each code ten times alternating between procedures and never once go a faster result from the PasteSpecial. Most of the time my Method was 3 times faster.

Well, running your tests, I agree with you. Method 1 runs in about ~4 milliseconds on my PC, wheras Method 2 runs in ~2,75 milliseconds.

Just for fun change the range as such:

<pre>
Public Declare Function QueryPerformanceFrequency Lib _
"kernel32" (lpFrequency As Currency) As Long
Public Declare Function QueryPerformanceCounter Lib _
"kernel32.dll" (lpPerformanceCount As Currency) As Long



Sub Test1()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency
QueryPerformanceFrequency y
QueryPerformanceCounter str

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False


Sheet1.Range("A1:A10000").Copy
Sheet2.Range("B1").PasteSpecial xlValues
Application.CutCopyMode = False


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

QueryPerformanceCounter fin
n = (fin - str)
MsgBox Format(n, "##########.############") / y
End Sub


Sub Test2()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency
QueryPerformanceFrequency y
QueryPerformanceCounter str

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False


Sheet2.Range("B1:B10000").Value = Sheet1.Range("A1:A10000").Value


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

QueryPerformanceCounter fin
n = (fin - str)
MsgBox Format(n, "##########.############") / y
End Sub</pre>

Method 1 runs in ~6 milliseconds whereas 2 runs in ~9 milliseconds on my PC. So, at least in Minneapolis, there seems to be variance based on range size.

Thanks for pointing this out.