Excel VBA: Case query

Question

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).

Maciej Los · Accepted Answer · 2015-03-01 11:54:32Z

2

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 ...

answered Mar 1, 2015 at 11:54

Maciej Los

8,6311 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shane M Hewitt Over a year ago

Thank you Maciej. This works perfectly. I had not realised that MsgBox always produces a string output. Regards, Shane.

Maciej Los Over a year ago

@ShaneMHewitt, you're very welcome ;) Cheers, Maciej

Davesexcel · Accepted Answer · 2015-03-01 11:40:12Z

0

Number may have to be dimmed as Single or Double

   Dim Number As Single

answered Mar 1, 2015 at 11:40

Davesexcel

6,9922 gold badges31 silver badges44 bronze badges

Collectives™ on Stack Overflow

Excel VBA: Case query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related