Using variable in object name (VBA Excel)

Question

Here is what I want to do:

Private Function foo(bar As Integer)
    For n = 0 To ComboBox(bar).ListCount - 1
       [some stuff]
    End For
End Function

I'm getting an error on the second line because I'm using (bar), rather than (e.g.) 2, or 3.

Can I accomplish what I want to do?

Sub or Function not defined on the 'Private Function' line. This doesn't happen when I substitute 2 or 3 for '(bar)' — Tom Alan
– Tom Alan, Commented Jul 23, 2015 at 15:26

Rory · Accepted Answer · 2015-07-23 15:47:13Z

2

If this is on a userform, you can use:

For n = 0 To Me.Controls("ComboBox" & bar).ListCount - 1

If it is a Worksheet module and an ActiveX combobox, you will need:

For n = 0 To Me.OLEObjects("ComboBox" & bar).Object.ListCount - 1

answered Jul 23, 2015 at 15:31

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

3 Comments

I'm getting 'Method or data member not found', here's my exact code: Private Function isDuplicate(supplier As String, numBox As String) As Boolean

@TomAlan Where is this code located - which module?

On Sheet1. This sheet also contains all the combobox code.

genespos · Accepted Answer · 2015-07-23 15:34:51Z

1

Try to send the name of the control to the function:

Private Function foo(bar As String)
    For n = 0 To Me.Controls(bar).ListCount -1
       [some stuff]
    Next n 
End Function

Or, if you use (for example) ComboBox1, ComboBox2, ComboBox3 ... ComboBoxN You can do so:

Private Function foo(bar As String)
    For n = 0 To Me.Controls("ComboBox" & bar).ListCount -1
       [some stuff]
    Next n 
End Function

answered Jul 23, 2015 at 15:34

genespos

3,3216 gold badges43 silver badges78 bronze badges

I'm getting 'Method or data member not found', here's my exact code: Private Function isDuplicate(supplier As String, numBox As String) As Boolean