I would like to assign a the values of a range to a user defined data type.
I have a dataset of measurements taken at mutliple times over the course of a week stored in an excel sheet. I have created a variable for the range of the data set. Then I created a user defined data type with date and single types. Now I would like to assign the values of the range to the 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 a user define data type:
Type phData
Dy As Date
ph As Single
End Type
and created a variable of the phData type and matched the size to the range:
Dim dailyData() As tradeData
Dim nrec as Integer
nrec = dataRng.Rows.Count
ReDim dailyData(nrec)
and defined the range of the dataset on the excel spreadsheet:
Dim dataRng As Range
Set dataRng = Range("A2", Range("A2").End(xlDown).End(xlToRight))
and now I would like to assign the values in the range to the phData type. I can assign one value at a time using:
dailyData(1).Dy= dataRng(1).Value
but I am need something more efficient as I have about 4,000 records.
Try this:
Dim rngData As Range
Dim varDummy As Variant
Dim DailyData() As phData
Dim iDailyData As Long
Set rngData = Range("A2", Range("A2").End(xlDown).End(xlToRight)) ' or whatever
varDummy = rngData ' Reads in whole range at once into array. (Must be Variant.)
' Much quicker than reading one cell at a time.
ReDim DailyData(1 To UBound(varDummy, 1))
' Parse data into your user-defined type array.
For iDailyData = LBound(DailyData) To UBound(DailyData)
With dailyData(iDailyData)
.Dy = CDate(varDummy(iDailyData, 1))
.ph = CSng(varDummy(iDailyData, 2))
End With
Next iDailyData
Haven't test the code before posting...
Check out this old but still quite useful article: http://www.avdf.com/apr98/art_ot003.html -- keeping in mind that you are no longer limited by Excel 5&7 limitations (unless you're using Excel 5 or 7, in which case I have some cool MC Hammer tapes I'd like to sell you...)