Excel VBA IF statements can take your VBA coding to the next level

If you’ve learned the basics of VBA coding (perhaps you’ve watched our popular VBA for Beginners series), you’re ready to move to the next level. What is next? Excel VBA IF statements move you towards more advanced coding, and more powerful macros. You may have wondered how we tell Excel to execute only part of a routine, according to a value in a cell, or something else. This is the basic idea behind an Excel VBA IF (‘conditional’) statement.

This series teaches you three techniques for dealing with a ‘conditional’ situation. These techniques are the techniques we use in our Excel development projects. But first, what is an Excel VBA IF statement? Why ‘if’?

Conditional statements allow us to instruct Excel to execute code only if a certain condition is met. A condition could relate to any object in Excel, but typically a condition relates to a value in a cell or VBA variable. For example, you might wish to count how many entries in a database are ‘male’, or write the word ‘male’ next to all the ‘male’ entries. In each case, we would ask Excel to do something only if a certain condition is met, in this case if the letter ‘M’ appears in a cell.

Got the idea? OK, let’s get into the first video! Don’t forget to download the files and work along with Chris.

Click here to download the Excel files for this video.

Video 1 – Simple Excel VBA IF Statement

In the first video, we learn how to create a simple if statement. A simple if statement involves just a single line of code, and is relatively easy to set up. In this example, we ask Excel to flash up a message box to tell us if an entry in a database is ‘male’ or not. You’ve just created your first Excel VBA IF statement. Well done, onwards!

Video 2 – IF – ELSE – END IF

So, we know how to ask Excel to do something if a condition is met. But, what if a condition is not met? What if we want Excel to notify us if the entry in the database is not a ‘male’? The next technique is IF – ELSE – END IF.

This technique allows us to direct the code in two directions. But, the required coding construct is more complicated and often confuses beginners; when using IF – ELSE – END IF, we have to remember that the instructions must begin on a new line. A simple if statement requires just a single line of code; IF – ELSE – END IF incorporates another condition and requires at least 5! One for if, else, end if, and at least one line for the instructions if the condition is met, and the instructions is the condition is not met. Don’t worry too much about it at this stage – just follow along with the video.

This can seem ‘code-heavy’; but the power of the routines, in our view, justifies the effort required to learn the syntax. In the video, Chris creates a routine to count up the number of males and non-males and in the database. The loop / conditional statement combination is VBA at its best; you can see how these techniques combine together beautifully to get a job done quickly. Chris even has time to validate the routine by using spreadsheet formulae to reach the same outcome. Nice!

Video 3 – Select Case

Oh no, more new syntax! Yes, select case is a new construct for us. But, this technique opens up a new world of possibilities. So far, We’ve dealt with conditional statements that direct the code in one or two directions. Select case allows us to direct the code in any number of directions, and even includes a ‘catch-all’ condition too. It is supremely flexible and powerful, and regularly features in our Excel development projects.

Chris implements select case in the video to count up the number of entries in three age categories (eg. 30-59 years old) in the database. But, he does not complete the report. Can you combine select case with a loop to get the information that the ‘client’ requires? Reference techniques from video 2, and let us know how you get on.

What did you make of the Excel VBA IF Conditional Statements for Beginners series? With these three techniques, we are confident you can deal with most situations where a conditional mechanism is required. Good luck!