Create named ranges WITHOUT using the Names Cells or Range

[Fernando Fernandes]

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]

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

 

[Héctor Miguel]

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 __

 

[Peter T]

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

 

[Jacob Skaria]

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

 

[Fernando Fernandes]

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!

 

[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

 

[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

 

[witek]

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.

 

[Jacob Skaria]

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

 

[Dave Peterson]

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.

 

[Bob Phillips]

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)

 

[Peter T]

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

 

[Jacob Skaria]

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

 

[Dave Peterson]

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

 

[Peter T]

Just for curiosity thought I'd see if 50k names can be added quickly to an
xml file, and indeed they can, very quickly. However the problem manifests
in a different guise when the xml is opened and the Names collection is
"compiled" to a normal collection in normal file style (I guess).

If you have one of the Excel versions specified try the following. Simply
run "NamesToXMLtest" and increase cDataRows * cDataCols towards a total of
50k.

Option Explicit
' Requires Excel-2003 Professional or Excel-2007
Const cDataRows As Long = 10 ' < change
Const cDataCols As Long = 2 ' < change
Const cTLcell As String = "B1"
Const cShtName As String = "TheSheet"
Const cPath As String = "c:\temp\" ' << check valid folder
Const cFilePre As String = cPath & "testXML.xml"
Const cFileNames As String = cPath & "testXMLNames.xml"
Const cFileXLS As String = "testNames.xls"
Const cFileXLXS As String = "testNames.xlxs"


Sub NamesToXMLtest()
' make a data file with cDataRows * cDataCols named cells
Dim t As Single

CleanUp

t = Timer
MakeDataXLS
Debug.Print , "MakeDataXLS", Timer - t

t = Timer
NamesToXLM
Debug.Print , "NamesToXLM", Timer - t

t = Timer
XLMtoXLS
Debug.Print , "XLMtoXLS", Timer - t

End Sub

Sub CleanUp()
On Error Resume Next
Kill cFilePre
Kill cFileNames
Workbooks(cFileXLS).Close False 'xl2003
Kill cPath & cFileXLS
Workbooks(cFileXLXS).Close False ' xl2007
Kill cPath & cFileXLXS
On Error GoTo 0
End Sub

Sub MakeDataXLS()
Dim nR As Long, nC As Long
Dim wb As Workbook

ReDim arr(1 To cDataRows, 1 To 1) As Double

Set wb = Workbooks.Add
wb.Worksheets(1).Name = cShtName
For nC = 1 To cDataCols
For nR = 1 To cDataRows
arr(nR, 1) = nC + nR / 100000
Next
wb.Worksheets(cShtName).Range(cTLcell).Offset(, nC -
1).Resize(cDataRows).Value = arr
Next

Application.DisplayAlerts = False
wb.SaveAs Filename:=cFilePre, FileFormat:=xlXMLSpreadsheet
Application.DisplayAlerts = True

wb.Close
End Sub

Sub NamesToXLM()
Dim ff As Integer
Dim nPos As Long, nPos2 As Long
Dim sTmp As String
Dim sNameXML As String
Dim sBuff As String

MakeNames sNameXML

ff = FreeFile
Open cFilePre For Input As #ff
sTmp = Input(LOF(ff), ff)
Close #ff

sBuff = Space(Len(sTmp) + Len(sNameXML))
nPos = InStr(sTmp, "<Worksheet ss:Name=")

Mid$(sBuff, 1, nPos - 1) = Left$(sTmp, nPos - 1)
Mid$(sBuff, nPos, Len(sNameXML)) = sNameXML
nPos2 = nPos + Len(sNameXML) + 1
Mid$(sBuff, nPos2, Len(sTmp) - nPos) = Mid$(sTmp, nPos, Len(sTmp) -
nPos)

Open cFileNames For Append As #ff
Print #ff, sBuff
Close #ff

End Sub

Sub MakeNames(sNameXML As String)
Dim nR As Long, nC As Long
Dim cnt As Long, nPos As Long
Dim nTLrw As Long, nTLcol As Long
Dim t As Single
Dim n As Long
Dim sN As String, sRef As String
Const Q As String = """"
Const cNAMEX As String = " <NamedRange ss:Name=" & Q & "#N" & Q & _
" ss:RefersTo=" & Q & "=#Ref" & Q & "/>"

t = Timer
With Range(cTLcell)
nTLrw = .Row - 1
nTLcol = .Column - 1
End With

ReDim arr(1 To cDataRows * cDataCols) As String

For nC = 1 To cDataCols
For nR = 1 To cDataRows
n = n + 1
sN = "name_" & Right$("00" & nC, 3) & "_" & Right$("0000" & nR,
5)
sRef = cShtName & "!R" & (nTLrw + nR) & "C" & (nTLcol + nC)
arr(n) = Replace(Replace(cNAMEX, "#Ref", sRef), "#N", sN) &
vbNewLine
cnt = cnt + Len(arr(n))
Next
Next

sNameXML = Space(cnt)

nPos = 1
For n = 1 To UBound(arr)
Mid$(sNameXML, nPos, Len(arr(n))) = arr(n)
nPos = nPos + Len(arr(n))
Next

sNameXML = "<Names>" & vbNewLine & sNameXML & "</Names>" & vbNewLine

Debug.Print "MakeNames", Timer - t
End Sub

Sub XLMtoXLS()
Dim sFile As String
Dim nFmt As Long
Dim wb As Workbook
Dim t As Single

If Val(Application.Version) > 11 Then
nFmt = 51 ' xlOpenXMLWorkbook
sFile = cFileXLXS
Else
nFmt = xlNormal
sFile = cFileXLS
End If

t = Timer
Set wb = Workbooks.Open(cFileNames)
Debug.Print "XLMtoXLS Open:" & cFileNames, Timer - t

t = Timer
wb.SaveAs Filename:=cPath & sFile, FileFormat:=nFmt
Debug.Print "XLMtoXLS Save:" & sFile, Timer - t

End Sub

Not quite sure why I'm doing this as I can't imagine ever using that many
names!

Regards,
Peter T

 

[Peter T]

To clarify -

Just for curiosity thought I'd see if 50k names can be added quickly to an
xml file, and indeed they can, very quickly. However the problem manifests
in a different guise when the xml is opened and the Names collection is
"compiled" to a normal collection in normal file style (I guess).

As qty of names in the xml increases to 50k the time to open the xml in
Excel (and compile the names collection) increases exponentially, plenty of
time for a coffee break!

Peter T

 

[Héctor Miguel]

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.