User-Defined Object In Array VBA

Question

As the subject line states, I am trying to store objects of a class I wrote into an Array. VBA is giving me the frustrating error:

'Object Variable or With Block variable not set'

I'm pretty sure I am defining my object correctly, and the local variables show that the object is of my defined type and all of it's fields are filled in, so I can't figure out where my issue is.

Dim Type1(2 To 250) As myClass
Dim Type2(2 To 250) As myClass
Dim Type3(2 To 250) As myClass
Dim temp_obj As myClass
Dim foo As String

For i = 2 To 250
    Set temp_obj = New myClass
    With temp_obj
        .field1 = Worksheets("Sheet1").Rows(i).Columns(2).Value
        .field2 = Worksheets("Sheet1").Rows(i).Columns(3).Value
        .field3 = Worksheets("sheet1").Rows(i).Columns(4).Value
        .field4 = Worksheets("Sheet1").Rows(i).Columns(5).Value
    End With
    foo = Worksheets("Sheet1").Rows(i).Columns(1).Value
    If foo = "Type1" Then
        Type1(i) = temp_obj
    ElseIf foo = "Type2" Then
        Type2(i) = temp_obj
    ElseIf foo = "Type3" Then
        Type3(i) = temp_obj
    End If
Next i

My class looks something like this:

Private pfield1 As Single
Private pfield2 As Integer
Private pfield3 As String
Private pfield4 As String

Public Property Get field1() As Single
    field1 = pfield1 
End Property

Public Property Get field2() As Integer
    field2 = pfield2
End Property

Public Property Get field3() As String
    field3 = pfield3
End Property

Public Property Get field4() As String
    field4 = pfield4
End Property

Public Property Let field1(p As Single)
    pfield1 = p
End Property

Public Property Let field2(p As Integer)
    pfield2 = p
End Property

Public Property Let field3(p As String)
    pfield3 = p
End Property

Public Property Let field4(p As String)
    pfield4 = p
End Property

Excel Hero · Accepted Answer · 2015-10-03 00:40:40Z

1

Try this change at the bottom:

If foo = "Type1" Then
    Set Type1(i) = temp_obj
ElseIf foo = "Type2" Then
    Set Type2(i) = temp_obj
ElseIf foo = "Type3" Then
    Set Type3(i) = temp_obj
End If

edited Oct 3, 2015 at 0:40

answered Oct 3, 2015 at 0:24

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

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

2 Comments

P. Jindra Over a year ago

When I make that change I get a type error: "Object doesn't support this property or method"

P. Jindra Over a year ago

Ah! Didn't see your second suggestion. That worked perfectly, and it makes total sense. Forgot to use the Set keyword since I was updating an Array. Thank you!

Collectives™ on Stack Overflow

User-Defined Object In Array VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related