India Salary tax computation in excel vba

Below we will look at a program in Excel VBA that calculates the tax on an income. The following tax rates apply to individuals who are residents of India.

 

Download the Indian Salary tax computation in excel vba. Tax Computation & CTC Structure 2015-16

 

 

Code in open sheet

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.Sheets(“Tax Computation & CTC Structure”).Visible = xlSheetVisible
ActiveWorkbook.Sheets(“Welcome”).Visible = xlSheetVeryHidden
Application.Calculation = xlCalculationAutomatic
Sheets(“Tax Computation & CTC Structure”).Select
‘Call My_Vba.Resetall
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 1

End Sub

Private Sub Workbook_Activate()
Application.ScreenUpdating = False
ActiveWorkbook.Protect (“password”)
Application.ScreenUpdating = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect (“password”)
ActiveWorkbook.Sheets(“Welcome”).Visible = xlSheetVisible
ActiveWorkbook.Sheets(“Tax Computation & CTC Structure”).Visible = xlSheetVeryHidden
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.Protect (“password”)
ThisWorkbook.Close savechanges:=True
Application.ScreenUpdating = False
End Sub

 


 

following code in work sheet

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells, KeyCells1, KeyCells2, KeyCells3, KeyCells4 As Range

Set KeyCells = Range(“Annual_CTC”)
Set KeyCells1 = Range(“Bonus_Ap”)
Set KeyCells2 = Range(“Basic,Hra,Conv,metronmetro”)
Set KeyCells3 = Range(“Medical,lta,veh,driv,pd”) ‘enta
Set KeyCells4 = Range(“var_paya”)
Set KeyCells5 = Range(“PF_Ap,ESI_Ap,Gratuity_Ap,Bonus_Ap”)
Set KeyCells6 = Range(“Renta”)

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
Call Missmatch
If Range(“Spl_all”).Value < 0 Then
Range(“lta”).Value = 0
Range(“veh”).Value = 0
Range(“driv”).Value = 0
Else: End If

ElseIf Not Application.Intersect(KeyCells1, Range(Target.Address)) Is Nothing Then
Application.DisplayAlerts = False
If Range(“Bonus_Ap”).Value = “Yes” Then
Range(“bonus_amt”).Formula = “=IF(Annual_CTC>0,IF(SUM(reimb_ff)>0,0,10000),0)”
Else
Range(“bonus_amt”).Value = 0

End If

ElseIf Not Application.Intersect(KeyCells2, Range(Target.Address)) Is Nothing Then
Application.DisplayAlerts = False
Call Missmatch

ElseIf Not Application.Intersect(KeyCells3, Range(Target.Address)) Is Nothing Then
Application.DisplayAlerts = False
Call Missmatch
Range(“lta”).Select

ElseIf Not Application.Intersect(KeyCells4, Range(Target.Address)) Is Nothing Then
Application.DisplayAlerts = False
Call Missmatch
Range(“comp_med”).Select

ElseIf Not Application.Intersect(KeyCells5, Range(Target.Address)) Is Nothing Then
Application.DisplayAlerts = False
Call Missmatch
Range(“PF_Ap”).Select

ElseIf Not Application.Intersect(KeyCells6, Range(Target.Address)) Is Nothing Then
Application.DisplayAlerts = False

If Range(“Renta”).Value > 0 Then

If Range(“Renta”).Value > 8333 Then
MsgBox (“Monthly rent is above Rs. 8333/-, You required to report the PAN of the landlord to the employer at the end of year.”)
Range(“rentlocation”).Value = “Delhi”
Range(“from”).Formula = “=MAX(” & “01/04/2014” & “,Effectivedate)”
Range(“to”).Value = “31/03/2016”
Else: End If
Range(“rentlocation”).Value = “Delhi”
Range(“from”).Formula = “=MAX(” & “01/04/2014” & “,Effectivedate)”
Range(“to”).Value = “31/03/2016”
Range(“Renta”).Select
Else: End If
Else: End If
End Sub


Sub Resetall()
On Error Resume Next
Sheets(“Tax Computation & CTC Structure”).Unprotect Password:=”password”
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Range(“Annual_CTC”).Value = “0”

Range(“Basic”).ClearContents
Range(“Basic”).Formula = “=ROUND(Annual_CTC*20%,0)”

Range(“reimb_amt”).Value = “0”
Range(“itamt”).Value = “0”
Range(“var_paya”).Value = “0”

Range(“Conv”).ClearContents
Range(“Conv”).Formula = “=IF(veh>0,0,IF(Annual_CTC>0,19200,0))”

Range(“Medical”).ClearContents
Range(“Medical”).Formula = “=IF(Annual_CTC>0,15000,0)”

Range(“lta”).ClearContents
Range(“lta”).Formula = “=IF(Annual_CTC>0,60000,0)”

Range(“veh”).ClearContents
Range(“veh”).Formula = “=IF(Annual_CTC>0,96000,0)”

Range(“driv”).ClearContents
Range(“driv”).Formula = “=IF(Annual_CTC>0,60000,0)”

Range(“rentlocation”).ClearContents
Range(“from”).ClearContents
Range(“to”).ClearContents

If Range(“Bonus_Ap”).Value = “Yes” Then
Range(“bonus_amt”).Formula = “=IF(Annual_CTC>0,IF(SUM(reimb_ff)>0,0,10000),0)”
Else
Range(“bonus_amt”).Value = 0
End If

Range(“us8c”).Value = “0”
Range(“medis”).Value = “0”
Range(“medps”).Value = “0”
Range(“usE”).Value = “0”
Range(“usU”).Value = “0”
Range(“usdd”).Value = “0”
Range(“usddb”).Value = “0”
Range(“usccd”).Value = “0”

Range(“Renta”).Value = “0”
Range(“ptax”).Value = “0”
Range(“prv_sal”).Value = “0”
Range(“inc_other”).Value = “0”
Range(“Deducted_tax”).Value = “0”

Range(“medbill”).Formula = “=C14”
Range(“medbill”).Select
Selection.Copy
Range(“billsamt”).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(“medbill”).Select

Range(“Emp_name”).Value = “”
Range(“DSG_name”).Value = “”

On Error Resume Next

Sheets(“Tax Computation & CTC Structure”).Protect Password:=”password”
Application.ScreenUpdating = False
End Sub

Sub Missmatch()
On Error Resume Next
Application.ScreenUpdating = False
Sheets(“Tax Computation & CTC Structure”).Unprotect Password:=”password”
Range(“Spl_all”).Select
Selectionvalue = 0
Range(“diffrence”).Select
Selection.Copy
Range(“Spl_all”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets(“Tax Computation & CTC Structure”).Protect Password:=”password”
On Error Resume Next
Application.ScreenUpdating = False
End Sub

Sub PrintSheet()
Application.ScreenUpdating = False
Dim f As Integer, t As Integer, Message, Ans
Application.ScreenUpdating = False

If Range(“Emp_name”).Value = “” Then
Range(“Emp_name”).Select
MsgBox (“Employee Name Shoud not be Blank”)
Exit Sub
End If

If Range(“DSG_name”).Value = “” Then
Range(“DSG_name”).Select
MsgBox (“Employee Designation Shoud not be Blank”)
Exit Sub
End If

If Range(“Annual_CTC”).Value < 1 Then
Range(“Annual_CTC”).Select
MsgBox (“Annual_CTC is not Correct”)
Exit Sub
End If

If Range(“Spl_all”).Value < 1 Then
Range(“Spl_all”).Select
MsgBox (“CTC Structure values are not Correct”)
Exit Sub
End If

Application.ScreenUpdating = False

Ans = MsgBox(“Are you sure want to Print ?”, vbYesNo + vbExclamation, “Confirmation – Yes No”)
If Ans = vbNo Then
Exit Sub
End If
ActiveSheet.PrintOut

Application.ScreenUpdating = False

End Sub