array use problem

[NikkoW]

I am probably overlooking something really obvious. I have looked through
the online Excel & VB prog reference and it looks right to me. The following
code generates a "Compile Error - For Each may only iterate over a
collection object or an array.":

Dim MyFiles As String, I As Variant
MyFiles = Array("Consolidated.xls", "CAN1a.xls", _
"CAN1b.xls", "CAN1c.xls")
For Each I In MyFiles

Thanks for the help.

Nick

 

[Juan Pablo González]

You have to Dim MyFiles as Variant too.. not as String.

 

[NikkoW]

Doh!

See...I said it was obvious.

Thanks!

You have to Dim MyFiles as Variant too.. not as String.

 

[Tom Ogilvy]

Sub tester2()
Dim MyFiles As Variant, I As Variant
MyFiles = Array("Consolidated.xls", "CAN1a.xls", _
"CAN1b.xls", "CAN1c.xls")
For Each I In MyFiles
Debug.Print I
Next
End Sub


Declare myfiles as Variant.

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will work:

Dim MyFiles() As String, I As Variant
Assign Array("Consolidated.xls", "CAN1a.xls", _
"CAN1b.xls", "CAN1c.xls"), MyFiles
For Each I In MyFiles

Alan Beban

 

[Harlan Grove]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will work:

Dim MyFiles() As String, I As Variant
Assign Array("Consolidated.xls", "CAN1a.xls", _
"CAN1b.xls", "CAN1c.xls"), MyFiles
For Each I In MyFiles

.....

And what are the practical benefits to this compared to

Dim MyFiles As Variant
MyFiles = Array("Consolidated.xls", "CAN1a.xls", _
"CAN1b.xls", "CAN1c.xls")

That is, what are the advantages of declaring MyFiles as a dynamic array of
strings vs declaring it a variant that would hold a dynamic array of
strings? While the former uses 12 bytes less storage, calling another
procedure to assign the latter uses even more memory (since the procedure
would need to be in memory, and your code is likely to require more than 12
bytes), is slower and adds complexity? Is there anything to love about it?