Don't Use Nested if's

[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

 

[RonPaii]

One thing missing in basic is short circuited boolean expressions like in C. I use nested if to put cheep test ahead of expensive ones.

 

[Albert D. Kallal]

One thing missing in basic is short circuited Boolean expressions like in C. I use nested if to put cheep test ahead of expensive ones.

Indeed - and the technique I outlined handles that case quite will.

And for those not aware of what a "short circuit" means?

It means that when you write a "compound" if/then statement, then 2nd (or more) parts of the conditions are NOT evaluated, and are skipped if the first (or previous) expression is false. After all, why keep "testing" and "checking" subsequent expressions if the first one fails?



So, take this example:

                        If IsDBNull(ctlC.Attributes("f")) Then
                            ' value is not null, then use it

                            If ctlC.Attributes("f") <> strPK Then
                                ' do something

                            End If
                        End If

So, in above, we have to FIRST check if the value is say not null.

Hence, we can't do this:

                        If IsDBNull(ctlC.Attributes("f")) And ctlC.Attributes("f") <> strPK Then
                            ' do something

                        End If

We can't use above, since the 2nd expression might be null.

but, short circuiting feature means that if the previous expression fails, then the code bails out, and does NOT evaluate any further expressions.

So, if the language supports short circuiting, then we can write it this way:
(this example is vb.net - it supports short circuiting).

                        If IsDBNull(ctlC.Attributes("f")) AndAlso ctlC.Attributes("f") <> strPK Then
                            ' do something

                        End If

So, above will not error out, since I used "AndAlso". If the first expression fails, then the additional expressions are not evaluated.

So, if you ever see the use of "AndAlso" or even "OrAlso", then these additional conditions don't require evaluation, and will be skipped.
(better stated then "skipped", is they are simply not evaluated).

So, as a FYI - above is what the mention of "short circuit" means.

I can't say I miss this feature much in VBA, but it often handy, say when you need to test if a collection say has at least 1 element, such as:

            If fURL.Count = 1 AndAlso fURL(0) = "Impersonate" Then
                GetUserInfo.Impersonating = True
            End If

So, in above, if the array does not have at least 1 element (and it starts at 0 index)?

Then the above code does not error out, since I used "AndAlso".

In VBA, the above would have to be written as:

            If fURL.Count = 1 Then
                ' array has at lest 1 value, starting at 0.
                If fURL(0) = "Impersonate" Then
                      GetUserInfo.Impersonating = True
               End If
            End If

So, indeed, if your coding language has short circuits, then you can often reduce nesting if's as the above shows.


R
Albert

 

[GustavBrock]

View attachment 122718

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

Exactly. You will grasp the flow of this code in a split-second, while you will have to read the second example to get it. Not to mention the mess you will get should you need an else block.

 

[Isaac]

I am a big advocate of just using helper columns to split out the logic for sure. Readability and maintainability