Which Runs Quicker???

[Djblois]

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]

I can't believe that either of those would be differentiable in terms of speed.

[Djblois]

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]

25 times milliseconds is still very little.

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

[Djblois]

I got rid of most of the selecting. Now, what do you mean concentrate on loops???

[Bob Phillips]

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]

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]

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]

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]

Thank you Lucas,

I am going to try that

[Norie]

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]

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...