19.3 IF Statements

Now, with what you know about some of the basics and what you’ve seen so far, you can make some pretty powerful functions. However, the functions will become even more versatile, and you can do even more interesting things once introduced to some control flow structures. What is control flow? Well, it’s essentially deciding when and under what conditions a block of code should be executed. This section introduces you to several different VBA commands that control how code executes. All programming languages have similar commands, so if you are familiar with any programming languages, these statements will be familiar. If this is new to you, the content isn’t complex, but you may want to take a bit of time to try to work out these and similar problems after watching the videos to help cement the concepts.

In this section, we will explore If...Then statements, Select Case statements, For loops, Do While and Do Until loops, and For Each statements.

If...Then Statements

You’ve already seen the If...Then statement in action.

If Condition Then
     true_instructions
Else 
     false_instructions
End If

The If keyword is followed by a condition or expression that evaluates to a TRUE or a FALSE. If it is TRUE, then the code following the Then keyword executes. Otherwise, the code executes other instructions. So those are separated by the Else keyword. The End If denotes the end of the IF statement. Interestingly, if we have multiple conditions we want to test out, we can also add an Else If.

You can omit the Else and just say if a condition is TRUE, then execute some instructions followed by an End If. Interestingly, if your TRUE instructions are a single line, you can simply put the following all on one line.

If Condition Then true_instruction

Even though you can do that, I will typically include an End If because it tends to be a little bit clearer.

Okay, let’s put these ideas into action. Consider the following function.

'If Then Statements
Function Greeting()
     If Time <= 0.5 Then
          Greeting = "Good Morning " & Application.UserName
     ElseIf Time <= 0.66667 Then
          Greeting = "Good Afternoon " & Application.UserName
     Else
          Greeting = "Good Evening " & Application.UserName
     End If
End Function

The function will return a greeting depending on the time of day. Depending on when you run it, you will get different answers. Let’s say it’s 9:00 PM. We might have the greeting Good evening, Tom.

Time is a built-in function that returns the current time. So, should we check to see if it’s before noon (half a day) and it is, then the message would say Good Morning and tack on the username from the Application.UserName field that we looked at earlier. If it’s afternoon, then we would check to see if less than two-thirds of the day has passed. If so, then the message would say Good Afternoon. If neither of those conditions is met, then the code after the Else statement executes, and the message will say Good Evening.

Of course, with each of those code blocks, we set the value that is returned from the function, so even with just If...Then statements, we can do some pretty powerful things.