Cirvis
02-17-2009, 08:55 AM
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
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