Adding Documents to a Collection

[SometimesInventorMakesMeAngry]

I'm having trouble with adding documents to a collection. For some reason, when I try to add part documents to a collection like this (VBA):

Dim clsSRs As Collection
Set clsSRs = New Collection
Dim oRefDocSR As Document
For Each oRefDocSR In ThisApplication.ActiveDocument.ReferencedDocuments
    If UsefulFunctions.GetiPropertyByName(oRefDocSR, "Description").Value Like "*SIDE RAIL*" And _
    oRefDocSR.DocumentType = kPartDocumentObject Then

        clsSRs.Add (oRefDocSR)

    End If
Next

 the items in the collection are of type Variant/Long, so I cannot access the documents' properties (because they're not objects). Note that UsefulFunctions.GetiPropertyByName is simply a function that returns a document's property and accepts the document object and property name as parameters; though this isn't really relevant to the problem.

However, when I try to add the same documents in the same manner, but to an array like this:

Dim i As Long
Dim oCVs() As PartDocument
Dim oRefDoc As Document
For Each oRefDoc In ThisApplication.ActiveDocument.ReferencedDocuments
    If UsefulFunctions.GetiPropertyByName(oRefDoc, "Description").Value Like "*SIDE RAIL*" And _
    oRefDoc.DocumentType = kPartDocumentObject Then
            
        i = i + 1
        ReDim Preserve oCVs(1 To i)
        Set oCVs(i) = oRefDoc
            
    End If
Next

the items in the array are of type PartDocument/PartDocument and I can now access their properties.  This can be see in the watches window screenshot below:

I would like to use collections as I've read they're more versatile and adding objects to an array and redimensioning as I did above is messy and inefficient. Can anyone provide some feedback? I've searched online and in these forums, but haven't found a solution.

[Anonymous]

I'm not that experienced with VBA, but everything I've read suggests you should be able to access a objects methods/properties while it is in a collection. As seen in this example:

' Demonstrates that a class object can be updated in a collection.
' https://excelmacromastery.com/excel-vba-collections/
Sub ChangeObject()

    Dim coll As New Collection
    Dim o As New Class1
    
    ' set value of fruit member of the class
    o.fruit = "Apple"
    
    ' Add object to collection
    coll.Add o
    
    ' Prints Apple
    Debug.Print "Object fruit is " & coll(1).fruit
    
    ' Change the fruit part of class1 object
    coll(1).fruit = "Pear"
    
    ' Prints pear
    Debug.Print "Object fruit is " & coll(1).fruit

End Sub

I don't see where you are trying to access the documents properties from the collection, Is there a specific error you're getting?

[Anonymous · Accepted solution]

I think the answer may be as simple as adding a "Call" to the add to collection line. With this code I was able to pop up a message box with each document name; without the "Call" in the add to collection line I get an error.  I'm not sure what difference it makes or if this is just a quirk of VBA.

Sub test()

Dim clsSRs As Collection
Set clsSRs = New Collection
Dim oRefDocSR As Document

For Each oRefDocSR In ThisApplication.ActiveDocument.ReferencedDocuments
    
       Call clsSRs.Add(oRefDocSR)

Next

For i = 1 To clsSRs.Count

MsgBox (clsSRs(i).DisplayName)

Next

End Sub

EDIT: After looking at the example a little closer, I noticed the way it is adding an object to the collection without parentheses, which also works without the call. Apparently using parentheses, it is adding a key and expects a second object to attach it to. I think it is similar to Dictionaries in VB.NET if you are familiar with that.

Sub test()

Dim clsSRs As Collection
Set clsSRs = New Collection
Dim oRefDocSR As Document

For Each oRefDocSR In ThisApplication.ActiveDocument.ReferencedDocuments
    
        clsSRs.Add oRefDocSR

Next

For i = 1 To clsSRs.Count

MsgBox (clsSRs(i).DisplayName)

Next

End Sub

[WCrihfield]

How about these two options:

ObjectCollection

Dim oPDocs As ObjectCollection
Set oPDocs = ThisApplication.TransientObjects.CreateObjectCollection
Dim oRefDoc As Document
For Each oRefDoc In ThisApplication.ActiveDocument.ReferencedDocuments
	If UsefulFunctions.GetiPropertyByName(oRefDocSR, "Description").Value Like "*SIDE RAIL*" And _
    oRefDocSR.DocumentType = kPartDocumentObject Then
	oPDocs.Add(oRefDoc)
    End If
Next

[SometimesInventorMakesMeAngry]

---

@Anonymous wrote:

I'm not that experienced with VBA, but everything I've read suggests you should be able to access a objects methods/properties while it is in a collection. As seen in this example:

' Demonstrates that a class object can be updated in a collection.
' https://excelmacromastery.com/excel-vba-collections/
Sub ChangeObject()

    Dim coll As New Collection
    Dim o As New Class1
    
    ' set value of fruit member of the class
    o.fruit = "Apple"
    
    ' Add object to collection
    coll.Add o
    
    ' Prints Apple
    Debug.Print "Object fruit is " & coll(1).fruit
    
    ' Change the fruit part of class1 object
    coll(1).fruit = "Pear"
    
    ' Prints pear
    Debug.Print "Object fruit is " & coll(1).fruit

End Sub

I don't see where you are trying to access the documents properties from the collection, Is there a specific error you're getting?

---

Hi! I am trying to access the objects' properties with a For Each block in some code I wrote, but I managed to narrow down the problem. The problem is that when I add documents to a collection, they are no longer of type Document; instead, they are now of type Long/Variant (i.e. no longer an object), so I can no longer access the document's properties. You can see this in my last screenshot in the original post.

When I try to access the properties, I receive the run-time error "Object Required".

[SometimesInventorMakesMeAngry]

---

@Anonymous wrote:

I think the answer may be as simple as adding a "Call" to the add to collection line. With this code I was able to pop up a message box with each document name; without the "Call" in the add to collection line I get an error.  I'm not sure what difference it makes or if this is just a quirk of VBA.

Sub test()

Dim clsSRs As Collection
Set clsSRs = New Collection
Dim oRefDocSR As Document

For Each oRefDocSR In ThisApplication.ActiveDocument.ReferencedDocuments
    
       Call clsSRs.Add(oRefDocSR)

Next

For i = 1 To clsSRs.Count

MsgBox (clsSRs(i).DisplayName)

Next

End Sub

EDIT: After looking at the example a little closer, I noticed the way it is adding an object to the collection without parentheses, which also works without the call. Apparently using parentheses, it is adding a key and expects a second object to attach it to. I think it is similar to Dictionaries in VB.NET if you are familiar with that.

Sub test()

Dim clsSRs As Collection
Set clsSRs = New Collection
Dim oRefDocSR As Document

For Each oRefDocSR In ThisApplication.ActiveDocument.ReferencedDocuments
    
        clsSRs.Add oRefDocSR

Next

For i = 1 To clsSRs.Count

MsgBox (clsSRs(i).DisplayName)

Next

End Sub

---

Hey! I think you might be onto something! I've read that the Call keyword is not required in VBA as seen in the Microsoft documentation here.

"You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist."

However, I have noticed that this is kind of finnicky in some cases, so I think it's worth a shot. I'll try and report back.

By the way, are you getting the "Object Required" error when you run your second example? And is the data type Long/Variant as opposed to some Document type?

[SometimesInventorMakesMeAngry]

---

@WCrihfield wrote:

How about these two options:

ObjectCollection

Dim oPDocs As ObjectCollection
Set oPDocs = ThisApplication.TransientObjects.CreateObjectCollection
Dim oRefDoc As Document
For Each oRefDoc In ThisApplication.ActiveDocument.ReferencedDocuments
	If UsefulFunctions.GetiPropertyByName(oRefDocSR, "Description").Value Like "*SIDE RAIL*" And _
    oRefDocSR.DocumentType = kPartDocumentObject Then
	oPDocs.Add(oRefDoc)
    End If
Next

---

Hey, thanks for replying! That does seem like a good option. I think that this will be a little less efficient because it does an API call every time you add an object to the collection (or maybe not? Please correct me if I'm wrong) compared to using native VB objects, but it's definitely worth a try!

[WCrihfield]

You will most likely have to create another variable that is defined as a PartDocument, then set its value to each item from the Collection, before trying to access its iProperties.

I also mistakenly left "SR" on the variable "oRefDoc" within the Sub call, from your first post.

[Anonymous]

My seconds example works the same as the first. It gets the proper type.

[SometimesInventorMakesMeAngry]

---

@Anonymous wrote:

I think the answer may be as simple as adding a "Call" to the add to collection line. With this code I was able to pop up a message box with each document name; without the "Call" in the add to collection line I get an error.  I'm not sure what difference it makes or if this is just a quirk of VBA.

This worked! Thank you! I don't understand why, but it works. 

If someone could explain why it works, that would be awesome. I posted a quote from Microsoft documentation saying that the Call keyword can be omitted, so I'm not sure why it made a difference in this case. The even stranger thing is that I tried it on some assembly documents (as opposed to part documents in this case) and that worked fine without the Call keyword.

[SometimesInventorMakesMeAngry · Accepted solution]

---

@Anonymous wrote:

My seconds example works the same as the first. It gets the proper type.

---

This made me look back at my code. I think the reason my first example didn't work is because I did not include the Call keyword, but I incorrectly used parentheses around the parameter(s).

The documentation says that 

• If the Call keyword is used, parentheses should be included
• If the Call keyword is not used, parentheses should not be included

I tried using both methods in the same code (with proper syntax) and can confirm they both work.

I have made this mistake in the past, but I usually get a syntax error; this time I didn't. 😞

[WCrihfield]

Ah, yeah... 🤔 I do remember reading that once before too, but since I don't heavily use VBA, in favor of iLogic & VB.NET, I had forgotten about it too.  Good work.