How to optimize my coding..
 

I create an XLS with about a dozen or so named ranges that are dynamic based
on formula built into each one. For example, I have a name range that is
based on a column having value of "X". I noticed that it takes about 10 secs
for the file to open. I wrote a macro to open the above XLS and import data
from another XLS and save as another file. Because it takes about 10 secs to
open the file, the process takes longer than I expect because I have about
~150 different data files I have to process. Turning the calculation to
"Manual" seems to help open the file faster but if the file is open by
someone else, will it be calculated automatically? If so, I think I am safe.
Second question, when is Excel define the name range? when I use it or does
it determine for all name ranges upon opening the file?

Thanks a bunch
M

How to optimize my coding..
 

When Excel starts, if a document with automatic calculation is the first
opened, that will be the calculation mode for all subsequent documents if
and until the calculation mode is changed, either manually or in code. If
however, Excel starts with a document with manual calculation mode set, such
as double-clicking that file when Excel is not running, then Manual mode is
the order.

Names will come into effect as soon as the document opens, as the formulae
will be calculated.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"matelot" wrote in message
...
I create an XLS with about a dozen or so named ranges that are dynamic
based
on formula built into each one. For example, I have a name range that is
based on a column having value of "X". I noticed that it takes about 10
secs
for the file to open. I wrote a macro to open the above XLS and import
data
from another XLS and save as another file. Because it takes about 10 secs
to
open the file, the process takes longer than I expect because I have about
~150 different data files I have to process. Turning the calculation to
"Manual" seems to help open the file faster but if the file is open by
someone else, will it be calculated automatically? If so, I think I am
safe.
Second question, when is Excel define the name range? when I use it or
does
it determine for all name ranges upon opening the file?

Thanks a bunch
M

How to optimize my coding..
 

Thanks for the explanation.

"Bob Phillips" wrote:

When Excel starts, if a document with automatic calculation is the first
opened, that will be the calculation mode for all subsequent documents if
and until the calculation mode is changed, either manually or in code. If
however, Excel starts with a document with manual calculation mode set, such
as double-clicking that file when Excel is not running, then Manual mode is
the order.

Names will come into effect as soon as the document opens, as the formulae
will be calculated.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"matelot" wrote in message
...
I create an XLS with about a dozen or so named ranges that are dynamic
based
on formula built into each one. For example, I have a name range that is
based on a column having value of "X". I noticed that it takes about 10
secs
for the file to open. I wrote a macro to open the above XLS and import
data
from another XLS and save as another file. Because it takes about 10 secs
to
open the file, the process takes longer than I expect because I have about
~150 different data files I have to process. Turning the calculation to
"Manual" seems to help open the file faster but if the file is open by
someone else, will it be calculated automatically? If so, I think I am
safe.
Second question, when is Excel define the name range? when I use it or
does
it determine for all name ranges upon opening the file?

Thanks a bunch
M