I'm even more surprised you can create that many names in 10 sec with the
those resources.You could test making names with the Refersto address string
rather than the range object, but again that might not improve speed.

If desperate you could look into BIFF8 utilities but overkill merely to save
10 seconds.

Actually, as opposite to what people might think, this does not corrupt
the
workbook at all.

Maybe not yet but as I mentioned you are getting pretty close. Although as
documented the number of names is limited only by available memory, you can
expect to hit serious problems with 64k names (not sure if xl2007 handles
that qty better, probably not).

I would be surprised if there were not some other way to beneficially
reorganize your logic without the use of so many names.

Regards,
Peter T



"Fernando Fernandes" wrote in
message ...
Hey Peter, thanks for you answer too.
Yes the computer is pretty quick, but it's from more than two years ago,
means, there are better one out there. But the system is going to be used
by
lots os people eith different hardware, so I cannot rely on the machine
power
to hope it will perform good in some places and knowing it will perform
bad
in other places.
the spec is 2GB RAM / HD 50GB / Intel HT-2GHz.


Yeah we have tried this loop as a first option, but using the method Add
of
the Names collection makes the time go up to more than 30 seconds. It's
painful for our users to wait so long, only because of names creation.

Actually, as opposite to what people might think, this does not corrupt
the
workbook at all. It performs really wall as mentioned in my answer to
Hector.

I wanted actually to know if there was a binary way of accessing the file,
or any other way of not using the Names collection the Range object or the
Cells object (which is faster than the Range())

Actually changing a property (the .Name) of the range object is much
faster
than using Names.Add, and more... I am testing the time using the
Applciation.Timer twice, once in the beginning and then at the and, and
this
is how I am able to know how long it takes.

Thanks once more, but I need some more ideas, different from using Excel
VBA
builtin solutions, if possible, of course.

Any other ideas will be deeply appreciated.

Thanks very much!

Fernando



"Peter T" wrote:

If you can create 54k names in 10 seconds you must have an extremely fast
system, curiosity what's the spec.

You could try something like this if appropriate in some sort of loop but
I
wouldn't expect much improvement.

' declare variables
Set nms = ActiveWorkbook.Names
Set ws = ActiveWorkbook.Worksheets("Sheet1")
'loop
m = 1
Set rng = ws.Range("a1")
nms.Add "myName_" & m, rng

Probably better not to add much more than 54k names as that might lead to
a
corrupt workbook.

Regards,
Peter T


"Fernando Fernandes" <Fernando wrote
in
message ...
Hey guys,
It's my first question here, and I have a good one, I think.
The scenario is: I need to create 54 thousand named ranges.

I want to find a solution to create them all (supposed we already have
all
valid names to be created and their valid references to be used in what
would
be the RefersTo).

I am able to create those names using:
Names.Add with all correct parameters
Range("xx").Name = "MyName"
Cells(x,y).Name = "MyName"

The problem is, my boss thinks that the 10 seconds that Excel takes to
do
it
are too much. So I was wondering if there is another way of using the
whole
list at once to create all the names at once (maybe binarily in the
file,
maybe load as XML, maybe using array, whatever). I need to bring this
manes
creation down to as fast as it can get.

Any ideas?

Thanks,

Fernando Fernandes