array use problem

Excel Programming >> array use problem

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

Excel358

Excel Programming >> array use problem You have to Dim MyFiles as Variant too.. not as String.

--
Regards

Juan Pablo González

> 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
>
>

Excel Programming >> array use problem Doh!

See...I said it was obvious.

Thanks!

> You have to Dim MyFiles as Variant too.. not as String.
>
> --
> Regards
>
> Juan Pablo González
>

> > 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
> >
> >
>
>

Excel Programming >> array use problem 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.

--
Regards,
Tom Ogilvy

> 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
>
>

Excel Programming >> array use problem 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

> Doh!
>
> See...I said it was obvious.
>
> Thanks!
>
>

>
>>You have to Dim MyFiles as Variant too.. not as String.
>>
>>--
>>Regards
>>
>>Juan Pablo González
>>

>>
>>>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
>>>
>>>
>>
>>
>
>

Excel Programming >> array use problem
>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?