VBA: Create an array of class module

Question

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

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

Is there a way to fix it and make this happen (or should I use a completely different approach :D)

Thanks in advance!

Answer 1

To expand on Zsmaster, here's a full example filling up an Array of 5 items with your custom class:

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:

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

Answer 2

use the new operator

Dim tmpData As New clsDBM

Because this statement that you're using: Dim tmpData As clsDBM simply defines a variable container or placeholder, of type clsDBM with a default value of Nothing (likewise: Dim i as Integer creates an empty integer with a default value of 0). To create an actual instance of that class object, you need to New it.