0

My question relates to storing multiple objects, created from a class, within a collection. To create each instance of the class, I loop through a multi-dimensional array; the array is populated from a variable range within a sheet in excel.

I am wondering if this is the most efficient method of creating a collection, when you wish to populate it from data within excel? Is there a more efficient way of doing this? I have just started using classes, so am still trying to find the best ways of working with them.

So firstly I populate the array from excel:

arrCars() = ws.Range(Cells(10, 1), Cells(mrow, 3)).Value

Then use the array to create a class object, and store the object within the collection.

For i = LBound(arrCars) To UBound(arrCars)
    Set iCar = New CCarData
    iCar.carModel = arrCars(i, 1)
    iCar.carYr = arrCars(i,2)
    iCar.carColour = arrCars(i,3)
    clctCars.Add iCar
Next i

Any recommendations are appreciated. I couldn't find another post on this - apologies if there is one which I've been unable to find.

Community
  • 1
  • 1
luke_t
  • 2,935
  • 4
  • 22
  • 38

2 Answers2

1

A lot depends on the class design, your approach to the problem, how flexible your program can be, and how dumb resistant.

You may try creating a constructor that would take parameters. Maybe it would not be faster, but cleaner. You often incorporate constructors in the class to take advantage of being able to test for initial parameter values and to make code neater.

Have a look at: Pass arguments to Constructor in VBA

If you do not want to create constructor with parameters try using WITH on the object you have just created, like

WITH iCar
  .carModel  = arrCars(i, 1)
  .carYr     = arrCars(i, 2)
  .carColour = arrCars(i, 3)
END WITH

Another thing is a way you loop through the array. Usually using LBound and UBound might be slower than using FOR EACH, see msdn for syntax.

Also remember about: early binding - not everything is AS Object; defining specific and explicit data types - not everything is as Variant; also casting when converting between the types, this way vba will not waste time for figuring out what data type is that, or memory to store everything as variant.

Let me know if any of those helped with the speed.

Community
  • 1
  • 1
MPękalski
  • 6,873
  • 4
  • 26
  • 36
0

I found this looking for an answer to a different question. A simpler way:

For Each R in ws.Range(Cells(10, 1), Cells(mrow, 3)).Rows
    Set iCar = New CCarData
    iCar.carModel = R.cells(1)
    iCar.carYr = R.Cells(2)
    iCar.carColour = R.Cells(3)
    clctCars.Add iCar
Next`

(I was surprised to find when testing this that R.cells(0) returns the contents of a cell to the left of the range selected and R.Cells(-1) is the one to the left of there)

bodgesoc
  • 191
  • 9