CODEWITHSUNDEEP
vbaexcel
Yawrf
Yawrf

Reputation: 71

Excel VBA If statement mistakenly triggering

OK, I have an If statement that shouldn't be triggering, but it is. Here's the current set-up: 

If HP.Value > Range("HPMax").Value Then
    MsgBox (HP.Value & "/" & Range("HPMax").Value)
    HP.Value = Range("HPMax").Value
    MsgBox (HP.Value & "/" & Range("HPMax").Value)
 End If

I have the Message Boxes in to show me that it is indeed happening. currently HP is at 29, and HPMax is at 60.

Upvotes: 1

Views: 231

Answers (1)

ThunderFrame
ThunderFrame

Reputation: 9461

You're not casting the textbox value to a number. Assuming you're comparing integer values, change your comparison to:

If IsNumeric(HP.Value) Then
    If Int(HP.Value) > Range("HPMax").Value Then

If the decimal place is important, use a Double:

If IsNumeric(HP.Value) Then
    If CDbl(HP.Value) > Range("HPMax").Value Then

EDIT In response to your comment:

The VBA equality (=, <>, <=, >=, >, <), addition (+) and concatenation (&) operators behave differently when both operands are String types, and when one or both operands is a numeric type.

'equality operators do string comparisons if both operands are strings, regardless of whether they're numeric
Debug.Print "25" < "200" 'Prints False
Debug.Print 25 < "200"   'Prints True
Debug.Print "25" < 200   'Prints True
Debug.Print 25 < 200     'Prints True

Debug.Print "25" > "200" 'Prints True
Debug.Print 25 > "200"   'Prints False
Debug.Print "25" > 200   'Prints False
Debug.Print 25 > 200     'Prints False

'+ concatenates if both items are strings, regardless of whether they're numeric
Debug.Print "25" + "200" 'Prints 25200
Debug.Print 25 + "200"   'Prints 225
Debug.Print "25" + 200   'Prints 225
Debug.Print 25 + 200     'Prints 225

'- always casts both operands to numbers, and if either is non numeric, throws a Type Mismatch error
Debug.Print "25" - "200" 'Prints -175
Debug.Print 25 - "200"   'Prints -175
Debug.Print "25" - 200   'Prints -175
Debug.Print 25 - 200     'Prints -175

'& Always concatenates
Debug.Print "25" & "200" 'Prints 25200
Debug.Print 25 & "200"   'Prints 25200
Debug.Print "25" & 200   'Prints 25200
Debug.Print 25 & 200     'Prints 25200

You might have code like this, that works most of the time

Dim x As String
x = "1"
Debug.Print x < "80", Int(x) < "80"  'Prints True True

But if x starts with "9", you have a bug

x = "9"
Debug.Print x < "80", Int(x) < "80"  'Prints False True

Upvotes: 10

Related Questions