Another Array Question (Sotra)
 

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

Another Array Question (Sotra)
 

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" wrote in message
...
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

Another Array Question (Sotra)
 

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" wrote in message
...
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" wrote in message
...
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

Another Array Question (Sotra)
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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