Case Statement in Excel

[altin]

I already have created the below VB functions in Excel which work fine, but now I'm trying to create a case statement which can call these functions from a third cell. i.e I'd like to enter "All, H Better" or "All L Better" and the correct function to be signed/called automatically. Any suggestions. Thanks.

Function AllHbetter(currentscore As Double, Goal As Double)
If currentscore >= Goal Then
AllHbetter = 4
End If
If currentscore < Goal Then
AllHbetter = 1
End If
If currentscore = nul Then
AllHbetter = 0
End If
End Function
Function AllLbetter(currentscore As Double, Goal As Double)
If currentscore <= Goal Then
AllLbetter = 4
End If
If currentscore > Goal Then
AllLbetter = 1
End If
If currentscore = nul Then
AllLbetter = 0
End If
End Function

 

[Blade Hunter]

'Method1
Function AllHbetter(currentscore As Double, Goal As Double)
If currentscore >= Goal Then AllHbetter = 4
If currentscore < Goal Then AllHbetter = 1
If currentscore = nul Then AllHbetter = 0
End Function
Function AllLbetter(currentscore As Double, Goal As Double)
If currentscore <= Goal Then AllLbetter = 4
If currentscore > Goal Then AllLbetter = 1
If currentscore = nul Then AllLbetter = 0
End Function
 
'Method 2
Function AllHbetter(currentscore As Double, Goal As Double)
Select Case currentscore
    Case Is >= Goal
        AllHbetter = 4
    Case Is < Goal
        AllHbetter = 1
    Case Else
        AllHbetter = 0
End Select
End Function
Function AllLbetter(currentscore As Double, Goal As Double)
Select Case currentscore
    Case Is <= Goal
        AllLbetter = 4
    Case Is > Goal
        AllLbetter = 1
    Case Else
        AllLbetter = 0
End Select
End Function

The reason Method one is so short is because your IF statements are single operations, If you only have one operation as the result of an if you don't need to put it on a second line and therefore don't need the end it

Just check the Syntax of the case statement, I free typed so it could be a little wonky.

 

[altin]

Thank you for your respond. I'd like to make this case statement to return the AllHbetter = 1 when currentscore is = 0 and AllHbetter = 0 when the currentscore = nul

Is a way how to do this ? Thanks again.

 

[Blade Hunter]

Thank you for your respond. I'd like to make this case statement to return the AllHbetter = 1 when currentscore is = 0 and AllHbetter = 0 when the currentscore = nul

Is a way how to do this ? Thanks again.

If there are only two possible outcomes (1 or 0) I don't understand why you want a case statement, use an if.