Passing variable to VarType of other Variable

At this risk I reposting, I need to clarify a question I
posted recently. I am trying to pass variables to the
variable types of two variables.

I have a For Each loop inside of a For NExt Loop. I need
to have A and B vary for the For Each Loop. That is I
only want 1 For Each loop.

Lets say that I know that for each state of X (1 to 3) I
will want the following:

1) For Each cell in Worksheet
2) For Each comment in Comments
3) For Each sheet in workbook

I think what I need to do is define A as an Object and
define B as a Collection. I then need to load these into
a public array

Public A as Object
Public B as Collection

Array(0,0)="Range"
Array(0,1)="Comment"
Array(0,2)="Worksheet"

Array (1,0) = "Worksheet"
Array (1,1) = "Comments"
Array (1,2) = "Workbook"


Sub Procedure1()
For X = 1 to 3
'Unload public array and pass to A and B as Types
'How do you pass these to A and B??????
For Each A in B
Call Procedure2
Next
Next

thanks hope that is clear

Sub CC()
Dim A As Object
Dim B As Object
Dim vArray(0 To 2) As Variant


Set vArray(0) = ActiveSheet.UsedRange
Set vArray(1) = ActiveSheet.Comments
Set vArray(2) = ThisWorkbook.Worksheets


For x = 0 To 2
'Unload public array and pass to A and B as Types
'How do you pass these to A and B??????
For Each A In vArray(x)
' Call Procedure2
Debug.Print x, TypeName(A), TypeName(vArray(x))
Next
Next

End Sub

--
Regards,
Tom Ogilvy

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> At this risk I reposting, I need to clarify a question I
> posted recently. I am trying to pass variables to the
> variable types of two variables.
>
> I have a For Each loop inside of a For NExt Loop. I need
> to have A and B vary for the For Each Loop. That is I
> only want 1 For Each loop.
>
> Lets say that I know that for each state of X (1 to 3) I
> will want the following:
>
> 1) For Each cell in Worksheet
> 2) For Each comment in Comments
> 3) For Each sheet in workbook
>
> I think what I need to do is define A as an Object and
> define B as a Collection. I then need to load these into
> a public array
>
> Public A as Object
> Public B as Collection
>
> Array(0,0)="Range"
> Array(0,1)="Comment"
> Array(0,2)="Worksheet"
>
> Array (1,0) = "Worksheet"
> Array (1,1) = "Comments"
> Array (1,2) = "Workbook"
>
>
> Sub Procedure1()
> For X = 1 to 3
> 'Unload public array and pass to A and B as Types
> 'How do you pass these to A and B??????
> For Each A in B
> Call Procedure2
> Next
> Next
>
> thanks hope that is clear

So Tom, it looks as though you loaded the B variable
(collection) into the array. How does the code know what
type of variable A is? I just assumed that I would have
to define that as well in the array?


Thanks

>-----Original Message-----
>Sub CC()
>Dim A As Object
>Dim B As Object
>Dim vArray(0 To 2) As Variant
>
>
>Set vArray(0) = ActiveSheet.UsedRange
>Set vArray(1) = ActiveSheet.Comments
>Set vArray(2) = ThisWorkbook.Worksheets
>
>
>For x = 0 To 2
> 'Unload public array and pass to A and B as Types
> 'How do you pass these to A and B??????
> For Each A In vArray(x)
> ' Call Procedure2
> Debug.Print x, TypeName(A), TypeName(vArray(x))
> Next
>Next
>
>End Sub
>
>--
>Regards,
>Tom Ogilvy
>
>"ExcelMonkey" <[email protected]> wrote
in message
>news:[email protected]...
>> At this risk I reposting, I need to clarify a question I
>> posted recently. I am trying to pass variables to the
>> variable types of two variables.
>>
>> I have a For Each loop inside of a For NExt Loop. I
need
>> to have A and B vary for the For Each Loop. That is I
>> only want 1 For Each loop.
>>
>> Lets say that I know that for each state of X (1 to 3) I
>> will want the following:
>>
>> 1) For Each cell in Worksheet
>> 2) For Each comment in Comments
>> 3) For Each sheet in workbook
>>
>> I think what I need to do is define A as an Object and
>> define B as a Collection. I then need to load these
into
>> a public array
>>
>> Public A as Object
>> Public B as Collection
>>
>> Array(0,0)="Range"
>> Array(0,1)="Comment"
>> Array(0,2)="Worksheet"
>>
>> Array (1,0) = "Worksheet"
>> Array (1,1) = "Comments"
>> Array (1,2) = "Workbook"
>>
>>
>> Sub Procedure1()
>> For X = 1 to 3
>> 'Unload public array and pass to A and B as Types
>> 'How do you pass these to A and B??????
>> For Each A in B
>> Call Procedure2
>> Next
>> Next
>>
>> thanks hope that is clear
>
>
>.
>

I made A as object. the for . . . next loop structure takes care of
assigning it the appropriate value (item in the collection).

--
Regards,
Tom Ogilvy

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> So Tom, it looks as though you loaded the B variable
> (collection) into the array. How does the code know what
> type of variable A is? I just assumed that I would have
> to define that as well in the array?
>
>
> Thanks
>
> >-----Original Message-----
> >Sub CC()
> >Dim A As Object
> >Dim B As Object
> >Dim vArray(0 To 2) As Variant
> >
> >
> >Set vArray(0) = ActiveSheet.UsedRange
> >Set vArray(1) = ActiveSheet.Comments
> >Set vArray(2) = ThisWorkbook.Worksheets
> >
> >
> >For x = 0 To 2
> > 'Unload public array and pass to A and B as Types
> > 'How do you pass these to A and B??????
> > For Each A In vArray(x)
> > ' Call Procedure2
> > Debug.Print x, TypeName(A), TypeName(vArray(x))
> > Next
> >Next
> >
> >End Sub
> >
> >--
> >Regards,
> >Tom Ogilvy
> >
> >"ExcelMonkey" <[email protected]> wrote
> in message
> >news:[email protected]...
> >> At this risk I reposting, I need to clarify a question I
> >> posted recently. I am trying to pass variables to the
> >> variable types of two variables.
> >>
> >> I have a For Each loop inside of a For NExt Loop. I
> need
> >> to have A and B vary for the For Each Loop. That is I
> >> only want 1 For Each loop.
> >>
> >> Lets say that I know that for each state of X (1 to 3) I
> >> will want the following:
> >>
> >> 1) For Each cell in Worksheet
> >> 2) For Each comment in Comments
> >> 3) For Each sheet in workbook
> >>
> >> I think what I need to do is define A as an Object and
> >> define B as a Collection. I then need to load these
> into
> >> a public array
> >>
> >> Public A as Object
> >> Public B as Collection
> >>
> >> Array(0,0)="Range"
> >> Array(0,1)="Comment"
> >> Array(0,2)="Worksheet"
> >>
> >> Array (1,0) = "Worksheet"
> >> Array (1,1) = "Comments"
> >> Array (1,2) = "Workbook"
> >>
> >>
> >> Sub Procedure1()
> >> For X = 1 to 3
> >> 'Unload public array and pass to A and B as Types
> >> 'How do you pass these to A and B??????
> >> For Each A in B
> >> Call Procedure2
> >> Next
> >> Next
> >>
> >> thanks hope that is clear
> >
> >
> >.
> >