The long way round seems faster...

[Sam Wilson]

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]

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.

 

[Jacob Skaria]

Sam, the first one would be faster..

If this post helps click Yes

 

[Sam Wilson]

It should be: it isn't.

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

 

[Sam Wilson]

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

 

[Rick Rothstein]

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

 

[EricG]

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]

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.

 

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