VBA - loop through array of strings

[Martin_King]

Hi,

I'm working on a project and was wondering: Is there a way to have a list of variables, each string, and then loop through them as an array

e.g.

Dim Cat, Dog, Mouse as string
For each String in array(Cat, Dog, Mouse)
...code
Next String

I've looked around and can't find something like this. I can loop through arrays of sheets, workbooks, ranges, etc but thats as I know what to put after "For Each" (e.g. sh, cell, etc)

Anyone ever used something similair?

Thanks,
Martin

 

[Peter_SSs]

First a comment on the following:

Dim Cat, Dog, Mouse as string

This line only declares Mouse as String. Cat and Dog will be Variants, not Strings. See my code below.

Is this the sort of thing you mean?

Sub Test()
    Dim Cat As String, Dog As String, Mouse As String
    Dim Itm
    
    Cat = "c"
    Dog = "d"
    Mouse = "m"
    
    For Each Itm In Array(Cat, Dog, Mouse)
        MsgBox Itm
    Next Itm
End Sub

 

[MickG]

Perhaps this:-

Dim Cat, Dog, Mouse As String
Dim st
Cat = 1
Dog = 2
Mouse = 3
For Each st In Array(Cat, Dog, Mouse)
MsgBox st
Next st

 

[vds1]

why not declare the array as string and you can refer them through index.

For example,

Sub test_Macro_Array()

Dim sAnimal(1 To 3) As String
sAnimal(1) = "Cat"
sAnimal(2) = "Dog"
sAnimal(3) = "Mouse"
For i = 1 To 3
  Debug.Print sAnimal(i)
Next i
End Sub

For More Clarity Read here. .
Array in Excel VBA

 

[Martin_King]

Thank you both, this is what I was after.

Peter, I didn't know that about the Dim; I've been using this method after inheriting modules and just assumed it was everything. Thanks for the heads up.

Could either of you explain why you have simply put Dim St / Itm then not declared it as anything e.g. variant
Why would this work? How does the VBA know that this refers to the strings in the array?

I will use this anyway as it does the job so don't feel obliged to go into too much detail - I will do my own research now

 

[Martin_King]

why not declare the array as string and you can refer them through index.

For example,

Sub test_Macro_Array()

Dim sAnimal(1 To 3) As String
sAnimal(1) = "Cat"
sAnimal(2) = "Dog"
sAnimal(3) = "Mouse"
For i = 1 To 3
  Debug.Print sAnimal(i)
Next i
End Sub

For More Clarity Read here. .
Array in Excel VBA

That is how I was tought and normaly do - it was more curiosity I guess as I had never worked out how to. I was thinking it would be easier for a user to debug if the words had more meaning than say animal(i).