VBA：创建一个类模块数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44631933/
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
VBA: Create an array of class module
提问by Razero
I'm trying to create an array of my custom class, but it gives me this error:
我正在尝试创建我的自定义类的数组，但它给了我这个错误：
Run-time error '91':
Object variable or With block variable not set
运行时错误“91”：
对象变量或未设置块变量
Here is my code so far:
到目前为止，这是我的代码：
Sub DBM_Format()
Dim coreWS As Worksheet
Dim WS As Worksheet
Dim LastRow As Long
Dim RowRange As Long
Dim dataList() As clsDBM
Dim tmpdate As Date
Set coreWS = Sheets(ActiveSheet.Name)
'Set WS = Sheets.Add
LastRow = coreWS.Columns("A").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).row
RowRange = LastRow - 1
Dim row As Integer
ReDim Preserve dataList(RowRange)
Dim i As Integer
Dim tmpData As clsDBM
For i = 0 To (RowRange - 1)
row = i + 2
tmpData.setDate = Format(Cells(row, 2), "MM/dd/yyyy hh:mm:ss")
tmpData.setBloodGlucose = Cells(row, 3)
tmpData.setCH = Cells(row, 4)
tmpData.setInzulinF = Cells(row, 5)
tmpData.setInzulinL = Cells(row, 6)
tmpData.setCategory = Cells(row, 8)
tmpData.setDayOfWeek = Weekday(dataList(i).pDate, vbMonday)
'dataList(i).setDate = Format(Cells(row, 2), "MM/dd/yyyy hh:mm:ss")
'dataList(i).setBloodGlucose = Cells(row, 3)
'dataList(i).setCH = Cells(row, 4)
'dataList(i).setInzulinF = Cells(row, 5)
'dataList(i).setInzulinL = Cells(row, 6)
'dataList(i).setCategory = Cells(row, 8)
'dataList(i).setDayOfWeek = Weekday(dataList(i).pDate, vbMonday)
Set dataList(i) = tmpData
Next i
End Sub
And the class module:
和类模块：
Option Explicit
Public pDayOfWeek As Integer
Public pDate As Date
Public pBloodGlucose As Double
Public pCH As Double
Public pInzulinF As Double
Public pInzulinL As Double
Public pCategory As String
Public Property Let setDayOfWeek(Value As Integer)
pDayOfWeek = Value
End Property
Public Property Let setDate(Value As Date)
pDate = Value
End Property
Public Property Let setBloodGlucose(Value As Double)
pBloodGlucose = Value
End Property
Public Property Let setCH(Value As String)
If IsNumeric(Value) Then
setCH = CDbl(Value)
Else
setCH = 0
End If
End Property
Public Property Let setInzulinF(Value As String)
If IsNumeric(Value) Then
pInzulinF = CDbl(Value)
Else
pInzulinF = 0
End If
End Property
Public Property Let setInzulinL(Value As String)
If IsNumeric(Value) Then
pInzulinL = CDbl(Value)
Else
pInzulinL = 0
End If
End Property
Public Property Let setCategory(Value As String)
If Value = "Something" Then
If Hour(pDate) < 9 Then
pCategory = "Something"
ElseIf Hour(pDate) < 11 Then
pCategory = "Something"
ElseIf Hour(pDate) < 14 Then
pCategory = "Something"
ElseIf Hour(pDate) < 16 Then
pCategory = "Something"
ElseIf Hour(pDate) < 19 Then
pCategory = "Something"
ElseIf Hour(pDate) < 21 Then
pCategory = "Something"
End If
Else
pCategory = Value
End If
pCategory = Value
End Property
So my class name is "clsDBM" and I'm trying to fill this array with corresponding data from a whorksheet. The table is well-formatted, there is no empty lines, so that is not the problem, but I can't figure out what is...
所以我的类名是“clsDBM”，我试图用来自 whorksheet 的相应数据填充这个数组。表格格式正确，没有空行，所以这不是问题，但我不知道什么是......
Is there a way to fix it and make this happen (or should I use a completely different approach :D)
有没有办法修复它并实现它（或者我应该使用完全不同的方法：D）
Thanks in advance!
提前致谢！
回答by Zsmaster
use the newoperator
使用新运算符
Dim tmpData As New clsDBM
Because this statement that you're using: Dim tmpData As clsDBMsimply defines a variable container or placeholder, of typeclsDBMwith a default value of Nothing(likewise: Dim i as Integercreates an emptyinteger with a default value of 0). To create an actual instanceof that class object, you need to Newit.
因为您正在使用的这个语句：Dim tmpData As clsDBM只是定义了一个变量容器或占位符，其类型clsDBM为默认值Nothing（同样：Dim i as Integer创建一个默认值为 的空整数0）。要创建该类对象的实际实例，您需要使用New它。
回答by Rik Sportel
To expand on Zsmaster, here's a full example filling up an Array of 5 items with your custom class:
为了扩展 Zsmaster，这里有一个完整的例子，用你的自定义类填充 5 个项目的数组：
Private myCls(0 To 4) As myClass
Private Sub Test()
Dim i As Integer
For i = 0 To 4
Set myCls(i) = New myClass
Next i
End Sub
In your case, you'd have to start the loop with:
在您的情况下，您必须以以下方式开始循环：
For i = 0 To (RowRange - 1)
row = i + 2
Set tmpData = New clsDBM
tmpData.setDate = Format(Cells(row, 2), "MM/dd/yyyy hh:mm:ss")
'... do stuff...
Set dataList(i) = tmpData
Next i
Or, alternatively forget about the tmpData object and do it like this:
或者，或者忘记 tmpData 对象并这样做：
For i = 0 To (RowRange - 1)
Set dataList(i) = New clsDBM
row = i + 2
dataList(i).setDate = Format(Cells(row, 2), "MM/dd/yyyy hh:mm:ss")
dataList(i). '...Do more stuff...
Next i
