Solved: Write out class data

[JKwan]

I defined a Pipeline Class - attributes of a pipeline. I was wondering is there an easy way to write this class onto a sheet without "looping the attributes" across the sheet. So, I wanted to do something like:

range("A1:A10") = Pipeline

Rather than

for each pipeline in pipelines

range("A1") = pipeline.length
range("B1") = pipeline.substance
....
....
next

[Jan Karel Pieterse]

If you put the data into a 2 dimensional array, you can write the array to the sheet in one line of code:


Dim Arr(1 to 10,1 to 2) as variant
'Code to fill array
Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr

[Bob Phillips]

I am assuming that pipelines class maintains a collection of pipeline objects, in which case you would need to add extra processing in the pipelines class to maintain that collection either in an array instead, or to also populate an array, and a method to return the array.

[JKwan]

xld
Yes, that is correct. From the way you are explaining... I am not gaining anything. I guess I will just step thru the collection.

Thank you both.

[Bob Phillips]

Well that depends upon how you look at it doesn't it?

If you build the array as you need it, then yes it doesn't seem to have gained anything.

If you maintain the array as you add to/remove from the pipelines class, then you just return the array when you need it.

And of course, doing it all in the class does give nice code separation.

[Jan Karel Pieterse]

Writing an aray to a worksheet is much, much faster than doing it cell-by-cell. Even if it means you have to populate the entire array before the write. So I guess it depends on the size of your collection whether the array route is needed.

[Bob Phillips]

Writing an aray to a worksheet is much, much faster than doing it cell-by-cell. Even if it means you have to populate the entire array before the write. So I guess it depends on the size of your collection whether the array route is needed.

I don't think that is the issue Jan Karel, it is populating the array from the class data.

[Jan Karel Pieterse]

xld: But the thread begins with sample code that seems to write the data of the class cell-by-cell to a worksheet.
So my remark was that writing the data from the class to an array and THEN writing it to a sheet will prove faster. But with just a handfull of cells to be filled, it isn't worth the effort.

[Bob Phillips]

I am not disagreeing with you Jan Karel about dropping an array into a worksheet, but he still needs to get the data from the class to the array. If the pipelines class is holding the pipeline data in an array to start, then no problem, but if it is a collection, which I suggested and the OP didn't refute, then he still has to move it from the collection to the array.

[Jan Karel Pieterse]

but he still needs to get the data from the class to the array.
Of course!

[JKwan]

Thank you both for continuing commenting, with both of your comments, I created a method in my class to dump to collection into an array. HOLY cow, the performance is day and nite. I was sleeping during the write to the SS. Now, with the array, I don't even have time to blink! Thanks for the suggestion.

[Bob Phillips]

Glad we helped mate, hope we didn't go too far off-topic.