Pay Packet Calculation Macro

Pay packet calculation macro in Excel:

  • First you see a worksheet with the legend
  • The macro code is then displayed as a text file
  • The code is shown in the Visual Basic Editor
  • In the Visual Basic Editor click on the run button and follow the data input instructions
  • You can then see the input and calculated data under the respective column cells
  • Two methods of accessing cells have been demonstrated – ‘Range’ and ‘Cells’
  • A ‘do while’ loop and an ‘if’ use has been effectively shown
  • The Microsoft Excel macro calculates the ‘perks’ and the total ‘package’.
  • Now you can extend this program for your practice
  • Later on we’ll see how to optimize such a macro
  • Remember you can ‘pause’ the Microsoft Excel training video to take a closer look at the code

The complete macro or VBA code:
Sub paypackage()
Dim empName As String
Dim empSalary As Integer
Dim empAllowances As Double
Range(“A1”).Value = “Employee Name”
Range(“B1”) = “Salary”
Range(“C1”) = “Allowances”
Range(“D1”) = “Package”
Range(“A1:D1”).Font.Bold = True
Row = 2
Dim enterdata As String
Do While enterdata <> “no”
enterdata = Application.InputBox(“Do you wish to enter data? Type any character to continue or ‘no’ to end”)
If enterdata = “no” Then Exit
Do empName = Application.InputBox(“Enter Employee name”, “Employee Name”)
Cells(Row, 1).Value = empName
empSalary = Application.InputBox(“Enter employee’s salary”, “Employee Salary”)
Cells(Row, 2).Value = empSalary
Cells(Row, 3).Value = Cells(Row, 2).Value * 0.5 Cells(Row, 4).Value = Cells(Row, 2).Value + Cells(Row, 3).Value Row = Row + 1 Loop
End Sub
Further reading:
Overview of formulas

3 thoughts on “Pay Packet Calculation Macro

  1. Yaumo

    How do you extract selected data in the body of an email to a field in an excel sheet? My code should be able to:

    1. Validate that the email is from, with current date and the title should be myTitle. If none of the above is met, then the code should give an error message and exit the sub.
    2. Go through body of the email and select the status “running” only as shown below for each process and copy to a cell in excel.
    Example: Find “BizeCacheControl” and extract status “running” only.

    Email Body Sample:

    WARNING: BizeAnnoIndex is not running. Status is Stopped.
    Version 1.0.5

    BizeCacheControl is currently running.
    Version 1.0.3

    BizeNotify is currently running.
    Version 1.4.5

    BizeRepl is currently running.
    Version 2.0.5

    3. If the status is not running, then a value of “Not running” should be placed in the destined cell instead.

    4. The above condition should cater for the four processes listed in the above data sample.

    5. The code should be executed in excel.

    I have downloaded codes but cannot find a suitable one for extracting selected data from the body of the email. The ones I found need to be executed in Outlook which I prefer not to do.

  2. Thilak

    Can you explain how to write vba code if the employee list is already created and to automatically calculate allowances and the salary

  3. Erwin Wabe

    Hi Sir,

    How can I start learning vba from start to finish, I tried your youtube free trial for structured VBA I logged in but it did not work. And how much does it cost to get the training and is it all through youtube?


Leave a Reply

Your email address will not be published. Required fields are marked *