1 Open the weeklySales worksheet of the SALESMAN workbook. Study the following procedure. You will see it is fairly similar to that in Listing 7.4. See if you can see what their differences are. Write and test the procedure.

Option Compare Text 'not case sensitive testing Sub getValidRepName()

Dim thisRepName As Variant Dim myCell As Object Dim isFound As Boolean Worksheets("Weeklysales").Select isFound = False 'assume repName is not found yet

Do Until isFound thisRepName = InputBox(prompt:="enter a rep name") 'For loop tries to find this representative name in list For Each myCell In Range("Rep_name")

If myCell = thisRepName Then 'good - it has been found myCell.Interior.ColorIndex = 4 isFound = True

MsgBox "found at " & myCell.Address End If Next Loop End Sub

2 Open a new workbook. Enter the following values into the range B2 to B6: January, February, March, April and May. Design a VBA program using structured English pseudocode, and from it write a sub procedure that will write the contents of these cells in reverse order in the cells from D2 to D6. (Hint: write the contents of the range into an array and then output the array in reverse order in the destination range using For num = 5 To 1 Step -1).

3 Open the weeklySales worksheet of the SALESMAN workbook. Go to the VBE and write a VBA procedure that will input from the user, the first two characters of a rep's name and check each cell in the rep_name range to see if it contains these letters. If it does, the program should output the message "found" with the corresponding rep name. The program should then continue checking to see if any other names contain the letters and do the same again if other names are found. Run and test your program.

4 Extend the sub procedure that you have written in (2) above by displaying a message box which counts the number of reps whose names begin with the same two first characters.

Write two VBA procedures that will prompt the user to input their age as an integer variable. One procedure should then check the value input is less than 120 using a Do. While loop. The other procedure should use a Do. Until loop. Check to see that your program works correctly using both structures.

0 0

Post a comment