Assigning values to a user defined data type

Question

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.

Answer 1

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...)

Answer 2

I'm not aware of any way to do this which doesn't involve looping.

However, it will be faster if you read the range data into an array first:

Dim theData
theData = dataRng.Value

You can now loop through the 2-D array "theData" and populate your array of UDT from that.

Tim