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