function with collection of custom objects as results

P

Perhogan

Joined
Jan 10, 2011
Messages
1
Reaction score
0
I'm not really expert in VBA sorry if the question looks stupid but i'm stuck since hours on this...

in an Excel Workbook, I have a function that populates a collection of custom objects,
and i want to call it from another workbook. The Workbook with the function is in another Excell Application (that in the final application will be invisible).

The visible application needs to call this function in the hidden application but i get an error:cry: (Automation error: old format or invalid type library) on the other side, if the second workbook is opened in the same application everything works.:confused:
importing the custom class module in the opening file does not help.
i also tried with the app.run command but the function does not return anything (despite it runs)


here the custom Object:
Code: 
Private pGroupName As String

Public Property Get GroupName() As String
    GroupName = pGroupName
End Property

Public Property Let GroupName(Value As String)
    pGroupName = Value
End Property
here the code of the function i want to call:
Code: 
Public Function GetColletion() As Collection

Dim a As customObject
Dim b As customObject
Dim c As Collection

Set a = New customObject
a.GroupName = "aaaa"

Set b = New customObject
b.GroupName = "bbbb"

Set c = New Collection

c.Add a
c.Add b

Set GetColletion = c

End Function
and here the code that generates the error:
Code: 
Public Sub test()
On Error GoTo EH
Dim f As Collection
Dim app As Application

Set app = CreateObject("Excel.Application")
Set WB = app.Workbooks.Open("C:\Users\ar5027\Desktop\in.xls")

Set f = WB.Worksheets("sheet1").GetColletion()
MsgBox f(2).GroupName

EH:
If Err.Number <> 0 Then
    MsgBox Err.Description
End If

    On Error Resume Next
    If Not WB Is Nothing Then
        WB.Close savechanges:=True
    End If
    Set WB = Nothing
    If Not app Is Nothing Then
        app.Quit
    End If
    Set app = Nothing
End Sub
while this code works.
Code: 
Public Sub test()
On Error GoTo EH
Dim f As Collection

Set WB = application.Workbooks.Open("C:\Users\ar5027\Desktop\in.xls")

Set f = WB.Worksheets("sheet1").GetColletion()
MsgBox f(2).GroupName

EH:
If Err.Number <> 0 Then
    MsgBox Err.Description
End If

    On Error Resume Next
    If Not WB Is Nothing Then
        WB.Close savechanges:=True
    End If
    Set WB = Nothing
    If Not app Is Nothing Then
        app.Quit
    End If
    Set app = Nothing
End Sub
 
Back
Top