Is there a way to optimize nested if clauses?

Question

for a while now I've tried to solve the decreased speed issue in my Access application when opening a print preview of certain reports. I've noticed that the slow reports have one thing in common - long, nested if clauses. I tried to search the internet for an answer for this issue, but some of the solutions do not apply to Access VBA or they just aren't possible to implement in the case of my application.

I was wondering if there are some commonly known ways that are used in order to avoid if clause monsters?

EDIT: A snip of code - it mostly handles the structure of the report based on certain conditions.

If (strCcDocNumber <> vbNullString) Then
    Dim strUpperPart As String, strLowerPart As String

    IDModule.placeIDStringsToPrivateVariables strCcDocNumber, ", "
    strUpperPart = IDModule.returnUpper()
    strLowerPart = IDModule.returnLower()

    txtIDs = strUpperPart & vbCrLf & strLowerPart
Else
    txtIDs = " " & vbCrLf & " "
End If

If (strOrderNumber = IO_OrderNumber.OrderNumberCode & "12345") Then
    txtIDs = txtIDs
    txtIDSpec1 = ModuleIDSpec1.getIDSpec1
    txtIDSpec2 = ModuleIDSpec2.getIDSpec2
    txtIDSpec1.Height = 330
    txtIDSpec2.Height = 330
    txtUpperLower = "- Ex" & vbCrLf & "- Ex2" & vbCrLf & vbCrLf & "- Ex3"
    On Error Resume Next
    For Each c In Me.Controls
        If (c.Tag = "IDSpec2Table" Or c.Tag = "IDSpec1Table") Then c.Height = 0
        If (c.Tag = "IDSpec2Table" Or c.Tag = "IDSpec1Table") Then c.Visible = False
        If (c.Tag = "IDSpec2Table" Or c.Tag = "IDSpec1TableExtra") Then c.Height = 0
        If (c.Tag = "IDSpec2Table" Or c.Tag = "IDSpec1TableExtra") Then c.Visible = False
        If (c.Tag = "IDSpec2Texts" Or c.Tag = "IDSpec1Texts") Then c.Visible = True
        If (c.Tag = "IDSpec2Texts" Or c.Tag = "IDSpec1Texts") Then c.Height = 330
        If (c.Tag = "IDSpec2Texts" Or c.Tag = "IDSpec1TextsExtra" And ModuleTarget.TargetGroup <> "23C") Then c.Visible = True
        If (c.Tag = "IDSpec2Texts" Or c.Tag = "IDSpec1TextsExtra" And ModuleTarget.TargetGroup <> "23C") Then c.Height = 330
        '+ many more tags
    Next
    On Error GoTo 0
    txtIDSpec1.Visible = True
    txtIDSpec2.Visible = True
    If (txtIDSpec1 = vbNullString And txtIDSpec2 = vbNullString) Then
        txtIDSpec1.Height = 0
        txtIDSpec2.Height = 0
        txtIDSpec1.Visible = False
        txtIDSpec2.Visible = False
    End If
Else
    '+a lot more similar conditions

EDIT: I remembered which if statements were the most troublesome ones. I think you can't change these ones into select cases or ElseIf statements, because all of the conditions need to be checked...

It goes like this:

If (condition) Then
Do this
   If (differentCondition) Then
       Do this also
        If (completelyDifferentCondition) Then
            Do this as well
        Else
            Do this instead
        End If
   End If
Else
   If (yetAnotherCondition) Then
        Do this
   Else
        Do this instead
   End If
End If

Hard to say without some kind of examples of what you're dealing with. The only thing purely related to nested ifs would be to put the most-likely-to-fail test on the outside... It would help to include some of the "solutions" which didn't work for you and explain why not. — Tim Williams, Jul 06 '18 at 07:12
post some code? what are you testing with your if statements? Are there loops? — ThunderFrame, Jul 06 '18 at 07:12
@ThunderFrame I added a piece of code. Sometimes there are loops in my if statements too (and no, they can't be moved outside) — veckorevyn, Jul 06 '18 at 07:30
You have multiples similar conditions that could be regrouped in one If — Vincent G, Jul 06 '18 at 07:32
Some conditions are redundant (for example, if Tag is "IDSpec2Table" you set twice the Height to 0) — Vincent G, Jul 06 '18 at 07:35
@VincentG it appears there were some old comments left in that code, I removed them. — veckorevyn, Jul 06 '18 at 07:38
I didn't take the comments into account when I wrote mine ;) — Vincent G, Jul 06 '18 at 07:39
@VincentG Oh wait, you're right. I'm wondering if I was supposed to write 'And' instead of 'Or'? If not, then I can clean that up for sure :D — veckorevyn, Jul 06 '18 at 07:41
Well, you cannot have at the same time the Tag equals to 2 different values, so And would be always false. But the 1st to 4th If could be assembled in the same bloc, same with the 5the to 8th. — Vincent G, Jul 06 '18 at 07:48
Note: you will probably see performance gains by not reading object properties (`ctl.Tag`) over and over again in your conditions, but instead reading them once into a VBA variable, and then using the variable in the conditions. — Andre, Jul 06 '18 at 11:18

score 2 · Accepted Answer · answered Jul 06 '18 at 07:56

I was wondering if there are some commonly known ways that are used in order to avoid if clause monsters?

First step is to work out what you want to achieve, not how you want to do it. In this context, you want to set height and visibility. From here, you can work out what conditions are required to set this.

When you first do this, you will have some monster clauses - but this is OK because you have not clarified your thinking. Work from an assumption of one state unless proven otherwise. Here is an example:

c.visible = True
If ((c.Tag = "IDSpec2Table" Or c.Tag = "IDSpec1Table") OR (c.Tag = "IDSpec2Table" Or c.Tag = "IDSpec1TableExtra")) then c.visible = True

Of course, the second line can now be simplified a little bit.

If (c.Tag = "IDSpec2Table" Or c.Tag = "IDSpec1Table" Or c.Tag = "IDSpec1TableExtra") then c.visible = True

I also I set marker Booleans - for example:

IsSpecTable = (c.Tag = "IDSpec2Table" Or c.Tag = "IDSpec1Table")
IsMySpecialFriend = (c.Tag = "IDSpec1TextsExtra" And ModuleTarget.TargetGroup <> "23C")
[...]
c.Visible = IsSpecTable Or IsMySpecialFriend

These are a couple of techniques I use to simplify complex business logic. I am also looking at the use of flags, but this means converting the text Tag to an enumerated value (I am doing this in VB.Net). This technique, though, simplifies the expression down to a simple mask with a And or Or operator as appropriate.

Thank you, this was the kind of answer I was looking for :) I have not originally created all the code, so I've found it hard to start editing and optimizing it. This reminds me, some dimensions happen inside the if statements, because if the statement doesn't become true, the function is not needed. Is this good practice or would it be wiser to Dim everything at the beginning of the sub / function? — veckorevyn, Jul 06 '18 at 08:03
@veckorevyn: Use `Option Explicit` and always declare variables. You could declare close to where they are used for the first time (could even be in the if block, but be very careful if doing it in a loop). Myself, I always declare at the start of the Sub/Function unless I have some specific needs in a loop. — AJD, Jul 06 '18 at 08:08

TinMan · Answer 2 · 2018-07-06T09:08:54.777

1

Consider using Select Case Statements when you have multiple If Statement based off the same value.

MSDN - Select Case Statement

Executes one of several groups of statements, depending on the value of an expression.

For Each c In Me.Controls
    Select Case c.Tag
    Case "IDSpec2Table", "IDSpec1Table", "IDSpec1TableExtra"
         c.Height = 0
         c.Visible = False
    Case "IDSpec2Texts", "IDSpec1Texts"
        c.Visible = True
        c.Height = 330
    Case "IDSpec1TextsExtra"
        If ModuleTarget.TargetGroup <> "23C" Then 
           c.Visible = True
           c.Height = 330
        End If
    End Select
Next

Performance: Select Case vs If vs If ElseIf

I mentioned in a comment that using a Select Case is more for readability than performance. Which is correct if we are comparing Select Case and If ElseIf statements (read Which way is faster? If elseif or select case).

Select Case and If ElseIf can be considerably faster than multiple If statements. This is because the VBA evaluates every condition in an If statement and will stop evaluating when one condition is meet in the Select Case statement. Note: not all languages do.

Consider this simple test.

Sub Test()
    Debug.Print "Test:If Statement Test:"
    If ret(1) = 1 Or ret(2) = 2 Or ret(3) = 3 Or ret(4) = 4 Or ret(5) = 5 Then

    End If

    Debug.Print vbNewLine; "Test:If ElseIf Statement"

    If ret(1) = 1 Or ret(2) = 2 Then

    ElseIf ret(3) = 3 Then

    ElseIf ret(4) = 4 Then

    ElseIf ret(5) = 5 Then

    End If

    Debug.Print vbNewLine; "Test:Select Case Statement"

    Select Case 1
        Case ret(1), ret(2)

        Case ret(3)

        Case ret(4)

        Case ret(5)

    End Select
End Sub

Function ret(n As Long) As Long
    ret = n
    Debug.Print n,
End Function

Notice that the If statement had to perform 5 operations even though they were all true. The ElseIf had to perform 2 operations because the first 2 operations were grouped in a single If clause. The Select Case only performed a single operation, even though, two operations were grouped together. This is because the Select Case will always stop evaluating conditions when a single condition is true.

edited Jul 06 '18 at 09:08

answered Jul 06 '18 at 07:49

TinMan

6,624
2
10
20

Why is a Select Case better than an If statement? – veckorevyn Jul 06 '18 at 07:51
Not true, if c.Tag = "IDSpec1TextsExtra" the results depends on ModuleTarget.TargetGroup <> "23C" – Vincent G Jul 06 '18 at 07:52
@veckorevyn Select Case are often implemented internally with jump table. – Vincent G Jul 06 '18 at 07:53
1

@VincentG It is not so much that it is better or offers any better performance, it is because it makes writing, reading and modifying code like this easier. Look at the code above and look at your code. Which do you think is easier to read and modifying? – TinMan Jul 06 '18 at 07:59
@TinMan The initial question was about performance, so I answered performance-wise, but I agree there is lots of other advantages. – Vincent G Jul 06 '18 at 08:01
@VincentG I updated my answer to address the performance differences. – TinMan Jul 06 '18 at 09:11
@TinMan Thank you, it does help a lot! I initially thought that there was no difference between using multiple if's and an elseif. This makes things a lot clearer. – veckorevyn Jul 06 '18 at 09:25
Good to hear. Happy Coding! – TinMan Jul 06 '18 at 09:29
@TinMan I edited my question, because I encountered an issue with my code that's pretty much 80% of the cases. I believe that these if's inside if's can't really be avoided? – veckorevyn Jul 06 '18 at 10:16
@veckorevyn They can definitely be reduced. There are times where you have multiple `If` statements with the exact same conditions. `If` 1 sets the visibility and `If` 2 sets the height. These should be combined from to make the code easier to read and modify. These statements are all just making simple scalar comparisions, they are not triggering any complex operations. Considering that today's computer can perform billions of operations per second, I don't think that there will be any noticeable improvement in performance. – TinMan Jul 06 '18 at 10:31

Is there a way to optimize nested if clauses?

2 Answers2

Performance: Select Case vs If vs If ElseIf