我最终设法填充了每个条目两个变量的集合。我这样做是通过定义一个称为" datarange"的类模块,该模块看起来像这样:
Public Strain As Double
Public Stress As Double
这就是我填充我的收藏的方式:
Sub ECalc()
Dim j As Integer
Dim mycol As Collection
Dim c As DataRange
Set mycol = New Collection
Set c = New DataRange
For j = 1 To 200
c.Strain = Sheets("Data").Range("I" & j).Value2
c.Stress = Sheets("Data").Range("K" & j).Value2
mycol.Add c
Next j
Debug.Print mycol.Count ' <--- This does work, I can see how many entries have been created (200 as stated by j = 200)
Debug.Print mycol.Item(20) ' <--- This does not work. WHY?
End Sub
当我通过" mycol.count"获得许多条目时,代码确实填充了我的收藏。但是,我无法像代码的最后一行一样访问单个项目。出现错误说:"运行时错误438对象不支持此属性或方法"
我在做什么错?
额外的信息:我不想在后续步骤中调整集合大小时使用数组。
edit 放大了我在OP的代码中找到的问题
是因为
mycol.Item(20)
您正在引用一个没有"默认值"值的object
(DataRange
类的对象(
因此,您必须代码:
Debug.Print mycol.Item(20).Stress ' print the 'Stress' property of the 'DataRange' object stored as the 20th item of your collection
Debug.Print mycol.Item(21).Strain ' print the 'Strain' property of the 'DataRange' object stored as the 21th item of your collection
,但是您必须将Set c = New DataRange
语句移入For
循环中,如下:
Sub ECalc()
Dim j As Long
Dim mycol As Collection
Dim c As DataRange
Set mycol = New Collection
For j = 1 To 200
Set c = New DataRange ' instantiate a new object of type 'DataRange' at every iteration
c.Strain = Sheets("Data").Range("I" & j).Value2 'assign current object 'Strain' property
c.Stress = Sheets("Data").Range("K" & j).Value2 'assign current object 'Stress' property
mycol.Add c ' add current object to the collection
Next
End Sub
否则,所有收集项目都将在For
循环之前与您实例化相同的DataRange
对象,从而使所有这些对象都与所有这些对象相同的 Strain
和 Stress
属性从i和k