Select Case Failing on Array Variable

[Excel Monkey]

I have an function which returns a 1-D array with 2 rows called Y. I pass
this to a variable called X. I know what to use a Select Case structure to
test the values of the values in the array. Prior to the Select Case, I can
test the variable X and confirm there is data within it. However my Select
case fails with a "Run-Time Error "9" Subscript out of range" error. I have
also tried changing the Select Case to "Select Case X" But when I do this I
get a "Run-time error 13. Type Mismatch" error. What am I doing wrong?
'*****************************
Sub test()
Dim a as String
Dim b as String
Dim X As Variant

X = Y(a, b)

Select Case x()
Case x(0) > 0
More Code......
End Select
'****************************************
Private Function Y(aa As String, bb As String) As Variant
Dim a As Double
Dim b As Double
ReDim temparray(0 To 1)

a = Function1 which extracts numbers from string(aa,bb)
b = Function2 which extracts numbers from string(aa,bb)

temparray(0) = a
temparray(1) = b

Y = temparray

End Function
"**************************

Thanks

EM

 

[Chip Pearson]

You have two problems. First, on the Select statement, you need to use
index the array. E.g.,

Select Case X(0)
....

But you Case statements evaluate to True or False:

Case X(0) > 0 ' evaluates to TRUE or FALSE

Thus, you need to change either the Select or the Case statements.
E.g,.

Select Case True
Case X(0) > 0
' code
Case X(0) = 0
' code
'...
End Select

Or, change the Case statements

Select Case X(0)
Case 0
' code
Case 1
' code
' etc
End Select

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[OssieMac]

You have to nominate which element of the array you wish to test. You can't
leave nothing between the parenthesis.

You can do this which tests an individual element of the array:-
Select Case X(0)
Case Is > 0

 

[Excel Monkey]

Yup that did it!

Thanks.
EM

You have two problems. First, on the Select statement, you need to use
index the array. E.g.,

Select Case X(0)
....

But you Case statements evaluate to True or False:

Case X(0) > 0 ' evaluates to TRUE or FALSE

Thus, you need to change either the Select or the Case statements.
E.g,.

Select Case True
Case X(0) > 0
' code
Case X(0) = 0
' code
'...
End Select

Or, change the Case statements

Select Case X(0)
Case 0
' code
Case 1
' code
' etc
End Select

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Excel Monkey]

Chip one last question. How do you incorproate two conditions into the Case
stmt:

From this:
Select Case True
Case x(1) > 0

To this:

Select Case True
Case x(0) > 0 And Case x(1) > 0

I am getting an error stmt on the second one.

Thanks

EM

 

[OssieMac]

Select Case True
Case x(0) > 0 And x(1) > 0