1.

Start Excel and open the workbook named Practice 10 in your Excel 2003 Practice folder.

File->Open

This work sheet contains student marks for a series of 29 tests. Your goal in this exercise is to add VBA code to a macro that will prompt the user for a number. Code should be added so that all test marks higher than the number provided by the user are shaded in gold, and all test marks lower than the number provided by the user are shaded in grey.   

2.

To begin, choose Tools->Macro->Macros from the menu bar. This will display the Macro dialogue box.

Type Marks in the Macro name text area. Next, select This Workbook from the Macros in: drop list at the bottom of the dialogue box, and then click Create.

The visual basic window will be displayed, with the opening and closing Sub statements for your new macro.

Now you can start adding code to the macro. The first thing to do is to declare the variables that you will need. You will need a variable to hold the user input, and you will also need a variable to hold the value of a cell as you check every value in the range.

Type the comment ‘ ----Variable Declarations---- in the visual basic window on the line under Sub Marks ().

On the following line, type Dim userInput As Variant

Next type Dim tempCell as Range on the next line

Your macro should now look like this.

The next step is to provide the InputBox that will prompt the user for a number.

3.

On the next line, directly underneath Dim tempCell as Range, type the following comment.

‘ The following line creates an inputBox and assigns the value to userInput

On the line beneath this, type userInput = InputBox(" Please Enter a Number", "Input")

You should now be able to click the  button on the visual basic window menu to run the incomplete macro. You should see the following inputBox.

Click the x in the upper right of the InputBox to close it.

There may be a problem comparing a Variant variable type (like userInput) to a cell value, because we are not sure what a type of value the Variant holds. To overcome this, you should position the cursor directly under the statement

 Dim tempCell As Range 

and type

Dim myInput As Integer

This will declare an integer value that we can use to compare with each cell value.

Next, position your cursor on the next line after

userInput = InputBox(" Please Enter a Number", "Input")

 and type the comment

‘ The next line tests to see if the input is a number

Then, on the following line type

If IsNumeric(userInput) Then myInput = myInput + userInput

The macro code should now look like this.

4.

On the lines immediately below the line you just entered, type the following comments.

‘ The following statements will step through each cell in the range B3:M31

‘and compare myInput to each value in the range changing the cell colour

‘as required. The range will only be traversed if myInput dose not equal 0

Beneath the comments type the following VBA code

For Each tempCell In Range("B3:M31")

If (myInput <> 0) Then

  If ( tempCell < myInput ) Then tempCell.Interior.ColourIndex = 15 ‘Grey

  If ( tempCell >=myInput) Then tempCell.Interior.ColourIndex = 44 ‘Gold

End If

5.

The macro code should now look like this.

Putting an empty space before each comment or block of comments, will make the code easier to read.

6.

There is still one more line of code to add. On the line beneath the End If statement, type

 Next tempCell

This will move the tempCell variable to the next cell in the range, so that the process starting with the For Each statement will repeat for each cell.

You can assign a shortcut key to the macro, by choosing Tools->Macro->Macros from the menu bar, and clicking the options button in the macro dialogue box.

The final VBA code for the macro should look like this.

 You can run the macro by using the run button on the visual basic window tool bar, or by clicking the Run button on the macro dialogue box.

When you run the Marks macro, you will see the following inputBox

Enter the number 65 in the text field and click OK.

Your worksheet should look like the following.

Experiment by running the macro and inputting different values in the inputBox.

When you are finished, save your Work and Exit Excel.