vba - Assigning values to a user defined data type -

i assign values of range user defined data type.

i have dataset of measurements taken @ mutliple times on course of week stored in excel sheet. have created variable range of data set. created user defined data type date , single types. assign values of range user defined data type.

data set:

02/11/2011  3.8  02/11/2011  2.4  02/11/2011  8.9  02/12/2011  5.7  02/12/2011  4.6  02/12/2011  2.6 

i've made user define data type:

type phdata     dy date     ph single end type 

and created variable of phdata type , matched size range:

dim dailydata() tradedata dim nrec integer nrec = datarng.rows.count redim dailydata(nrec) 

and defined range of dataset on excel spreadsheet:

dim datarng range set datarng = range("a2", range("a2").end(xldown).end(xltoright)) 

and assign values in range phdata type. can assign 1 value @ time using:

 dailydata(1).dy= datarng(1).value 

but need more efficient have 4,000 records.

try this:

dim rngdata range dim vardummy variant dim dailydata() phdata dim idailydata long  set rngdata = range("a2", range("a2").end(xldown).end(xltoright)) ' or whatever  vardummy = rngdata ' reads in whole range @ once array. (must variant.)                    ' quicker reading 1 cell @ time.  redim dailydata(1 ubound(vardummy, 1))  ' parse data user-defined type array. idailydata = lbound(dailydata) ubound(dailydata)     dailydata(idailydata)         .dy = cdate(vardummy(idailydata, 1))         .ph = csng(vardummy(idailydata, 2))     end next idailydata 

haven't test code before posting...

check out old still quite useful article: http://www.avdf.com/apr98/art_ot003.html -- keeping in mind no longer limited excel 5&7 limitations (unless you're using excel 5 or 7, in case have cool mc hammer tapes i'd sell you...)