VBA – Project #540 PointsDownload and open Project 5.xlsm. Save it as yourLastName_P5.xlsm (replacing yourLastName with – your last name!) and write the VBA code to completethe assignment requirements outlined in this document.The high-level view of this project is that it requires you to develop a Payroll Solution that achieves the following:1. Delivers a user … Continue reading “VBA – Project #5 | My Assignment Tutor”
VBA – Project #540 PointsDownload and open Project 5.xlsm. Save it as yourLastName_P5.xlsm (replacing yourLastName with – your last name!) and write the VBA code to completethe assignment requirements outlined in this document.The high-level view of this project is that it requires you to develop a Payroll Solution that achieves the following:1. Delivers a user interface to facilitate adding employee data to the Payroll Worksheet2. Delivers a solution that processes the data on the Payroll Worksheet and prepares it for delivery to our Payroll Service provider (ADP) who creates theACH records for our bi-weekly payroll3. Delivers a Payroll Summary Report that summarizes totals for the Payroll data to be sent to ADPDeliverable 1: A User Interface for adding employees to the Payroll worksheet (one at a time)Insert a new worksheet and design an input area where a user will enter a new employee’s payrolldata. Data to be captured includes: Employee ID, Name, City, State, Department, Health Insurance,Pay Basis, Hours Worked and Rate of Pay. The layout could be similar to this screen shot, though Iwould expect a better user interface that includes headings, better formatting and some styling thatmakes the input area attractive and the process intuitive.EmpID’s should be assigned by the system, and not entered by the user. The user will input datadirectly onto the worksheet – and click a button to add the data to the Payroll Worksheet. When theuser clicks the button to add the data, validate the inputs as defined here to ensure the integrity of thedata in our payroll system.Data Validation:Use the “Switch” method – so that bad data is identified in a single validation. Highlight each cell that contains bad data. If the data is bad – the row shouldnot be added to the Payroll worksheet.• Ensure that the user has provided a value for the Employee Name, City, State and Department cells.• The Health Insurance value represents a deduction withheld from the employee’s pay corresponding to the level of coverage chosen. Ensure that thevalue in the cell for Health Insurance cell is valid: Family, Single or None. To simplify payroll processing, rewrite the data back to the input area in uppercase (UCASE() function) – for example: (Range(“B3”) = Ucase(range(“b3”)• Validate the Pay Basis. Only Hourly or Salary values are valid. To simplify payroll processing, rewrite the data back to the input area also in upper case.• Validate the Hours Worked for a reasonable value. Anything above 60 hours is invalid.• Validate the Rate of Pay for Hourly employees – anything above $50 / hour is invalid. There is no upper limit for employees paid on a Salary basis.If any value is invalid, highlight the cell, display a descriptive message box and end the processing. DO NOT WRITE BAD DATA to the Payroll worksheet.If the data is valid, process it:• Find the last row of data on the Payroll Worksheet• Assign the new Employee and ID. Find the last Employee ID in that last row of the Payroll worksheet, increment it by 5 and write that value back tothe Employee ID cell on the Input area.• Write each value from the input worksheet to the first blank row of the payroll worksheet (last row + 1) – mapping the input data to the Payroll Datacolumns. For a good challenge, find a way to use a looping mechanism for this step. Think about how the Cells(row,col) object might serve thatpurpose.• Format numeric data written to the Payroll worksheet, as needed.• Display a descriptive message box to inform the user that the employee was added. Provide information about the row# and the assigned EmpID.Deliverable 2: Process Payroll Worksheet Data to Calculate PayrollYou will process all rows of the Payroll worksheet to calculate the data for the columns pictured below.Formulas for each column are provided in the followingsection.Employees are classified as HOURLY or SALARY and Reg Pay, OT Hrs, OT Rate, and OT Pay (Columns J, K, L, M) are calculated using the Pay Basis (Column G).When Pay Basis = Hourly: Hourly Employees are paid for each hour they work. Any hours worked above 40 are deemed “Overtime” and are paid at a higherwage. (Regular) Reg Pay is calculated as Hours Worked (up to max of 40) times the employee’s Regular Pay Rate. Hourly employees who work more than 40hours 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.When Pay Basis = Salary: Salary employees are paid a flat wage. Their (Regular) Reg Pay is the same as their Regular Pay Rate. You do not calculate a salaryperson’s Regular Pay Rate times Hours Worked. They are not eligible to earn overtime pay. A Salary employee’s OT Hrs and OT Rate, and OT Pay shouldalways display as 0.Gross Pay (Column N) = Reg Pay + OT PayThe next 3 columns (Columns O, P, Q) represent money withheld from the employee’s pay by the employer. They are calculated using standardwithholding rates. The standard withholding tax rates should be defined as named CONSTANTS in your code (using the CONST declaration) and have thefollowing values (these values do not display on the worksheet – though they are used to calculate Fed 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% Fed W/H (Column O) = gross pay * federal tax rateInsurance (column R) is aso a deduction and is determined by the value of Health Ins (Column F)State W/H (Column P) = gross pay * state tax rateIf Health Ins is: Family, Insurance = $125.00City W/H (Column Q) = gross pay * city tax rateSingle, Insurance = $50.00None, Insurance = $0.00 Net Pay (Column S) is the amount the employee actually receives. Net pay = Gross Pay – Fed W/H – State W/H – City W/H – InsuranceConnect the Calculate Payroll command button to your VBA code, so that when clicked, the payroll is calculated for each row in the payroll worksheet.Deliverable 3: Produce a Summary of the Processed PayrollAs another output of processing the payroll data, produce the following Summary in the area provided (just left of the Payroll data).Accumulate each row as part of the loop that processes the worksheet data (Deliverable 2). Use accumulator statements. (Youmay NOT write a sum function in the cells AND you may not use the WorksheetFunction.SUM function in VBA).Coding Requirements:• Write your code in a code module (not a sheet or workbook code sheet). This assignment requires the creation of two Subroutines:o One to capture and validate inputs and write data to the Payroll worksheet, ando One to Process the payroll data and produce the summary report.• Follow good coding practices – declare variables for ALL inputs used within a validation, calculation or formula. Do NOT calculate directly from ORinto a CELL. Meaning – you must declare and use variables for all payroll processing – and only assign the values back to the worksheet after allprocessing is done. The only exception is for Deliverable 1 – which is spelled out in the assignment introduction.• Format your CODE – by using indentation and comments to make your code more readable.• Organize your code into sections to:o Declare your variableso Declare and assign your program named constantso Assign worksheet values to variableso Calculate required valueso Assign calculated values back to the worksheeto Format worksheet data as neededThere is a hidden worksheet calledBackupData – there in case you need to resetthe data in the payroll worksheet. You cancopy and paste the data, if needed.As an insurance policy – BEFORE saving and closing your Excel document, copy and paste your code into an email to yourself OR to a Notepad file so that youhave a copy of your code in case something “unusual” happens. Then, SAVE your Excel document – making certain that it is saved as a Macro-Enableddocument. Submit your macro enabled Excel document to the Blackboard Assignment for grading.NOTE: No late Assignments will be accepted for this (or any) assignment.Grading Rubric Deliverable 1: Input, Validate and Write New Employee DataPtsWell-designed User Interface3Validate User Input – with switch logic5Generate EmpID2Write New Employee to Payroll WorksheetUsing a Loop2+2 (extra credit)Format Data1Command Button to execute VBA processing1Total for Deliverable 112Deliverable 2: Process Payroll DataLoop to process all worksheet data2Correct Calcs for Hourly, Salary Payroll6Correct Calcs for Withholdings, Gross and Net Pay3Format Data on Payroll Analysis2Command Button to execute VBA processing1Total for Deliverable 214Deliverable 3: Summary ReportAccumulate data for Summary Report (accumulator statements – NO SUM() functions )2Correct Results2Format Data in Summary report2Total for Deliverable 36Best PracticesFollow Good Coding Practices: variables, constants, assign data from & to worksheet3Organize code: Dim’s, Inputs, Assignments, Processing, Output and Format Output3Format your code for readability: indent2Error Free Code: Syntax, Execution-6Total for Best Practices8Total Score40
