VBA - How to add a collection to a collection of collections

Question

I am trying to create code to represent a form document using VBA in Word 2007. I have created classes to represent Section, QuestionSet and Question.

So, I have 15 sections. I created a function to create each "Section" object, to add it to the "Section" collection, then destroy the object, as a result, the objects remain constant in the collection (or something else).

Can I use the same method to add collections to collections, or will I need to define each collection explicitly?

Code in the module:

Public Sections As Collection Function DefineSection(ByVal SectionName As String) Set Section = New clsSection Section.myName = SectionName Sections.Add Section, SectionName End Function Function DefineQuestionSet(ByVal SectionName As String, ByVal Name As String, ByVal NoOfQuestions As Integer, ByVal IsMutuallyExclusive As Boolean, Optional ByVal DependentOnSection As String) Dim Qsets As Collection Set Qsets = New Collection Set QuestionSet = New clsQuestionSet QuestionSet.Name = Name QuestionSet.NoOfQuestions = NoOfQuestions QuestionSet.MutuallyExclusive = IsMutuallyExclusive If Not (DependentOnSection) = "" Then QuestionSet.DependentOnSection = DependentOnSection End If Qsets.Add QuestionSet Sections.Item(SectionName).Add Qsets End Function 

This is then called through:

 Sub Initilise() Set Sections = New Collection DefineSection "PersonalDetails" DefineQuestionSet "PersonalDetails", "PersonalDetails", 29, False End Sub 

Answer 1

Yes You can absolutely add collections to collections in collections ad infinitum. The code you posted looks as if it should work just by looking at it. Do you have any special problems?

UPDATE: VBA only passes references to objects. If you explicitly destroyed the object after it was assigned to the collection (for example, Set myObj = Nothing ), you will also destroy the object inside the collection.

[EDIT]: This seems to be wrong. From this website (first linked by Steveau in the comments):

To use collections to manage class objects, you must do the following:

• Create an instance of the class
• Set class properties and methods
• Add class to public collection
• Unload class instance

You can expect that unloading an instance of a class causes the class to close and end. However, the class object is saved because you add it to the collection, which then owns the class reference. This is a very powerful technique that allows you to control links to objects through the collection; the class object does not stop acting until you remove it from the collection.

UPDATE: there is no reason why you cannot add a collection to an object. You just need the class for which your object is created to support this method. For example, in your module of the clsSection class clsSection you need the Add method, which adds the objects passed to it to the collection stored in clsSection :

 Private QSetsColl As Collection Public Sub Add(QSets As Object) If QSetsColl Is Nothing Then Set QSetsColl = New Collection QSetsColl.Add QSets End Sub 

Answer 2

Try this simple example:

 Private Sub CommandButton1_Click() Dim masterCollection As New collection masterCollection.Add "first key", createDetailCollection() masterCollection.Add "second key", createDetailCollection() masterCollection.Add "third key", createDetailCollection() End Sub 

The following function returns an initialized collection.

 Function createDetailCollection() Dim collection As New collection createCollection = collezioneBuy End Function 

Answer 3

I think I now have an answer, but if someone can clarify, that would be helpful.

I think the code is trying to add a collection to the object, which obviously won't work. So I need to create a collection to which I can add an AND the collection object. and etc.