Why does VBA think ucase("x") is a number?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. 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
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().
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You could test it with If IsNumeric then do separate tests for numbers and other.
 
Upvote 0
You could test it with If IsNumeric then do separate tests for numbers and other.
Yeah, that's what I figured. I was just hoping that there was a way to do it in the Select Case. (sigh) Thanks
 
Upvote 0
... I was just hoping that there was a way to do it in the Select Case...
Re-posted with corrections:
VBA Code:
Select Case MaxFinalRtg
    Case "z", "Z", 0.001 To 1000    'Do not scale; Use the provided maximum scale
    Case ""                         'Use the default maximum scale (100)
        MaxFinalRtg = MaxFinalRtgDef
    Case Else                       'If none of the above, error
        ErrMsg ...
        Exit Function
End Select
 
Upvote 0
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.
It is only working for "z" because the "z" Case statement is "before" the > 0 test.
I am only guessing but i think by including a function such as UCase it is doing an implicit conversion and converting
UCase("x")>0 to UCase("x")>"0"

I'm not sure you will like the alternative way of using Select Case but in case you haven't seen it before it looks like this:
Rich (BB code):
Sub AlternativeSelectCase()

Dim MaxFinalRtg As Variant
Dim MaxFinalRtgDef As Double
Dim ErrMsg As String

MaxFinalRtg = "x"

Select Case True
    Case UCase(MaxFinalRtg) = "Z"       'Do not scale
        Debug.Print "Z"
    Case MaxFinalRtg = ""               'Use the default maximum scale (100)
        MaxFinalRtg = MaxFinalRtgDef
        Debug.Print "Blank"
    Case IsNumeric(MaxFinalRtg) And MaxFinalRtg > 0                'Use the provided maximum scale
        Debug.Print ">0"
    Case Else                           'If none of the above, error
        ErrMsg = "Invalid Maximum Final Rating (" & MaxFinalRtg & ")" _
            & vbCrLf & vbCrLf & " Must be blank, 'Z', or a number > 0"
        MsgBox ErrMsg
        Exit Sub
End Select
End Sub
 
Upvote 0
I am only guessing but i think by including a function such as UCase it is doing an implicit conversion and converting
UCase("x")>0 to UCase("x")>"0"
I don't think it's quite that - otherwise UCase$("x")>0 would also return true, but it actually raises an error. UCase returns a Variant, which contains a string and the rules for variants with relational operators are a little odd. Essentially (as I understand it from the language spec) the effective type of the operator is determined by the type of the value on the right hand side - so in this case it's Integer. The operands are therefore let coerced to the effective type before the comparison is made. So "04">3 will return true since "04" is coerced to 4, which is greater than 3 (whereas "04">"3" would return false as it's a string comparison).
Obviously "x" cannot be coerced to an integer so you would expect a type mismatch error with the ucase("x")>0 as you would get if you attempted "X">0, but there is an exception with variants that states:
There is an exception to the rules in the preceding table when both operands have a declared type of Variant, with one operand originally having a value type of String, and the other operand originally having a numeric value type. In this case, the numeric operand is considered to be less than (and not equal to) the String operand, regardless of their values.
Not a clue why that should be though!
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top