By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,168 Members | 2,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,168 IT Pros & Developers. It's quick & easy.

Using user-defined variables in excel vba DLL

P: 1
Sourcode :
'


Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Type CustomerInfo
  4.     company As String
  5.     contact As String
  6.     RegionCode As Integer
  7.     Sales As Long
  8. End Type
  9.  
  10. Type CountryInfo
  11.     CountryName As String
  12.     CountryCode As Integer
  13. End Type
  14.  
  15. Type Out_CustomerInfo
  16.     company As String
  17.     contact As String
  18.     RegionCode As Integer
  19.     Sales As Long
  20.     SalesStatus As String
  21. End Type
  22.  
  23.  
  24. Dim SalesStatus As String
  25.  
  26.  
  27.  
  28.  
  29. Sub main(
  30.  
  31. Dim Customers() As CustomerInfo
  32. 'Input
  33. Dim Out_Customers As Out_CustomerInfo
  34. 'Out
  35.  
  36. Dim Country As CountryInfo
  37. Dim MYSHEET As Object
  38. Dim i As Integer
  39. Dim m As Integer
  40. m = 3
  41. ReDim Customers(m) As CustomerInfo
  42.  
  43.  
  44. Set MYSHEET = ThisWorkbook.Worksheets("sheet1")
  45.  
  46. Customers(1).company = "Acme '
  47. Customers(1).contact = "Tim Robertson"
  48. Customers(1).RegionCode = 3
  49. Customers(1).Sales = 150677
  50.  
  51. Customers(2).company = "ALPHA"
  52. Customers(2).contact = "Dennie"
  53. Customers(2).RegionCode = 1
  54. Customers(2).Sales = 100
  55.  
  56. Country.CountryName = " Republic"
  57. Country.CountryCode = 6
  58.  
  59. ' DLL  Setting 
  60. Dim obj As Project1.Class1
  61. Set obj = New Project1.Class1
  62.  
  63.  
  64. For i = 1 To 2
  65.  
  66.  
  67. Out_Customers = obj.bb(Customers(), Country, i) 
  68.                         ' --- ERR  POINT
  69.  
  70.  
  71. MYSHEET.Cells(i, 1).Value = Out_Customers.company
  72. MYSHEET.Cells(i, 2).Value = Out_Customers.contact
  73. MYSHEET.Cells(i, 3).Value = Out_Customers.Sales
  74. MYSHEET.Cells(i, 4).Value = Out_Customers.SalesStatus
  75. Next i
  76.  
  77.  
  78.  
''Only user defined types defined in public object modules can be coerced to or from a variant or
passed to late-bound functions


Please let me know a solution
Jun 25 '12 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.