Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Label Options - Forms Object vs Control Box Object | Excel Discussion (Misc queries) | |||
Object required??? What object? | Excel Discussion (Misc queries) | |||
Use of arrays | Excel Worksheet Functions | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
ARRAYS | Excel Programming |