VBA: reading Excel range into an object array

Question

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?

I know how to assign a range to an array. My question was how to assign to an object array, as defined by a class module. — rjsoft
– rjsoft, Commented Jan 3, 2012 at 20:45
Then it might help those trying to answer your question if you explain what it is that you're trying to accomplish. If you just want to assign an array to another array in VBA, your question doesn't really have anything to do with Excel. Perhaps you could post some prototype code? — Robert Harvey
– Robert Harvey, Commented Jan 3, 2012 at 20:46
In VBA, objects variables are pointers, so it will be SET rng = Range"B4:D6" — Patrick Honorez
– Patrick Honorez, Commented Jan 3, 2012 at 20:59
@rjsoft: are you a java programmer ? forget compulsive depency on objects. think results and efficiency. Happy new year. — Patrick Honorez
– Patrick Honorez, Commented Jan 4, 2012 at 20:08

Jon49 · Accepted Answer · 2012-01-03 21:57:31Z

4

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

answered Jan 3, 2012 at 21:57

Jon49

4,6164 gold badges41 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rjsoft Over a year ago

I was afraid of that. For a large range, this looping may be a performance issue. Thanks.

Jon49 Over a year ago

Just make sure you minimize how often you have your VBA "talk" to Excel, that will help performance, not make it ideal, but help. Also, I've found using byref speeds things up and here's some other tips that can help keep things relatively fast: shamrock-software.eu/vb.htm also, I think Dictionary is faster than Collection, you'll have to double check on that one, but that could help keep things fast. I'm sure you know some of these already, but something might be new to you.

brettdj Over a year ago

@rjsoft Jon49's variant array code will be very fast - much quicker than a standard range loop (+1)

rjsoft Over a year ago

@Jon49 I'm familiar with all the techniques from your link. Thanks again.

Collectives™ on Stack Overflow

VBA: reading Excel range into an object array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related