Can Names refer to Collections?

  • Thread starter Thread starter Jag Man
  • Start date Start date
J

Jag Man

I would like to have a Collection of Objects persist between Excel sessions.
I have done this
with arrays of strings using the Names feature, e.g.:

ActiveWorkbook.Names.Add Name:="testArray", RefersTo:=anArray

However, if I try to do it with a Collection, as shown below, I get a 1004
error.

What am I missing?

TIA

Ed

' testing use of Collection of objects as Names
Public Sub test5()
Dim i As Variant
Dim aCollection As Collection
Set aCollection = New Collection
For i = 0 To 5
aCollection.Add i, "i" & CStr(i)
Next i
Debug.Print aCollection("i2")
' doesn't work
'ActiveWorkbook.Names.Add Name:="testColl", RefersTo:=aCollection
End Sub
 
Jag Man,

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.

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).


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
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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.
Click to expand...

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).
Click to expand...

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


Ed
 
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)
 
Back
Top