CODEWITHSUNDEEP
excelvba
engrken
engrken

Reputation: 59

Using a combination of IF, And in excel VBA

Im trying make a condition in excel VBA where there are two posible condition of variable x and y,

Say for example

Sub Test()

X = 6
Y = 11

If X < 3 Or X > 5 And Y < 10 Then
    X = 10
Else
    X = 11
End If
MsgBox X
End Sub

For the X term, when X<3 or X>5 seems work well however when i change the Y to any value greater than 10 say 11 then the result is 10 but supposed to be it should be 11. Can you please let me know if i missing something in my code so that when X<3 or X>5 and (y=11)>10 the result must be 11.

Regards, Kenneth

Upvotes: 1

Views: 1263

Answers (2)

Selfish
Selfish

Reputation: 6190

This is an issue of operator precedence. In VB the order in Logical and Bitwise Operators is as follows:

  1. Negation (Not)
  2. Conjunction (And, AndAlso)
  3. Inclusive disjunction (Or, OrElse)
  4. Exclusive disjunction (Xor)

The result is that and is executed before or in your condition, leading to the right side of the or being the whole expression X > 5 And Y < 10.

Your condition is executed like:

If X < 3 Or (X > 5 And Y < 10) Then

I believe what you actually want is the following (this should solve your issue):

If (X < 3 Or X > 5) And Y < 10 Then

Sources:

Upvotes: 4

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Consider:

Sub Test()

X = 6
Y = 11

If (X < 3 Or X > 5) And Y < 10 Then
    X = 10
Else
    X = 11
End If
MsgBox X
End Sub

Upvotes: 1

Related Questions