IF returns FALSE when it sould return TRUE

问题:

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

回答1:

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

回答2:

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.

回答3:

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