PPP is the Payroll Processing Program.
Requirements: Excel 2007 or higher or compatible, on a Windows computer.
Overview of the PPP
For the most part, the Payroll Processing Program will help people organize their payroll directly in Excel. Currently, we are in the development phase, which started on October 4, 2023. Also, we are aware of the many payroll programs out there. However, we just want to develop one for ourselves and for our users.
Key program specifications
- Start date: October 4, 2023
- Expected release date: December 27, 2023
- Supporting application: Excel
In the beginning, PPP may look plain. But, as we go along, you will see nice changes, with great functionality. Hopefully, that will be the case.
Also, keep in mind, this is not a tutorial. It is more of a documentation. However, at certain points, we do provide tips on various aspects of the program.
Furthermore, as you go along through our process, you will be able to download different versions of the PPP along the way.
Table of contents
Firstly, we will set up the page and create a blank interface or canvas.
Page setup for PPP
Here are the main specifications for the page setup.
- Margins: top: .35″, bottom: .5″, left: .25″, right: .25″
- Header: .2″ and footer: .25″
- Orientation: Landscape
Blank PPP interface
So, we will start off with a blue theme, with a light color for the content area.
Version .1 completed – October 4, 2023
Click on the button below to download version .1.
Download version .1 Size: 8 kB
Calculate gross pay
In this section, we will create the gross pay interface and calculate the gross pay for the PPP.
Gross pay interface
The gross pay interface looks good. Right now, we do not have any calculated fields. In the next version, we will perform the calculations. Moreover, we may apply additional changes to the interface.
Version .2 completed – October 5, 2023
Download version .2 Size: 10 kB
Gross pay calculation
Before we put in the formulas for our calculations, we will define names for the input and output boxes.
The following list represents all the names assigned to each box, in the gross pay area.
- Regular hours: txtRegHrs
- Overtime hours: txtOtHrs
- Weekend hours: txtWkndHrs
- Regular pay rate: txtRegRate
- Overtime pay rate: txtOtRate
- Weekend pay rate: txtWkndRate
- Regular pay: txtRegPay
- Overtime pay: txtOtPay
- Weekend pay: txtWkndPay
- Total hours: txtTotalHrs
- Gross pay: txtGrossPay
Instead of referring to a cell, like H5, we will use the descriptive name, such as txtRegHrs. Additionally, we will be able to use these names if we decide to code. Also note, we changed the name of the regular hours box from txtRegHours to txtRegHrs.
Below are the assignments of each name in visual form.
So, we have five calculated boxes: txtRegPay, txtOtPay, txtWkndPay, txtTotalHrs, and txtGrossPay.
Although we gave our calculated boxes names, we will need to modify them to make a formula. Therefore, we will have to use the Name Manager to make these changes.
Below is how we changed txtRegPay to the calculated formula, txtRegHrs * txtRegRate.
Notice how we have the formula for txtRegPay in the Refers to: box.
- txtRegPay = txtRegHrs * txtRegRate
- txtOtPay = txtOtHrs * txtOtRate
- txtWkndPay = txtWkndHrs * txtWkndRate
- txtTotalHrs = txtRegHrs + txtOtHrs + txtWkndHrs
- TxtGrossPay = txtRegPay + txtOtPay + txtWkndPay
Update the calculated boxes
Because we changed txtRegPay to a formula, we need to reassign it to the box, starting with an equal sign, as shown below.
Once you select txtRegPay, you will notice how the formula boxes surround boxes txtRegHrs and txtRegRate, like below.
As a result of confirming our selection, we get the following output.
Since we do not have any inputs for hours and pay rate, our calculated fields show 0 as a result. We no longer need to touch the calculated boxes.
Now, all we have to do is put in some sample hours and pay rates, and watch it go.
Finally, we have a gross pay calculator. Sometimes, people look at the overtime rate as time and a half, which you could make an automatic calculation. However, we want people to have freedom over their inputs. That does not mean we will not change it later.
At the moment, you have to click in each box to input a value. When we get close to completion, we plan to make it where you can just press tab and go to the next input field.
Version .3 completed – October 5, 2023
Download version .3 Size: 11 kB
Clear the gross pay form
Presently, to clear the gross pay form, you have to click into each box and press delete. However, we want to make it easier to do that. Thus, let’s create a button to clear the contents of the gross pay form.
In order to apply code to this button, we will need to save the PPP as a macro-enabled workbook. Going forward, we will be using a .xlsm file.
Code listing to clear gross pay form
'clearGrossPay ' Clear gross pay form Public Sub clearGrossPay() Range("txtRegHrs", "txtRegRate").Value = "" Range("txtOtHrs", "txtOtRate").Value = "" Range("txtWkndHrs", "txtWkndRate").Value = "" End Sub
Version .4 completed – October 6, 2023
Download version .4 Size: 17 kB
When a person enters data in the gross pay form, we only want them to enter numbers of a certain range. For this purpose, we use the data validation tool in Excel.
Here are the hourly restrictions:
- We will only accept decimal numbers, starting at zero.
- For regular hours, the range is 0 to 40.
- Secondly, overtime hours will go from 0 to 40.
- Finally, weekend hours will go from 0 to 40. Again, users can enter numbers like 38.25.
Here are the rate restrictions:
- For regular rate, 0 to 5000.
- The overtime rate range is, 0 to 10000. Yes, we know it is above time in a half for the max regular rate. However, we offer flexibility.
- We offer the same rate for weekend hours, 0 to 10000.
Now, we will show you a snapshot of how to set the regular hours validation.
Although it is not necessary, we want to show a message if the user inputs invalid information.
Test it out
So, we created data criteria and error alerts for all input fields for gross pay. After entering the letter ‘a’ for regular hourly rate, we receive the following message:
Version .5 completed – October 7, 2023
Download version .5 Size: 17 kB
Sometimes, the labels wrap to a second line when we zoom up. Therefore, we decided to make it zoom to 100% when the program opens. Thereafter, user will still be able to control the zoom.
Private Sub Workbook_Open() ActiveWindow.Zoom = 100 End Sub
Deductions and net pay
In addition to starting the deductions form, we had to make updates to the gross pay form. Moreover, we updated the code to clear the gross pay form.
Updated code listing
So, we created a reference with a range of cells, for both the gross pay and deductions forms. This way, we can just have one line of code to clear both forms independently.
'clearDeductions ' Clear deductions Public Sub clearDeductions() Range("refDeductions").Value = "" End Sub 'clearGrossPay ' Clear gross pay form Public Sub clearGrossPay() Range("refGrossPay").Value = "" End Sub Private Sub Workbook_Open() ActiveWindow.Zoom = 100 End Sub
Version .6 completed – October 8, 2023
Download version .6 Size: 21 kB
Starting employee information and navigation bar
Moreover, we updated the total deductions and net pay to include two decimal places.
In the next sections, we work on the employee information and navigation bar. For now, version .7 includes the starting point.
Version .7 completed – October 10, 2023
Download version .7 Size: 21.3 kB
Employee information for PPP
This data input form is starting to look good. We just put the employee information module in there. Believe it or not, we are just beginning. However, getting the interface done in Excel was tricky.
Code to clear employee information was added to the program.
'clearDeductions ' Clear deductions Public Sub clearDeductions() Range("refDeductions").Value = "" End Sub 'clearEmpInfo ' Clear employee information Public Sub clearEmpInfo() Range("refEmpInfo").Value = "" End Sub 'clearGrossPay ' Clear gross pay form Public Sub clearGrossPay() Range("refGrossPay").Value = "" End Sub Private Sub Workbook_Open() ActiveWindow.Zoom = 100 End Sub
Version .8 completed – October 10, 2023
Download version .8 Size: 25.1 kB
Before we create the navigation bar, we have to make additional sheets to navigate to. Therefore, we made a home page and a pay stub sheet. Neither one of those pages are done yet. However, when the user clicks on an item, it has a place to go.
See how the tabs look in Excel below. Most likely, the home page will not have a navbar. Therefore, we put a button in there to go to the PPP sheet.
We think the navigation bar is good to go. Although we have to work on the functionality of the PPP, we will work on the pay stub for now.
Version .9 completed – October 11, 2023
Download version .9 Size: 31.9 kB
With this pay stub, you have room to add two items in the earnings section. Also, in the deductions section, you have space for three more items. We need to fix that, because there is a possiblity of having 12 deductions in the PPP form.
Sample pay stub
Looks like we have some work to do with this form. But, it has the look we want. Almost everything on the pay stub should generate from the PPP tab. In addition, if we clear the PPP form, the pay stub should clear too.
It does not work the way we want yet.
Version .10 completed – October 11, 2023
Download version .10 Size: 38.9 kB
If you have any questions or comments about the PPP, then send us an email at firstname.lastname@example.org.
- Add a holiday or special day to the Yearly Calendar
- Dynamic Yearly Calendar for Excel
- Ultimate Monthly Calendar for Excel
- Variance formula, percent of change