creating object variables using dynamic names

[Carl]

I have been struggling with this problem and would
appreciate some help. I suspect that there is a simple
solution, but after reading a VBA book, searching the
internet and trying various apporaches, I am stuck.

Simply put, I'd like to be able to do something like:

For i = 0 To maxLists
objTemp = "listJobTitles" & i
With objTemp
...
End With
Next i

Instead, I am forced to explicitly define each occurence
of objects as follows...

For i = 0 To maxLists
Select Case i
Case 0
objTemp = listJobTitles0
Case 1
objTemp = listJobTitles1
Case 2
objTemp = listJobTitles3
...
...
...
End Select

With objTemp
....
End With
Next i

Essentially I need a way to build the name of an object
using some form of expression rather than using explicit
definitions. In scripting languages such as JScript and
PHP you can use eval(expression) ... but as a newbie, I
am at a loss on how best to handle this in VBA.

TIA

 

[Jay Freedman]

Hi, Carl,

You don't say what kind of things listJobTitles0, listJobTitles1...
are, but if they're members of a collection of objects, you can refer
to them like this:

For i = 0 To maxLists
objTemp = listJobCollection("listJobTitles" & i)
With objTemp
...
End With
Next i

This syntax is actually shorthand for
listJobCollection.Item("listJobTitles" & i) but there's no reason to
use the longer syntax.

 

[Tony Strazzeri]

Hi Carl,

I think this from an earlier posting of mine may show you how to deal with that.

paste the following code into the declarations
section of a form into which you have places a commandbutton called
"CommandButton1" and four checkboxes called "cbApples", "cbOranges",
"cbPears", "cbLemons"

Run the form and select one or more of the items then click the button

'*****Start of code *****
Dim cbAy() As Object
Const NumItems = 4

Private Sub CommandButton1_Click()
For I = 1 To NumItems
With cbAy(I)
If .Value = True Then
msg = msg & .Caption & vbCr
End If
End With
Next

MsgBox "The following were selected " & vbCr & msg
End Sub

Private Sub UserForm_Initialize()
ReDim cbAy(NumItems)
Set cbAy(1) = cbApples
Set cbAy(2) = cbOranges
Set cbAy(3) = cbPears
Set cbAy(4) = cbLemons
End Sub
'***** End of code *****

Hope this Helps
Cheers
TonyS.

 

[Carl Thompson]

Hi thanks for the response ...

The things are List Boxes in a formWizard. I've got a
group of 8 of them. There names only differ by the digit
appended to them at the end. I've seen some examples of
references to Word form objects ... but I take it that
they can be refenced differently.

Thanks again.