ExcelBanter - Object Arrays

Paul,
The following creates an array of workbooks.

Sub foo()
Dim v As Variant, i
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Set wb1 = Workbooks("wb1.xls")
Set wb2 = Workbooks("wb2.xls")
Set wb3 = Workbooks("wb3.xls")
v = Array(wb1, wb2, wb3)
For i = LBound(v) To UBound(v)
Debug.Print v(i).Sheets(1).Name
Next i
End Sub

You could skip the assignment to individual workbook variables and build the
array using

v = Array(Workbooks("wb1.xls"), Workbooks("wb2.xls"), Workbooks("wb3.xls"))

You can also use an array of workbook names to index into the Workbooks
collection.
Dim s as String
v = Array("wb1.xls","wb2.xls","wb3.xls")
For s = LBound(v) to UBound(v)
Debug.Print Workbooks(CStr(s)).Name
Next s

You can integrate over entire collection (although admittedly, this may get
workbooks you don't want to include):
For i = 1 to Workbooks.Count
If LCase(Left(Workbooks(i).Name,2)) = "wb" Then
Debug.Print Workbooks(i).Name
End If
Next i

You could add workbooks of interest to a separate collection, and affix your
own index (which has to be a string but need not be numeric):

Dim col as Collection
col.Add Workbooks("wb1.xls"), "1"
col.Add Workbooks("wb2.xls"), "2"
col.Add Workbooks("wb3.xls"), "3"

BTW, in the following:

Dim wb0, wb1, wb2, wb3, ActWB As Workbook

only ActWB will be of type Workbook. The others will be Variant. If you want
them all to be of type Workbook, you need to follow each of them with 'As
Workbook.'

Dim wb0 As Workbook, wb1 As Workbook, wb2 As Workbook, wb3 As Workbook,
ActWB As Workbook

Regards,
Bob Kilmer

"Paul" wrote in message
...
Hello,

I may just be beating up the wrong tree here but I'll
never know without asking.

I have a series of workbooks that code is used to extract
matching entries according to a selected criteria. My
problem is that I declare the variables as Objects, and
reference them in an array so I can use the index
funcation to cycle through them.

What I have found is that the array dim as variant,
returns the text string of the object not the reference
to it.

So what I would like to be able to do is to cycle through
the objects (whether Workbooks, Worksheets or Ranges).
Following is an exerpt from the code....
----------------------------------------------------------
Sub GenerateReportMain()
On Error Resume Next
Dim wb0, wb1, wb2, wb3, ActWB As Workbook
Dim i As Range
Dim wb1ActRange As Variant
Dim wb2ActRange As Variant
Dim wb3ActRange As Variant
Dim wbActiveRange As String
Dim wbActRange As Variant
Dim ActiveWB As Variant
Dim WorkSheetNames As Variant
Dim ProgramShort As Variant
Dim ProgGroupIndex As Integer
WorkSheetNames = Array

'Progress Bar Dimensions
Dim Counter As Integer
Dim PctDone As Single
Dim wb1Calcs As Integer
Dim wb2Calcs As Integer
Dim wb3Calcs As Integer
Dim TotalCalcs As Integer

WorkSheetNames = Array
("Training_Main", "Symposiums_Main", "MajorEvents_Main")
wbActRange = Array(wb1ActRange, wb2ActRange, wb3ActRange)
ProgramShort = Array("t", "s", "m")
ActiveWB = Array(wb1, wb2, wb3)
Counter = 1
TotalCalcs = 0
ProgGoupIndex = 1
While ProgGroupIndex < 4
' Used to Cycle through the code to advance wb1, wb2,
wb3... And other Array Arguments.

ActWb = ActiveWB (ProgGroupIndex)
ActWS = ActiveWS (ProgGroupIndex)
ActRange = ActiveRange(ProgGroupIndex)
ProgShort = ProgramShort(ProgGroupIndex)

For Each i In ActWB.Worksheets(ActWS).Range(ActRange)
If Rows(i.Row).Row 2 And Rows(i.Row).Row <
ActWB.Worksheets(ActWS).UsedRange.Rows.Count Then
.......Processs Code..........

next i
ProgGroupIndex = ProgGroupIndex + 1
wend
end sub
----------------------------------------------------------

At the moment, I have to repeat about 120 Lines of code
three times to complete the import, so If i can cycle
through the variables then that would make my job a lot
easier.

Thanks.