Thanks Jacob for the answer, but unfortunately it still does not meet my needs.
As I said, using the Names collection is taking way too long.

So I tried already creating the names using:

Names.Add
Range().Name
Cells().Name
Range().CreateNames

Out of these, the fastest one is Cells(i, j).Name, but I need something even
faster than that. That's why I was thought of asking here, maybe someone can
give me an idea of doing it not using the conventional VBA way, but still
using VBA.

I would say that less tha half of these names are used across the workbook,
but they need to be there. I even created a routine that checks if the name
needs to be created or if it's already there. These cells can change
positions all the time, but sometimes they dont. And I improved performance
with this array already (mentioned to Hector, above).

Loop the names collection is fast, if there's nothing inside the loop. :( If
there is one simple condition, that it becomes awfully bad performance :(.

I do appreciate your answer and will to help, but I will need something more
here. Someone who can go an extra mile. I have worked with VBA for 10 years
now, and I never faced this problem before. The problem is project/boss both
crazy about nanoseconds of diference in ways of doing code.

Thanks again, if you have any other ideas, they'll be deeply appreciated!

ps: each cell must have a different name.

Fernando

"Jacob Skaria" wrote:

54K names !! Could you please tell us more about the data ...

Through VBA you can name a range this way; may be you can use this within a
loop...

ActiveWorkbook.Names.Add "Name1", Sheet1.Range("A1:C10")

If this post helps click Yes
---------------
Jacob Skaria


"Fernando Fernandes" wrote:

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

Dear Fernando

OK. Also appreciate the way you have responded to the answers.Can we try
something different here.

Instead of using this as Names why dont you store your data into a 2-D array
during Workbook Open and then access this from cell using a User Defined
Function..

If this post helps click Yes
---------------
Jacob Skaria


"Fernando Fernandes" wrote:

Thanks Jacob for the answer, but unfortunately it still does not meet my needs.
As I said, using the Names collection is taking way too long.

So I tried already creating the names using:

Names.Add
Range().Name
Cells().Name
Range().CreateNames

Out of these, the fastest one is Cells(i, j).Name, but I need something even
faster than that. That's why I was thought of asking here, maybe someone can
give me an idea of doing it not using the conventional VBA way, but still
using VBA.

I would say that less tha half of these names are used across the workbook,
but they need to be there. I even created a routine that checks if the name
needs to be created or if it's already there. These cells can change
positions all the time, but sometimes they dont. And I improved performance
with this array already (mentioned to Hector, above).

Loop the names collection is fast, if there's nothing inside the loop. :( If
there is one simple condition, that it becomes awfully bad performance :(.

I do appreciate your answer and will to help, but I will need something more
here. Someone who can go an extra mile. I have worked with VBA for 10 years
now, and I never faced this problem before. The problem is project/boss both
crazy about nanoseconds of diference in ways of doing code.

Thanks again, if you have any other ideas, they'll be deeply appreciated!

ps: each cell must have a different name.

Fernando

"Jacob Skaria" wrote:

54K names !! Could you please tell us more about the data ...

Through VBA you can name a range this way; may be you can use this within a
loop...

ActiveWorkbook.Names.Add "Name1", Sheet1.Range("A1:C10")

If this post helps click Yes
---------------
Jacob Skaria


"Fernando Fernandes" wrote:

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