Home and Learn - Free Excel VBA Course

3.8 Excel VBA Practice 3

Add another name to your spreadsheet from practice 1 and practice 2. This time, type the name in cell A3. In cell B3, enter a score. What we'll do is to put a grade in cell C3 and a some text in cell C4. We'll use Select Case.

Add another Sub to your 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:

Excel VBA code demonstrating Select Case

Try it out. Put another button on your form. Select your new Sub from the Assign Macro dialogue box. If you haven't already done it, enter a name in cell A3. Enter a score in cell B3. Now click on cell C3 to make it the ActiveCell. Click your button and you should go from this:

A spreadsheet showing a student's score