2

I am trying to create ArrayList of Class like in Java, in Visual Basic Excel 2003.

Java

List<Employee> employees = new ArrayList<Employee>();
Employee employee = new Employee();
employee.setName("tom");
employees.add(employee);

VB

Dim resultList As New Collection    
Dim Manager As Employee
Manager.Name = "df"    
resultList.Add ("rr") 'correct
resultList.Add (Manager) 'error

But this gives the following error:

only user-define types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

Dave
  • 8,163
  • 11
  • 67
  • 103
user510783
  • 275
  • 4
  • 15

1 Answers1

1

There is no type information associated with a UDT so it can't be added to a collection as there is no way to reliably convert to/from a variant as the number & types of its members is unknown.

You can either replace the Employee Type with a Class or as you don't appear to be using a key, a typed array: arr() as Employee

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • with arr() I need to define array size. I do not know the array size. Can't arraylist/list be used? – user510783 Apr 05 '13 at 12:09
  • No, collections hold variants and a UDT cannot be converted to such. Arrays do *not* need to be defined with a fixed size, you can make them dynamic and resize as needed; http://stackoverflow.com/questions/8850984/populating-vba-dynamic-arrays – Alex K. Apr 05 '13 at 12:12