VBA if else doesn't return correct values

Question

Trying to return a L, M, H value based on the values of two inputs. Here is what I have:

If (80 <= x <= 120 And y > 120) Or (x > 120 And y > 120) Or (x > 120 And 80 <= y <= 120) Then
    CDI = "H"

ElseIf (x < 80 And y <= 120) Or (x < 120 And y < 80) Then
    CDI = "L"

ElseIf (x < 80 And y > 120) Or (80 <= x <= 120 And 80 <= y <= 120) Or (x > 120 And y < 80) Then
    CDI = "M"

End If

The values being returned aren't what I need. For example, (0,291) returns H when it should clearly be M. Why?

Robert Harvey · Accepted Answer · 2013-11-25 21:15:11Z

3

The conditions with the variable in the middle (even if they are acceptable to the VBA interpreter) are almost certainly not going to produce the result you expect. Change all such conditions to the equivalent of the form:

variable condition constant

Example: Change

80 <= x <= 120

to

x >= 80 and x <= 120

answered Nov 25, 2013 at 21:15

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

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

4 Comments

jluk Over a year ago

DUH, thanks so much that fixed it. I'm new to VBA but made the poor assumption it could act as python does. Thanks again!

Elias Over a year ago

@Robert, so it handles the variable condition variable as an or statement?

Robert Harvey Over a year ago

@Elias: I think it probably has more to do with order of operations, and the way results of calculations are intepreted as boolean values, but I haven't really checked.

Elias Over a year ago

I think you're right. False < 120 = True, because False = 0 And True = 1

rawdog · Accepted Answer · 2013-11-25 21:19:17Z

1

Try this:

If (x >= 80 And x <= 120 And y > 120) Or (x > 120 And y > 120) Or (x > 120 And y >= 80 And y <= 120) Then
    CDI = "H"

ElseIf (x < 80 And y <= 120) Or (x < 120 And y < 80) Then
    CDI = "L"

ElseIf (x < 80 And y > 120) Or (x >= 80 And x <= 120 And y >= 80 And y <= 120) Or (x > 120 And y < 80) Then
    CDI = "M"

End If

answered Nov 25, 2013 at 21:19

rawdog

7309 silver badges25 bronze badges

Collectives™ on Stack Overflow

VBA if else doesn't return correct values

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related