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

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

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

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 -

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 -

JNW - You may find this thread from Daily Dose of Excel interesting.
It includes a simple VBA lookup function.

http://www.dailydoseofexcel.com/arch...mance-monitor/

To get the data from a worksheet range into an array simply declare
the array as a variant and use = to fill the array:

Dim myarray() as variant
myarray = Range("myrange").value

That will give you a base 1 array with the values from the spreadsheet
range; obviously you don't get the formatting or any other
information.

You access the values using the row and column index numbers:

ArrayValue1 = myarray(1,1) ' (row index, column index)

so you don't need an INDEX function.

A simple VBA lookup is given in the DDofE link, although I'm sure
there are better examples around.

Also you may find the two post on ranges and arrays from my blog
interesting:

http://newtonexcelbach.wordpress.com...es-and-arrays/
http://newtonexcelbach.wordpress.com...-and-arrays-2/




On May 23, 12:31*am, JNW wrote:
Doug-

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

On May 22, 6:08*am, JNW wrote:
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

Also make sure you have uninstalled the Google Desktop Office Search COM
addins: they slow down Excel 2007 significantly.

Although there are some slow things in Excel 2007 I have not found the
calculation engine itself to be generally slower, assuming you are not using
any of the new Excel 2007 features such as referencing full columns or rows
or the new conditional formatting etc.

But I have not done much testing on circular refs with 2007.

If you can send me a zipped workbook I will be happy to take a look at it.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"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

I can only confirm what's said in various threads about perfs of XL2007. If
you ever used VBA, and want/need to come out with a workable application,
then you are in trouble. It may be I did something wrong, but here what I
experince:

My Excel VBA application looks into 80 rows/ 30 columns sheets and does some
computation, coloring or so. With my old PC, 600Mhz, XP and Excel 2003, it
used to take around 4-5 sec. to complete. I was very proud early this year to
buy a new PC, 2Ghz, 2Mb ram, Excel 2007 and Vista. Curiously enough, the
exact same program on the same sheet takes 45 seconds now (yes, not a typo,
it takes 10 times longer!)
Thinking it could be due to the use of €œpure€� VBA, I created an add-in,
using VSTO with VS2005. I got then even worse performances: the same file
takes now 66 seconds to complete.
I decided then to embed most of the code into a COM object. First results
were disappointing (around 20sec), but by optimizing here and there, I
eventually got a reasonable response time, around 7-8 seconds.
That COM solves my performance problem (at least, the users will accept).
Then, remains that I dont know how to deploy a solution ExcelVBA-COM, as I
dont know how to automatically insert the reference into the users Excel at
setup time. Can anyone help here?
The most natural solution would have been to create a simple DLL instead of
a COM (I dont really need to instantiate objects in my apps, just call
functions). However, I am totally unable to create, with VS2005, a DLL that
would be callable from VBA. Does anyone have an example?
Note: I know how to call from VBA a function in an existing DLL, but each
time I try to call mine, I got the error saying the entry points are not
known (though the DLL file seems to be found). I would appreciate if somebody
can tell me how to make my own DLL callable from Excel VBA.

Thanks,


"Charles Williams" wrote:

Also make sure you have uninstalled the Google Desktop Office Search COM
addins: they slow down Excel 2007 significantly.

Although there are some slow things in Excel 2007 I have not found the
calculation engine itself to be generally slower, assuming you are not using
any of the new Excel 2007 features such as referencing full columns or rows
or the new conditional formatting etc.

But I have not done much testing on circular refs with 2007.

If you can send me a zipped workbook I will be happy to take a look at it.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"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