If Statements

A commonly used command in VBA is the If statement. It's used to check that a certain condition is met prior to executing your code. It can be a single condition, or you can have more complex If statements with multiple conditions and different code for each condition.

The syntax of an If statement is:

If Condition Then
    'Code to be executed
End If

The condition usually checks that a variable has a certain value. For example, you might have a worksheet in your workbook that you only want people with a password to be able to access. In this case you would probably have a user input (where the user would type the password, let's call the variable strInput) and then you could compare their input to the correct password. The If statement would look something like this:

If strInput = strCorrectPassword Then
    'Display protected worksheet
End If

Taking this idea further, you could also have a message appear if the user enters the wrong password. This would use an ElseIf command:

If strInput = strCorrectPassword Then
    'Display protected worksheet
ElseIf strInput <> strCorrectPassword Then
    Msgbox "The password you have entered is incorrect.", vbExclamation, "Incorrect Password"
End If

As you can see, by using the If... Then and ElseIf... Then statements you can create reasonably complex If statements for a large number of conditions. In addition to these two you can use the "Else" statement, which executes when none of the conditions in the If statement are met.

To learn about If statements we're going to write a macro that determines a letter grade for a group of students based on their numerical (%) grade. Open a new workbook and enter the following data into Sheet1, starting with the "Student" column in cell B2:

Student	Grade (%)	Grade
Dan	93
James	72
Laura	37

Then add a command button to the sheet and label it as "Generate grade". Your workbook should look like this:

Click on the button and then View Code. First we need to declare a variable to store the grade. An integer type will be fine for this:

Dim intGrade As Integer

We then need to read the first grade in from the worksheet and use an If statement to determine what range the grade falls in, in order to assign an A, B, C, etc. to the student. This should look like this:

intGrade = Range("C3").Value
If intGrade > 90 Then
    Range("D3").Value = "A"
ElseIf intGrade > 80 And intGrade < 90 Then
    Range("D3").Value = "B"
ElseIf intGrade > 70 And intGrade < 80 Then
    Range("D3").Value = "C"
ElseIf intGrade > 60 And intGrade < 70 Then
    Range("D3").Value = "D"
Else
    Range("D3").Value = "F"
End If

As you can see, this example demonstrates the use of If... Then, ElseIf... Then, and Else... It also shows how you can use 2 conditions for If or ElseIf statements (e.g. intGrade must be greater than 70 but less than 80).

The If statement above will only determine the letter grade for the first student, Dan. We now need to copy and paste the code above twice in order to determine the letter grade for the remaining 2 students. When you do this make sure you change the cell reference each time (so the first If statement block should read the grade from cell C3 and then output an A, B, C, etc. to cell D3; the 2nd should use cells C4 and D4, and the 3rd should use cells C5 and D5). This would be quicker and more efficient with a Loop, but we'll learn about those next time.

Your finished code should look something like this (some of the lines aren't visible in this screenshot, but it should give you the idea):

Now when you go back to the worksheet and click on the button, the macro should automatically give display an A for Dan, a C for James and an F for Laura.

Once you have finished this tutorial, save the workbook somewhere as we'll use it in the next tutorial to learn about Do While loops.