CODEWITHSUNDEEP
vbaexcel
Shane M Hewitt
Shane M Hewitt

Reputation: 305

Excel VBA: Case query

I have this code, which sorts an input number into a convenient range:

Public Sub TestCase2()

Dim Number As Long
Dim Message As String

Number = InputBox("Enter a number:", "Number entry")
Message = "Number is "

Select Case Number
Case Is < -10
    MsgBox (Message & "less than -10")
Case Is < 0
    MsgBox (Message & "equal to or greater than -10 but less than zero")
Case Is = 0
    MsgBox (Message & "equal to zero")
Case Is < 10
    MsgBox (Message & "greater than zero but less than 10")
Case Else
    MsgBox (Message & "greater than or equal to 10")
End Select

End Sub

It falls over when I enter -10.5 - I would have thought it would output: Number is less than -10 But it says: Number is equal to or greater than -10 but less than zero.

If I enter -10.6, the output is as expected: Number is less than -10.

Also, it falls over with an entry of 0.001: it says Number is equal to zero. Which it isn't.

I cannot work with integers here - the input has to have decimals (up to 3).

Is there a neater way of doing this? Many thanks for your constructive input.

Regards, Shane (VBA newbie of 4 weeks).

PS I know I can do this with If-Then statements, but I'm just getting to grips with Select Case-Case Else).

Upvotes: 0

Views: 287

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

InpuBox function returns string. So, to be able to compare value returned by this function, you need to convert it into proper numeric format:

Dim sNumber As String
sNumber = InputBox(...)

'prevent Cancel click
If sNmber = "" Then Exit Sub

'prevent non-numeric value
If Not IsNumeric(sNmber) Then Exit Sub

Select Case CDbl(sNumber)
....

If you want to use If .. else statement instead of Select case , please see: If .. Then ... Else ...

Upvotes: 2

Davesexcel
Davesexcel

Reputation: 6984

Number may have to be dimmed as Single or Double

   Dim Number As Single

Upvotes: 0

Related Questions