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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
| Option Explicit
'*********************************
'*Function to initialize the form*
'*********************************
Private Sub UserForm_Initialize()
With CompTypebox 'Initialization of the Comptype box
.Clear
.AddItem: .List(0, 0) = "REFA"
.AddItem: .List(1, 0) = "REFB"
End With
With Wafbox 'Initialization of the Waf box
.Clear
.AddItem: .List(0, 0) = "150mm"
.AddItem: .List(1, 0) = "200mm"
End With
End Sub
'*******************************
'*Function to update the result*
'*******************************
Private Sub MyUpdate()
Dim TempResult, CompType, Puce, Volume, Year As Variant
'We convert the entry data
Puce = CDblOrNull(Pucebox.Value)
Volume = CDblOrNull(Volumebox.Value)
Year = CDblOrNull(Yearbox.Value)
'We check if all the data are available
TempResult = PowerPuceCost(Puce, Volume, Year, Wafbox.Value, CompTypebox.Value, "A")
If IsNull(TempResult) Then
LabA.Caption = ""
LabB.Caption = ""
LabC.Caption = ""
LabST.Caption = ""
LabE.Caption = ""
LabAVERAGE.Caption = ""
Else
TempResult = PowerPuceCost(Puce, Volume, Year, Wafbox.Value, CompTypebox.Value, "A")
LabA.Caption = CStr(Round(TempResult, 2))
TempResult = PowerPuceCost(Puce, Volume, Year, Wafbox.Value, CompTypebox.Value, "B")
LabB.Caption = CStr(Round(TempResult, 2))
TempResult = PowerPuceCost(Puce, Volume, Year, Wafbox.Value, CompTypebox.Value, "C")
LabC.Caption = CStr(Round(TempResult, 2))
TempResult = PowerPuceCost(Puce, Volume, Year, Wafbox.Value, CompTypebox.Value, "ST")
LabST.Caption = CStr(Round(TempResult, 2))
TempResult = PowerPuceCost(Puce, Volume, Year, Wafbox.Value, CompTypebox.Value, "E")
LabE.Caption = CStr(Round(TempResult, 2))
If CompType = "REFB" Then
TempResult = Application.WorksheetFunction.Average(CDblOrNull(LabA.Caption), CDblOrNull(LabB.Caption), CDblOrNull(LabC.Caption), CDblOrNull(LabST.Caption), CDblOrNull(LabE.Caption))
LabAVERAGE.Caption = CStr(Round(TempResult, 2))
ElseIf CompType = "REFA" Then
TempResult = Application.WorksheetFunction.Average(CDblOrNull(LabA.Caption), CDblOrNull(LabB.Caption))
End If
End If
End Sub
'*************************************************
'*Functions to update the result in case of event*
'*************************************************
Private Sub PuceBox_Change()
Call MyUpdate
End Sub
Private Sub VolumeBox_Change()
Call MyUpdate
End Sub
Private Sub YearBox_Change()
Call MyUpdate
End Sub
Private Sub WafBox_Change()
Call MyUpdate
End Sub
Private Sub CompTypeBox_Change()
Call MyUpdate
End Sub
'******************************************
'*Function to calculate the power Puce cost*
'******************************************
Private Function PowerPuceCost(ByVal CompType, ByVal Volume, ByVal Puce, ByVal Supplier, ByVal Waf, ByVal Year) As Variant
Dim CoefVolume, AdderY, AdderX, Puce_size, VolumeTemp As Variant
On Error GoTo ErrorTag
If IsNull(Puce) Or (Puce = 0) Or IsNull(Volume) Or (Volume = 0) Or _
(Year = 0) Or IsNull(Year) Or (Waf = "") Or (Supplier = "") Or (CompType = "") Then
GoTo ErrorTag
End If
Puce_size = Puce
If CompType = "REFA" Then
If Waf = "150mm" Then
If Supplier = "A" Then
AdderY = 0.05
AdderX = 0.032
ElseIf Supplier = "B" Then
AdderY = -0.066
AdderX = 0.0369
Else
AdderX = 0
AdderY = 0
End If
ElseIf Waf = "200mm" Then
If Supplier = "A" Then
AdderY = 0.044
AdderX = 0.0269
ElseIf Supplier = "B" Then
AdderY = 0.022
AdderX = 0.0254
Else
AdderX = 0
AdderY = 0
End If
End If
ElseIf CompType = "REFB" Then
If Waf = "150mm" Then
If Supplier = "A" Then
AdderY = 0.0594
AdderX = 0.0197
ElseIf Supplier = "B" Then
AdderY = 0.0488
AdderX = 0.0229
ElseIf Supplier = "C" Then
'AdderY=
'AdderX=
ElseIf Supplier = "ST" Then
AdderY = 0.0711
AdderX = 0.0198
ElseIf Supplier = "E" Then
AdderY = 0.1689
AdderX = 0.0192
Else
AdderX = 0
AdderY = 0
End If
ElseIf Waf = "200mm" Then
If Supplier = "A" Then
AdderY = -0.0227
AdderX = 0.0195
ElseIf Supplier = "B" Then
AdderY = 0.0844
AdderX = 0.0154
ElseIf Supplier = "C" Then
'AdderY=
'AdderX=
ElseIf Supplier = "ST" Then
AdderY = 0.0846
AdderX = 0.0148
ElseIf Supplier = "E" Then
AdderY = 0.0856
AdderX = 0.0163
Else
AdderX = 0
AdderY = 0
End If
End If
End If
VolumeTemp = Volume
If VolumeTemp < 30000 Then
CoefVolume = 1.08
ElseIf (VolumeTemp >= 30000) And (VolumeTemp <= 100000) Then
CoefVolume = 1.02
ElseIf (VolumeTemp > 100000) And (VolumeTemp < 500000) Then
CoefVolume = 0.94
ElseIf (VolumeTemp >= 500000) And (VolumeTemp < 1000000) Then
CoefVolume = 0.9
ElseIf (VolumeTemp >= 1000000) Then
CoefVolume = 0.85
Else
CoefVolume = 1
End If
PowerPuceCost = CoefVolume * (Puce_size * AdderX + AdderY)
Exit Function
ErrorTag:
PowerPuceCost = Null
End Function
'**************************
'*Button to close the form*
'**************************
Private Sub OKButton_Click()
Unload PowerPuceEstimatorForm
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Unload PowerPuceEstimatorForm
End Sub |
