Excel VBA IF Conditional Statements for Beginners
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.
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!