VBA IF ELSE Statements for Powerful Microsoft Applications

Most users don’t realize the true potential of Microsoft applications. Hidden within each application is the power of visual basic for applications or “vba”. Visual basic turns Microsoft applications into powerful programming platforms which offer far more functionality than a mere spreadsheet, word processor or database. Vba allows the user to program an almost endless number of functions into whichever application they are using.

This tutorial will show you how to access and use the power of vba in excel. The developer tab and vba statements below can also be applied in all of the Microsoft office applications in a very similar manner. To understand the tutorial, you need to know how to add a command button in excel as well as being able to access the vba editor. Microsoft Excel – VBA will give you all the skills you need to add vba commands to excel.

For the purposes of the tutorial, we will use the following practical example. Assume you are helping your teacher keep track of her student’s performance and the teacher has asked you to assign a result of pass or fail to each student. Students who have achieved a result of fifty percent or more must be assigned a passing grade whilst students who achieve less than fifty percent will fail the course.

The original spreadsheet she sends you would probably look something like this:

To create a vba command to assign a grade to the above students you could use the if statement programmed into a command button in your spreadsheet. Let’s take a look at the if statement to understand how it could help you.

About the if else statement in vba

If statements are the basis of most modern programs and procedures. These type of statements allow a programmer to create a set of logical choices with resulting instructions. The statement follows the following program flow:

When the if statement is called, the program checks a value against a given condition. If the statement is true, the program executes the instructions contained within the if code. If the statement is false then the program executes the code contained within the else code.

Based on our example above, we could word an if statement to assign grades as follows: If the student’s grade is 50 or above then the student should be assigned a passing grade. If not then the student will be assigned a failing grade.

If we use the if statement above the excel will check each grade against the following condition (grade >= 50) to see whether the student’s grade is greater than or equal to fifty. If the student’s grade is greater than fifty then the resulting statement will be true and the program will assign a “Pass” to that student. If the statement is false the program will assign “Fail” to that student.

Here is how we would write the vba to achieve the above:

First we need to create a command button within the excel spreadsheet which will contain our code. Creating command buttons is a fairly simple task in excel, and Excel VBA and Macros with MrExcel, will show you how to do that quickly and easily.

Double click the command button and add the following code in the vba editor:

First we declare the variables that our procedure will use. If you don’t know what variables are or how to declare them then Excel Course (Basic and Advanced) will show you how to use variables.

The above code will check each of the cells within the range. If the student’s score is 50 or more for the test then the code will record “passed” next to the student’s name. If the student’s score is less than 50 then the program will record “failed” next to their name.

If statements can make checking conditions within a spreadsheet really simple. And if statements are even more powerful because you can include if statements within other if statement. This is called nesting an if statement.

Nesting if statements

Let us say that our teacher would also like to know who got more than 80 for the test so that she can reward those students. We could check for students who got over 80 by adding another if statement within the passing grade statement. Take a look at the code below:

The above code would check if a student’s score more than 50 on their test. If it is, it would then move onto the next if statement and check whether the result was greater than 80. If the results were greater than 80 then the program would assign a “First Class” to the student. If not then the program would assign a “passed” to the student. The result of the above if vba statement on our worksheet would look like this:

Multiple if else statements.

Finally, the if else statement does not only have to refer to and either or choice but it can be used where multiple options are possible.

Take our example but assume instead of a “pass” or “fail”, the teacher would like us to assign an “A” for students who achieved more than 80 for the test, a “B” for students who achieved more than 70 for the test, a “C” for those students who achieved from than 60 and a “D” grade for students who achieved more than 50 and students who received less than 50 would be assigned a “fail”.

By using multiple ElseIf statements within the code we have access to a number of conditions that the program can check for and the output will differ depending on those conditions. Take a look at the sample code below:

The program begins by checking the value of the grade to see if it is greater than 80. If it is it assigns an “A” to the student. If not it moves onto the next statement and checks whether the students mark is greater the 70 and so on. If the student’s mark is not greater than 50 then the student is automatically assigned a “Failed.” This is what our results would look like using the above code:

One thing that is important to note with the if statement is that it will check each if statement in the logical order in which it is coded. As soon as it gets a result, it will end the if loop and move onto the next cell. So if we wrote the if statements in the reverse order, i.e. checking if the test result was a D first using the following code:

You would get the following result:

You will notice that the above results show all students either achieving a D result or a Failed result. This is as a result of programming logic mentioned above.

Vba is a powerful addition to and Microsoft application and the if statement is just one example of how vba can make your life so much easier. For more programming with vba, take a look at Excel 2010 VBA. This course will teach you to unlock the power of all of your Microsoft applications.