Excel VBA Nested Ifs

Basic Description

You can nest one (or even more than one) If Statement inside another. Examine the following code:

Nested IFs

An Integer variable has been set up called score. This has been set to a value of 27. The first If Statement, the outer one, is this:

If score > 20 And score < 30 Then

So we want to test score to see if it’s greater than 20 AND less than 30. If it’s not, then we have an Else part:

Else
MsgBox “Not between 20 and 30”

However, If score is indeed between 20 and 30 then the outer If Statement evaluates to TRUE. In which case, the code that gets executed is another If Statement. This one:

If score < 25 Then
MsgBox “A-”
Else
MsgBox “A+”
End If

This new If Statement checks to see if score is less than 25. If it is, then we know that the variable called score is between 20 and 25. A score of between 20 and 25 gets the message “”A-“. Any other result and we know that score is between 25 and 30. In which case, the message is “A+”.

Nested If Statements are a great way to narrow down your search for a particular value. They can be tricky to use, though. But stick with them and your programming skills will come on a treat!

 

Practice 1 – If Statement

Suppose we had a spreadsheet with a person’s name in cell A1 and a score in cell B1. We’d like to examine this score and see if it falls within a certain range. If it’s 85 or above, for example, we’d like to award a grade of “A”. We want this grade to appear in cell C1. As well as placing a grade in cell C1, we want to change the background color of the cell.

So we want to go from this:

practice1-1

to this:

practice1-2

 

 

Practice 1 – If Statement (Solution)

We want this to happen on the click of a button. So, the question is, How can we do all this with VBA code? Let’s start by breaking the problem down. To make a start, go back to your spreadsheet from previous slide and type a name in cell A1 (the name can be anything you like). Type a score of 90 in cell B1.

Now go back to your coding window. Create a new Sub and call it SetGrades. As the first line of your code, set up an integer variable called score:

Dim score As Integer

What we want to do is to place the value from B1 on our spreadsheet inside of this score variable. To get a value from a cell, you can use ActiveCell.Value. This goes on the right of an equal sign:

score = ActiveCell.Value

The ActiveCell is whichever cell is currently selected. The Value refers to whatever you have typed inside the cell. This Value is then assigned to the score variable. Remember: anything you type to the right of an equal sign is what you want to store. The variable on the left of the equal sign is where you are storing it. But I strongly suggest you to use a specific cell range instead of ActiveCell. We will learn about ActiveCell details in later articles. So, write below code instead of previous one:

score = range(“B1”).Value

Now your coding window should now look like this:

practice1-3

Now that we have a score from the spreadsheet, we can test it with an If Statement. We want to check if it’s in the range 90 to 100. The If Statement to add to your code is this:

If score >= 90 And score <= 100 Then
End If

You should know what the above does by now. If not, revise the section on conditional and logical operators. If the score is indeed greater than or equal to 90 and less than or equal to 100 then the first thing we need to do is place an “A” in cell C1.

ActiveCell Referencing (Not Re-commanded):

May be you have a question, Why I introduced ActiveCell when its not recommended from my side? The only reason is that you may be faced some problem with ActiveCell in future. When you use ActiveCell you can point to another cell. You do the pointing with Row and Column numbers. These are typed between round brackets. For example, to point to the currently ActiveCell (the cell you have clicked in to select) the numbers you need to type are 1, 1:

ActiveCell(1, 1).Value
If you want to move one column over from where you are (1, 1) then you add 1 to the Column position:

ActiveCell(1, 2).Value
If you wanted to move one column to the left of where you are, you deduct 1:

ActiveCell(1, 0).Value
To move two columns to the left, you’d need a minus number:

ActiveCell(1, -1).Value
You can move up and down the rows in a similar way – just add or deduct from the first 1 between round brackets.

We want to type an “A” in cell C1, which is one column to the right of the ActiveCell. The code to do that is this:
ActiveCell(1, 2).Value = “A”

So we’re storing “A” into the Value property of ActiveCell(1,2).

Your coding window should now look like this:

practice1-4

Now change it to:

practice1-5

You can test it out at this stage. Go back to your spreadsheet. Add a new button and select SetGrades from the Assign Macro dialogue box. Change the button text to Set Grades. Now click inside your B1 cell, the cell with the score of 90. When you click your button, the letter “A” will appear next to it, in cell C1. There are only two things left to do, now: change the background color of cell C1 from white to green, and center the text.

Changing the Background Color of a Cell:

As well as Cell having a Value property, it also has an Interior.Color property, and an Interior.ColorIndex property. You can use either of these to set the background color of a cell. If you want to use Interior.Color then after an equal sign, you need to specify an RGB color:

Range(“C1”).Interior.Color = RGB(0, 255, 0)

RGB colors use the numbers 0 to 255 to set a Red, a Green, and a Blue component. If you want full Red, you set the R position to 255 and the Green and Blue parts to 0: RGB(255, 0, 0). If you want full Green you set its position to 255 and switch the other two positions to 0: RGB(0, 255, 0). Likewise, Blue has 255 in its position and 0 in the R and G positions: RGB(0, 0, 255). If you want White, you switch all positions to 255: RGB(255, 255, 255). If you want Black, you switch all the positions to 0: RGB(0, 0, 0)

Changing the Background Color of a Cell:

You can have mixture of colors by setting the various positions to any number between 0 and 255:

RGB(255, 255, 0)
RGB(100, 100, 255)
RGB(10, 10, 100)

The Interior.ColorIndex property, on the hand, uses a single number to return a color:

Range(“C1”).Interior.ColorIndex = 1

The numbers are built-in constants. This means that the number 1 stands for Black, the Number 2 for White, the number 3 for Red, and so on up to a value of 56. The problem with using ColorIndex, though, is that the index numbers don’t really correspond to a color – the index number is simply the position of the color in the Excel color palette. So the first color in the palette is index 1, the second color index 2, the third color index 3, etc. ColorIndex 4 is a green color at the moment. But if Microsoft reordered its color index, the new color at position 4 might end up being red!

Add the following line to your code:

Range(“C1”).Interior.Color = RGB(0, 255, 0)

If you like, though, try a ColorIndex instead:

Range(“C1”).Interior.ColorIndex = 4

Your code will then look like this:

practice1-6

If you want to clear a background color from a cell you can use the xlColorIndexNone constant:

Range(“C1”).Interior.ColorIndex = xlColorIndexNone

 

Cell Content Alignment:

You can align data in a spreadsheet cell with the properties HorizontalAlignment and VerticalAlignment. After an equal sign, you then type one of the following constants for HorizontalAlignment:
xlCenter
xlLeft
xlRight

For VerticalAlignment the constants are these:

xlBottom
xlCenter
xlTop

So to align the contents of your cell in the center, the code would be this:

Range(“C1”).HorizontalAlignment = xlCenter

If you wanted to right-align the contents, the code would be this:

Range(“C1”).HorizontalAlignment = xlRight

If you only wanted bottom-left for your cell alignment, you only need a vertical alignment of bottom:

Range(“C1”).VerticalAlignment = xlBottom

 

Add one of the alignment options to your own code. Try the following:

ActiveCell. HorizontalAlignment = xlCenter

Now you can also repeat this for other cells or change the range above for cell C1 to A1:C1 if you want. The whole of your code should now look like this:

practice1-7

Result:

Click your button and try it out. When the code is run, your spreadsheet should look like ours below:

Result

OK, save your work as you’ll need the spreadsheet for Excel VBA practice 2 in the next article.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment