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!

David Zemens · Accepted Answer · 2017-06-19 13:35:25Z

8

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.

edited Jun 19, 2017 at 13:35

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

answered Jun 19, 2017 at 13:33

Zsmaster

1,5594 gold badges20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rik Sportel · Accepted Answer · 2017-06-19 14:37:04Z

3

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

edited Jun 19, 2017 at 14:37

answered Jun 19, 2017 at 14:18

Rik Sportel

2,6781 gold badge17 silver badges26 bronze badges

2 Comments

Patrick Lepelletier Over a year ago

the tempvariable is usually better. Also do not forget using ´with ´ statement if you use many times data(i), or TmpVariable.

Rik Sportel Over a year ago

Using "with" statement indeed avoids an extra IO operation and is good practice. The temp variable however eats up extra memory and is less optimized than working directly against the array. You would need it when using a Collection object.

Collectives™ on Stack Overflow

VBA: Create an array of class module

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related