Best Practice

Since I found out yo may write
[Sheet2!A1:b2].value
instead of :
Worksheets("Sheet2").Range("A1:B2").Value

I would like to know wether this is bad practice or just a performance issue

TIA

--
AP

Using the [] method is slower than second method. I always use
the second method, though that is a matter of personal
preference.


"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:%23VQ3WzZNGHA.964@tk2msftngp13.phx.gbl...
> Since I found out yo may write
> [Sheet2!A1:b2].value
> instead of :
> Worksheets("Sheet2").Range("A1:B2").Value
>
> I would like to know wether this is bad practice or just a
> performance issue
>
> TIA
>
> --
> AP
>
>

I thought VBA was compiled before execution.
If true, how come one method is faster than the other?

TIA

--
AP

"Chip Pearson" <chip@cpearson.com> a écrit dans le message de
news:Ow$Rm0ZNGHA.1312@TK2MSFTNGP09.phx.gbl...
> Using the [] method is slower than second method. I always use
> the second method, though that is a matter of personal
> preference.
>
>
> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> news:%23VQ3WzZNGHA.964@tk2msftngp13.phx.gbl...
> > Since I found out yo may write
> > [Sheet2!A1:b2].value
> > instead of :
> > Worksheets("Sheet2").Range("A1:B2").Value
> >
> > I would like to know wether this is bad practice or just a
> > performance issue
> >
> > TIA
> >
> > --
> > AP
> >
> >
>
>

The contents of the [] characters are evaluated at run time.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:e25dS7ZNGHA.1608@TK2MSFTNGP11.phx.gbl...
>I thought VBA was compiled before execution.
> If true, how come one method is faster than the other?
>
> TIA
>
> --
> AP
>
> "Chip Pearson" <chip@cpearson.com> a écrit dans le message de
> news:Ow$Rm0ZNGHA.1312@TK2MSFTNGP09.phx.gbl...
>> Using the [] method is slower than second method. I always use
>> the second method, though that is a matter of personal
>> preference.
>>
>>
>> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
>> news:%23VQ3WzZNGHA.964@tk2msftngp13.phx.gbl...
>> > Since I found out yo may write
>> > [Sheet2!A1:b2].value
>> > instead of :
>> > Worksheets("Sheet2").Range("A1:B2").Value
>> >
>> > I would like to know wether this is bad practice or just a
>> > performance issue
>> >
>> > TIA
>> >
>> > --
>> > AP
>> >
>> >
>>
>>
>
>

Thanks for that definite response!

--
AP

"Chip Pearson" <chip@cpearson.com> a écrit dans le message de
news:%23rtTs9ZNGHA.2828@TK2MSFTNGP12.phx.gbl...
> The contents of the [] characters are evaluated at run time.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> news:e25dS7ZNGHA.1608@TK2MSFTNGP11.phx.gbl...
> >I thought VBA was compiled before execution.
> > If true, how come one method is faster than the other?
> >
> > TIA
> >
> > --
> > AP
> >
> > "Chip Pearson" <chip@cpearson.com> a écrit dans le message de
> > news:Ow$Rm0ZNGHA.1312@TK2MSFTNGP09.phx.gbl...
> >> Using the [] method is slower than second method. I always use
> >> the second method, though that is a matter of personal
> >> preference.
> >>
> >>
> >> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> >> news:%23VQ3WzZNGHA.964@tk2msftngp13.phx.gbl...
> >> > Since I found out yo may write
> >> > [Sheet2!A1:b2].value
> >> > instead of :
> >> > Worksheets("Sheet2").Range("A1:B2").Value
> >> >
> >> > I would like to know wether this is bad practice or just a
> >> > performance issue
> >> >
> >> > TIA
> >> >
> >> > --
> >> > AP
> >> >
> >> >
> >>
> >>
> >
> >
>
>

I sometimes use [] because of brevity. Harlan always jumps on me when I do
it.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Chip Pearson" <chip@cpearson.com> wrote in message
news:Ow$Rm0ZNGHA.1312@TK2MSFTNGP09.phx.gbl...
> Using the [] method is slower than second method. I always use the second
> method, though that is a matter of personal preference.
>
>
> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> news:%23VQ3WzZNGHA.964@tk2msftngp13.phx.gbl...
>> Since I found out yo may write
>> [Sheet2!A1:b2].value
>> instead of :
>> Worksheets("Sheet2").Range("A1:B2").Value
>>
>> I would like to know wether this is bad practice or just a performance
>> issue
>>
>> TIA
>>
>> --
>> AP
>>
>>
>
>

> Harlan always jumps on me when I do it.

Why am I not surprised?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:en9PSDaNGHA.536@TK2MSFTNGP09.phx.gbl...
>I sometimes use [] because of brevity. Harlan always jumps on me
>when I do it.
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:Ow$Rm0ZNGHA.1312@TK2MSFTNGP09.phx.gbl...
>> Using the [] method is slower than second method. I always use
>> the second method, though that is a matter of personal
>> preference.
>>
>>
>> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
>> news:%23VQ3WzZNGHA.964@tk2msftngp13.phx.gbl...
>>> Since I found out yo may write
>>> [Sheet2!A1:b2].value
>>> instead of :
>>> Worksheets("Sheet2").Range("A1:B2").Value
>>>
>>> I would like to know wether this is bad practice or just a
>>> performance issue
>>>
>>> TIA
>>>
>>> --
>>> AP
>>>
>>>
>>
>>
>
>

Following conclusions reached on the use of [], does the same apply to using (for example) [SumA1:A100)] instead of Application.Sum(Range("A1:A100")) or WorkSheetFunction.Sum(Range("A1:A100"))?

Thanks

David