Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems Defining Object Variable in For Each Loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems Defining Object Variable in For Each Loop
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" wrote in 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems Defining Object Variable in For Each Loop
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. -----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 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems Defining Object Variable in For Each Loop
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. -----Original Message----- 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 In article , says... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems Defining Object Variable in For Each Loop
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 In article , says... 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} |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems Defining Object Variable in For Each Loop
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 -----Original Message----- 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 In article , says... 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} . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems Defining Object Variable in For Each Loop
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 In article , says... 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 -----Original Message----- 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 In article , says... 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} . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining Range of For Each Loop for Cells and Worksheets | Excel Programming | |||
Defining a variable within a sub... | Excel Programming | |||
Defining a variable within a sub... | Excel Programming | |||
Defining a discontiguous Range object | Excel Programming | |||
Defining a Variable | Excel Programming |