Reference to Item in Collection

[Guest]

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

 

[Chip Pearson]

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

 

[Guest]

Thanks. I think this is what I need.

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