-
Notifications
You must be signed in to change notification settings - Fork 0
/
Module2.bas
133 lines (118 loc) · 9.34 KB
/
Module2.bas
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Sub calc_TaxatNormalRate()
Dim ResStatus As String
Dim isSenior As Boolean
Dim gender As String
Dim dob As String
Dim Status As String
Const seniorDate As String = "01/04/1946"
Status = Mid(ThisComponent.Sheets(1-1).getCellRangeByName("sheet1.Status").value, 1, 1)
ResStatus = Mid(ThisComponent.Sheets(1-1).getCellRangeByName("sheet1.ResidentialStatus1").value, 1, 3)
gender = Mid(ThisComponent.Sheets(1-1).getCellRangeByName("sheet1.Gender1").value, 1, 1)
dob = Dformat1(ThisComponent.Sheets(1-1).getCellRangeByName("sheet1.DOB"), "yyyy-mm-dd")
If CheckDateMaxDDMMYYYY(dob, 31, 3, 1946) = True Then
isSenior = True
Else
isSenior = False
End If
'Sheet5.Range("Calc_SplRate").value = Round(Sheet17.Range("SI.TotSplRateIncTax2").value)
If Status = "H" Then
'Sheet17.Range("THRESOLD").Value = Round(Sheet5.Range("HUF_TH").Value)
ThisComponent.Sheets(5-1).getCellRangeByName("TXN_CALC").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("HUF").value)
ThisComponent.Sheets(5-1).getCellRangeByName("avgratetax").value = ThisComponent.Sheets(5-1).getCellRangeByName("HUF_AVG").value
ThisComponent.Sheets(5-1).getCellRangeByName("Rebate_AgriInc_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("HUF_rebate").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_SplRate").value = ThisComponent.Sheets(17-1).getCellRangeByName("SI.TotSplRateIncTax").value
ThisComponent.Sheets(5-1).getCellRangeByName("Sur_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("HUF_Surcharge").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Clac_MR").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("HUF_MR").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_NetSur").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("HUF_NetSur").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_ED").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("HUF_ED").value)
ThisComponent.Sheets(5-1).getCellRangeByName("avgratetax").value = ThisComponent.Sheets(5-1).getCellRangeByName("HUF_AVG").value
ElseIf ResStatus = "RES" And isSenior Then
'Sheet17.Range("THRESOLD").Value = Round(Sheet5.Range("RES_senior_TH").Value)
ThisComponent.Sheets(5-1).getCellRangeByName("TXN_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("RES_senior").value)
'Sheet5.Range("Calc_SplRate").Value = Sheet17.Range("SI.TotSplRateIncTax").Value
ThisComponent.Sheets(5-1).getCellRangeByName("Rebate_AgriInc_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("RES_senior_rebate").value)
'Sheet5.Range("Calc_SplRate").Value = Sheet17.Range("SI.TotSplRateIncTax").Value
ThisComponent.Sheets(5-1).getCellRangeByName("Sur_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("RES_senior_Surcharge").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Clac_MR").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("RES_senior_MR").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_NetSur").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("RES_senior_NetSur").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_ED").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("RES_senior_ED").value)
ThisComponent.Sheets(5-1).getCellRangeByName("avgratetax").value = ThisComponent.Sheets(5-1).getCellRangeByName("RES_senior_AVG").value
ElseIf ResStatus = "RES" And gender = "F" Then
'Sheet17.Range("THRESOLD").Value = Round(Sheet5.Range("Res_F_TH").Value)
ThisComponent.Sheets(5-1).getCellRangeByName("TXN_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_F_TXN").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Rebate_AgriInc_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_F_rebate").value)
'Sheet5.Range("Calc_SplRate").Value = Sheet17.Range("SI.TotSplRateIncTax").Value
ThisComponent.Sheets(5-1).getCellRangeByName("Sur_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_F_Surcharge").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Clac_MR").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_F_MR").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_NetSur").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_F_NetSur").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_ED").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_F_ED").value)
ThisComponent.Sheets(5-1).getCellRangeByName("avgratetax").value = ThisComponent.Sheets(5-1).getCellRangeByName("Res_F_AVG").value
ElseIf ResStatus = "RES" Then
'Sheet17.Range("THRESOLD").Value = Round(Sheet5.Range("Res_M_TH").Value)
ThisComponent.Sheets(5-1).getCellRangeByName("TXN_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_M_TXN").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Rebate_AgriInc_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_M_rebate").value)
'Sheet5.Range("Calc_SplRate").Value = Sheet17.Range("SI.TotSplRateIncTax").Value
ThisComponent.Sheets(5-1).getCellRangeByName("Sur_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_M_Surcharge").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Clac_MR").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_M_MR").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_NetSur").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_M_NetSur").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_ED").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("Res_M_ED").value)
ThisComponent.Sheets(5-1).getCellRangeByName("avgratetax").value = ThisComponent.Sheets(5-1).getCellRangeByName("Res_M_AVG").value
ElseIf ResStatus = "NRI" Then
'Sheet17.Range("THRESOLD").Value = Round(Sheet5.Range("NRI_TH").Value)
ThisComponent.Sheets(5-1).getCellRangeByName("TXN_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Rebate_AgriInc_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_rebate").value)
'Sheet5.Range("Calc_SplRate").Value = Sheet17.Range("SI.TotSplRateIncTax").Value
ThisComponent.Sheets(5-1).getCellRangeByName("Sur_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_Surcharge").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Clac_MR").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_MR").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_NetSur").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_NetSur").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_ED").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_ED").value)
ThisComponent.Sheets(5-1).getCellRangeByName("avgratetax").value = ThisComponent.Sheets(5-1).getCellRangeByName("NRI_AVG").value
ElseIf ResStatus = "NOR" Then
'Sheet17.Range("THRESOLD").Value = Round(Sheet5.Range("NRI_TH").Value)
ThisComponent.Sheets(5-1).getCellRangeByName("TXN_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Rebate_AgriInc_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_rebate").value)
'Sheet5.Range("Calc_SplRate").Value = Sheet17.Range("SI.TotSplRateIncTax").Value
ThisComponent.Sheets(5-1).getCellRangeByName("Sur_Calc").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_Surcharge").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Clac_MR").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_MR").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_NetSur").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_NetSur").value)
ThisComponent.Sheets(5-1).getCellRangeByName("Calc_ED").value = Round(ThisComponent.Sheets(5-1).getCellRangeByName("NRI_ED").value)
ThisComponent.Sheets(5-1).getCellRangeByName("avgratetax").value = ThisComponent.Sheets(5-1).getCellRangeByName("NRI_AVG").value
End If
End Sub
Function CheckDateMaxDDMMYYYY(ByVal dt As String, ByVal maxday As Integer, ByVal maxmonth As Integer, maxyear As Integer) As Boolean
CheckDateMaxDDMMYYYY = True
If (Val(Mid(dt, 1, 4)) > maxyear) Then
CheckDateMaxDDMMYYYY = False
'MsgBox "INVALID DATE, " & errormsg
GoTo exit1
End If
If (Val(Mid(dt, 1, 4)) = maxyear) And (Val(Mid(dt, 6, 2)) > maxmonth) Then
CheckDateMaxDDMMYYYY = False
'MsgBox "INVALID DATE, " & errormsg
GoTo exit1
End If
If (Val(Mid(dt, 1, 4)) = maxyear) And (Val(Mid(dt, 6, 2)) = maxmonth) And (Val(Mid(dt, 9, 2)) > maxday) Then
CheckDateMaxDDMMYYYY = False
'MsgBox "INVALID DATE, " & errormsg
GoTo exit1
End If
exit1:
End Function
Function Dformat1(dt As Variant, timepass As String) As String
'yyyy-mm-dd'
Dim formateddate As String
Dim day As String
Dim month As String
Dim year As String
If Len(dt) > 0 Then
year = Mid(dt, 7, 4)
month = Mid(dt, 4, 2)
day = Mid(dt, 1, 2)
formateddate = year & "-" & month & "-" & day
Dformat1 = formateddate
Else
Dformat1 = ""
End If
End Function