Object Arrays
 

Paul,

Also,

Sub foo2()
Dim w(0 To 2) As Workbook, i As Integer
Set w(0) = Workbooks("wb1.xls")
Set w(1) = Workbooks("wb2.xls")
Set w(2) = Workbooks("wb3.xls")
For i = LBound(w) To UBound(w)
Debug.Print w(i).Sheets(1).Name
Next i
End Sub

BTW, it is "beating around the bush" (not getting to the point) or "barking
up the wrong tree" (looking in the wrong place). I guess you could be
beating up a tree, if you were frustrated. <g

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.