Don't Use Nested if's (2 Viewers)

[Steve R.]

I have long advocated minimizing the use of complex "if" statements. This also applies to database programing.

Stop writing nested IFs and IFS formulas in Excel: Use SWITCH instead

Write cleaner Excel logic by eliminating repetitive and lengthy formulas.

www.howtogeek.com

 

[The_Doc_Man]

The corresponding advice in Access - use SELECT CASE vs. deeply nested IF/ELSE IF/ELSE statements - is pretty good, too.

I have always used the rule that if I have to make a choice among more than three situations involving a single variable, it is SELECT CASE time. But there is always the bug-a-boo situation where the items being nested aren't against the same variable. In my genealogy database project, I have had the issue of nesting SELECT CASE statements because I have to look at combinations of keywords that can legally appear together vs. other cases where two particular keywords in sequence are clearly erroneous.

I'll stay with nested IF in VBA if and only if the number of options is VERY small. Otherwise, SELECT CASE in VBA or CASE in SQL are my GOTO syntax choices. (Yeah, I'm one of the hard-core guys who still uses GOTO statements, too!)

 

[CJ_London]

You also have the functions choose and switch

 

[Albert D. Kallal]

I have long advocated minimizing the use of complex "if" statements. This also applies to database programing.

Stop writing nested IFs and IFS formulas in Excel: Use SWITCH instead

Write cleaner Excel logic by eliminating repetitive and lengthy formulas.

www.howtogeek.com

A great suggesting.

In fact, I often try to avoid "nesting" if's too deep, EVEN when a "set" of conditions are requried.

In other words, even with a "case" statement not appropriate, I still try to avoid nesting.

You can do this by "reversing" the conditions.

So, say this:

(no need to really ready the code, but what matters is often the code indent level starts to grow and grow for each if).

So, what you can do is have some condition check, and simple "bail out" of the routine.

This also tends to "reduce" the context of a bunch of "if's" and how deep we are.

You brain now says:

If we get this far, keep going, everything is good, write more code!!!

So, the above then becomes this:

So, often, I approach things as:

If bad condition - bail out of routine.

And then over time, might add a few more conditions that will "bail out".

So, in most cases, one does not have to look at previous code, but simple write:

' if expired project, don't run
if me.ProjectExpired then
    return
end if

' get here , good to go!!!

So, often it's not a simple case statement, but say you have 2 or 4 or even 8 conditions to test and meet?

You don't have to nest them, and thus you avoid having to write code inside of a say 8 levels nesting of "if's".

reverse the conditions - don't nest, and adopt a "bail out of code", and then when you come back months later?

Need to add a new condition?

Just add it, but you not be adding another nesting level.....

You also THEN have a place in the code like:

' good to go, conditions met, process the data......
' code here follows....


Note also, when you brain reads the 2 screen caps - the 2nd one is processed and MUCH easier to read.

The first example, your brain has to "start building up" a context of all those if's.....(and thus it hard to read).


R
Albert