VBA Practice 3

Add another name to your spreadsheet, in cell C1. In cell C2, enter a score. What we'll do is to put a grade in cell C3 and a comment in cell C4. We'll use Select Case, this time.

Add another Sub to code. Call it SelectCaseTest . We start the coding the same as before:

Dim score As Integer

score = ActiveCell.Value

We then start our Select Case:

Select Case score

The Select Case will examine what is in the variable called score . The first Case to evaluate as either TRUE or FALSE is this:

Case 0 To 35

This says, "is it the Case that score has a value from 0 to 35?".

If the score is indeed 0 To 35 then we can execute some code:

ActiveCell(1, 2).Value = "F"

ActiveCell(1, 2).HorizontalAlignment = xlCenter

ActiveCell(1, 3).Value = "Terrible - needs attention"

The first two lines are the same as before. The third line puts the text "Terrible - needs attention" in the cell next to the grade.

We can fill out the possible score values in the same way as for the first Case. Here's the full code:

Try it out. Put another button on your form. Select your new Sub from the Assign Macro dialogue box. Enter a name in cell C1. Enter a score in cell C2. Now click on cell C2 to make it the ActiveCell. Click your button and you should go from this:

To this:

With ... End With

In a previous lesson, you had code like this:

ActiveCell(1, 2).Value = "F"

ActiveCell(1, 2).HorizontalAlignment = xlCenter

ActiveCell(1, 3).Value = "Terrible - needs attention"

We were accessing various properties of the ActiveCell. In the code above, we have ActiveCell three times. However, you can speed up your subroutines by using the repeated object only once, on the first line. You then type the property you need after a dot. The syntax is this:

With object

.property

End With

As an example, suppose we want to change various aspects of the ActiveCell. We want to the font name, the font size, the boldness, the italics, etc. We could do it like this:

ActiveCell.Font.Bold = True

ActiveCell.Font.Color = vbBlue

ActiveCell.Font.Name = "Arial"

ActiveCell.Font.Size = 22

ActiveCell.Font.Italic = True

But notice the repetition here. We've used ActiveCell.Font five times. By using a With Statement, we can just type the ActiveCell.Font once.  Like this:

With ActiveCell.Font

​ .Bold = True

​ .Color = vbBlue

​ .Name = "Arial"

​ .Size = 22

​ .Italic = True

End With

So you start with the word With . After a space, you type the name of the object you want to manipulate. We want to manipulate the Font property. This has lots of properties of its own. These ones:

Type a dot and then the name of the Font property in the above list that you want to change. The equal sign and the value are used in the normal way. The whole thing end with the words End With .

With Statements are quite intuitive, so we don't really need to say too much about them. But just remember: if you're typing the same object over and over, you might do better to use a With … End With statement.

OK, that's enough of Conditional Logic. We'll move and take a look at another variable type that's quite common – As String .