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