PDA

View Full Version : Solved: Which Runs Quicker???



Djblois
01-24-2007, 10:07 AM
I have some code to format a column the way I want and I use similar code multiple times. I was wondering which would run quicker???

detail.Range("S1").FormulaR1C1 = "Unit Cost ($)"
detail.Range("S1").WrapText = True
With detail.Columns("S:S")
.TextToColumns Destination:=Range("S1")
.Style = "Comma"
.AutoFit
End With

or


Range("S1").FormulaR1C1 = "Unit Cost ($)"
Range("S1").WrapText = True
With Columns("S:S")
.TextToColumns Destination:=Range("S1")
.Style = "Comma"
.AutoFit
End With


the only difference is I removed detail from the front of the lines. Detail is the variable name I set to that sheet. I read in one of the articles here that the more periods you have the slower it would run. But I also read that using variables, so excel doesn't have to figure out where is quicker. So which one is quicker?

Bob Phillips
01-24-2007, 10:13 AM
I can't believe that either of those would be differentiable in terms of speed.

Djblois
01-24-2007, 10:16 AM
I know if I use it once it wouldn't hurt speed but what if they are used 25 times?? would that matter then???

Bob Phillips
01-24-2007, 10:25 AM
25 times milliseconds is still very little.

Concentrate on the big problems, such as loops, selecting ranges, et al.

Djblois
01-24-2007, 10:45 AM
I got rid of most of the selecting. Now, what do you mean concentrate on loops???

Bob Phillips
01-24-2007, 11:00 AM
Well loops are often the time consumers. For instance, looping thorugh 10,000 rows deleting rows where a certain condition is met is slow. It is much quicker to filter on that condition, and delete the visible rows.

There is always a trade-off between easy to understand, maintainable code, and finely tuned, optimised code. There are no cut and dried answers, just depends upon the circumstances.

Often, turning screenupdating off and setting calcualtion to manual at the start and restting at the end can save huge amounts of time, your dot qualifiers don't even figure in performance issues.

Djblois
01-24-2007, 11:06 AM
how do you set calculation to manual?? And what does it mean??

I already filter and delete selected. Thank you for all your help.

Bob Phillips
01-24-2007, 11:13 AM
Application.Calculation = xlCalculationManual


and set to xlCalculationAutomatic when completed.

What it does it to stop Excel from recalculating every time something changes. So, if you write to the worksheet say 1000 times, Excel will recalculate 1000 times. That could be expensive on a busy worksheet, so turning calculation to manual avoids all of that. Setting it back to AUtomatic at the end menas that just one recalculation is invoked.

lucas
01-24-2007, 11:35 AM
You can test the time it takes a procedure to run...Ken has a function in the kb:
http://vbaexpress.com/kb/getarticle.php?kb_id=542

Djblois
01-24-2007, 11:43 AM
Thank you Lucas,

I am going to try that

Norie
01-25-2007, 11:10 AM
Daniel

Why have you removed detail from the code?

If you do that then the range is unqualified so VBA will format the range on whatever the active sheet is.

johnske
01-25-2007, 09:23 PM
I have some code to format a column the way I want and I use similar code multiple times. I was wondering which would run quicker???

detail.Range("S1").FormulaR1C1 = "Unit Cost ($)"
detail.Range("S1").WrapText = True
With detail.Columns("S:S")
.TextToColumns Destination:=Range("S1")
.Style = "Comma"
.AutoFit
End With

or


Range("S1").FormulaR1C1 = "Unit Cost ($)"
Range("S1").WrapText = True
With Columns("S:S")
.TextToColumns Destination:=Range("S1")
.Style = "Comma"
.AutoFit
End With


the only difference is I removed detail from the front of the lines. Detail is the variable name I set to that sheet. I read in one of the articles here that the more periods you have the slower it would run. But I also read that using variables, so excel doesn't have to figure out where is quicker. So which one is quicker?A large number of property calls inside a loop is what takes time.

The idea of using either a variable or a With statement is simply to reduce the number of property calls on the inside of a loop and either way produces the same end (the With statement simply eliminates the need for any intermediate variable). As EL XID said, any speed difference is not even worth considering... :)