Vba Cell Assignment Rules

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

 

Contents

Quick Guide to the VBA If Statement

DescriptionFormatExample
If ThenIf [condition is true] Then
    [do something]
EndIf
If score = 100 Then
       Debug.Print"Perfect"
EndIf
If Else If [condition is true] Then
    [do something]
Else
    [do something]
EndIf
If score = 100 Then
       Debug.Print"Perfect"
Else
       Debug.Print"Try again"
EndIf
If ElseIfIf [condition 1 is true] Then
    [do something]
ElseIf [condition 2 is true] Then
    [do something]
EndIf
If score = 100 Then
       Debug.Print"Perfect"
ElseIf score > 50 Then
       Debug.Print"Passed"
ElseIf score <= 50 Then
       Debug.Print"Try again"
EndIf
Else and ElseIf
(Else must come
after ElseIf's)
If [condition 1 is true] Then
      [do something]
ElseIf [condition 2 is true] Then
      [do something]
Else
      [do something]
EndIf
If score = 100 Then
       Debug.Print"Perfect"
ElseIf score > 50 Then
       Debug.Print"Passed"
ElseIf score > 30 Then
       Debug.Print"Try again"
Else
       Debug.Print"Yikes"
EndIf
If without Endif
(One line only)
If [condition is true] Then [do something]If value <= 0 Then value = 0

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

If Range("A1").Value > 5 ThenDebug.Print"Value is greater than five."ElseIf Range("A1").Value < 5 ThenDebug.Print"value is less than five."ElseDebug.Print"value is equal to five."EndIf

 
 

What is If and why do you need it?

The VBA If statement is 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.

 
 
Click Here to Download the Test Data

 
 

Format of the VBA If Then Statement

The format of the If Then statement is as follows

If [condition is true] Then

The If keyword is followed by a Condition and the keyword Then

 
 
Every time you use an If Then statement you must use a matching End If statement.
When the condition evaluates to true, all the lines between If Then and End If are processed.

If [condition is true] Then     [lines of code]     [lines of code]     [lines of code] EndIf

 
 
To make your code more readable it is good practice to indent the lines between the If Then and End If statements.

 
 

Indenting Between If and End If

Indenting simply means to move a line of code one tab to the right. The rule of thumb is to indent between start and end statements like

Sub … End Sub
If Then … End If
If Then… ElseIf … Else … Endif
For … Next
Do While … Loop
Select Case … End Case

 
 
To indent the code you can highlight the lines to indent and press the Tab key. Pressing Shift + Tab will Outdent the code i.e. move it one tab to the left.

You can also use the icons from the Visual Basic Toolbar to indent/outdent the code

Select code and click icons to indent/outdent

 
 
If you look at any code examples on this website you will see that the code is indented.

 
 

A Simple If Then Example

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

Sub ReadMarks() Dim i AsLong' Go through the marks columnsFor i = 2 To 11 ' Check if marks greater than 50If Range("C" & i) > 50 Then' Print student name to the Immediate Window(Ctrl + G)Debug.Print Range("A" & i) & " " & Range("B" & i) EndIfNextEndSub

 
 
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.

 
 

If 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

ConditionThis is true when
x < 5x is less than 5
x <= 5x is less than or equal to 5
x > 5x is greater than 5
x >= 5x is greater than or equal to 5
x = 5x is equal to 5
x <> 5x does not equal 5
x > 5 And x < 10x is greater than 5 AND x is less than 10
x = 2 Or x >10x is equal to 2 OR x is greater than 10
Range("A1") = "John"Cell A1 contains text "John"
Range("A1") <> "John"Cell A1 does not contain text "John"


 
 
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 side equal to the right side”.

 
 
The following table demonstrates how the equals sign is used in conditions and assignments

Using Equals Statement TypeMeaning
LoopUntil x = 5ConditionIs x equal to 5
DoWhile x = 5ConditionIs x equal to 5
If x = 5 ThenConditionIs x equal to 5
For x = 1 To 5AssignmentSet the value of x to 1, then to 2 etc.
x = 5AssignmentSet the value of x to 5
b = 6 = 5 Assignment and Condition Assign b to the result of condition 6 = 5
x = MyFunc(5,6) AssignmentAssign x to the value returned from the function

 
 
The last entry in the above table shows a statement with two equals. The first equals sign is the assignment and any following equals signs are conditions.

This might seem confusing at first but think of it like this. Any statement that starts with a variable and an equals is in the following format

[variable] [=] [evaluate this part]

 
 
So whatever is on the right of the equals sign is evaluated and the result is placed in the variable. Taking the last three assignments again, you could look at them like this

[x] [=] [5]
[b] [=] [6 = 5]
[x] [=] [MyFunc(5,6)]

 
 

Using If 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 ThenDebug.Print"High Destinction"ElseIf Marks >= 75 ThenDebug.Print"Destinction"EndIfEndSub

 
 
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 trueIf Marks >= 75 ThenDebug.Print"Destinction"ElseIf Marks >= 85 Then' code will never reach hereDebug.Print"High Destinction"EndIfEndSub

 
 
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. These 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 ThenDebug.Print"Destinction"ElseIf marks >= 85 And marks <= 100 ThenDebug.Print"High Destinction"EndIf

 
 
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 If Else

The Else statement is used as a catch all. It basically means “if no conditions were true” or “everything else”. In 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 ThenDebug.Print"High Destinction"ElseIf Marks >= 75 ThenDebug.Print"Destinction"ElseIf Marks >= 55 ThenDebug.Print"Credit"ElseIf Marks >= 40 ThenDebug.Print"Pass"Else' For all other marksDebug.Print"Fail"EndIfEndSub

 
 
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 rowDim startRow As Long, lastRow AsLong startRow = 2 lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row Dim i As Long, Marks AsLongDim sClass AsString' Go through the marks columnsFor i = startRow To lastRow Marks = Range("C" & i) ' Check marks and classify accordinglyIf 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"EndIf' Write out the class to column E Range("E" & i) = sClass NextEndSub

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

Results

 
 

Using If And/If Or

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 AsLongFor i = 2 To 11 ' Store marks for current student marks = Range("C" & i) ' Check if marks greater than 50 and less than 75If marks >= 50 And marks < 80 Then' Print first and last name to Immediate window(Ctrl G)Debug.Print Range("A" & i) & Range("B" & i) EndIfNextEndSub

 
 
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 AsLong' Go through the marks columnsFor i = 2 To 11 marks = Range("D" & i) ' Check if marks greater than 50 and less than 80If marks = "History"Or marks = "French"Then' Print first and last name to Immediate window(Ctrl G)Debug.Print Range("A" & i) & " " & Range("B" & i) EndIfNextEndSub

 
 
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.

 
 

Using If And

The AND works as follows

Condition 1Condition 2Result
TRUETRUETRUE
TRUEFALSEFALSE
FALSETRUEFALSE
FALSEFALSEFALSE

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

 
 

Using If Or

The OR keyword works as follows

Condition 1Condition 2Result
TRUETRUETRUE
TRUEFALSETRUE
FALSETRUETRUE
FALSEFALSEFALSE

 
 
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 AsLong subject = "History" marks = 5 If (subject = "French"Or subject = "History") And marks >= 6 ThenDebug.Print"True"ElseDebug.Print"False"EndIfEndSub

 
 

Using If Not

There is also a NOT operator. This returns the opposite result of the condition.

ConditionResult
TRUEFALSE
FALSETRUE

 
 
The following two lines of code are equivalent.

If marks < 40 ThenIfNot marks >= 40 Then

 
 
as are

If True ThenIf Not False Then

and

If False ThenIf Not True Then

 
 
Putting the condition in parenthesis makes the code easier to read

IfNot (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 NothingThen

 
 
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

IfNot mySheet Is NothingThen

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

IfNot (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 AsLong' 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 EndSub

 
 
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 AsLongFor i = 2 To 11 ' Store marks for current student marks = Range("C" & i) ' Check if student passes or failsIf marks >= 40 Then' Write out names to to Column F Range("E" & i) = "Pass"Else Range("E" & i) = "Fail"EndIfNextEndSub

 
 
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 AsLongFor 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") NextEndSub

 
 
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 AsLongFor 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"EndIfNextEndSub

 
 
Using nested IIfs we could do it like this

Sub UsingNestedIIF() Dim i As Long, marks As Long, result AsStringFor i = 2 To 11 marks = Range("C" & i) result = IIf(marks >= 55,"Credit",IIf(marks >= 40,"Pass","Fail")) Range("E" & i) = result NextEndSub

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

 
 

What to Watch Out For

It is important to understand that the IIf function always evaluates both the True and False parts of the statement regardless of the condition.

In the following example we want to divide by marks when it does not equal zero. If it equals zero we want to return zero.

marks = 0 total = IIf(marks = 0, 0, 60 / marks)

 
 
However, when marks is zero the code will give a “Divide by zero” error. This is because it evaluates both the True and False statements. The False statement here i.e. (60 / Marks) evaluates to an error because marks is zero.

If we use a normal IF statement it will only run the appropriate line.

marks = 0 If marks = 0 Then 'Only executes this line when marks is zero total = 0 Else 'Only executes this line when marks is Not zero total = 60 / marks EndIf

 
 
What this also means is that if you have Functions for True and False then both will be executed. So IIF will run both Functions even though it only uses one return value. For example

'Both Functions will be executed every time total = IIf(marks = 0, Func1, Func2)

 
 
(Thanks to David for pointing out this behaviour in the comments)

 
 

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.

Also as we discussed in the last section IIF always evaluates the True and False parts so if you are dealing with a lot of data the IF statement would be faster.

My rule of thumb is to use IIf when it will be simple to read and doesn’t require function calls. 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

SelectCase [variable] Case [condition 1] Case [condition 2] Case [condition n] CaseElseEndSelect

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

Sub AddClass() ' get the last rowDim startRow As Long, lastRow AsLong startRow = 2 lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row Dim i As Long, Marks AsLongDim sClass AsString' Go through the marks columnsFor i = startRow To lastRow Marks = Range("C" & i) ' Check marks and classify accordinglyIf 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"EndIf' Write out the class to column E Range("E" & i) = sClass NextEndSub

 
 
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 <=100”.

Sub AddClassWithSelect() ' get the first and last rowDim firstRow As Long, lastRow AsLong firstRow = 2 lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row Dim i As Long, marks AsLongDim sClass AsString' Go through the marks columnsFor i = firstRow To lastRow marks = Range("C" & i) ' Check marks and classify accordinglySelectCase 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"CaseElse' For all other marks sClass = "Fail"EndSelect' Write out the class to column E Range("E" & i) = sClass NextEndSub

 
 

Using Case Is

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

SelectCase marks Case Is >= 85 sClass = "High Destinction"Case Is >= 75 sClass = "Destinction"Case Is >= 55 sClass = "Credit"Case Is >= 40 sClass = "Pass"CaseElse' For all other marks sClass = "Fail"EndSelect

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 AsLong marks = 7 SelectCase marks Case Is = 5, 7, 9 Debug.PrintTrueCaseElseDebug.PrintFalseEndSelectEndSub

 
 

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.

 
 
Click Here to Download the Test Data

 
 
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 subjectDim subject AsString subject = Range("G1").Value If subject = ""ThenExitSubEndIf' Get first and last rowDim firstRow As Long, lastRow AsLong firstRow = 2 lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row ' Clear any existing output Range("H:L").ClearContents ' Track output rowDim outRow AsLong outRow = 1 Dim i As Long, marks As Long, rowSubject AsString' Read through dataFor i = firstRow To lastRow marks = Range("C" & i).Value rowSubject = Range("D" & i).Value 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 failIf marks < 40 Then Range("L" & outRow).Value = "Fail"ElseIf marks >= 40 Then Range("L" & outRow).Value = "Pass"EndIf' Move output to next row outRow = outRow + 1 EndIfNext i EndSub

 
 

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

 
 

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.

 
 

ElseElseIfIf StatementSelectSelect Case

This documentation is archived and is not being maintained.

Selecting and Activating Cells [Excel 2003 VBA Language Reference]

When you work with Microsoft Excel, you usually select a cell or cells and then perform an action, such as formatting the cells or entering values in them. In Visual Basic, it is usually not necessary to select cells before modifying them.

For example, if you want to enter a formula in cell D6 using Visual Basic, you don't need to select the range D6. You just need to return the Range object and then set the Formula property to the formula you want, as shown in the following example.

For examples of using other methods to control cells without selecting them, see How to reference cells and ranges .

Using the Select Method and the Selection Property

The Select method activates sheets and objects on sheets; the Selection property returns an object that represents the current selection on the active sheet in the active workbook. Before you can use the Selection property successfully, you must activate a workbook, activate or select a sheet, and then select a range (or other object) using the Select method.

The macro recorder will often create a macro that uses the Select method and the Selection property. The following Sub procedure was created using the macro recorder, and it illustrates how Select and Selection work together.

The following example accomplishes the same task without activating or selecting the worksheet or cells.

Selecting Cells on the Active Worksheet

If you use the Select method to select cells, be aware that Select works only on the active worksheet. If you run your Sub procedure from the module, the Select method will fail unless your procedure activates the worksheet before using the Select method on a range of cells. For example, the following procedure copies a row from Sheet1 to Sheet2 in the active workbook.

Activating a Cell Within a Selection

You can use the Activate method to activate a cell within a selection. There can be only one active cell, even when a range of cells is selected. The following procedure selects a range and then activates a cell within the range without changing the selection.



See Also | How to Reference Cells and Ranges | Looping Through a Range of Cells | Referring to Cells by Using Index Numbers | Working with the Active Cell



0 Replies to “Vba Cell Assignment Rules”

Lascia un Commento

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *