Problems Defining Object Variable in For Each Loop

[ExcelMonkey]

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 sheet 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 sheet. It never updates.

I can't figure out why. I am wondering if its is because
I have set up my array saying ActiveSheet. 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) = ActiveSheet.Comments
Set ObjCollArray(1) = ActiveSheet.UsedRange
Set ObjCollArray(2) = ActiveSheet.UsedRange
Set ObjCollArray(3) = ActiveSheet.UsedRange
Set ObjCollArray(4) = ActiveSheet.UsedRange
Set ObjCollArray(5) = ActiveSheet.UsedRange

Private Sub ListAuditResults()
Dim PasteStartCell As String
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim AuditTypes As Integer
Dim AuditShtName As String


Application.Calculation = xlManual
'Application.ScreenUpdating = False

On Error Resume Next
'Set up name of new summary sheet
Set sh1 = ActiveWorkbook.Sheets("Audit Results")
On Error GoTo 0

'If Sheet 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 worksheet 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 sheet is not the
'comments sheet, 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

 

[Bob Phillips]

I don't understand why you would expect the parent of a VBA varaibale to be
a worksheet.

Could I suggest that you strip bits out of this code until you get down to
the essence of the problem and what you wan t to do, rather than presenting
so much code which makes it difficult for us to replicate.

As an aside, this won't work

If LCase(sh.Name) <> "Audit Results" Then

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[ExcelMonkey]

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 Worksheet
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) = ActiveSheet.Comments
Set ObjCollArray(1) = ActiveSheet.UsedRange
Set ObjCollArray(2) = ActiveSheet.UsedRange
Set ObjCollArray(3) = ActiveSheet.UsedRange
Set ObjCollArray(4) = ActiveSheet.UsedRange
Set ObjCollArray(5) = ActiveSheet.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 sheet 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 sheet name. However, ?
CurObj.Parent.Name returns the sheet name that my cursor
is actively in. This is because I set up my inital array
as

Set ObjCollArray(1) = ActiveSheet.UsedRange

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

 

[Tushar Mehta]

When you set something to the ActiveSheet.{whatever} it sets the
variable to whatever is the activesheet at the time of the set
statement. From your comments (and a brief look at the code) it
appears you believe it should create a dynamic link that evaluates
'activesheet' when you refer to the object. Unfortunately, not so.

Also, you fix one potential bug. Without a 'option compare text'
clause, Lcase(sheet.name)<>"Audit..." will always be false!

Finally, you can clean up the code some. If you intend deleting the
Audit Results worksheet if it already exists, why go through hoops
checking if it exists? Also, deleting an existing worksheet will pop
up an alert; you may want to suppress it.

Dim x As Worksheet
Application.DisplayAlerts = False
With ActiveWorkbook
On Error Resume Next
.Worksheets("Audit Results").Delete
On Error GoTo 0
Set x = .Worksheets.Add()
x.Name = "Audit Results"
End With
Application.DisplayAlerts = True

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[ExcelMonkey]

I think my problem is that my array says:

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

I then reference these within a For Each sheet Loop which
loops through specific sheets. I am not sure how to set
this up differently do that it works.

 

[Tushar Mehta]

Inside the loop whereever you want to refer to the comments (or
usedrange) for the sheet associated with the loop, use sh.Comments (or
sh.UsedRange). Now, you can safely throw away ObjCollArray.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I think my problem is that my array says:

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

I then reference these within a For Each sheet Loop which
loops through specific sheets. I am not sure how to set
this up differently do that it works.

{snip}

 

[ExcelMonkey]

Well you see, this is how I orginally built it. But I am
running a diagnostic on a spreadsheet which reviews cells
commenets etc. I have a user form which where I click off
which type of diagnostic I want to run. The different
types of diagnostics (7 -10) are on comments or cells. I
was originally using your methodology but decided that
insted of using mulitple loops within my sheet loop

For each sh in Workbook
'Loop1
For each comment in sh.comments
Code
Next
'Loop2
For each cell in sh.Worksheet.UsedRange
code
Next

More loops etc
Next

I would only use 1 Loop. But in doing that, I need to
know ahead of time which collection I need prior to going
into the loop. Therefore, I passed the collection type to
the array at the beginning of the routine based on what
checkboxes I checked off.

For each sh in Workbook
For each CurObj in ObjCollArray(variable)
Code
Next
Next

As I am only using 1 For Each loop within the sheet loop,
I can only do what you are suggesting if I know ahead of
time what time of Object and Collection I need, and I also
need to somehow pass these to the two variables I created
CurObj and ObjCollArray.

Am I missing something here?

Thanks for your patience

 

[Tushar Mehta]

Not sure why you want to minimize the number of For statements. If I
was in your shoes, my code would be structured along the lines of:

Option Explicit

Public Type UserDiagnosticChoices
doComments As Boolean
doCells As Boolean
'...
End Type
Sub CheckCOmments(aWS As Worksheet)
'...
End Sub
Sub checkCells(aWS As Worksheet)
'...
End Sub
Sub Main()
Dim UserChoices As UserDiagnosticChoices
UserForm1.Manager UserChoices
Dim aWS As Worksheet
For Each aWS In ActiveWorkbook.Worksheets
If UserChoices.doComments Then CheckCOmments aWS
If UserChoices.doCells Then checkCells aWS
'...
Next aWS
End Sub


and Userform1 would contain:
Option Explicit

Public Sub Manager(ByRef UserChoices As UserDiagnosticChoices)
UserForm1.Show
'if user clicked ok set doComments, doCells, etc.
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions