Hi all,

Any ideas why this:

sub demo()

application.calculation = xlcalculationmanual
range("B2:U201").formula = "=$A2&B$1"

end sub

would be so much slower than this:

sub demo()

application.calculation = xlcalculationmanual

dim i as integer
dim j as integer

for i = 1 to 200
for j = 1 to 20
range("A1").offset(i,j).formula = "=" &
range("a1").offset(i,0).address(false,true) & "&" &
range("a1").offset(0,j).address(true,false)
next j
next i

end sub

The actual formula in my code is more complex than =$A1&B$2, but the
principle is the same.

Sam

If you go to the store to get milk, eggs, and bread and get it all in one trip,
it's much quicker than making 3 separate trips.

Writing to all 4000 of those cells (B2:U201) in one step is much quicker than
writing to 4000 cells one at a time.



Sam Wilson wrote:

Hi all,

Any ideas why this:

sub demo()

application.calculation = xlcalculationmanual
range("B2:U201").formula = "=$A2&B$1"

end sub

would be so much slower than this:

sub demo()

application.calculation = xlcalculationmanual

dim i as integer
dim j as integer

for i = 1 to 200
for j = 1 to 20
range("A1").offset(i,j).formula = "=" &
range("a1").offset(i,0).address(false,true) & "&" &
range("a1").offset(0,j).address(true,false)
next j
next i

end sub

The actual formula in my code is more complex than =$A1&B$2, but the
principle is the same.

Sam

--

Dave Peterson

That's what I expected, but it takes 3 times as long.

"Dave Peterson" wrote:

If you go to the store to get milk, eggs, and bread and get it all in one trip,
it's much quicker than making 3 separate trips.

Writing to all 4000 of those cells (B2:U201) in one step is much quicker than
writing to 4000 cells one at a time.



Sam Wilson wrote:

Hi all,

Any ideas why this:

sub demo()

application.calculation = xlcalculationmanual
range("B2:U201").formula = "=$A2&B$1"

end sub

would be so much slower than this:

sub demo()

application.calculation = xlcalculationmanual

dim i as integer
dim j as integer

for i = 1 to 200
for j = 1 to 20
range("A1").offset(i,j).formula = "=" &
range("a1").offset(i,0).address(false,true) & "&" &
range("a1").offset(0,j).address(true,false)
next j
next i

end sub

The actual formula in my code is more complex than =$A1&B$2, but the
principle is the same.

Sam

--

Dave Peterson

I missed the part that said that using all cells was slower. I'd agree that
it's that UDF that must be causing the delay.

Sam Wilson wrote:

That's what I expected, but it takes 3 times as long.

"Dave Peterson" wrote:

If you go to the store to get milk, eggs, and bread and get it all in one trip,
it's much quicker than making 3 separate trips.

Writing to all 4000 of those cells (B2:U201) in one step is much quicker than
writing to 4000 cells one at a time.



Sam Wilson wrote:

Hi all,

Any ideas why this:

sub demo()

application.calculation = xlcalculationmanual
range("B2:U201").formula = "=$A2&B$1"

end sub

would be so much slower than this:

sub demo()

application.calculation = xlcalculationmanual

dim i as integer
dim j as integer

for i = 1 to 200
for j = 1 to 20
range("A1").offset(i,j).formula = "=" &
range("a1").offset(i,0).address(false,true) & "&" &
range("a1").offset(0,j).address(true,false)
next j
next i

end sub

The actual formula in my code is more complex than =$A1&B$2, but the
principle is the same.

Sam

--

Dave Peterson


--

Dave Peterson

Sam, the first one would be faster..

If this post helps click Yes
---------------
Jacob Skaria


"Sam Wilson" wrote:

Hi all,

Any ideas why this:

sub demo()

application.calculation = xlcalculationmanual
range("B2:U201").formula = "=$A2&B$1"

end sub

would be so much slower than this:

sub demo()

application.calculation = xlcalculationmanual

dim i as integer
dim j as integer

for i = 1 to 200
for j = 1 to 20
range("A1").offset(i,j).formula = "=" &
range("a1").offset(i,0).address(false,true) & "&" &
range("a1").offset(0,j).address(true,false)
next j
next i

end sub

The actual formula in my code is more complex than =$A1&B$2, but the
principle is the same.

Sam

It should be: it isn't.

The way I expected to be faster takes three times as long.

"Jacob Skaria" wrote:

Sam, the first one would be faster..

If this post helps click Yes
---------------
Jacob Skaria


"Sam Wilson" wrote:

Hi all,

Any ideas why this:

sub demo()

application.calculation = xlcalculationmanual
range("B2:U201").formula = "=$A2&B$1"

end sub

would be so much slower than this:

sub demo()

application.calculation = xlcalculationmanual

dim i as integer
dim j as integer

for i = 1 to 200
for j = 1 to 20
range("A1").offset(i,j).formula = "=" &
range("a1").offset(i,0).address(false,true) & "&" &
range("a1").offset(0,j).address(true,false)
next j
next i

end sub

The actual formula in my code is more complex than =$A1&B$2, but the
principle is the same.

Sam

I just stuck a "Debug.Print Time" statement at the beginning and end of each
macro and ran them on separate newly inserted worksheets... the first one
shows no difference (both statements appear to print out at the same time)
whereas your second one shows a time difference of one second. These results
were consistent over three separate trials. So, I would conclude that the
timing is in line with expectations... and the opposite of what you are
reporting. If it helps any, I am running XL2003 (SP3) on Vista (SP1).

--
Rick (MVP - Excel)


"Sam Wilson" wrote in message
...
It should be: it isn't.

The way I expected to be faster takes three times as long.

"Jacob Skaria" wrote:

Sam, the first one would be faster..

If this post helps click Yes
---------------
Jacob Skaria


"Sam Wilson" wrote:

Hi all,

Any ideas why this:

sub demo()

application.calculation = xlcalculationmanual
range("B2:U201").formula = "=$A2&B$1"

end sub

would be so much slower than this:

sub demo()

application.calculation = xlcalculationmanual

dim i as integer
dim j as integer

for i = 1 to 200
for j = 1 to 20
range("A1").offset(i,j).formula = "=" &
range("a1").offset(i,0).address(false,true) & "&" &
range("a1").offset(0,j).address(true,false)
next j
next i

end sub

The actual formula in my code is more complex than =$A1&B$2, but the
principle is the same.

Sam

I've stripped it down to two sub which run sequentially, logged start time
and end time for each and run a couple of hundred times. The way that should
be faster is consistently 3 time slower than the long winded way.

In the actual code (not the sample code I posted) the formula applied is a
UDF from a third party - their VB is password protected so I can't see what
it's doing but that UDF must be the cause.

It's a strange one.

"Rick Rothstein" wrote:

I just stuck a "Debug.Print Time" statement at the beginning and end of each
macro and ran them on separate newly inserted worksheets... the first one
shows no difference (both statements appear to print out at the same time)
whereas your second one shows a time difference of one second. These results
were consistent over three separate trials. So, I would conclude that the
timing is in line with expectations... and the opposite of what you are
reporting. If it helps any, I am running XL2003 (SP3) on Vista (SP1).

--
Rick (MVP - Excel)


"Sam Wilson" wrote in message
...
It should be: it isn't.

The way I expected to be faster takes three times as long.

"Jacob Skaria" wrote:

Sam, the first one would be faster..

If this post helps click Yes
---------------
Jacob Skaria


"Sam Wilson" wrote:

Hi all,

Any ideas why this:

sub demo()

application.calculation = xlcalculationmanual
range("B2:U201").formula = "=$A2&B$1"

end sub

would be so much slower than this:

sub demo()

application.calculation = xlcalculationmanual

dim i as integer
dim j as integer

for i = 1 to 200
for j = 1 to 20
range("A1").offset(i,j).formula = "=" &
range("a1").offset(i,0).address(false,true) & "&" &
range("a1").offset(0,j).address(true,false)
next j
next i

end sub

The actual formula in my code is more complex than =$A1&B$2, but the
principle is the same.

Sam

For me, the first method was 4-20 times faster than the second, depending on
how many times I ran it (I added an outer loop to each demo routine).

Do you experience the same slowdown with these demo routines as with your
"real" routines?

Eric

"Sam Wilson" wrote:

Hi all,

Any ideas why this:

sub demo()

application.calculation = xlcalculationmanual
range("B2:U201").formula = "=$A2&B$1"

end sub

would be so much slower than this:

sub demo()

application.calculation = xlcalculationmanual

dim i as integer
dim j as integer

for i = 1 to 200
for j = 1 to 20
range("A1").offset(i,j).formula = "=" &
range("a1").offset(i,0).address(false,true) & "&" &
range("a1").offset(0,j).address(true,false)
next j
next i

end sub

The actual formula in my code is more complex than =$A1&B$2, but the
principle is the same.

Sam