arraysexcelvbaforeachcallbyname

Iterate Over Static Array of Objects


I want to iterate over several ComboBox objects in the same Worksheet sht:

Dim obj As ComboBox
Dim sht as Worksheet
...
For Each obj In Array(sht.ComboBox1, sht.ComboBox2)
Next obj

Runtime error 424: object required (raised at For Each ...)

I also tried to address the objects by iterating over the names:

Dim s as Variant
Dim obj As ComboBox
Dim sht as Worksheet
...
For Each s In Array("ComboBox1", "ComboBox2")
    obj = CallByName(sht, s, VbGet)
Next s

Runtime error 91: Object variable or With block variable not set. (raised at obj = ...)

  1. What is the correct way?

  2. And why don't these approaches work as I would expect them to?


Solution

  • Approach 1

    Prepending Set as suggested by @KostasK. to the assignment works:

    Dim s as Variant
    Dim obj As ComboBox
    Dim sht as Worksheet
    ...
    For Each s In Array("ComboBox1", "ComboBox2")
        Set obj = CallByName(sht, s, VbGet)
    Next s
    

    Approach 2

    The ComboBox is part of Worksheet.OLEObjects. In this case obj must be declared as type OLEObject to work (as long as Option Explicit is set)

    Dim s as Variant
    Dim obj As OLEObject
    Dim sht as Worksheet
    ...
    For Each s In Array("ComboBox1", "ComboBox2")
        Set obj = sht.OLEObjects(s)
    Next s