Can Names refer to Collections?
 

The important thing is that it works and achieves your objective. In the
final analysis that is what counts.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jag Man" wrote in message
...

Names are a an Excel property, not VBA. It can refer to a range of
cells,
or
it can be a simple value, but not a collection object, especially as
objects
are destroyed.

Arrays do work, although things have to be copied back and forth.


You are only talking about a transient store anyway, the Name is the
persistent part. Here is another solution that uses an array , and the
Join
and Split methods, so it is Excel 2000 and beyond dependent (unless you
write you own Join and Split functions).

Thanks. I'll give oit a try. A little bit like "serializing" objects for
persistance
in other languages.


Ed


Public Sub Populate()
Dim aCollection
Dim i As Long

ReDim aCollection(0)
For i = 0 To 5
ReDim Preserve aCollection(i)
aCollection(i) = "i" & CStr(i)
Next i
ActiveWorkbook.Names.Add Name:="PersistentData",
RefersTo:=Join(aCollection)

End Sub

Public Sub Retrieve()
Dim aCollection
Dim i As Long

aCollection = Split(Evaluate(Names("PersistentData").RefersTo))
For i = LBound(aCollection) To UBound(aCollection)
Debug.Print aCollection(i)
Next i

End Sub

--

HTH

Bob Phillips