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.

 

[Joe4]

NateO,

After your last post, I decided to test it myself to see what would happen. Since copy one range happens so quickly, I put the commands in loops that repeat 10,000 times.

First macro:

Sub MyTest()

    Application.ScreenUpdating = False
   
    For i = 1 To 10000
        Sheets("Sheet1").Cells(i, 1).Copy _
            Destination:=Sheets("Sheet2").Cells(i, 1)
    Next
    
    Application.ScreenUpdating = True
    
    MsgBox "Done!"

End Sub

Second macro:

Sub MyTest2()

    Application.ScreenUpdating = False
    
    For i = 1 To 10000
        Sheets("Sheet2").Cells(i, 1) = Sheets("Sheet1").Cells(i, 1)
    Next
    
    Application.ScreenUpdating = True
    
    MsgBox "Done!"

End Sub

The first macro, using copy, takes 17 seconds to run. The second macro only took 3 seconds. This seems to support the assertion that the web site said about trying to avoid using "Copy".

Maybe I am not understanding you correctly? Are we talking about the same assertion?

Also, what is the best way of adding a timer to your code? Is there some "ready built" code already out there? It does sound like something that would be very useful.

Thanks.

 

[Legacy 12630]

On 2003-02-13 08:38, jmiskey wrote:
Ponsonby,

Is that a yes?

It was a smiley face.

 

[Joe4]

Ponsonby,

I know that! I was trying to figure out if you are trying to "imply" something with it.
This message was edited by jmiskey on 2003-02-13 10:25
This message was edited by jmiskey on 2003-02-13 10:36

 

[NateO]

Howdy, well as Ponsonby sapiently mentioned earlier you're doing two different things here, in one case you're bringing everything, and in another, you're pasting as values....

Here's a timer for you.

Here's my timed procedures:<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


For i = 1 To 10000
Sheets("Sheet1").Cells(i, 1).Copy
Sheets("Sheet2").Cells(i, 1).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Next

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


For i = 1 To 10000
Sheets("Sheet2").Cells(i, 1) = Sheets("Sheet1").Cells(i, 1)
Next

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

In this case, the second is a hell of a lot faster, the paste special variety of operations actually makes cell selections, which really slows it down in the loop scenario. But for a block like this, you wouldn't want to loop, which Dave would be the first to tell you. Loop when necessary & that's it. With something like:<pre>
Sub Test3()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency
QueryPerformanceFrequency y
QueryPerformanceCounter str

Sheets("Sheet1").Range("a1:a10000").Copy
Sheets("Sheet2").Range("a1").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

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

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

Sheets(2).Range("a1:a10000").Value = _
Sheets(1).Range("a1:a10000").Value

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

Test3 is much much quicker than Test4.

So, a bunch of moving parts. Find something you really want to do, figure out the best way to do it manually and reproduce the code and time 'em. You would never copy a block like this one cell at time, it's inefficient, it will be with code too.

Edit: All of these procedures were in different modules, never declare the same function (api's in this case) twice in the same module, or in different modules, use a public declaration.

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

 

[Joe4]

I think you hit on a key point, which I did not know. You said that:

"the paste special variety of operations actually makes cell selections".

So it appears that the fastest method may differ, depending if you just want to copy the cells, or copy the values.

By the way, I realize that I would never want to copy a whole range like this with a loop. The only reason I did this was to see which method is fastest. Without a timer, if I copy the whole range at once, both methods take less than one second! So it is impossible for me to determine which one is quicker without a timer!

Thanks for your help!

 

[NateO]

On 2003-02-13 11:07, jmiskey wrote:
I think you hit on a key point, which I did not know. You said that:

"the paste special variety of operations actually makes cell selections".

So it appears that the fastest method may differ, depending if you just want to copy the cells, or copy the values.

By the way, I realize that I would never want to copy a whole range like this with a loop. The only reason I did this was to see which method is fastest. Without a timer, if I copy the whole range at once, both methods take less than one second! So it is impossible for me to determine which one is quicker without a timer!

Thanks for your help!

You're welcome. This is the same timer used when Bob I were splitting hairs over .x milliseconds. This is the only timer on your PC capable of such resolution (<10 milliseconds) that I'm aware of.

Glad to be of help. Have a good one.

 

[Legacy 12630]

Concern over writing efficient code can be taken too far.

Efficiency only becomes meaningful when there are a large number of actions to be run – as with the posted example of looping 10,000 times.

More often than not, run time differences between alternative coding possibilities are immaterial.

For example, the original post in this thread requested an alternative to copy/paste for copying a whole worksheet to another sheet.
This involves only one action.

If the run time to do this 10,000 times is 17 seconds by the “slow” method, then to do it once (as required by the original post) presumably only takes 0.0017 seconds.

Is it really necessary to seek code that does it faster than 0.0017 seconds?

 

[Joe4]

Ponsonby,

I agree, that if you are only copying a few ranges, saving fractions of seconds isn't really worth it. However please understand that I use oversimplified examples in my postings. We have macros that run for hours and copy thousands of ranges.

There are other instances too, in which loops may be involved, and I like to have a good understanding of the best way to do things.

 

[Legacy 12630]

On 2003-02-13 11:30, jmiskey wrote:
Ponsonby,

I agree, that if you are only copying a few ranges, saving fractions of seconds isn't really worth it. However please understand that I use oversimplified examples in my postings. We have macros that run for hours and copy thousands of ranges.

There are other instances too, in which loops may be involved, and I like to have a good understanding of the best way to do things.

Yes - agreed. We are talking the same language.

Would be interesting to see the macros you have that run for hours(!!) and investigate what loops and other inefficiencies could be avoided.

Very often, the biggest savings in run times can be achieved by finding alternatives to running large loops.

 

[Joe4]

That's is what I am doing right now, trying to weed out some of the inefficiencies. I think is my third post this week on inefficiencies!

By the way, most of the loops involved are looping through numerous files. We have a software package that produces numerous reports for each client. We are combining & analyzing the reports for each client.

There can be anywhere from 10-99 reports, and there are over 100 clients. We set up a batch file that kicks off the job, and it runs overnight, doing all the clients.