IF returns FALSE when it sould return TRUE

user_10554194 0
Viewed 119

I tried to do something like this 

sub test()
a=inputbox("value1:")
b=inputbox("value2:")
c=inputbox("value3:")

  if a<b and a>c then 

    msgbox(a)

    else
      msgbox(b)
      msgbox(c)
   end if

end sub

when I enter values such as 5 for a, 10 for b and 2 for c, condition should return TRUE and then message box with a is shown, but it returns FALSE and displays message boxes with b and c. I think the solution is pretty simple but I can't figure it out.

thanks a lot

conditional-statements vba excel
3 Answers

Your InputBoxes are returning strings that look like numbers and "5" is not less than "10". Do one of the following:

  • Dim a as long, b as long, c as long
  • Use Application.InputBox with Type:=1 argument.
  • Convert the strings to real numbers like a=clng(inputbox("value1:")) or if int(a)<int(b) and int(a)>int(c) then.
Admin1

This happens because VBA is treating your input as strings. Just add a CInt() or a CLng() to the variables to excel know that they are numeric.

Sub test()

a = CInt(InputBox("value1:"))
b = CInt(InputBox("value2:"))
c = CInt(InputBox("value3:"))

  If a < b And a > c Then
    MsgBox (a)

    Else
      MsgBox (b)
      MsgBox (c)
   End If
End Sub
user_79758630

The other two answers are excellent and I would ultimately go with Jeeped's explicit variable declaration method (Dim a as long, b as long, c as long).

While I would never suggest not declaring your variables - which you found out the hard way why it's always good practice to do so - you could also simply multiply your inputbox by 1.

Here's just another trick to force a string to a numerical value using the power of multiplication (this is a similar trick used in worksheet formulas that use the double negative --() to convert a value into a number).

a = InputBox("value1:") * 1
b = InputBox("value2:") * 1
c = InputBox("value3:") * 1

If a < b And a > c Then

    MsgBox (a)

Else

    MsgBox (b)
    MsgBox (c)

End If
user_57817450
Related