Clever Geek Handbook

VBA: create array of class module

I am trying to create an array of my custom class, but it is giving me this error:

Runtime error '91':

Object variable or with unspecified block variable

Here is my code:

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 am trying to populate this array with the appropriate data from wholsheet. The table is well formatted, there are no empty lines, so this is not a problem, but I cannot figure out which is ...

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

Thanks in advance!

+3


source to share


2 answers


use new operator

Dim tmpData As New clsDBM


Because this operator you are using: Dim tmpData As clsDBM

simply defines a variable container or placeholder of the type clsDBM

with a default value Nothing

(similar to: Dim i as Integer

creates an empty integer with a default value of 0

). To create an actual instance of this class object, you need New

it.

+5


source


To expand on Zsmaster, here's a complete example of populating a 5 element array 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 need 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 else, forget about the tmpData object and do 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
+3


source






More articles:


All Articles