VBA：创建一个类模块的数组

Question

我试图创建一个我的自定义类的数组，但是它给出了这个错误：

运行时错误'91'： 对象变量或With块变量未设置

这是我的代码：

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

类模块：

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

我的类名是“clsDBM”，我正在尝试使用工作表中对应的数据来填充这个数组。该表格格式良好，没有空行，所以那不是问题，但我无法弄清楚问题所在...

有没有办法解决这个问题并使其发生（或者我应该采用完全不同的方法:D）

提前致谢！

- Razero

Answer 1

使用new操作符。

Dim tmpData As New clsDBM

因为你正在使用的语句是：Dim tmpData As clsDBM，它仅仅定义了一个变量容器或占位符，类型为clsDBM，默认值为Nothing（同样地：Dim i as Integer创建了一个空整数，默认值为0）。要创建该类对象的实际实例，你需要使用New。

- Zsmaster

Answer 2

为了更好地解释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

在您的情况下，您需要从以下内容开始循环：

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

或者，你可以忘记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

- Rik Sportel

---

通常情况下，使用临时变量更好。如果您多次使用data(i)或TmpVariable，请不要忘记使用“with”语句。 - Patrick Lepelletier

使用“with”语句确实会避免额外的IO操作，是一个好的编程习惯。然而，临时变量会占用额外的内存，而且不如直接对数组进行操作优化。当使用Collection对象时，您需要它。 - Rik Sportel