Pass User defined Type between two open Excel workbooks

[Cirvis]

Hello!

I am looking for any hints, how to pass User defined Type between two open Excel workbooks or alternatively how to pass any other data set (collection, dictionary).
I found that it would be very convenient to pass a bunch of data with clearly defined attributes (for example: Quantity, Part Name, etc) so that they can be easily identified in other procedures of the same workbook or even more important in the different workbook. I would be very grateful if someone could suggest an idea how to pass bunch of data in different, more eficient way.

Could anyone help me to solve this issue?

*************************************************************************** *******************************************

Application: Excel 2007.

Code explanation:
I have two workbooks named A (owns user-defined type variable) and B (receives user-defined type variable from A).
I have procedure (see Code) “Public Sub GetExternalData” in the workbook B, which addreses to “Public Function Pass_UsedItem” located in the workbook A and retrieves user-defined type as functions value- however code does not work as intended- it generates the error (see Errors)

Errors:
„Only public user defined types defined in public object modules can be used as parameters or return types for public procedures of class modules or as fields of public user defined types”

Code in workbook A (owns the data):

 
'Create user-defined type to store records of used spare parts
Public Type UsedSpareItem
    .sArticle As String
    .sPartName As String
    .dQtyUsed As Double
End Type
 
‘Declare global user defined type and size it to store 6 records
Public g_UsedItem(5) As UsedSpareItem
 
Sub SomeSub()
‘**** used to assingn values to user-defined type
 
With g_UsedItem(0)
     .sArticle = “Article”
     .sPartName = 123456
     .dQtyUsed = 1
End with
End sub
 
Public Function Pass_UsedItem() As UsedSpareItem
‘**** function for passing user-defined type to the workbook B
 
    Pass_UsedItem = g_UsedItem(0)
End Function

Code in workbook B (receivs user-defined type from workbook A):

 
'Create user-defined type to store records of used spare parts
Public Type UsedSpareItem
     .sArticle As String
     .sPartName As String
     .dQtyUsed As Double
End Type
 
‘Declare global user defined type and size to have 5 records
Public g_UsedItem(5) As UsedSpareItem
 
‘Declare global workbook
Public g_wTargetWb As Workbook
 
Public Sub GetExternalData()
'**** used to get data from workbook A
    Dim sPathToFile As String
    Dim sFileName as String
 
    sPathToFile=” C:\Documents and Settings\user\My Documents”
    sFileName=”Workbook_A.xlsm”
 
    Set g_wTargetWb = Workbooks.Open(sPathToFile & "\" & sFileName)
 
    g_UsedItem(0) = Application.Run(g_wTargetWb.Name & "!Pass_UsedItem")
 
End Sub

[Bob Phillips]

UDTs are problemmatical, I stopped (trying) to use them years ago.

Create a class and use that

[vba]

Option Explicit

Private m_ssArticle As String
Private m_ssPartName As String
Private m_ddQtyUsed As Double

Public Property Get sArticle() As String
sArticle = m_ssArticle
End Property
Public Property Let sArticle(ByVal ssArticle As String)
m_ssArticle = ssArticle
End Property

Public Property Get sPartName() As String
sPartName = m_ssPartName
End Property
Public Property Let sPartName(ByVal ssPartName As String)
m_ssPartName = ssPartName
End Property

Public Property Get dQtyUsed() As Double
dQtyUsed = m_ddQtyUsed
End Property
Public Property Let dQtyUsed(ByVal ddQtyUsed As Double)
m_ddQtyUsed = ddQtyUsed
End Property
[/vba]

and

[vba]

Sub SomeSub()
'**** used to assingn values to user-defined type
Set g_UsedItem(0) = New UsedSpareItem
With g_UsedItem(0)
.sArticle = "Article"
.sPartName = 123456
.dQtyUsed = 1
End With
End Sub
[/vba]

[Cirvis]

Hi! Thank you a lot for a fast response. The code works fine.

However I have not succeeded so far to provide possibility of storing multiple UsedSpareItem records what was possible with UDTs (see: The old code with UDT).

Is there a way store multiple records by using clases?

**********************************************************
The old code with UDT:

[vba]
With g_UsedItem(0)
.sArticle = 10023
.sPartName = "Inlet valve"
.dQtyUsed = 1
End with

With g_UsedItem(1)
.sArticle = 10024
.sPartName = "Exaust valve"
.dQtyUsed = 1
End with
[/vba]

[Cirvis]

Hi! Thank you a lot for a fast response. The code works fine.

However I have not succeeded so far to provide possibility of storing multiple UsedSpareItem records what was possible with UDTs (see: The old code with UDT).

Is there a way store multiple records by using clases?

**********************************************************
The old code with UDT:

[VBA]
With g_UsedItem(0)
.sArticle = 10023
.sPartName = "Inlet valve"
.dQtyUsed = 1
End With

With g_UsedItem(1)
.sArticle = 10024
.sPartName = "Exaust valve"
.dQtyUsed = 1
End With
[/VBA]

[Bob Phillips]

Yes, use a collection class.