No looping/iterations please.
Create a new Excel Workbook named yourlastName P3.xlsm (replacing yourlastName with-your last namel) and write the VBA code to estimate employee P3.xism( payroll information. Create the page/column headings- as illustrated below. Caleulete Pay for Payroll Estimator City State Insurance Pay Bans Worked Rate Reg Pay OT rs OT Rate The user will input data directly into columns A through G (with the expectation of processing data one row at a time-based upon the Active Row), input of the value in column G('Regular Pay Rate"), the user will click a command button in the upper left corner of the worksheet that will execute your VBA subroutine that performs the processing necessary to validate the user input and handle any input errors. If the inputs are valid, the VBA code will also calculate the employee's payroll(columns H through Q) and display the calculated values back to the worksheet Data Validation: Before calculating the row's payroll-first determine that the input in Columns D through G is valid. Use the "Switch" method - so that bad data is identified in a single validation. Highlight each cell with bad data. If any of the data is bad- payroll should NOT be processed Ensure that the value in the cell for Insurance (column D) is valid: Family, Single or None. Anything ee-should generate an error message to the user and end execution of your VBA sub routine. Validate the Pay Basis (column E). Only Hourly or Salary values are valid. Anything else-should generate an error message to the user and end execution of your VBA sub routine. Validate the Hours Worked (column F) for a reasonable value. Anything above 60 hours is questionable and should generate an error message to the user and end execution of your VBA sub routine Validate the Regular Pay Rate (column G) for hourly employees-anything above $50/hour is questionable should generate an error message to the user and end execution of your VBA sub routine. Background Information and Formulas Employees are classified with a Pay Basis of either Hourly or Salary-and payroll processing is based upon that pay basis For Hourly Employees-Regular (Reg) Pay is cakculated as Hours Worked (40 or less) times the employee's Regular Pay Rate. Hourly employees who work more than 40 hours are eligible for overtime pay. OT Hrs is calculated for hourly employees who worked more than 40 hours by subtracting 40 from the hours worked. The Overtime (OT) Rate is calculated by multiplying the Regular Pay Rate times 1.5 and OT Pay is calculated by multiplying OT Hrs times OT Rate. Salary employees are not eligible to earn overtime pay- but are paid their Regular Pay Rate- regardless of the number of hours worked Employees may elect Insurance (column D)-and the employee portion of the cost is deducted from their pay. Insurance options include: Family, Single and None. The employee portion deducted is based upon the option selected. The family coverage costs $125.00, Single costs $50.00, and None has no cost (S0.00). Use a SELECT CASE to validate, evaluate and process the Insurance coverage. The standard withholding tax rates should be defined as named CONSTANTS in your code (using the CONST declaration) and have the following values (these values will not display on the worksheet-though they are used to calculate Federal W/H, State W/H, and City W/H (as defined below): Federal Tax Rate 15% State Tax Rate 5.50% City Tax Rate 2.75% Other formulas needed include: gross pay OT Pay + Reg Pay net pay- gross pay-federal W/H-state W/H-city W/H-Life Ins federal W/H- gross pay federal tax rate state W/H gross pay state tax rate city W/H-gross pay city tax rate ADD a command button in the upper left corner that, when clicked, executes the VBA code to process the Active row of data. When clicked, the program should display the calculated values in the worksheet, as displayed in the sample above. You may test your code with my data-or use your own Payroll Estimator Celoulate Pay for Acthe Raw Hous Reelar Pay Jackson Nance Mentor O None Hourly225 5.0 562.50 0.00 $37 50 $000 $562.50 $84.38 $10.94 $1S47 $0.00 $411.12 Lauren Honaker Etna OH Family SalaryS $250.00 52,250.00 0.00$0.00 S0.00 $2,250.00 533750 S2.5 $61.88 5125.00 51,601.88 .00$0.00 55,000.00 5750.00 $275.00 $337.50 $50.00 51,787.30 . Validate the User Input, terminate execution if the data has errors-otherwise calculate the payroll data in columns H through Q . Calculate the Payroll values based upon the Pay Basis using the formulas provided in the sections above. . Provide background colors for the calculated cells-select one color for Hourly employees and another color for salary employees. For information on the Colorindex property (which gives you a broader pallet)-check out this link: http://analystcave.com/excelyba-colorindex vs-vba-solor-property Coding Requirements: Follow good coding practices-declare variables for ALL inputs to be validated or used within a formula CELL. Meaning-you must declare and use variables for all processing- and only assign the values back to the worksheet after all processing is done. Format your CODE-by using indentation and comments to make your code more readable Organize your code into sections to: . Do NOT colculate directly from OR into a e o Declare your variables o Declare and assign your program named constants o Assign worksheet values to variables o Validate user input o Cakulate required values o Assign calculated values back to the worksheet o Format worksheet data as needed As an insurance policy-BEFORE saving and dosing your Excel document, copy and paste your code into an email to yourself OR to an Notepad file so that you have a copy of your code in case something"unusual" happens. Then, SAVE your Excel document-making certain that it is saved as a Macro-Enabled document. Submit your macro enabled Excel document to the Blackboard Assignment for grading NOTE: No late Assignments will be accepted for this assignment Grading Rubric Processing Requirements Validate User Input Calculate Required Values Format Data as needed Command Button to execute V8A processing Coding Requirements Follow Good Coding Practices: variables, constants, assign data from & to worksheet Format your code for readability: indent Organize your code Error Free Code: Syntax, Execution, & Logic Total Score 30 Ready for a challenge? Once you have the assignment completed as outlined above, you may attempt to add this functionality. Do so by created a second subroutine- modified to attempt these additional instructions: Add a table of data for States-and either do a VLOOKUP (in your VBA code to find the corresponding State Tax W/H rate) or write a loop to lookup the value. If the State does not exist in your table- default to the State tax defined above (5.5%) Add another command button that says "Summarize Payroll" and have it drop in totals at the bottom of each row of columns H, K, L, M, N, O, P and Q-OR create a summary report on a different worksheet. Extra credit for extra effort is always an option in this class. Create a new Excel Workbook named yourlastName P3.xlsm (replacing yourlastName with-your last namel) and write the VBA code to estimate employee P3.xism( payroll information. Create the page/column headings- as illustrated below. Caleulete Pay for Payroll Estimator City State Insurance Pay Bans Worked Rate Reg Pay OT rs OT Rate The user will input data directly into columns A through G (with the expectation of processing data one row at a time-based upon the Active Row), input of the value in column G('Regular Pay Rate"), the user will click a command button in the upper left corner of the worksheet that will execute your VBA subroutine that performs the processing necessary to validate the user input and handle any input errors. If the inputs are valid, the VBA code will also calculate the employee's payroll(columns H through Q) and display the calculated values back to the worksheet Data Validation: Before calculating the row's payroll-first determine that the input in Columns D through G is valid. Use the "Switch" method - so that bad data is identified in a single validation. Highlight each cell with bad data. If any of the data is bad- payroll should NOT be processed Ensure that the value in the cell for Insurance (column D) is valid: Family, Single or None. Anything ee-should generate an error message to the user and end execution of your VBA sub routine. Validate the Pay Basis (column E). Only Hourly or Salary values are valid. Anything else-should generate an error message to the user and end execution of your VBA sub routine. Validate the Hours Worked (column F) for a reasonable value. Anything above 60 hours is questionable and should generate an error message to the user and end execution of your VBA sub routine Validate the Regular Pay Rate (column G) for hourly employees-anything above $50/hour is questionable should generate an error message to the user and end execution of your VBA sub routine. Background Information and Formulas Employees are classified with a Pay Basis of either Hourly or Salary-and payroll processing is based upon that pay basis For Hourly Employees-Regular (Reg) Pay is cakculated as Hours Worked (40 or less) times the employee's Regular Pay Rate. Hourly employees who work more than 40 hours are eligible for overtime pay. OT Hrs is calculated for hourly employees who worked more than 40 hours by subtracting 40 from the hours worked. The Overtime (OT) Rate is calculated by multiplying the Regular Pay Rate times 1.5 and OT Pay is calculated by multiplying OT Hrs times OT Rate. Salary employees are not eligible to earn overtime pay- but are paid their Regular Pay Rate- regardless of the number of hours worked Employees may elect Insurance (column D)-and the employee portion of the cost is deducted from their pay. Insurance options include: Family, Single and None. The employee portion deducted is based upon the option selected. The family coverage costs $125.00, Single costs $50.00, and None has no cost (S0.00). Use a SELECT CASE to validate, evaluate and process the Insurance coverage. The standard withholding tax rates should be defined as named CONSTANTS in your code (using the CONST declaration) and have the following values (these values will not display on the worksheet-though they are used to calculate Federal W/H, State W/H, and City W/H (as defined below): Federal Tax Rate 15% State Tax Rate 5.50% City Tax Rate 2.75% Other formulas needed include: gross pay OT Pay + Reg Pay net pay- gross pay-federal W/H-state W/H-city W/H-Life Ins federal W/H- gross pay federal tax rate state W/H gross pay state tax rate city W/H-gross pay city tax rate ADD a command button in the upper left corner that, when clicked, executes the VBA code to process the Active row of data. When clicked, the program should display the calculated values in the worksheet, as displayed in the sample above. You may test your code with my data-or use your own Payroll Estimator Celoulate Pay for Acthe Raw Hous Reelar Pay Jackson Nance Mentor O None Hourly225 5.0 562.50 0.00 $37 50 $000 $562.50 $84.38 $10.94 $1S47 $0.00 $411.12 Lauren Honaker Etna OH Family SalaryS $250.00 52,250.00 0.00$0.00 S0.00 $2,250.00 533750 S2.5 $61.88 5125.00 51,601.88 .00$0.00 55,000.00 5750.00 $275.00 $337.50 $50.00 51,787.30 . Validate the User Input, terminate execution if the data has errors-otherwise calculate the payroll data in columns H through Q . Calculate the Payroll values based upon the Pay Basis using the formulas provided in the sections above. . Provide background colors for the calculated cells-select one color for Hourly employees and another color for salary employees. For information on the Colorindex property (which gives you a broader pallet)-check out this link: http://analystcave.com/excelyba-colorindex vs-vba-solor-property Coding Requirements: Follow good coding practices-declare variables for ALL inputs to be validated or used within a formula CELL. Meaning-you must declare and use variables for all processing- and only assign the values back to the worksheet after all processing is done. Format your CODE-by using indentation and comments to make your code more readable Organize your code into sections to: . Do NOT colculate directly from OR into a e o Declare your variables o Declare and assign your program named constants o Assign worksheet values to variables o Validate user input o Cakulate required values o Assign calculated values back to the worksheet o Format worksheet data as needed As an insurance policy-BEFORE saving and dosing your Excel document, copy and paste your code into an email to yourself OR to an Notepad file so that you have a copy of your code in case something"unusual" happens. Then, SAVE your Excel document-making certain that it is saved as a Macro-Enabled document. Submit your macro enabled Excel document to the Blackboard Assignment for grading NOTE: No late Assignments will be accepted for this assignment Grading Rubric Processing Requirements Validate User Input Calculate Required Values Format Data as needed Command Button to execute V8A processing Coding Requirements Follow Good Coding Practices: variables, constants, assign data from & to worksheet Format your code for readability: indent Organize your code Error Free Code: Syntax, Execution, & Logic Total Score 30 Ready for a challenge? Once you have the assignment completed as outlined above, you may attempt to add this functionality. Do so by created a second subroutine- modified to attempt these additional instructions: Add a table of data for States-and either do a VLOOKUP (in your VBA code to find the corresponding State Tax W/H rate) or write a loop to lookup the value. If the State does not exist in your table- default to the State tax defined above (5.5%) Add another command button that says "Summarize Payroll" and have it drop in totals at the bottom of each row of columns H, K, L, M, N, O, P and Q-OR create a summary report on a different worksheet. Extra credit for extra effort is always an option in this class
