CODEWITHSUNDEEP
arraysexcelvbaobject
rjsoft
rjsoft

Reputation: 1

VBA: reading Excel range into an object array

How do I read an Excel range into an object array?

To clarify, for this Excel range of 6 cells... 

John    Roberts    56
Sam     Alito      52

and this class...

Class supremes      
Public firstName        
Public lastName     
Public age  
Dim supreme As New supremes

I'd like to read the Excel range into an array of supreme such that: 

arr(1).firstName = "John"   
arr(2).age = 52

For a standard array, this is done with a single assignment...

arr = range("supremes")

Is there a similar command to populate the object array?

Upvotes: 0

Views: 7207

Answers (1)

Jon49
Jon49

Reputation: 4606

There isn't any special way to read data into an array object. You just need to roll your own code.

dim i as long
dim rData as range
dim vData as variant

set rData=selection

vData=rData

for i=1 to ubound(vdata)
  arr(i).FirstName=vdata(i,1)
  arr(i).LastName=vdata(i,2)
  arr(i).Age=vdata(i,3)
next i

Upvotes: 4

Related Questions