JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,482
- Office Version
- 365
- Platform
- Windows
I need to test a variable (MaxFinalRtg) for valid values, which are: "z", "Z", "", or a number>0.
I thought this Select Case code would work
But it fails if MaxFinalRtg is any text string (other than "z"). It looks like UCase() converts "z" into something that IsNumber() thinks is a number.
How do I get around this VBA gotcha in my Select Case code? I couldn't see how to use IsNumber().
I thought this Select Case code would work
VBA Code:
Select Case UCase(MaxFinalRtg)
Case "Z" 'Do not scale
Case "" 'Use the default maximum scale (100)
MaxFinalRtg = MaxFinalRtgDef
Case Is > 0 'Use the provided maximum scale
Case Else 'If none of the above, error
ErrMsg "Invalid Maximum Final Rating (" & MaxFinalRtg & ")" _
& vbCrLf & vbCrLf & " Must be blank, 'Z', or a number > 0"
Exit Function
End Select
But it fails if MaxFinalRtg is any text string (other than "z"). It looks like UCase() converts "z" into something that IsNumber() thinks is a number.
Code:
?"x">0 'Error
?ucase("x")>0
True
?"X">0 'Error
?ucase("ab,c")>0
True
How do I get around this VBA gotcha in my Select Case code? I couldn't see how to use IsNumber().