Doug-

Thanks for the information about the slow down with using worksheetfunction.

I don't use it much but when I do it's for either vlookup, match, or index.

Do you know of (or can write) an example of how to replace these
worksheetfunctions with array functionality in vba? I am not very good with
arrays yet.

Thanks

" wrote:

I haven't found a significant difference in performance with the
different file formats, other than time for opening and saving, which
is much quicker in xlsb than xls.

On a dual core machine recalculation is quicker in 2007, but VBA
doesn't use the dual cores,and is slower, sometimes much slower. In
particular any operation that involves interaction between VB and the
spreadsheet (such as using worksheetfunction) seems to be very much
slower in 2007.

More details he
http://newtonexcelbach.wordpress.com...edback-please/
http://newtonexcelbach.wordpress.com...nction-vs-udf/

The only solution I know is to avoid using worksheetfunction, and work
on arrays inside VB, rather than reading and writing directly to
worksheet ranges.




On May 22, 8:08 am, JNW wrote:
I tried all the extensions below and these are the results using xl2007
xls (original format): 6 min 37 sec
xlsb: 7 min 9 sec
xlsm: 6 min 47 sec
xlsx: 3 min 30 sec

running an xls using 2003 is in the 3-4 minute range.

Thanks for the suggestions. I'll have to wait to change the file to an xlsx
until the client gets all their users to xl07.

This begs another question though... why is the xlsx faster than the xlsm
(which I would presume is better for handling macros)? Do you know why they
made the distinction between all of these different file types? (or where I
can find documentation on that?)

Thanks again. You've relieved a lot of frustration.



"Nick Hodge" wrote:
You will find calculation faster if you save as an xlsb file as opposed to
xlsx or xlsm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

web:www.excelusergroup.org
web:www.nickhodge.co.uk

"JNW" wrote in message
...
I have read multiple threads here regarding speed issues and xl2007. They
all have said that it has to do with charting or lack of SP1.

So what if I don't create charts with my code, and have installed SP1? I
am
still experiencing a 50% slowdown. I've found that it somehow has
something
to do with calculation. I have a number of circular references that are
needed in the workbook. I have iterations on and set to the same setting
as
they were in 2003. I've tried turning calculations off and on at various
points in the code, but to no avail.

Does anyone have any experience with this? Alternatively, does anyone
have
a reference as to how xl2007 calculates (what order) and maybe a
comparison
between 2003 and 2007 calculation steps?

Thanks- Hide quoted text -

- Show quoted text -