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

1. Initial setup
2. Calculate gross pay
3. Deductions and net pay
4. Employee information
5. Navigation bar
6. Pay stub

---

Initial setup

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

TOC ↑

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.

Calculated formulas

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.

The formulas

• txtRegPay = txtRegHrs * txtRegRate
• txtOtPay = txtOtHrs * txtOtRate
• txtWkndPay = txtWkndHrs * txtWkndRate
• txtTotalHrs = txtRegHrs + txtOtHrs + txtWkndHrs
• TxtGrossPay = txtRegPay + txtOtPay + txtWkndPay

TOC ↑

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.

Sample inputs

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.

Tabbing

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

TOC ↑

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

Now that we have the code in there, we can assign it to the button. Keep in mind, our button is just a regular shape, not like a form button.

Version .4 completed – October 6, 2023

Download version .4 Size: 17 kB

TOC ↑

Data validation

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:

1. We will only accept decimal numbers, starting at zero.
2. For regular hours, the range is 0 to 40.
3. Secondly, overtime hours will go from 0 to 40.
4. Finally, weekend hours will go from 0 to 40. Again, users can enter numbers like 38.25.

Here are the rate restrictions:

1. For regular rate, 0 to 5000.
2. 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.
3. 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

Zoom

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

TOC ↑

---

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

TOC ↑

---

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 update

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

---

Navigation bar

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

TOC ↑

Pay stub

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

---

Support

If you have any questions or comments about the PPP, then send us an email at [email protected].

---

Related

• 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

---

Software home Techronology home

The post PPP – Payroll Processing Program appeared first on Techronology.