Hello all,
I am using the Named ranges in excel to store metadata about each cell in
excel that can persist with the excel file. So 1 cell = 1 Named
Range.Advantage of using this approach is excel automatically updates the
name references, even if we cut-and paste cells.
As per this link(http://office.microsoft.com/en-us/ex...992911033.aspx
), the amount of named ranges is limited only by the amount of available
memory.
I wrote a small macro that adds names to each cell in excel, considering an
average data of 10000 rows into 10 columns.
Like this:
Sub addNames()
Dim objRange As Range
Dim actSheet As Excel.Worksheet
For r = 1 To 10000
For c = 1 To 10
Set actSheet = Excel.ActiveSheet
Set objRange = actSheet.Cells(r, c)
Set nm = ActiveWorkbook.Names.Add("NMR" & r & "C" & c,
objRange)
Next
Next
End Sub
After 65472 cells, excel does not allow me to add more than the specified
amount of names. This is regardless of whatever RAM, whatever amount of
memory is available on user's machine.
My question is:
a. Is there a limit on number of names(named ranges) we can add to excel?
b. Is there some other property that is attached to a cell in excel that can
serve as a metadata holder, and will persist across sessions. I tried with ID
property, but it does not persist across excel session.

not su

this is 2003
Names in a workbook Limited by available memory
http://office.microsoft.com/en-us/ex...CH062527721033

this is 2007
Names in a workbook Limited by available memory
http://office.microsoft.com/en-us/ex...738491033.aspx
--


Gary Keramidas
Excel 2003


"bapat" wrote in message
...
Hello all,
I am using the Named ranges in excel to store metadata about each cell in
excel that can persist with the excel file. So 1 cell = 1 Named
Range.Advantage of using this approach is excel automatically updates the
name references, even if we cut-and paste cells.
As per this link(http://office.microsoft.com/en-us/ex...992911033.aspx
), the amount of named ranges is limited only by the amount of available
memory.
I wrote a small macro that adds names to each cell in excel, considering an
average data of 10000 rows into 10 columns.
Like this:
Sub addNames()
Dim objRange As Range
Dim actSheet As Excel.Worksheet
For r = 1 To 10000
For c = 1 To 10
Set actSheet = Excel.ActiveSheet
Set objRange = actSheet.Cells(r, c)
Set nm = ActiveWorkbook.Names.Add("NMR" & r & "C" & c,
objRange)
Next
Next
End Sub
After 65472 cells, excel does not allow me to add more than the specified
amount of names. This is regardless of whatever RAM, whatever amount of
memory is available on user's machine.
My question is:
a. Is there a limit on number of names(named ranges) we can add to excel?
b. Is there some other property that is attached to a cell in excel that can
serve as a metadata holder, and will persist across sessions. I tried with ID
property, but it does not persist across excel session.