VBA IF Statement – A Complete Guide

“Guess, if you can, and choose, if you dare.” – Pierre Corneille

This post provides a complete explanation of the VBA If statement.

If you want to see the format of the If Statement and a quick example then go here.

To find out more about the alternate If statement then check out the IIf function.

If you are new to VBA and programming then you may want to read the post from start to finish.

©Bigstock | Student thinking

Format of the VBA If Statement

The format of the If statement is as follows

If Then

ElseIf  Then

Else

End If

The following code shows a simple example of using the If statement

If Range("A1").Value > 5 Then
Debug.Print "Value is greater than five."
ElseIf Range("A1").Value < 5 Then
Debug.Print "value is less than five."
Else
Debug.Print "value is equal to five."
End If

 

What is If and why do you need it?

If statements are used to allow your code to make choices when it is running .

You will often want to make choices based on the data your macros reads.

For example, you may want to read only the students who have marks greater than 70. As you read through each student you would use the If Statement to check the marks of each student.

The important word in the last sentence is check. The If statement is used to check a value and then to perform a task based on the results of that check.

The Test Data

We’re going to use the following test data for the code examples in this post.

 

A Simple If Example

The following code prints out the names of all students with marks greater than 50 in French.

Sub ReadMarks()

Dim i As Long
' Go through the marks columns
For i = 2 To 11
' Check if marks greater than 50
If Range("C" & i) > 50 Then
' Print student name to the Immediate Window(Ctrl + G)
Debug.Print Range("A" & i) & " " & Range("B" & i)
End If

Next

End Sub

Results

Bryan Snyder

Juanita Moody

Douglas Blair

Leah Frank

Monica Banks

Play around with this example and check the value or the > sign and see how the results change.

 

Conditions

The piece of code between the If and the Then keywords is called the condition. A condition is a statement that evaluates to true or false. They are mostly used with Loops and If statements. When you create a condition you use signs like >,<>,>=,

The following are examples of conditions

 

You may have noticed x=5 as a condition. This should not be confused with x=5 when used as an assignment. When equals is used in a condition it means “is the left equal to the right”. The following table demonstrates how equals is used in conditions and assignments

 

Using ElseIf

The ElseIf statement allows you to choose from more than one option. In the following example we print for marks that are in the Distinction or High Distinction range.

Sub UseElseIf()

If Marks >= 85 Then
Debug.Print "High Destinction"
ElseIf Marks >= 75 Then
Debug.Print "Destinction"
End If

End Sub

The important thing to understand is that order is important. The If condition is checked first.

If it is true then “High Distinction” is printed and the If statement ends.

If it is false then the code moves to the next ElseIf and checks it condition.

Let’s swap around the If and ElseIf from the last example. The code now look like this

Sub UseElseIfWrong()

' This code is incorrect as the ElseIf will never be true
If Marks >= 75 Then
Debug.Print "Destinction"
ElseIf Marks >= 85 Then
' code will never reach here
Debug.Print "High Destinction"
End If

End Sub

In this case we check for a value being over 75 first. We will never print “High Distinction” because if a value is over 85 is will trigger the first if statement.

To avoid these kind of problems we should use two conditions. The help state exactly what you are looking for a remove any confusion. The example below shows how to use these. We will look at more multiple conditions in the section below.

If marks >= 75 And marks < 85 Then
Debug.Print "Destinction"
ElseIf marks >= 85 And marks

Let’s expand the original code. You can use as many ElseIf statements as you like. We will add some more to take into account all our mark classifications.

 

Using Else with If

The Else statement is used as a catch all. It basically means “if no conditions were true” or “everything else”. If the previous code example we didn’t include a print statement for a fail mark. We can add this using Else.

Sub UseElse()

If Marks >= 85 Then
Debug.Print "High Destinction"
ElseIf Marks >= 75 Then
Debug.Print "Destinction"
ElseIf Marks >= 55 Then
Debug.Print "Credit"
ElseIf Marks >= 40 Then
Debug.Print "Pass"
Else
' For all other marks
Debug.Print "Fail"
End If

End Sub

So if it is not one of the other types then it is a fail.

Let’s write some code to through our sample data and print the student and their classification.

Sub AddClass()

' get the last row
Dim startRow As Long, lastRow As Long
startRow = 2
lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

Dim i As Long, Marks As Long
Dim sClass As String

' Go through the marks columns
For i = startRow To lastRow
Marks = Range("C" & i)
' Check marks and classify accordingly
If Marks >= 85 Then
sClass = "High Destinction"
ElseIf Marks >= 75 Then
sClass = "Destinction"
ElseIf Marks >= 55 Then
sClass = "Credit"
ElseIf Marks >= 40 Then
sClass = "Pass"
Else
' For all other marks
sClass = "Fail"
End If

' Write out the class to column E
Range("E" & i) = sClass
Next

End Sub

The results look like this with column E containing the classification of the marks

Results

 

Using More Complex Conditions

You can have more than one condition in an If Statement. The VBA keywords And and Or allow use of multiple conditions.

These words work in a similar way to how you would use them in English.

Let’s look at our sample data again. We now want to print all the students that got over between 50 and 80 marks.

We use And to add an extra condition. The code is saying: if the mark is greater than or equal 50 and less than 75 then print the student name.

Sub CheckMarkRange()

Dim i As Long, marks As Long
For i = 2 To 11

' Store marks for current student
marks = Range("C" & i)

' Check if marks greater than 50 and less than 75
If marks >= 50 And marks < 80 Then
' Print first and last name to Immediate window(Ctrl G)
Debug.Print Range("A" & i) & Range("B" & i)
End If

Next

End Sub

Results

Douglas Blair

Leah Frank

Monica Banks

 

In our next example we want the students who did History or French. So in this case we are saying if the student did History OR if the student did French.

Sub ReadMarksSubject()

Dim i As Long, marks As Long

' Go through the marks columns
For i = 2 To 11
marks = Range("D" & i)
' Check if marks greater than 50 and less than 80
If marks = "History" Or marks = "French" Then
' Print first and last name to Immediate window(Ctrl G)
Debug.Print Range("A" & i) & " " & Range("B" & i)
End If

Next

End Sub

Results

Bryan Snyder

Bradford Patrick

Douglas Blair

Ken Oliver

Leah Frank

Rosalie Norman

Jackie Morgan

Using Multiple conditions like this is often a source of errors. The rule of thumb to remember is to keep them as simple as possible.

 

And

The AND works as follows

Condition 1
Condition 2
Result


TRUE
TRUE
TRUE


TRUE
FALSE
FALSE


FALSE
TRUE
FALSE


FALSE
FALSE
FALSE

What you will notice is that AND is only true when all conditions are true

 

Or

The OR keyword works as follows

Condition 1
Condition 2
Result


TRUE
TRUE
TRUE


TRUE
FALSE
TRUE


FALSE
TRUE
TRUE


FALSE
FALSE
FALSE

What you will notice is that OR is only false when all the conditions are false.

Mixing AND and OR together can make the code difficult to read and lead to errors. Using parenthesis can make the conditions clearer.

Sub OrWithAnd()

Dim subject As String, marks As Long
subject = "History"
marks = 5

If (subject = "French" Or subject = "History") And marks >= 6 Then
Debug.Print "True"
Else
Debug.Print "False"
End If

End Sub

 

Not

There is also a NOT operator. This makes the condition give the opposite result

Condition
Result


TRUE
FALSE


FALSE
TRUE

 

The following two lines of code are equivalent.

If marks < 40 Then If Not marks >= 40 Then

Putting the condition in parenthesis makes the code easier to read

If Not (marks > 40) Then

A common usage of Not when checking if an object has been set. Take a worksheet for example. Here we declare the worksheet

Dim mySheet As Worksheet
' Some code here

We want to check mySheet is valid before we use it. We can check if it is nothing.

If mySheet Is Nothing Then

There is no way to check if it is something as there is many different ways it could be something. Therefore we use Not with Nothing

If Not mySheet Is Nothing Then

If you find this a bit confusing you can use parenthesis like this

If Not (mySheet Is Nothing) Then

 

The IIF function

VBA has an fuction similar to the Excel If function. In Excel you will often use the If function as follows:

=IF(F2=””,””,F1/F2)

The format is

=If(condition, action if true, action if false).

VBA has the IIf statement which works the same way. Let’s look at an example. In the following code we use IIf to check the value of the variable val. If the value is greater than 10 we print true otherwise we print false.

Sub CheckVal()

Dim result As Boolean
Dim val As Long

' Prints True
val = 11
result = IIf(val > 10, True, False)
Debug.Print result

' Prints false
val = 5
result = IIf(val > 10, True, False)
Debug.Print result

End Sub

In our next example we want to print out Pass or Fail beside each student depending on their marks. In the first piece of code we will use the normal VBA If statement to do this.

Sub CheckMarkRange()

Dim i As Long, marks As Long
For i = 2 To 11

' Store marks for current student
marks = Range("C" & i)

' Check if student passes or fails
If marks >= 40 Then
' Write out names to to Column F
Range("E" & i) = "Pass"
Else
Range("E" & i) = "Fail"
End If

Next

End Sub

In the next piece of code we will use the IIf function. You can see that the code is much neater here.

Sub CheckMarkRange()

Dim i As Long, marks As Long
For i = 2 To 11

' Store marks for current student
marks = Range("C" & i)

' Check if student passes or fails
Range("E" & i) = IIf(marks >= 40,"Pass","Fail")

Next

End Sub

You can see the IIf function is very useful for simple cases where you are dealing with two possible options.

 

Using Nested IIf

You can also nest IIf statements like in Excel. This means using the result of one IIf with another. Let’s add another result type to our previous examples. Now we want to print Distinction, Pass or Fail for each student.

Using the normal VBA we would do it like this

Sub CheckResultType2()

Dim i As Long, marks As Long
For i = 2 To 11

' Store marks for current student
marks = Range("C" & i)

If marks >= 75 Then
Range("E" & i) = "Distinction"
ElseIf marks >= 40 Then
' Write out names to to Column F
Range("E" & i) = "Pass"
Else
Range("E" & i) = "Fail"
End If

Next

End Sub

Using nested IIfs we could do it like this

Sub UsingNestedIIF()

Dim i As Long, marks As Long, result As String
For i = 2 To 11

marks = Range("C" & i)
result = IIf(marks >= 55,"Credit",IIf(marks >= 40,"Pass","Fail"))

Range("E" & i) = result


Next

End Sub

Using nested IIf is fine in simple cases like this. The code is simple to read and therefore not likely to have errors.

 

If Versus IIf

So which is better?

You can see for this case that IIf is shorter to write and neater. However if the conditions get complicated you are better off using the normal If statement. A disadvantage of IIf is that it is not well known so other users may not understand it as well as code written with a normal if statement.

My rule of thumb is to use IIf when it will be simple to read and for more complex cases use the normal If statement.

 

Using Select Case

The Select Case statement is an alternative way to write an If statment with lots of ElseIf’s. You will find this type of statement in most popular programming languages where it is called the Switch statement. For example Java, C#, C++ and Javascript all have a switch statement.

The format is

Select Case
Case
Case
Case
Case Else
End Select

Let’s take our AddClass example from above and rewrite it using a Select Case statement.

Sub AddClass()

' get the last row
Dim startRow As Long, lastRow As Long
startRow = 2
lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

Dim i As Long, Marks As Long
Dim sClass As String

' Go through the marks columns
For i = startRow To lastRow
Marks = Range("C" & i)
' Check marks and classify accordingly
If Marks >= 85 Then
sClass = "High Destinction"
ElseIf Marks >= 75 Then
sClass = "Destinction"
ElseIf Marks >= 55 Then
sClass = "Credit"
ElseIf Marks >= 40 Then
sClass = "Pass"
Else
' For all other marks
sClass = "Fail"
End If

' Write out the class to column E
Range("E" & i) = sClass
Next

End Sub

The following is the same code using a Select Case statement. The main thing you will notice is that we use “Case 85 to 100” rather than “marks >=85 And marks

Sub AddClassWithSelect()

' get the first and last row
Dim firstRow As Long, lastRow As Long
firstRow = 2
lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

Dim i As Long, marks As Long
Dim sClass As String

' Go through the marks columns
For i = firstRow To lastRow
marks = Range("C" & i)
' Check marks and classify accordingly
Select Case marks
Case 85 To 100
sClass = "High Destinction"
Case 75 To 84
sClass = "Destinction"
Case 55 To 74
sClass = "Credit"
Case 40 To 54
sClass = "Pass"
Case Else
' For all other marks
sClass = "Fail"
End Select
' Write out the class to column E
Range("E" & i) = sClass
Next

End Sub

 

Using Case Is

You could rewrite the select statement in the same format as the original ElseIf. You can use Is with Case.

Select Case marks
Case Is >= 85
sClass = "High Destinction"
Case Is >= 75
sClass = "Destinction"
Case Is >= 55
sClass = "Credit"
Case Is >= 40
sClass = "Pass"
Case Else
' For all other marks
sClass = "Fail"
End Select

You can use Is to check for multiple values. In the following code we are checking if marks equals 5, 7 or 9.

Sub TestMultiValues()

Dim marks As Long
marks = 7

Select Case marks
Case Is = 5, 7, 9
Debug.Print True
Case Else
Debug.Print False
End Select

End Sub

 

Try this Exercise

We covered a lot in this post about the If statement. A good way to help you understand it is by trying to write some code using the topics we covered. The following exercise uses the test data from this post. The answer to the exercise is below.

We are going to use cell G1 to write the name of a subject.

In the columns H to L write out all the students who have marks in this subject. We want to classify their result as pass or fail. Marks below 40 is a fail and marks 40 or above is a pass.

Column H: First name

Column I: Second name

Column J: Marks

Column H: Subject

Column I: Result type – Pass or Fail

If cell G1 contains “French” then your result should look like this

Result of exercise

 

Answer to Exercise

The following code shows how to complete the above exercise. Note: There are many ways to complete this so don’t be put off if your code is different.

Sub WriteSubjectResults()

' Get subject
Dim subject As String
subject = Range("G1")

If subject = "" Then
Exit Sub
End If

' Get first and last row
Dim firstRow As Long, lastRow As Long
firstRow = 2
lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

' Clear any existing output
Range("H:L").ClearContents

' Track output row
Dim outRow As Long
outRow = 1

Dim i As Long, marks As Long, rowSubject As String
' Read through data
For i = firstRow To lastRow
marks = Range("C" & i)
rowSubject = Range("D" & i)
If rowSubject = subject Then
' Write out student details if subject French
Range("A" & i & ":" & "D" & i).Copy
Range("H" & outRow).PasteSpecial xlPasteValues

' Write out pass or fail
If marks < 40 Then
Range("L" & outRow) = "Fail"
ElseIf marks >= 40 Then
Range("L" & outRow) = "Pass"
End If
' Move output to next row
outRow = outRow + 1
End If

Next i

End Sub

 

What’s Next?

If you want to read about more VBA topics you can view a complete list of my posts here. I also have a free eBook(see below) which you will find useful if you are new to VBA.

 

Get the Free eBook

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

 

 

The post VBA IF Statement – A Complete Guide appeared first on Excel Macro Mastery.