Problems Defining Object Variable in For Each Loop

From: ExcelMonkey (anonymous_at_discussions.microsoft.com)
Date: 02/28/05 

Date: Mon, 28 Feb 2005 04:37:50 -0800

Sorry Bob. I am struggling with sending too much vs not
enough. Really stuck on this and can't move forward.
This is what happens when you try to change your poorly
written code that is working into better more efficient
code that doesn't work at all!.

Normally I would build the loop as follows:

For each sh in Workbook
   For each cell in Work***
     code
   Next
   For Each comment in Comments
     code
   Next
Next

However, I have different objects (cell, comments) and
collection(workseets, comments). Yet I only wanted 1 For
Each Loop. I have created some checkboxes to click off
the type of routine I want to do. So I decided that I
would define some public variables and pass the following
into a public array

Public ObjCollArray As Variant
Public ObjType As String
Public CollType As String
Public CurObj As Object 'Object like cell, comment

Private Sub OKButton_Click()
Set ObjCollArray(0) = Active***.Comments
Set ObjCollArray(1) = Active***.UsedRange
Set ObjCollArray(2) = Active***.UsedRange
Set ObjCollArray(3) = Active***.UsedRange
Set ObjCollArray(4) = Active***.UsedRange
Set ObjCollArray(5) = Active***.UsedRange

This array was set up identically to a checkboX array that
I have which houses Booleans for certain checkboxes which
are checked off. So if the second check box is checked,
it implies that I want to look for hard coded data in
cells utilizing ObjCollArray(1) above. I need a Range
Object with a Worksheets collection with a Used Range
property.

Then I would build a new loop structure with only 1 inner
loop within the *** loop:

For Each sh In .Worksheets
      If LCase(sh.Name) <> "Audit Results" Then
        For AuditTypes = 0 To 5
            'Only using 1 loop now
            For Each CurObj In ObjCollArray(1)
               Debug.Print sh.Name, ObjCollArray
>(AuditTypes).Parent.Name
               ObjType = TypeName(CurObj)
               CollType = TypeName(ObjCollArray(1))
            Call MainAudit(AuditTypes)
            Next
        Next
      End If
Next

So going with the example, I wanted my CurObj to be my
Range Object and my ObjCollArray(1) to be my Worksheets
Collection with a UsedRange property. As a check I typed
in:

Debug.Print sh.Name, CurObj.Parent.Name

expecting to see the same *** name. However, ?
CurObj.Parent.Name returns the *** name that my cursor
is actively in. This is because I set up my inital array
as

Set ObjCollArray(1) = Active***.UsedRange

I in conlusion, I think its the Active*** which is
interfering with my For each sh in Workbook Loop. Is it
possible to not use Active***.UsedRange with my Set
command? Sorry this is so long winded and thank-you.

>-----Original Message-----
>I built a loop which I knew was going to have various
>objects and collections. So I decided to set up a
>variable called ObjCollArray which I declared as a Public
>Variable. Then when I enter the For Next Loop, I use
the
>format:
>
>For Each ObjCur in ObjCollArray(variable)
>
>Next
>
>Problem is that this loop is within another For Each Loop
>which loops through my sheets. When I do a Debug Print
on
>the *** name and the ObjCollArray.Parent.Name, they do
>not equal each other.
>
>Debug.Print sh.Name, ObjCollArray(AuditTypes).Parent.Name
>
>Secondly, as my sheets loop, ObjArray.Parent.Name stays
on
>the same ***. It never updates.
>
>I can't figure out why. I am wondering if its is because
>I have set up my array saying Active***. Here is my
code
>
>
>
>
>Public ObjCollArray As Variant
>Public Comrng As Range
>Public Hardrng As Range
>Public Errrng As Range
>Public Colrnge As Range
>Public Validrng As Range
>Public ValidErrrng As Range
>Public ObjType As String
>Public CollType As String
>Public CurObj As Object
>
>Private Sub OKButton_Click()
>Set ObjCollArray(0) = Active***.Comments
>Set ObjCollArray(1) = Active***.UsedRange
>Set ObjCollArray(2) = Active***.UsedRange
>Set ObjCollArray(3) = Active***.UsedRange
>Set ObjCollArray(4) = Active***.UsedRange
>Set ObjCollArray(5) = Active***.UsedRange
>
>Private Sub ListAuditResults()
>Dim PasteStartCell As String
>Dim sh As Work***
>Dim sh1 As Work***
>Dim AuditTypes As Integer
>Dim AuditShtName As String
>
>
>Application.Calculation = xlManual
>'Application.ScreenUpdating = False
>
>On Error Resume Next
>'Set up name of new summary ***
>Set sh1 = ActiveWorkbook.Sheets("Audit Results")
>On Error GoTo 0
>
>'If *** called "Audit Results" already exists
>'then delete it and prepare to create a new one
>
>If Not sh1 Is Nothing Then
> Application.DisplayAlerts = False
> sh1.Delete
> Application.DisplayAlerts = True
>End If
>
>
>With ActiveWorkbook
>
> 'Add a work*** for results to be pasted to
> .Worksheets.Add(After:=.Worksheets
>(.Worksheets.Count)).Name = "Audit Results"
>
> 'Set up column headings for summary report
> 'these will set up based on the numbers
> 'options chosen.
>
> PasteStartCell = Range("B2").Address
>
> 'Set first paste cell and column header for Commented
>Cells
>
> If ComChkBx = True Then
> Set Comrng = .Worksheets("Audit Results").Range
>(PasteStartCell).Offset(0, ChkbxArray(0, 1) * 2 - 2)
> Comrng.Offset(-1, 0) = "Cell Comments"
> End If
>
> 'Set first paste cell and column header for Hard
Coded
>Cells
> If HardCodedChkBx = True Then
> Set Hardrng = .Worksheets("Audit Results").Range
>(PasteStartCell).Offset(0, ChkbxArray(1, 1) * 2 - 2)
> Hardrng.Offset(-1, 0) = "Hard Coded Cells"
> End If
>
> 'Set first paste cell and column header for Cells
with
>Errors
> If ErrorChkBx = True Then
> Set Errrng = .Worksheets("Audit Results").Range
>(PasteStartCell).Offset(0, ChkbxArray(2, 1) * 2 - 2)
> Errrng.Offset(-1, 0) = "Errors"
> End If
>
> 'Set first past cell for data validation cells
> If DataValChkBx = True Then
> Set Validrng = .Worksheets("Audit Results").Range
>(PasteStartCell).Offset(0, ChkbxArray(3, 1) * 2 - 2)
> Validrng.Offset(-1, 0) = "Validation"
> End If
>
> 'Set first past cell for data validation cells
> If DataValErrChkBx = True Then
> Set ValidErrrng = .Worksheets("Audit
>Results").Range(PasteStartCell).Offset(0, ChkbxArray(4,
1)
>* 2 - 2)
> ValidErrrng.Offset(-1, 0) = "Validation Errors"
> End If
>
> 'This should equal the entire number of Audit Types
> 'in the userfor. It should be the total amount
> 'not only the ones that were chosen
>
> 'Note these numbers feed are compared to a Select
> 'Case stmt in the main module. So they cannot start
> 'with 0 (i.e. 0 to 5 must be 1 to 6)
> For Each sh In .Worksheets
> If LCase(sh.Name) <> "Audit Results" Then
> 'After its been determined that the *** is not the
> 'comments ***, code checks various conditions
> 'For AuditTypes = 0 To 5
> For Each CurObj In ObjCollArray(1)
> 'Debug.Print CurObj.Parent.Name,
CurObj.Address
> Debug.Print sh.Name, ObjCollArray
>(AuditTypes).Parent.Name
> ObjType = TypeName(CurObj)
> CollType = TypeName(ObjCollArray(1))
> Call MainAudit(2)
> Next
> 'Next
> End If
> Next
>End With
>.
>

Loading