12

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 have created a function to create each 'Section' Object add it to the 'Sections' Collection then destroy the object, the result being that the objects remain persistent in the collection (or something).

Is it possible to use the same method to add collections to collections or would I have to define each collection explictly?

Code in 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

Then this is called via:

Sub Initilise()

    Set Sections = New Collection

    DefineSection "PersonalDetails"
    DefineQuestionSet "PersonalDetails", "PersonalDetails", 29, False

End Sub
Community
  • 1
  • 1
Stevo
  • 2,601
  • 3
  • 24
  • 32

3 Answers3

8

Yes. You can absolutely add collections to collections to collections ad infinitum. The code you have posted looks like it should work just from glancing through it. Are you having specific problems?

UPDATE: VBA only passes around references to objects. If you explicitly destroy an object after assigning it to a collection (eg, Set myObj = Nothing) then you will also be destroying the object inside the collection.

[EDIT]: Apparently this is not true. From this website (first linked by Stevo in the comments):

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

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

You might expect that unloading the instance of the class results in the class being closed and terminated. However, the class object persists because you add it to a collection, which then owns the reference to the class. This is a very powerful technique that allows you to control object references through a collection; the class object does not terminate until you remove it from the collection.

UPDATE: There's no reason why you can't add a collection to an object. You just need the class your object is instantiated from to support such a method. For example, in your clsSection class module you need an Add method which adds objects passed to it to a collection stored in the 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
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • Unfortunately I've changed the code for now, to try and just create independent collections - however I got an error from the line 'Sections.Item(SectionName).Add Qsets' in the 'DefineQuestionSet' function. – Stevo Feb 25 '11 at 16:07
  • Just to clarify, the code adds the objects to the collection and then destroys the instance of the object, but it remains persistent in the collection, so I don't have to keep track of the objects. Is it the same for the collections? i.e. create collection B, add to parent collection A, destroy collection B, but because it's added to collection A, it remains an 'item' in collection A. – Stevo Feb 25 '11 at 16:12
  • the error message is the ever enigmatic 'object doesn't support this property or method' – Stevo Feb 25 '11 at 16:20
  • Thanks, that helped me work it out. Although on the point about objects being destroyed, but the properties remaining part of the collection: http://support.microsoft.com/kb/198465 – Stevo Feb 25 '11 at 17:05
  • Thanks for the link. I learned something new today. I updated my answer to reflect the info in the link. – mwolfe02 Feb 25 '11 at 17:21
  • Thanks for the edit, I now understand what's actually happening to the object when in the collection! - With respect to the design of my code - I have decided that I need to create a container collection to hold a collection and the meta information about the collection, this should give me what I want, although reading your response, I'll have a look at containing it within the object (i was shying away from that, for some reason) - Anyhow, thanks again. – Stevo Feb 25 '11 at 17:41
1

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 follow function return a initializated collection

Function createDetailCollection()
    Dim collection As New collection
    createCollection = collezioneBuy
End Function
daniele3004
  • 13,072
  • 12
  • 67
  • 75
  • Perhaps you should use `Set createCollection = collezioneBuy` instead of `createCollection = collezioneBuy` in `createDetailCollection()`. – Ans Sep 12 '17 at 15:03
0

I think I have the answer now, but if someone could clarify that would be helpful.

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

Stevo
  • 2,601
  • 3
  • 24
  • 32