Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Generate Payroll in Excel VBA (with Easy Steps)

Are you an HR professional and know how to make an employee payroll in Excel? Then, look no further, because in the following tutorial we’ll demonstrate a step-by-step guide on how to generate payroll in Excel VBA. In addition, we’ll also learn to automate payroll in Excel.


Download Practice Workbook


2 Steps to Generate Payroll in Excel VBA

First of all let’s consider the Employee Information dataset shown in the B4:H15 cells which contains the “Employee Name”, “Pay Rate”, “Total Work Time”, “Overtime Rate”, “Total Overtime”, “Health Insurance”, and “Other Deductions” columns respectively. So, let’s explore how to generate payroll in Excel VBA with the necessary illustrations.

Dataset for payroll in excel vba

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


📌 Step 01: Insert the VBA Code into a Module

  • First, navigate to the Developer tab >> click the Visual Basic button.

Insert the VBA Code into a Module

Now, this opens the Visual Basic Editor in a new window.

  • Second, go to the Insert tab >> select Module.

Inserting Module in VBA window

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Sub Calculate_Gross_Pay()

Worksheets("VBA Code").Range("I6:I15").Formula = "=(C6*D6)+(E6*F6)"
Range("I6:I15").NumberFormat = "$#,###"

End Sub

Sub Calculate_Tax()

Worksheets("VBA Code").Range("J6:J15").Formula = "=0.2*I6"
Range("J6:J15").NumberFormat = "$#,###"

End Sub

Sub Calculate_Net_Payment()

Worksheets("VBA Code").Range("K6:K15").Formula = "=I6-(J6+G6+H6)"
Range("K6:K15").NumberFormat = "$#,###"

End Sub

Code for payroll in excel vba

Code Breakdown:

Now, in the following section, we’ll explain the 3 VBA Macros.

  • In the first macro, “Calculate_Gross_Pay”, use the Worksheet and Range objects to apply the formula to the I6:I15 range of cells located in the “VBA Code” worksheet.
  • Next, utilize the Number Format property to format the results as Currency.
  • Similarly, compute the “Tax” and “Net Payment” by executing the second and third macros respectively.

Code explanation for payroll in excel vba

Read More: How to Create Payroll Calculator in Excel (with Easy Steps)


📌 Step 02: Run the VBA Macros

  • Third, close the VBA window >> click the Macros button.

This opens the Macros dialog box.

  • Following this, select the “Calculate_Gross_Pay” macro >> hit the Run button.

Run the VBA Macros

  • In turn, choose the “Calculate_Tax” macro >> press Run.

  • Last but not least, execute the “Calculate_Net_Payment” macro.

Executing VBA Macro

Subsequently, the results should appear in the animated GIF given below.

GIF showing the results for payroll in excel vba

Read More: How to Make a Payroll System in Microsoft Excel with Payslip


How to Automate Payroll in Excel

Alternatively, we can also automate payroll in Excel using the VLOOKUP function to pull in data from a source worksheet to calculate the employee payroll. Now, let’s assume the Employee Information of Wilbur and Company dataset shown in the B4:J14 cells which depicts the “Employee ID”,  “Name”, “Department”,  “Pay Rate”, “Total Work Time”, “Overtime Rate”, “Total Overtime”, “Health Insurance”, and “Other Deductions”.

How to Automate Payroll in Excel

📌 Steps: 

  • At the very beginning, go to the C8 cell >> move to the Data tab and insert a Data Validation drop-down by following the steps shown in the GIF.

Data Validation Steps

  • Then, navigate to the C9 cell >> insert the expression below.

=VLOOKUP(C8,'Employee Information'!B4:$J$14,2,FALSE)

Here, the C8 cell refers to the “Employee ID”.

Formula Breakdown:

  • VLOOKUP(C8,’Employee Information’!B4:$J$14,2,FALSE) → looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here, C8 ( lookup_value argument) is mapped from the B4:J14 (table_array argument) array. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → Max

📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.

Using VLOOKUP function

  • In a similar style, use the VLOOKUP function to retrieve information related to “Max” and in each case specify the correct column number. For instance, the column number for “Department” is “3”, for “Pay Rate” is “4”, and so on.
  • Not long after, apply the SUM function to calculate the “Total Deductions”.

=SUM(E11:E12)

In this case, the E11 and E12 cells indicate the deductions for “Health Insurance” and “Other Deductions”.

Using SUM function

  • Afterward, copy and paste the formula into the C16 to obtain the “Gross Pay”.

=(C11*C12)+(C13*C14)

For example, the C11, C12, C13, and C14 cells represent the “Pay Rate”, “Total Work Time”, “Overtime Rate”, and “Total Overtime” respectively.

Calculating Gross Payment in payroll in excel

  • Later, calculate the “Tax” using the following expression.

=E13*C16

In this situation, the C16 and E13 cells point to the “Gross Payment” and “Tax Rate”.

Computing Tax in payroll in excel

  • Consequently, insert the formula into the Formula Bar to compute the Net Payment.

=C16-(C17+E14)

For example, the C16 and C17 cells refer to the “Gross Payment” and “Tax Rate” whereas the E14 cell represents the “Total Deductions”.

Calculating Net Payment payroll in excel

Read More: How to Calculate Payroll Overtime with Formula in Excel


Things to Remember

There are a few things to keep in mind when running the VBA Code.

  • In the first place, make sure to change the worksheet name to “VBA Code” or specify the correct worksheet name.

Worksheet name for payroll in excel vba

  • Second, specify the appropriate range of cells where to return the calculated output.

Range of cells in payroll in excel vba


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Conclusion

To sum up, we hope this article helps you understand how to generate payroll in Excel VBA. Now, if you have any queries, please leave a comment below. And, you can read more articles like this on our website ExcelDemy.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo