Excel VBA: Adding objects to a collection within a class

Question

I am trying to create a class (named ClassSection) that contains a collection (named DefectCollection). It needs a function to add items to that collection but I'm having trouble making it work. I get Error 91 "Object variable or with block variable not set."

I have looked at the other answers on here, which is what got me this far, but I don't understand what I'm missing.

Here is the class module code:

Public DefectCollection As Collection

Private Sub Class_Initialise()
    Set DefectCollection = New Collection
End Sub

Public Function AddDefect(ByRef defect As CDefect)
    DefectCollection.Add defect [<---- error 91]
End Function

And here is the code that calls the function: ('defect' is another class, which works fine - I want each 'ClassSection' to be able to hold an unlimited number of 'defects')

Dim SC As Collection
Dim section As ClassSection
Set SC = New Collection

Dim SurveyLength As Double

For Each defect In DC
    SurveyLength = WorksheetFunction.Max(SurveyLength, defect.Pos, defect.EndPos)
Next defect

SurveyLength = Int(SurveyLength)

For i = 0 To numSurveys
    For j = 0 To SurveyLength
        Set section = New ClassSection
        section.ID = CStr(j & "-" & dates(i))
        SC.Add Item:=section, Key:=section.ID
    Next j
Next i


Dim meterage As Double

For Each defect In DC
    meterage = Int(defect.Pos)
    Set section = SC.Item(meterage & "-" & defect.SurveyDate)
    section.AddDefect defect
Next defect

Thanks!

Private Sub Class_Initialise() should be Private Sub Class_Initialize() — Axel Richter
– Axel Richter, Commented Apr 27, 2015 at 10:16

Matteo NNZ · Accepted Answer · 2015-04-27 10:24:36Z

1

You get the error because the DefectCollection is Nothing. This is due to the fact that you mispelled the initalization method:

Private Sub Class_Initialise() '<-- it's with "Z", not "S"

Hence, the initialization of the class is never called, the object remain Nothing by default and the method fails when trying to add an object to Nothing

answered Apr 27, 2015 at 10:24

Matteo NNZ

12.8k13 gold badges62 silver badges104 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA: Adding objects to a collection within a class

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related