name references

[SuzyQ]

I have constants name1, name2, name3 etc.

I have a loop structure with a counter. If the counter is at 3, then I want
to use the constant name3. If it is at 2 then I want to use the constant
name2 etc.

How do I reference the correct constant?

"name" & xloop

 

[John W. Vinson]

I have constants name1, name2, name3 etc.

I have a loop structure with a counter. If the counter is at 3, then I want
to use the constant name3. If it is at 2 then I want to use the constant
name2 etc.

How do I reference the correct constant?

"name" & xloop

Why not create an Array of constants?

Or are you talking about fields in a table? What's the context?

 

[Tom van Stiphout]

On Sat, 6 Feb 2010 14:39:02 -0800, SuzyQ

That's asking a bit much of VBA: it's not a dynamic language.
You could do a Select Case:
dim i as integer
dim name as string
select case i
case 1
name = name1
case 2
name = name2
end select

Or:
dim names(2) as string
names(0) = "albert"
names(1) = "bob"
names(2) = "charlise"
for i = lbound(names) to ubound(names)
debug.print names(i)
next

or:
dim names as variant
names = Array("albert", "bob", "charlise")
Dim name As Variant
For Each name In names
Debug.Print name
Next

-Tom.
Microsoft Access MVP

 

[SuzyQ]

I have it in an array, but I know there's a way to do it by referencing. I
wasn't clear with my original post thinking I was giving enough general
information to get an answer.

I've done this in other languages and just haven't been able to get a handle
on doing it in VBA

what I want to do is build the variable name (or constant in this case)

This is what I want... (although this example is trivial, there are other
applications that I would like to use this format on - and in other languages
have been able to build a variable name in code)

Const cIntUpperbound = 10
Const cIntLowerbound = 1

Const cStrGoodbye1 = "Adéu" 'Catalan
Const cStrGoodbye2 = "Dovidenja" 'Croatian
Const cStrGoodbye3 = "Farvel" 'Danish
Const cStrGoodbye4 = "Au revoir" 'French
Const cStrGoodbye5 = "Adeus" 'Galician or Portuguese
Const cStrGoodbye6 = "Auf Wiedersehen" 'German
Const cStrGoodbye7 = "Arrivederci" 'Italian
Const cStrGoodbye8 = "Ciao" 'Italian
Const cStrGoodbye9 = "Adiós" 'Spanish
Const cStrGoodbye10 = "Kwaheri" 'Swahili

Dim intGoodbye As Integer

Randomize
intGoodbye = Int((cIntUpperbound - cIntLowerbound + 1) * Rnd +
cIntLowerbound) 'gets a psuedo random number from 1 to 10

'then using the base of the constant name cStrGoodBye and the value of
intGoodBye build the constant name to reference in the next line of code (ie
if intGoodBye = 1 then the constant that I want to build is cStrGoodBye1

'in foxpro I could do it like this (it's actually been over 10 years since
I've used fox pro so syntax may not be accurate be the gist is correct

GoodByeVar = "cStrGoodBye" + intGoodBye
'and then reference it like this
&GoodByeVar

MsgBox PUT_THE_PROPER_CONSTANT_HERE_IN_ONE_LINE, , "Good Bye"

 

[SuzyQ]

Here is a more appropriate example of what I want
The following code could be reduced to a loop from 1 to 10 like

for x = 1 to 10
strSearchX = "strSearch" & x
If strSearchX <> "" Then
If strSearchString = strSearchX Then
inList = True
GoTo exitInlist 'you're done exit function
End If
End If
next x

if I could build a variable name using the base of the name and a counter
and then had some way of referencing the newly built variable.


Public Function inList(strSearchString As String, strSearch1 As String,
Optional strSearch2 As String, _
Optional strSearch3 As String, Optional strSearch4
As String, Optional strSearch5 As String, _
Optional strSearch6 As String, Optional strSearch7
As String, Optional strSearch8 As String, _
Optional strSearch9 As String, Optional strSearch10
As String) As Boolean

inList = False

'first search variable is required, no need to check to see if it is
present
If strSearchString = strSearch1 Then 'if the search string matches the
first parameter in the list then set inlist to true
inList = True
GoTo exitInlist 'you're done exit function
End If

If strSearch2 <> "" Then 'there is a second search variable
If strSearchString = strSearch2 Then 'it matches
inList = True
GoTo exitInlist 'you're done exit function
End If
End If
If strSearch3 <> "" Then
If strSearchString = strSearch3 Then
inList = True
GoTo exitInlist 'you're done exit function
End If
End If
If strSearch4 <> "" Then
If strSearchString = strSearch4 Then
inList = True
GoTo exitInlist 'you're done exit function
End If
End If
If strSearch5 <> "" Then
If strSearchString = strSearch5 Then
inList = True
GoTo exitInlist 'you're done exit function
End If
End If
If strSearch6 <> "" Then
If strSearchString = strSearch6 Then
inList = True
GoTo exitInlist 'you're done exit function
End If
End If
If strSearch7 <> "" Then
If strSearchString = strSearch7 Then
inList = True
GoTo exitInlist 'you're done exit function
End If
End If
If strSearch8 <> "" Then
If strSearchString = strSearch8 Then
inList = True
GoTo exitInlist 'you're done exit function
End If
End If
If strSearch9 <> "" Then
If strSearchString = strSearch9 Then
inList = True
GoTo exitInlist 'you're done exit function
End If
End If
If strSearch10 <> "" Then
If strSearchString = strSearch10 Then
inList = True
GoTo exitInlist 'you're done exit function
End If
End If

exitInlist:
End Function

 

[Arvin Meyer [MVP]]

Wouldn't:

MsgBox cStrGoodbye & intGoodbye, , "Good Bye"

do it for you?

 

[SuzyQ]

No, because cStrGoodbye is not a variable cStrGoodbye1 or cStrGoodbye2 etc
are my variables (constants in this case)

 

[Arvin Meyer [MVP]]

Have you tried it?

If intGoodbye = 1 Then

cStrGoodbye & intGoodbye

is the same as:

cStrGoodbye1
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[SuzyQ]

yes - it won't even compile because the variable is not defined.

 

[SuzyQ]

I could have swore that in a previous post that I placed on different topic,
one of the responders took issue that I was not looping with similar variable
names and posted a solution (that did not involve an array), but since my
question was on different topic, I didn't use his suggestion at the time,
thinking I would come back to it later, but now I can't find it to see his
suggestion (at least six months ago). The post was to handle such a case
where I have multiple variables with same base name with a count at the end.

 

[Arvin Meyer [MVP]]

Ahh yes. It wouldn't compile like that, Sorry. Try something like this, If 9
doesn't work for the array, try 10:

Public Function GetArray() As String()
Dim cStrGoodbye(9) As String
cStrGoodbye(0) = "Adéu" 'Catalan
cStrGoodbye(1) = "Dovidenja" 'Croatian
cStrGoodbye(2) = "Farvel" 'Danish
cStrGoodbye(3) = "Au revoir" 'French
cStrGoodbye(4) = "Adeus" 'Galician or Portuguese
cStrGoodbye(5) = "Auf Wiedersehen" 'German
cStrGoodbye(6) = "Arrivederci" 'Italian
cStrGoodbye(7) = "Ciao" 'Italian
cStrGoodbye(8) = "Adiós" 'Spanish
cStrGoodbye(9) = "Kwaheri"

GetArray = cStrGoodbye
End Function

 

[Dirk Goldgar]

I could have swore that in a previous post that I placed on different
topic,
one of the responders took issue that I was not looping with similar
variable
names and posted a solution (that did not involve an array), but since my
question was on different topic, I didn't use his suggestion at the time,
thinking I would come back to it later, but now I can't find it to see his
suggestion (at least six months ago). The post was to handle such a case
where I have multiple variables with same base name with a count at the
end.

Youi can do it with *control* names on a form or report. If you had text
boxes named txtGoodbye1, txtGoodbye2,
...., txtGoodbye10, then you could pick one by number like this:

MsgBox Me.Controls("txtGoodbye" & intGoodByeVar)