Another Array Question (Sotra)

[David F. Schrader]

I have a question about working with arrays. It's a rather an
obtuse kind of question, dealing with manipulating them while
passing them into "functions" and "subroutines" to be evaluated
with the result either stored in another cell or the color of
a cell changed to some color.

An example of the type of things I might be passing as array
items would be:

Last Name : Single Item - Character (20)
First Name : Multi-Item - Character (15)
Buildings : Multi-Item - Character (04)
Room : Multi-Item - Character (04)
StartTime : Single Item - Character (
EndTime : Single Item - Character (
AccessNumber : Multi-Item - Character (10)

(Enough for this question)

The call to the script function would be:

If Registered( _
"Smith" _
, ( "Joseph" , "Joe" , "Jo" ) _
, ( "MCH" , "BEL" , "DHA" , "STL" ) _
, ( ("104" . "04") , ("22" , "32") , ... ) _
, ( "03/10/05|09:00") _
, ( "03/10/05|11:45") _
, ( "1022-34-54" ) _
) then etc...
My problem is with the function header which I've written as:

Function Registered ( _
LName As String _
, ParamArray FName As Variant _
, ParamArray Building As Variant _ <--- error location
, ParamArray Room As Variant _
, StartTime As String _
, EndTime As String _
, ParamArray AccessNumber As Variant _
) As Boolean
Body of Function follows
The problem is that no matter what I try it appears to refuse
to allow more than a single variant array to be passed to the
function. The error message I get is "Expected )" and the error
is posted on the comma at the line that starts the line for the
"Building". Moving the "StartTime" and "EndTime" lines up after
the "LName" line simply moves the error down two lines.

Any ideas? Is this a futile effort and impossible (before I try
and spend a lot more time trying to make it work)?

David

P.S. Sorry for the length but better a little longer with clarity
than too short with confusion. dfs

[Bob Phillips]

David,

You can only have one ParamArray, and it must be the last argument. But the
paramarray can be an array of arrays. As an example

Function Registered(LName As String, _
StartTime As String, _
EndTime As String, _
ParamArray FName()) As Boolean
Dim i As Long

For i = LBound(FName) To UBound(FName)
Select Case i
Case 0: Debug.Print FName(0)(2)
Case 1: Debug.Print FName(1)(1)
Case 2: Debug.Print FName(2)(0)
End Select
Next i

End Function

Sub qaz()
Dim ary1, ary2, ary3

ary1 = Array(1, 2, 3)
ary2 = Array("a", "b", "c")
ary3 = Array("1a", "2b", "3c")

Debug.Print Registered("A", "1", "2", ary1, ary2, ary3)
End Sub


--

HTH

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


"David F. Schrader" <[email protected]> wrote in message
news:[email protected]...
> I have a question about working with arrays. It's a rather an
> obtuse kind of question, dealing with manipulating them while
> passing them into "functions" and "subroutines" to be evaluated
> with the result either stored in another cell or the color of
> a cell changed to some color.
>
> An example of the type of things I might be passing as array
> items would be:
>
> Last Name : Single Item - Character (20)
> First Name : Multi-Item - Character (15)
> Buildings : Multi-Item - Character (04)
> Room : Multi-Item - Character (04)
> StartTime : Single Item - Character (
> EndTime : Single Item - Character (
> AccessNumber : Multi-Item - Character (10)
>
> (Enough for this question)
>
> The call to the script function would be:
>
> If Registered( _
> "Smith" _
> , ( "Joseph" , "Joe" , "Jo" ) _
> , ( "MCH" , "BEL" , "DHA" , "STL" ) _
> , ( ("104" . "04") , ("22" , "32") , ... ) _
> , ( "03/10/05|09:00") _
> , ( "03/10/05|11:45") _
> , ( "1022-34-54" ) _
> ) then etc...
> My problem is with the function header which I've written as:
>
> Function Registered ( _
> LName As String _
> , ParamArray FName As Variant _
> , ParamArray Building As Variant _ <--- error location
> , ParamArray Room As Variant _
> , StartTime As String _
> , EndTime As String _
> , ParamArray AccessNumber As Variant _
> ) As Boolean
> Body of Function follows
> The problem is that no matter what I try it appears to refuse
> to allow more than a single variant array to be passed to the
> function. The error message I get is "Expected )" and the error
> is posted on the comma at the line that starts the line for the
> "Building". Moving the "StartTime" and "EndTime" lines up after
> the "LName" line simply moves the error down two lines.
>
> Any ideas? Is this a futile effort and impossible (before I try
> and spend a lot more time trying to make it work)?
>
> David
>
> P.S. Sorry for the length but better a little longer with clarity
> than too short with confusion. dfs
>
>

[David F. Schrader]

Bob,

That's what I was afraid of. I appreciate your reply
AND the example was gravy on top. Could you be
even nicer, perhaps, and provide an example using
"strings" rather than "boolean"s?

Many, many thanks.

David

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> David,
>
> You can only have one ParamArray, and it must be the last argument. But
the
> paramarray can be an array of arrays. As an example
>
> Function Registered(LName As String, _
> StartTime As String, _
> EndTime As String, _
> ParamArray FName()) As Boolean
> Dim i As Long
>
> For i = LBound(FName) To UBound(FName)
> Select Case i
> Case 0: Debug.Print FName(0)(2)
> Case 1: Debug.Print FName(1)(1)
> Case 2: Debug.Print FName(2)(0)
> End Select
> Next i
>
> End Function
>
> Sub qaz()
> Dim ary1, ary2, ary3
>
> ary1 = Array(1, 2, 3)
> ary2 = Array("a", "b", "c")
> ary3 = Array("1a", "2b", "3c")
>
> Debug.Print Registered("A", "1", "2", ary1, ary2, ary3)
> End Sub
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "David F. Schrader" <[email protected]> wrote in message
> news:[email protected]...
> > I have a question about working with arrays. It's a rather an
> > obtuse kind of question, dealing with manipulating them while
> > passing them into "functions" and "subroutines" to be evaluated
> > with the result either stored in another cell or the color of
> > a cell changed to some color.
> >
> > An example of the type of things I might be passing as array
> > items would be:
> >
> > Last Name : Single Item - Character (20)
> > First Name : Multi-Item - Character (15)
> > Buildings : Multi-Item - Character (04)
> > Room : Multi-Item - Character (04)
> > StartTime : Single Item - Character (
> > EndTime : Single Item - Character (
> > AccessNumber : Multi-Item - Character (10)
> >
> > (Enough for this question)
> >
> > The call to the script function would be:
> >
> > If Registered( _
> > "Smith" _
> > , ( "Joseph" , "Joe" , "Jo" ) _
> > , ( "MCH" , "BEL" , "DHA" , "STL" ) _
> > , ( ("104" . "04") , ("22" , "32") , ... ) _
> > , ( "03/10/05|09:00") _
> > , ( "03/10/05|11:45") _
> > , ( "1022-34-54" ) _
> > ) then etc...
> > My problem is with the function header which I've written as:
> >
> > Function Registered ( _
> > LName As String _
> > , ParamArray FName As Variant _
> > , ParamArray Building As Variant _ <--- error location
> > , ParamArray Room As Variant _
> > , StartTime As String _
> > , EndTime As String _
> > , ParamArray AccessNumber As Variant _
> > ) As Boolean
> > Body of Function follows
> > The problem is that no matter what I try it appears to refuse
> > to allow more than a single variant array to be passed to the
> > function. The error message I get is "Expected )" and the error
> > is posted on the comma at the line that starts the line for the
> > "Building". Moving the "StartTime" and "EndTime" lines up after
> > the "LName" line simply moves the error down two lines.
> >
> > Any ideas? Is this a futile effort and impossible (before I try
> > and spend a lot more time trying to make it work)?
> >
> > David
> >
> > P.S. Sorry for the length but better a little longer with clarity
> > than too short with confusion. dfs
> >
> >
>
>

[Bob Phillips]

David,

The arrays were one long and two strings. The Boolean was just showing what
the Function returns. Although I didn't add the code, you could return True
or False depending upon how the Function does its stuff.

--

HTH

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


"David F. Schrader" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> That's what I was afraid of. I appreciate your reply
> AND the example was gravy on top. Could you be
> even nicer, perhaps, and provide an example using
> "strings" rather than "boolean"s?
>
> Many, many thanks.
>
> David
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > David,
> >
> > You can only have one ParamArray, and it must be the last argument. But
> the
> > paramarray can be an array of arrays. As an example
> >
> > Function Registered(LName As String, _
> > StartTime As String, _
> > EndTime As String, _
> > ParamArray FName()) As Boolean
> > Dim i As Long
> >
> > For i = LBound(FName) To UBound(FName)
> > Select Case i
> > Case 0: Debug.Print FName(0)(2)
> > Case 1: Debug.Print FName(1)(1)
> > Case 2: Debug.Print FName(2)(0)
> > End Select
> > Next i
> >
> > End Function
> >
> > Sub qaz()
> > Dim ary1, ary2, ary3
> >
> > ary1 = Array(1, 2, 3)
> > ary2 = Array("a", "b", "c")
> > ary3 = Array("1a", "2b", "3c")
> >
> > Debug.Print Registered("A", "1", "2", ary1, ary2, ary3)
> > End Sub
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "David F. Schrader" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a question about working with arrays. It's a rather an
> > > obtuse kind of question, dealing with manipulating them while
> > > passing them into "functions" and "subroutines" to be evaluated
> > > with the result either stored in another cell or the color of
> > > a cell changed to some color.
> > >
> > > An example of the type of things I might be passing as array
> > > items would be:
> > >
> > > Last Name : Single Item - Character (20)
> > > First Name : Multi-Item - Character (15)
> > > Buildings : Multi-Item - Character (04)
> > > Room : Multi-Item - Character (04)
> > > StartTime : Single Item - Character (
> > > EndTime : Single Item - Character (
> > > AccessNumber : Multi-Item - Character (10)
> > >
> > > (Enough for this question)
> > >
> > > The call to the script function would be:
> > >
> > > If Registered( _
> > > "Smith" _
> > > , ( "Joseph" , "Joe" , "Jo" ) _
> > > , ( "MCH" , "BEL" , "DHA" , "STL" ) _
> > > , ( ("104" . "04") , ("22" , "32") , ... ) _
> > > , ( "03/10/05|09:00") _
> > > , ( "03/10/05|11:45") _
> > > , ( "1022-34-54" ) _
> > > ) then etc...
> > > My problem is with the function header which I've written as:
> > >
> > > Function Registered ( _
> > > LName As String _
> > > , ParamArray FName As Variant _
> > > , ParamArray Building As Variant _ <--- error location
> > > , ParamArray Room As Variant _
> > > , StartTime As String _
> > > , EndTime As String _
> > > , ParamArray AccessNumber As Variant _
> > > ) As Boolean
> > > Body of Function follows
> > > The problem is that no matter what I try it appears to refuse
> > > to allow more than a single variant array to be passed to the
> > > function. The error message I get is "Expected )" and the error
> > > is posted on the comma at the line that starts the line for the
> > > "Building". Moving the "StartTime" and "EndTime" lines up after
> > > the "LName" line simply moves the error down two lines.
> > >
> > > Any ideas? Is this a futile effort and impossible (before I try
> > > and spend a lot more time trying to make it work)?
> > >
> > > David
> > >
> > > P.S. Sorry for the length but better a little longer with clarity
> > > than too short with confusion. dfs
> > >
> > >
> >
> >
>
>