array use problem

  • Thread starter Thread starter NikkoW
  • Start date Start date
N

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
 
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.
 
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
 
Alan Beban said:
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
Click to expand...
.....

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?
 
Back
Top