Excel vba: nested IF's? or combined IF?

[DutchKevin]

Hi, a quick question on principle.
What is the better option in Excel VBA when it comes to testing a whole column for some conditions.
Nesting the If functions in a logic way? Narrowing down to cells that matter
If Cell <> ""
If Cell<> "TestText"
If Len(Cell) > 5
' code here
End If
End If
End If

Or combine them into one and test all cells for all conditions

If Cell <> "" AND Cell <> "TestText" AND Len(Cell) > 5
' code here too
End if

Thanks

 

[mikerickson]

The first thing that I see is that there are redundant conditions. You can remove the cell<>"" condition and let the Len(cell)>5 hadle those cases.

But to your question, I would go with the way that explains the code the clearest. In 6 months, somebody is going to rework the code, either you (who have forgotten it by then) or someone whose never seen the code before. I'd choose the method that explains the code the easiest. That tends towards nested IF.

Yours is a good example, inequalities joined by AND or OR are often mis-interpreted.

Ease of editing lends itself to nested IFs.

 

[DutchKevin]

Thanks Mik
I have the nested setup as preferred as well. And as long as the code runs smooth I'll stick to that.
It was more in light of performance with a large amount of empty cells and 0 cells that got me thinking to filter those upfront, before the hard part starts.

 

[mikerickson]

AFAIK, the speed of execution isn't effected much by which kind of IF structure one uses.
What I have found to speed things is to do the exclusions outside a loop (if possible), resulting in a shorter loop.
You mention cells. Moving the values into an array and working on the array and then writing back to the sheet is much faster than working directly with cells.