Select Case statement, question about inclusive

[hellind]

Function Score2Grade(dScore As Double) As Double

MsgBox dScore
    Select Case dScore
        Case Is < 283.2
            Score2Grade = 5
        Case 283.21 To 307.8
            Score2Grade = 4
        Case 307.81 To 332.2
            Score2Grade = 3
        Case 332.21 To 380
            Score2Grade = 2
        Case Is > 380
            Score2Grade = 1
        Case Else
            Score2Grade = Null 'Unexpected Case, all to throw error message
    End Select
    
End Function

MsgBox Return 332.2

Does anyone know why it gets caught at Case Else?

I was expecting it go return Score2Grade = 3 since it's inclusive.

 

[hellind]

'dScore = Round(dScore, 2)
If (dScore) > 332.2 Then
    MsgBox (dScore)
End If

MsgBox (dScore) return 332.2

If (dScore) > 332.2 Return True

Round(dScore, 2) solves the problem

I think dScore is some number between 332.2 and 332.21.

But why wounldn't it show in messagebox?

 

[LPurvis]

Hi.

Not to be funny... but you have noticed that your messagebox is at the start of your procedure? And that it simply returns the parameter which you pass to the function?

The function itself will work - but the messagebox isn't testing that at all.
Nothing's been interpreted yet and the parameter will always remain at that value.

You'd need to move it to the end and return the function value.
MsgBox Score2Grade

However I'd suggest removing it altogether from the function and testing your function alone.

MsgBox Score2Grade(332.2)

Cheers.

 

[hellind]

Yes the msgbox is at the start. Yes I am msgboxing the input dScore.

The input dScore is 332.2.

My question is it's not evaluating
Case 307.81 To 332.2

 

[vbaInet]

Rather an odd message box there.

Try casting your values to double like this:

Case CDbl(307.81) to CDbl(332.2)

 

[hellind]

Wouldn't the function declaration already cast it as double?

Function Score2Grade(dScore As Double) As Double

Anyway, put a dScore = cDBL(dScore) in the first line, and it doesn't work.

It only works if I Round(dScore,2).

 

[vbaInet]

It's casting your input, not what it's checking against. Try changing your data type to float.

 

[vbaInet]

It's casting your input, not what it's checking against. Try changing your data type to float.

Oops, thinking of Java there. Try adding a zero to 332.2, that is 332.20 with the double cast.

 

[JANR]

Does anyone know why it gets caught at Case Else?

I was expecting it go return Score2Grade = 3 since it's inclusive.

The function itself will work - but the messagebox isn't testing that at all.
Nothing's been interpreted yet and the parameter will always remain at that value.

What Leigh Purvis is trying to tell you is to remove

MsgBox dScore

From your function as it dosen't do enything exept return the parameter back to you.

Function Score2Grade(dScore As Double) As Double
    Select Case dScore
        Case Is < 283.2
            Score2Grade = 5
        Case 283.21 To 307.8
            Score2Grade = 4
        Case 307.81 To 332.2
            Score2Grade = 3
        Case 332.21 To 380
            Score2Grade = 2
        Case Is > 380
            Score2Grade = 1
        Case Else
            Score2Grade = Null 'Unexpected Case, all to throw error message
    End Select
    
End Function

To test it rund this sub:

Public Sub Test()
MsgBox "The score is " & Score2Grade(332.2)
End Sub

Hope this helps

JR

 

[raskew]

Hi -

Try playing with this:

Public Function Score2Grade(dScore As Double) As Double

   Score2Grade = Switch(dScore < 238.2, 5, dScore <= 307.81, 4, dScore <= 332.2, 3, dScore <= 380, 2, dScore > 380, 1, True, 0)

End Function

To call from the debug (immediate) window:

? score2grade(332.3)
 2

HTH - Bob

 

[gemma-the-husky]

i thought so - if you happen to get a value between 332.2 and 332.21 (it can happen - doubles are strange creatures you get a problem. but you dont have to specify ranges as a case will evaluate the first case test that satisfies only

so

Function Score2Grade(dScore As Double) As Double
    Select Case dScore
        Case < 283.2
            Score2Grade = 5
        Case <307.8
            Score2Grade = 4
        Case <332.2
            Score2Grade = 3
        Case <380
            Score2Grade = 2
        Case else:  
            Score2Grade = 1
    End Select

and a value of say 300 will return 4

note that if dscore is null, (you pass a null to the function) the function will fail with a rte - as the argument of a double type wont accept null.

 

[raskew]

Hi David -

Did you take a look at the Switch() function in the previous post, e.g.

? score2grade(332.2)
 3 
? score2grade(332.205)
 2 
? score2grade(332.21) 
 2

I agree with your assesment of a Null value. I fought with that for more than a few minutes before concluding that it wasn't doable.

Best wishes - Bob

 

[Steve R.]

Another alternative approach. This is a two step method. I prefer the use use of the Case Statement for complex situations to the use of the IF Statement. However, for me, I find that the IF Statement is a good "auxiliary" that can be used to identify the Case Statement to be executed by giving it a specific integer value. So, choose wisely.

dim intCase as integer
intCase=0
if numScore <283.2 then intCase =1
if numScore => than 283.2 AND numScore<307.8 then intCase = 2
if numScore => than 307.8 AND numScore<232.2 then intCase = 3
if numScore => than 332.2 AND numScore<380.0 then intCase = 4
if numScore => than 380.0 then intCase = 5

Select Case intCase
        Case 0
            ERROR CONDITION
        Case 1
            Do Whatever, allows a lot of code to be placed here.
        Case 2
            Do Whatever
        Case 2
            Do Whatever
        Case 3  
            Do whatever
        Case 4
            Do Whatever
        Case 5
            Do Whatever
        Case Else
            ERROR CONDITION        
    End Select

 

[gemma-the-husky]

Hi David -

Did you take a look at the Switch() function in the previous post, e.g.

? score2grade(332.2)
 3 
? score2grade(332.205)
 2 
? score2grade(332.21) 
 2

I agree with your assesment of a Null value. I fought with that for more than a few minutes before concluding that it wasn't doable.

Best wishes - Bob

bob - was that directed at me?

if its null you either have to cast the value to a number

nz(myvar,0)

or

make the argument a variant type (which accepts a null)

myfunc(invar) 'untyped

 

[LPurvis]

FWIW, though I don't personally agree with the decision (method), I'd understood the Null assignment to be a deliberate action to raise an error on the Else clause.
(Based on the comment "Unexpected Case, all to throw error message").

Whereas actually raising an error would generally be preferable.

 

[hellind]

i thought so - if you happen to get a value between 332.2 and 332.21 (it can happen - doubles are strange creatures you get a problem. but you dont have to specify ranges as a case will evaluate the first case test that satisfies only

so

Function Score2Grade(dScore As Double) As Double
    Select Case dScore
        Case < 283.2
            Score2Grade = 5
        Case <307.8
            Score2Grade = 4
        Case <332.2
            Score2Grade = 3
        Case <380
            Score2Grade = 2
        Case else:  
            Score2Grade = 1
    End Select

and a value of say 300 will return 4

note that if dscore is null, (you pass a null to the function) the function will fail with a rte - as the argument of a double type wont accept null.

You understood the problem. The value dScore seems somewhere in between 332.2 and 332.21. But why doesn't msgbox returns the 3rd decimal?

Your suggested solution will work except for the fact I want to catch else as an Error.

 

[vbaInet]

From your original code, the range covers all posibilities so I don't see why you need the else statement. You should only restrict the function's paramter to numeric values before it's called. Here's a workaround for the message box.

Function Score2Grade(dScore As Double) As Integer
    Dim lenScoreDP As Integer, instrPos As Integer
    Select Case dScore
        Case Is < 283.2
            Score2Grade = 5
        Case 283.21 To 307.8
            Score2Grade = 4
        Case 307.81 To 332.2
            Score2Grade = 3
        Case 332.21 To 380
            Score2Grade = 2
        Case Is > 380
            Score2Grade = 1
    End Select
    
    instrPos = InStr(1, CStr(dScore), ".")
    
    If instrPos = 0 And dScore > 0 Then
        MsgBox CStr(dScore) & ".0"
    ElseIf instrPos = 1 And dScore > 0 Then
        lenScoreDP = Len(Right(CStr(dScore), Len(CStr(dScore)) - instrPos))
        MsgBox CStr(dScore) & IIf(lenScoreDP = 1, "0", "")
    Else
        MsgBox dScore
    End If
End Function

Notice the function is returning an integer not a double. A double isn't needed. The else statement in the msgbox bit is there for negative values.

Your thoughts?

 

[gemma-the-husky]

look its this that is the problem

Function Score2Grade(dScore As Double) As Double

you cannot pass a null value INTO this, as you get a run time errror in the call mechanism - not one you can trap inside the routine

if you have a null then either you need to convert the null first

ie rating = Function Score2Grade(nz(anyvalue,0))

OR

have the parameter as a variant in the function declaration

Function Score2Grade(dScore As VARIANT) As Double

and now you can say

if isnull(dscore) then etc