Passing a Workbook array

[bcmiller]

I am writing code to consolidate values from several workbooks. Eac
workbook is set-up identically. Initially, I open each workbook from
user defined list. I then make a function call to grab certain value
from each workbook and add them to various totals. At the moment, whe
the code tries to activate a workbook in the passed array, it goe
nuts, exits the functions and continues with the code in the sub fro
which the function was called. I am passing the workbook array as
paramarray of type variant. Has anyone had similar problems and know
how to fix this???

Cheers

BC

---- Code Sample ----
Call Sum_Details(NumberOfFiles, FileWorkbooks())

Function Sum_Details(NumberOfFiles As Integer, ParamArra
FileWorkbooks() As Variant)

....

For i = 1 To NumberOfFiles
FileWorkbooks(i).Activate
......

End Function
---- Code Sample ---

 

[Harlan Grove]

For i = 1 To NumberOfFiles
FileWorkbooks(i).Activate

....

Learn how to use the VBE debugging tools, especially watch expressions. Had
you watched FileWorkbooks, you might have noticed that it's a zero-based
array.

Don't pass NumberOfFiles, use LBound(FileWorkbooks) and
UBound(FileWorkbooks) as iteration bounds.

 

[Harlan Grove]

One more thing. If you pass an array as an argument that itself is defined
as a ParamArray argument, e.g.,

Function foo(ParamArray a() As Variant)
....
End Function

....

x = foo(Array(yada, yada, yada))

then there'll be only one entry in a(), but that entry will itself be an
array, so inside foo() you'd need to check whether a(i) were an array, and
if so index its entries, e.g., a(i)(j).