Reference to Item in Collection

I want to cycle through a collection and add members on the basis of whether
they exist. For example:

Function Test(Optional M1, Optional M2, Optional M3)
Dim Group as New Collection

With Group
If Not IsMissing(M1) then .Add M1
If Not IsMissing(M1) then .Add M1
If Not IsMissing(M1) then .Add M1
end with
.. . .
End Function

Is it possible to build a reference such that I could use a loop:

For i=1 to 10
If Not IsMissing("M" & i) then Group.Add "M" & i
next i

Is there a function that would tell VBA to recognize the string as a
variable (or object, etc.)? This is quite simple to do in SAS.

Thanks,

Chad

Chad,

You could declare the argument to Test as a ParamArray. E.g.,

Sub Test(ParamArray M() As Variant)
Dim Ndx As Long
For Ndx = LBound(M) To UBound(M)
If IsMissing(M(Ndx)) = True Then
Debug.Print "missing " & Ndx
Else
Debug.Print M(Ndx)
End If
Next Ndx
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Chad" <[email protected]> wrote in message
news:[email protected]...
>I want to cycle through a collection and add members on the
>basis of whether
> they exist. For example:
>
> Function Test(Optional M1, Optional M2, Optional M3)
> Dim Group as New Collection
>
> With Group
> If Not IsMissing(M1) then .Add M1
> If Not IsMissing(M1) then .Add M1
> If Not IsMissing(M1) then .Add M1
> end with
> . . .
> End Function
>
> Is it possible to build a reference such that I could use a
> loop:
>
> For i=1 to 10
> If Not IsMissing("M" & i) then Group.Add "M" & i
> next i
>
> Is there a function that would tell VBA to recognize the string
> as a
> variable (or object, etc.)? This is quite simple to do in SAS.
>
> Thanks,
>
> Chad
>
>
>

Thanks. I think this is what I need.

Chad

"Chip Pearson" wrote:

> Chad,
>
> You could declare the argument to Test as a ParamArray. E.g.,
>
> Sub Test(ParamArray M() As Variant)
> Dim Ndx As Long
> For Ndx = LBound(M) To UBound(M)
> If IsMissing(M(Ndx)) = True Then
> Debug.Print "missing " & Ndx
> Else
> Debug.Print M(Ndx)
> End If
> Next Ndx
> End Sub
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Chad" <[email protected]> wrote in message
> news:[email protected]...
> >I want to cycle through a collection and add members on the
> >basis of whether
> > they exist. For example:
> >
> > Function Test(Optional M1, Optional M2, Optional M3)
> > Dim Group as New Collection
> >
> > With Group
> > If Not IsMissing(M1) then .Add M1
> > If Not IsMissing(M1) then .Add M1
> > If Not IsMissing(M1) then .Add M1
> > end with
> > . . .
> > End Function
> >
> > Is it possible to build a reference such that I could use a
> > loop:
> >
> > For i=1 to 10
> > If Not IsMissing("M" & i) then Group.Add "M" & i
> > next i
> >
> > Is there a function that would tell VBA to recognize the string
> > as a
> > variable (or object, etc.)? This is quite simple to do in SAS.
> >
> > Thanks,
> >
> > Chad
> >
> >
> >
>
>
>