FUNCTIONS

1. TEXT NUMBERS

1. Name a sheet Text Numbers.
2. Create a function called TextNumbers.
3. This function should accept a single parameter that is a number. If the number is not 1, 2, 3, 4, 5 or 6, it should return Invalid Entry. If the number is in the correct range, it should return the text label for the number. For example, 1 should return one and 2 should return two, etc.
4. Use a CASE statement for your logic in the function.
5. Test the function for the numbers 0, 1, 2, 3, 4, 5, 6, and 7 in this worksheet.

2. DATE CHECK

1. Name a sheet Date Check.
2. Create a function called DateCheck. that accepts a single date as an input. Check the entered date to see if it is "Before Today", "After Today", or "Matches Today." The function should return one of these 3 phrases. Note: In VBA, the command Date will return the current date.
3. Test the function for 3 different dates where each creates 3 different answers

PROGRAM SUBROUTINES (EXERCISES modified FROM Albright, Ch. 6 and 7)

3. CODE 1

1. Name a sheet Code Check. Create a sub btn_CheckCode1 and run it from a button in your Worksheet labeled "Enter Code #1"
2. HINT: Always SAVE before you run your code in case you end up with an endless loop!!!
3. Use an Input Box to collect an input from the user. Keep asking the user to enter a code until they enter something (make sure it isnt blank).
4. If they enter something, display a message box that says Thank you for entering XXXX where XXXX is what they entered.
5. Test with your name and put a screenshot of the message box in the sheet

4. CODE 2

1. Create a second button labeled Enter Code #2. Create a sub btn_CheckCode2
2. HINT: Always SAVE before you run your code in case you end up with an endless loop!!!
3. Use an Input Box to collect an input from the user. Suppose that all valid product codes start with C and are followed by four digits. Keep asking the user to enter a code until they enter a validly formatted code.
4. Once they enter a valid code, display a message box that says Thank you for entering XXXX where XXXX is what they entered.
5. Test with your name, 1234, C123, C1234, C12345 and put a screenshot of the message boxes that output in the sheet.

5. FORMAT EMPLOYEE SCORES

The file Employee Scores.xlsx contains 4 different scores for 15 employees. Put three copies of the Sheet1 from Employee Scores.xlsx in your file. One sheet called Employee Scores1, one called Employee Scores 2 and one called Employee Scores Raw. The raw file can be used to recopy the data if you accidently mess it up.

Create a sub called btn_EmployeeScoresReset to do the following to the Employee Scores 1 sheet by calling it from a button on sheet Employee Scores. This Reset button can be used to remove formatting and remove the Average row.

1. Clear the formatting from the range A1 to E19.
2. Clear the values in the range A19 to E19 (remove anything in the average row)

We will do 3 versions of this exercise.

In the first exercise, use direct range addresses:

Range("A1").Font.Bold = True or Cells(1,1).Font.Bold = True.

For the second exercise, you will use range objects:

Dim rgLabel as Range

Set rgLabel = Range("A1")

rgLabel.Font.Bold = True

For the final exercise, you can modify either of the previous 2 exercises to perform those modifications that will accept differing numbers of Employees and/or Scores.

Create a sub called btn_EmployeesScores to do the following formatting to this dataset by calling it from a button on the sheet Employee Scores. Note you will use direct range addresses reference cells.

1. Boldface the font of the label in cell A1 and change its font size to 14.
2. Boldface and italicize the headings in row 3 and change their horizontal alignment to the right.
3. Change the color of the font for the employee numbers in column A to blue.
4. Change the background (the Interior property) of the range with scores to light grey. (Hint: Remember ADPVBA3ProblemSolving.xlsm Problem 2 Solution has all the color indexes)
5. Enter the label Averages in cell A19 and boldface it.
6. Enter a formula in cell B19 that averages the scores above it. Similarly, complete averages for the cells in the range C19:E19.
7. Note you can use the Reset button above to remove the formatting and the Averages and then you can test your code again.

Create a sub called btn_EmployeeScores1 and call it from a button on the sheet Employee Scores 1. Modify your btn_EmployeesScores code so that you use range objects to reference cells. You should create at least 4 range objects:

cell A1 as rgTitle

the headings in row 3 as rgHheadings

the employee numbers in column A as rgEmpNumbers

the range of scores as rgScores

Refer to these range objects as you redo parts a-f.

Finally, we are going to modify one of your previous exercises (you can choose which approach you like/understood the best and use that version) to create a final version of this formatting. Create a sub called btn_EmployeeScores2 that runs from a button on the sheet Employee Scores 2.

For this version, you want to modify your code so that it will work even if more data are added to the data set more scores, more employees, or both. Try your program on the original data and then add an extra column of scores and some extra employees, and see if it still works properly. If not, modify until it works! Also try adding fewer than 15 employees and fewer than 4 scores. Again, see if your code works properly!