VBA for payroll data entry and analysis

diripio

New Member
Joined
Mar 31, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I am trying to create a macro for a small business to help with their payroll. It would be simply the user typing in the name and the hours worked. The other cells would have formulas to help calculate the net pay. The table below shows an example of what I am doing right now. On one tab, I have the names of the employees and their pay rates. On the other tab, I manually enter the employee names and the ones in green have formulas. I would highlight the columns starting from pay rate to net pay and drag down the formulas to help me with the total deductions and net pay. How can I go about doing on vba userform? Or is there another better way to do this. Once I have a quarter worth of payroll entries, I would like to turn them into visual data.

EmployeePay Rate
Joe$ 8.75
Bill$ 10.50
Tom$ 12.00
Andy$ 8.75
Tony$ 8.75
Jenny$ 8.75
Allah$ 12.75

DateEmployeePay RateRegular Hours WorkedOvertime HoursGrossFICAMedW/HCreditAdvance PaymentTotal DeductionsNet Pay
Tony$ 8.75
80​
15​
$ 896.88$ 55.61$ 13.00$ 68.61$ 828.26
Bill$ 10.50
80​
15​
$ 1,076.25$ 66.73$ 15.61$ 100.00$ 182.33$ 893.92
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
@diripio If you have things established as per above, with the formulas working, is there a real advantage in using vba and userform?
Either way, you will still be required to input just two items of data per employee.
Will you be isolating each quarter by having separate data sets or will you have continuous data with one quarter following on from the previous?

If you select the rows for a quarter that you wish to preserve as hard data then, without changing the selection, Copy > Paste Special > Values, it will convert your formulas to hard values.

It might be an idea to convert your entry data range into an Excel Table. That way you will get alternate contrasting row colour. Provided that you have formulas in the last row of your table, your formulas will automatically be applied as and when you enter new dat a row immediately below the table. You can still use Copy > Paste Special > Values to preserve rows above. You can re-colour rows of a given quarter.

MRXLMAY21.xlsm
ABC
19Stuff1Stuff2Stuff3
20176
21286
22396
234106
245116
256126
26336532
27446-38
Sheet1
Cell Formulas
RangeFormula
C24:C27C24=-[@Stuff1]+[@Stuff2]


Food for thought maybe?
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top