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: <short version, long version at the end
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 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.


See long version of the code (below) with Debug.Print statements:

Sub addNames()
On Error GoTo ERR_MSG

Dim r As Long
Dim c As Long
Dim iCnt As Long

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)
If r 6500 Then
Debug.Print i
End If
i = i + 1
Next

If r Mod 500 = 0 Then
Debug.Print r
End If
DoEvents
Next
Exit Sub
ERR_MSG:
MsgBox Err.Number & ": " & Err.Description
Exit Sub
End Sub