I’m working on an EXCEL Macro (Microsoft office 365 for entreprise – can’t find the version number).
I’m struggling with a small problem, I would appreciate some help.

In my code (below), I work with the CAD softwareCATIA to modify some CATParts properties. All the updates I have to do are stored in an Excel worksheet named CATParts.

I split my code in two parts :

• GetPartsExportData, a function to read all properties and store it in a dictionary organized as this : <Key=Part name> : <Value = Dict[<Key=property>:<Value=property value>]>. This function returns the dictionary
• Test : The sub that gets the result of GetPartsExportData to do some process with CATIA. No matter what happens thgen, the problem is linked to this dictionary return.

When I run the Sub Test, I get the following error : Object required. I put a Debug.Print before return : ExportData type is Dictionary. I put a second Debug.Print after catching the return value of the function and there ExportData Type is Empty. So I understand the error but I can’t find a solution to solve this issue.

I made some researches and maybe it is a problem with Value/Object-Set/no Set. I’m a beginner with VBA so I’ve not yet really well understood how to manipulate objects.

Does someone have an explaination for this problem ?

The code :

Sub Test()
    Set catia = GetObject(, "CATIA.APPLICATION")
    
    ExportData = GetPartsExportData("Part3")
    Debug.Print ("Type after return : " & TypeName(ExportData))
    
    For i = 1 To catia.Documents.Count
        Application.StatusBar = i & "/" & catia.Documents.Count
        Set myDocument = catia.Documents.Item(i)
        
        If TypeName(myDocument) = "PartDocument" Then ' Ne traiter que les CATParts
            Set myProduct = myDocument.product
            Set myPart = catia.Documents.Item(i).part
        
            ' Vérifier que la Part a bien été détectée à l'import
            Flag = False
            For Each Name In PartsNameCollection
                If Name = myPart.Name Then
                    Flag = True
                End If
            Next Name
            If Flag = True Then
            Debug.Print TypeName(ExportData)
                Dim PartProperties As New Scripting.Dictionary
                For Each k In ExportData.Keys
                    Debug.Print "-->" & k & "-"
                Next k
                Debug.Print "-------------------------------------------"
                Debug.Print "Existe ?: " & ExportData.Exists(myPart.Name) & "-" & myPart.Name & "-" & ExportData.Keys(0) & "-"
                Debug.Print myPart.Name = ExportData.Keys(1) & "-" & TypeName(myPart.Name) & "-" & TypeName(ExportData.Keys(0))
                Debug.Print TypeName(ExportData(myProduct.PartNumber))
                
                For Each prop In PropertiesCatalogue
                    Flag = False
                    For Each Property In myProduct.UserRefProperties
                        tmp = Split(Property.Name, "")
                        UserPropertyName = tmp(UBound(tmp)) 'Nom de propriété raccourci
                        
                        If UserPropertyName = prop Then
                            Flag = True
                            Set myProduct.UserRefProperties.Item(UserPropertyName).Value = PartProperties(prop)
                            Exit For
                        End If
                    Next Property
                    If Flag = False Then
                        Debug.Print "*********************** " & ExportData(myProduct.PartNumber).Item(prop)
                        myProduct.UserRefProperties.CreateString prop, ExportData(myProduct.PartNumber).Item(prop)
                    End If
                Next prop
            End If
        End If
        
    Next i
End Sub

Public Function GetPartsExportData() As Scripting.Dictionary
    ' Renvoie un dictionnaire qui contient les propriétés modifiées/mises à jour à partir de l'onglet 'CATParts'
    ' Les données exportées ne sont pas liées aux données importées, le dictionnaire est recomposé à partir d'excel uniquement
    Dim ExportData As Scripting.Dictionary
    Set ExportData = New Scripting.Dictionary
    
    For i = 1 To PartsCollection.Count
        Dim PartsData As Scripting.Dictionary
        Set PartsData = New Scripting.Dictionary
        
        For j = 1 To PropertiesCatalogue.Count
            prop = PropertiesCatalogue(j)
            PartsData.Add prop, Worksheets("CATParts").Cells(i + 2, j) ' Décalage d'index à cause de la mise en page de l'onglet
        Next j
        ExportData.Add Worksheets("CATParts").Cells(i + 2, 1), PartsData
    Next i
    Debug.Print "Type before return : " & TypeName(ExportData)
    
    Set GetPartsExportData = ExportData
End Function

The Output :
Type before return : Dictionary
Type after return : Empty

The error : “424 : Objet requis”

I changed

• Dim ExportData As Scripting.Dictionary into As New Scripting.Dictionary in the sub and the function
• I tried to put and remove set before asignations