Solved: Efficient way to store/retrieve data of large UDT

[GreenTree]

I have a project which uses User Defined Types extensively, and I need to be able to store the data into a spreadsheet which I can save, and then retireve it from the same spreadsheet later. Initially, it takes several minutes to generate the data from a bunch of sources with a bunch of processing, so being able to store & retrieve it for the next time I need to use it is essential. The data types look something like the following (somewhat simplified for illustrative purposes):
[vba]Type JobType
StartTime as integer ' minutes since midnight
StopTime as integer
Cost as double
Description as string
End Type

Type DayType
NumberJobs as integer ' may be one, may be as many as 10 jobs in a day
DayCost as double
DayRemarks as string
Jobs (1 to 10) as JobType
End Type

Type ProjectType
NumberDays as integer ' projects are from one to four days long
ProjectCost as double
BillingCode as integer
ProjectRemarks as string
Days(1 to 4) as DayType
end Type

Type MonthType
NumberOfProjects as integer 'from 3 to 15 projects in a month
MonthCost as double
MonthCashFlow as double
HoursBilled as double
Projects (1 to 15) as ProjectType
End Type[/vba]
Then the variable that holds everthing would be
[vba]Public Year(1 to 12) as MonthType[/vba]
Up until now, I've been saving everything in a spreadsheet using a lot of counters & For-Next loops to put everything into the cells and then to read it back in when the spreadsheet reloads (or reinitializes). My experience with programming (Pascal, Basic, a little 6502 Assembly language, some Ada, if you remember that) is greater than my experience with VBA, and there may well be a slick & efficient way (with Variants??) to put this stuff into a spreadsheet so that storing & reading it happens much quicker than running all the loops. Am I right in thinking that there is, or am I barking up the wrong tree?

Many thanks!

[mikerickson]

I wrote a BBS using 6502 AL for the C-64.

I'd be looking at variant arrays

Dim myArray as Variant

myArray=Range("a1:c3000").Value
Range("aa1:ac3000)).Value = myArray

will move the data from A1:C3000 to AA1:AC3000, leaving a the values in myArray(1 to 3000, 1 to 3).

The bulk read and bulk write are much faster than single cell loops, so I'd do all the looping to create an array but only one write to spreadsheet instruction.
Similarly one read from spreadsheet instruction and monster looping to assign all those values to the different properties of the types.

[GreenTree]

Thanks, Mike. That makes good sense that the reads are the time-consuming part of things and that assigning values to variables happens pretty fast. This approach does require "touching" each piece of data twice (first time during the loop, putting it in the monster array, second time is writing the array), but I can believe that the gains make it worthwhile. I'll use this approach as I rewrite some major sections of code this week, and see how things go.

Thanks for the help!

G.T.

[Aussiebear]

Yikes.... for a second there I thought a whole section of the crowd at the cricket had suddenly become vba fanatics.

For all non cricket lovers, when the crowd is bored they begin what we call a "Mexican Wave" as signified by GT's fan club.

[GreenTree]

There are some cool smilies here -- felt like playing around with them. Much like your sig line, in fact!