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

BTW, no I am not using Excel 2007, this solution must be written with VBA and
implemented in Excel 2003.

hi, Fernando !

- where do you have the list for "valid names" ?
- all of them will be referring to a single cell each ?
- where do you have the list for RefersTo part of the names ?

can you select any arrangement that includes "the name/s" AND "the cell/s"
and execute one single instruction ? (i.e.)

Selection.CreateNames Top:=False, Left:=True, Bottom:=False, Right:=False

how can we repro your layout and needs ?

regards,
hector.

p.s. how is the performance of your w-book with ~54k named ranges (???)

__ OP __
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?

Hector, first of all thanks for the answer.

1) The list of valid names come from a recordset originated in SQL Server. I
make it an array, and i do have all the cell addresses where they should be
implemented. usuallay 500 rows by about 60 columns. These cells are
referenced throughout the workbook, and their names are recreated every time
this workbook is opened.
2) Yes all of them reference a single cell each. some of them reference the
same cell, but it's ok, since Excel offers the choice of a cell to have more
than one name.
3) the list of refersto is (or can be) created dynamically too.
4) I wanna know if it's possible to do it. I can put the names and the
references in any format as I said before, since I have all of this in the
logic, and it runs quite quickly with arrays.
5) The createNames is good, but it does not solve my problems, since the
data is not all in one column (or one row). If I try to create names like
this, it will create names for the columns and for the rows, which for me
would be good, but this system needs each cell to be named separately.

6) to repro, put random data in the range A9 to BH500 it will create
aproximatelly 30k names. Since I do it in two sheets, it goes up to about 60k
names. Try to name them (each) as, let's say, your name plus row plus column.
Given that you know their addresses and the names that each of those cells
will have.

ps: the only reason I posted my question is that I believe there must be a
way to make it faster. The main problem is performance, since this system
does more things than only creating these names. All the rest is performing
great, but the file still needs sometimes 10 seconds, sometimes 30 seconds
just on the name creation. So I saw Excel 2007 has XML based files in it, and
all the names could (I also dont know how, this is just a thought) be
inserted directly in the XML, if the proper code was written. So is there a
way to make something similar but in a XL2K3 workbook? If so, how? :)

ps: the workbook performs great since these names are only references. What
hurts its performance are not all the thousand references, but the table
functionality that users put it there. After it opens, it always takes about
two seconds to recalculate the tables. With ir without the names, the tables
perform the same.

This is something that I could find elsewhere on the internet, that's why I
am asking here! :) If you (or anyone) have any other ideas, they will all be
veeery welcome!

Thanks once more Hector!

"Héctor Miguel" wrote:

hi, Fernando !

- where do you have the list for "valid names" ?
- all of them will be referring to a single cell each ?
- where do you have the list for RefersTo part of the names ?

can you select any arrangement that includes "the name/s" AND "the cell/s"
and execute one single instruction ? (i.e.)

Selection.CreateNames Top:=False, Left:=True, Bottom:=False, Right:=False

how can we repro your layout and needs ?

regards,
hector.

p.s. how is the performance of your w-book with ~54k named ranges (???)

__ OP __
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?

hi, Fernando !

The main problem is... this system does more things than only creating these names.
All the rest is performing great, but the file still needs sometimes 10 seconds
sometimes 30 seconds just on the name creation...

I was able to create a set of names at once but still dumped in the most important part:
I can not find a way to assign individual references to each name in the same step :-((

(still working...)
regards,
hector.

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

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

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

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

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

Fernando Fernandes wrote:

The problem is, my boss thinks that the 10 seconds that Excel takes to do it
are too much.

Have you set calculation to manual before running the code?


I will get double bonus if my workbook calculation goes under 1 minute. :)

54 thousand names in a single workbook makes it sound like an impossible
workbook to maintain.

I don't think I'd do it.

I bet that there are other options that are better.

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

--

Dave Peterson

At last, someone has said the sensible thing <g

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave Peterson" wrote in message
...
54 thousand names in a single workbook makes it sound like an impossible
workbook to maintain.

I don't think I'd do it.

I bet that there are other options that are better.

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

--

Dave Peterson

Just because you can, doesn't mean you should! <vbg

Bob Phillips wrote:

At last, someone has said the sensible thing <g

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave Peterson" wrote in message
...
54 thousand names in a single workbook makes it sound like an impossible
workbook to maintain.

I don't think I'd do it.

I bet that there are other options that are better.

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

--

Dave Peterson

--

Dave Peterson

Hi Fernando, did you get a chance to try out populating data into a
multi-dimensional array at Workbook Open and then reference from cell using a
UDF which brings out the desired value...

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