vba 为用户定义的数据类型赋值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4947840/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Assigning values to a user defined data type
提问by sar
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.
我有一个存储在 Excel 表中的一周内多次测量的数据集。我为数据集的范围创建了一个变量。然后我用日期和单一类型创建了一个用户定义的数据类型。现在我想将范围的值分配给用户定义的数据类型。
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:
并创建了一个 phData 类型的变量并将大小与范围匹配：
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:
并在excel电子表格上定义数据集的范围：
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:
现在我想将范围内的值分配给 phData 类型。我可以使用以下方法一次分配一个值：
dailyData(1).Dy= dataRng(1).Value
but I am need something more efficient as I have about 4,000 records.
但我需要更高效的东西，因为我有大约 4,000 条记录。
采纳答案by Jean-Fran?ois Corbett
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...)
查看这篇旧但仍然非常有用的文章：http: //www.avdf.com/apr98/art_ot003.html——请记住，您不再受 Excel 5&7 的限制（除非您使用的是 Excel 5 或 7 ，在这种情况下，我有一些很酷的 MC Hammer 磁带我想卖给你......）
回答by Tim Williams
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.
您现在可以遍历二维数组“theData”并从中填充您的 UDT 数组。
Tim
蒂姆
