VBA Excel Error 424 with collection object reference

Question

So I am creating a User form which collects data and will use that data to generate a report. There are a lot of questions that the user is asked. Specifically, there are blocks of questions about a test on this form which are identical. Numerous exist so that the user can input information about numerous tests on the form. I am relatively new to VBA excel, and I could not figure out how to make a class module for all of the repetitive question blocks. Consequently, I have made 8. the questions in each block are named differently by a number, for example: TestType1_ComboBox, TestType2_ComboBox, and so on.

Yes, I realize this is highly inefficient, but I had too much trouble setting up the class modules.

I want to be able to reference all of the X_ComboBoxes for example so I can AddItem's to all of them easily with a loop. I made a collection so I could reference all of the TestType_ComboBoxes for efficiency. I plan on doing this type of reference for all of the controls in the 8 question blocks.

Problem: I get the 424 object reference error when I try to reference an object in a collection.

''' create collections:
Public ttComboBox As Collection
Public ttAmountBlocked As Collection
Public ttgcFileName As Collection
Public ttsoiDate As Collection
Public ttsoiTime As Collection
Public ttReportUse As Collection


Sub ReportGenerationTool()
' set variables:
'...other code
' make organList an array list
Set organList = CreateObject("System.Collections.ArrayList")
'...more code

' set collections and add respective controls
Set ttComboBox = New Collection
With ttComboBox
    .Add (Report_Form.TestType1_ComboBox)
    .Add (Report_Form.TestType2_ComboBox)
    .Add (Report_Form.TestType3_ComboBox)
    .Add (Report_Form.TestType4_ComboBox)
    .Add (Report_Form.TestType5_ComboBox)
    .Add (Report_Form.TestType6_ComboBox)
    .Add (Report_Form.TestType7_ComboBox)
    .Add (Report_Form.TestType8_ComboBox)
End With
ttComboBox.Item(1).AddItem "Test"
'**^THIS IS WHERE I GET THE 424 ERROR**'

Set ttAmountBlocked = New Collection
'...similar set up as above 
Set ttgcFileName = New Collection
'...similar set up as above 
Set ttsoiDate = New Collection
'...similar set up as above
Set ttsoiTime = New Collection
'...similar set up as above
Set ttReportUse = New Collection
'...similar set up as above 



' initialize report form
Call ReportForm_Initialize

' show report form
Report_Form.Show


End Sub

Normally, I had this line of code: ttComboBox.Item(1).AddItem "Test" (and others like it) in the ReportForm_Initialize() sub. Essentially I want to have this: Report_Form.TestType1_ComboBox.AddItem "Test" with this: ttComboBox(1).AddItem "Test" If not with collections, how do I get this functionality?

Answer 1

That error means that an object is expected but none is provided, so your collection does not actually contain objects (like a form control ComboBox), but something else.

I confirm this with a test scenario, screenshot below

If you look at the collection in the Locals window, you'll see that it holds string values, not form control/combobox objects! Since a string value property doesn't have an .Add method, it raises the 424 error.

Why does the collection contain strings instead of ComboBoxes?

In VBA, parentheses normally force an evaluation of an expression

Sometimes this doesn't matter. For instance, ("hello") evaluates the same as "hello", but in the case of an object like a form control, parentheses will evaluate to the default property of that object (probably the .Value property).

How to fix it??

Simple, just remove the parentheses from the .Add statements.

With ttComboBox
    .Add Report_Form.TestType1_ComboBox
    .Add Report_Form.TestType2_ComboBox
    .Add ...etc.

Or you could be a little fancier and change the entire block:

With ttComboBox
    .Add (Report_Form.TestType1_ComboBox)
    .Add (Report_Form.TestType2_ComboBox)
    .Add (Report_Form.TestType3_ComboBox)
    .Add (Report_Form.TestType4_ComboBox)
    .Add (Report_Form.TestType5_ComboBox)
    .Add (Report_Form.TestType6_ComboBox)
    .Add (Report_Form.TestType7_ComboBox)
    .Add (Report_Form.TestType8_ComboBox)
End With

To this instead (assuming these are the only comboboxes on your form):

Dim cbox as Object

For each cbox in Report_Form.Controls
    If TypeName(cbox) = "ComboBox" Then
        ttComboBox.Add cBox
    End If
Next