Loop through list

[KenHigg]

Is there a way to do something like the following:

dim strTemp as string

strTemp = "'red,'yellow',blue'"

for each strTemp
 msgbox strTemp
next strTemp

Maybe with an array...? I've done it before but it escapes me

???
ken

 

[boblarson]

Is there a way to do something like the following:

dim strTemp as string

strTemp = "'red,'yellow',blue'"

for each strTemp
 msgbox strTemp
next strTemp

Maybe with an array...? I've done it before but it escapes me

???
ken

Just use the SPLIT function (assigned to a single dimension array)


Dim varSplit As Variant


varSplit = Split(strTemp,",")

var(0) would equal 'red'
var(1) would equal 'yellow'
var(2) would equal 'blue'

 

[tehNellie]

Split is your friend. It'll give you your array.

 

[KenHigg]

Cool.

So:

Dim strTableNames(5) as string
Dim X As Integer
    
strTableNames(0) = "tblMain"
strTableNames(1) = "tblPartStatus"
strTableNames(2) = "tblPartTypes"
strTableNames(3) = "tblSystemVars"
strTableNames(4) = "tblUsers"
    
For X = 0 To 4
    Msgbox strTableNames(X)
Next X

Would be:

dim strTableNames as string
dim X As Integer
dim strTableName as variant

strTableNames = "tblMain,tblPartStatus,tblPartTypes,tblSystemVars,tblUsers"

strTableName = Split(strTableNames,",")

For X = 0 To UBound(strTableName)
    Msgbox strTableName(X)
Next X

???
ken

 

[boblarson]

I think you've got it, with ONE exception. You need to declare strTableNames as a VARIANT. So, varTableNames As Variant.

 

[KenHigg]

ah I got those swapped around. Anyway thanks...

Looks like in this situation and with this small amout of data in may be just as easy to set up a standard array huh...


ken

 

[RoyVidar]

The split function returns a "standard" array, so just declare as dynamic array, say

Dim TableNames() As String
TableNames = Split("tblMain,tblPartStatus,tblPartTypes,tblSystemVars,tblUsers", ",")
debug.print Join(TableNames, " -@- ")

then maintenance will be easier when the amount of data grows