Dim iSelection As Integer
Dim aProperties(7, 1)
Dim sK1 As Single
Dim sK2 As Single
Dim sK3 As Single
Dim sFM As Single
Dim sArea As Single
Dim lCp As Long
Dim sFP As Single
Dim iSpares As Integer
Dim lCBM As Long
Dim lCBM0 As Long
Dim sFBM As Single
Dim bQuestion As Boolean
Dim strFilterInfo As String
Dim strFilterMOC As String
Dim strFilterType As String
Dim iTemp As Integer
Dim sHolder As Single
Dim iCounter As Integer
Sub displayAddFilter()
Unload addEquipmentForm
Load filterForm
filterForm.Caption = "Add a Filter to Unit " & Range("unitNumber").Value
Call prepareFilterFormUnits
filterForm.Show
End Sub
Sub displayEditFilter()
Load filterForm
Call prepareFilterFormUnits
Call fillFilterForm
filterForm.Caption = "Edit Filter Fr-" & (Val(Range("unitNumber")) + iSelection)
filterForm.filterFinish.Caption = "Edit"
filterForm.filterAdd.Enabled = False
filterForm.filterAdd.Visible = False
filterForm.Show
End Sub
Sub prepareFilterFormUnits()
With filterForm.cbArea
.RowSource = ""
.AddItem "square meters"
.AddItem "square feet"
End With
Select Case True
Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
.Value = "square meters"
filterForm.cbArea.ListIndex = 0
Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
.Value = "square feet"
filterForm.cbArea.ListIndex = 1
End Select
End Sub
Sub addFilter()
If Range("userAddedFilters").Offset(1, 0).Value = "" Then
Call editEquipment.unhideEquipment("userAddedFilters")
End If
Call getFilterData
Call calculateFilterCosts
Application.ScreenUpdating = False
Call insertRowsFilter
Call insertFilter
Application.ScreenUpdating = True
End Sub
Sub editFilter()
Call getFilterData
Call calculateFilterCosts
Application.ScreenUpdating = False
Call insertFilter
Call listFilterProperties
Application.ScreenUpdating = True
End Sub
Sub addMultipleFilters(iCounter As Integer)
Application.ScreenUpdating = False
If Range("userAddedFilters").Offset(1, 0).Value = "" Then
Call editEquipment.unhideEquipment("userAddedFilters")
End If
Call getFilterData
sArea = sArea / iCounter
Call calculateFilterCosts
sArea = sArea * iCounter
lCp = lCp * iCounter
lCBM = lCBM * iCounter
With filterForm
.tbBaseCost.Value = lCp
.tbBaseCost = Format(filterForm.tbBaseCost, ["$\ #,###,###,###"])
.tbModuleCost.Value = lCBM
.tbModuleCost = Format(filterForm.tbModuleCost, ["$\ #,###,###,###"])
End With
Call insertRowsFilter
Call insertFilter
Application.ScreenUpdating = True
End Sub
Sub editMultipleFilters(iCounter As Integer)
Application.ScreenUpdating = False
Call getFilterData
sArea = sArea / iCounter
Call calculateFilterCosts
sArea = sArea * iCounter
lCp = lCp * iCounter
lCBM = lCBM * iCounter
With filterForm
.tbBaseCost.Value = lCp
.tbBaseCost = Format(filterForm.tbBaseCost, ["$\ #,###,###,###"])
.tbModuleCost.Value = lCBM
.tbModuleCost = Format(filterForm.tbModuleCost, ["$\ #,###,###,###"])
End With
Call insertFilter
Call listFilterProperties
Application.ScreenUpdating = True
End Sub
Sub getFilterData()
sArea = convert(filterForm, "area", Val(filterForm.tbArea))
Select Case True
Case filterForm.obBent
strFilterInfo = "filterBent"
strFilterType = "Bent"
Case filterForm.obCartridge
strFilterInfo = "filterCartridge"
strFilterType = "Cartridge"
Case filterForm.obDiscAndDrum
strFilterInfo = "filterDiscAndDrum"
strFilterType = "Disc And Drum"
Case filterForm.obGravity
strFilterInfo = "filterGravity"
strFilterType = "Gravity"
Case filterForm.obLeaf
strFilterInfo = "filterLeaf"
strFilterType = "Leaf"
Case filterForm.obPan
strFilterInfo = "filterPan"
strFilterType = "Pan"
Case filterForm.obPlateAndFrame
strFilterInfo = "filterPlateAndFrame"
strFilterType = "Plate And Frame"
Case filterForm.obTable
strFilterInfo = "filterTable"
strFilterType = "Table"
Case filterForm.obTube
strFilterInfo = "filterTube"
strFilterType = "Tube"
End Select
sHolder = -99
If sArea < Range(strFilterInfo & "Amin") Then
sHolder = sArea
sArea = Range(strFilterInfo & "Amin")
End If
sK1 = Worksheets("Equipment Cost Data").Range(strFilterInfo & "K1").Value
sK2 = Worksheets("Equipment Cost Data").Range(strFilterInfo & "K2").Value
sK3 = Worksheets("Equipment Cost Data").Range(strFilterInfo & "K3").Value
sFBM = Worksheets("Equipment Cost Data").Range(strFilterInfo & "FBM").Value
End Sub
Sub calculateFilterCosts()
lCp = 10 ^ (sK1 + sK2 * (Log(sArea) / Log(10)) + sK3 * _
((Log(sArea) / Log(10)) ^ 2)) * (iSpares + 1) / 397 * Range("CEPCI")
If sHolder <> -99 Then
sArea = sHolder
End If
lCBM = lCp * sFBM
filterForm.tbBaseCost.Value = lCp
filterForm.tbBaseCost = Format(filterForm.tbBaseCost, ["$\ #,###,###,###"])
filterForm.tbModuleCost.Value = lCBM
filterForm.tbModuleCost = Format(filterForm.tbModuleCost, ["$\ #,###,###,###"])
End Sub
Sub insertRowsFilter()
bQuestion = False
iSelection = 0
Range("userAddedFilters").Select
Do While bQuestion = False
iSelection = iSelection + 1
If IsEmpty(ActiveCell.Offset(iSelection, 0)) Then
bQuestion = True
End If
Loop
ActiveCell.Offset((iSelection + 1), 0).Rows("1:1").EntireRow.insert Shift:=xlDown
End Sub
Sub insertFilter()
Range("userAddedFilters").Offset(iSelection, 0).Value _
= "=""Fr-"" & unitNumber + " & Val(iSelection)
Range("userAddedFilters").Offset(iSelection, 1).Value = strFilterType
iTemp = roundAmount(sArea * Range("preferenceArea"))
Range("userAddedFilters").Offset(iSelection, 2).Value = "=ROUND(" & _
sArea & "*preferenceArea, " & iTemp & ")"
iTemp = roundAmount(filterForm.tbBaseCost)
Range("userAddedFilters").Select
ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
(filterForm.tbBaseCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 7).Style = "Currency"
ActiveCell.Offset(iSelection, 7).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
iTemp = roundAmount(filterForm.tbModuleCost)
ActiveCell.Offset(iSelection, 8).Value = "=ROUND(" & _
(filterForm.tbModuleCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 8).Style = "Currency"
ActiveCell.Offset(iSelection, 8).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
iTemp = roundAmount(filterForm.tbBaseCost)
Range("userAddedFilters").Select
ActiveCell.Offset(iSelection, 9).Value = "=ROUND(" & _
(filterForm.tbBaseCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 9).Style = "Currency"
ActiveCell.Offset(iSelection, 9).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
iTemp = roundAmount(filterForm.tbModuleCost)
ActiveCell.Offset(iSelection, 10).Value = "=ROUND(" & _
(filterForm.tbModuleCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 10).Style = "Currency"
ActiveCell.Offset(iSelection, 10).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
End Sub
Sub fillFilterForm()
Select Case Range("userAddedFilters").Offset(iSelection, 1).Value
Case "Bent"
filterForm.obBent = True
Case "Cartridge"
filterForm.obCartridge = True
Case "Disc And Drum"
filterForm.obDiscAndDrum = True
Case "Gravity"
filterForm.obGravity = True
Case "Leaf"
filterForm.obLeaf = True
Case "Pan"
filterForm.obPan = True
Case "Plate And Frame"
filterForm.obPlateAndFrame = True
Case "Table"
filterForm.obTable = True
Case "Tube"
End Select
filterForm.tbArea = Val(Range("userAddedFilters").Offset(iSelection, 2))
End Sub
Sub listFilterProperties()
editEquipmentForm.lbEquipmentDescription.Clear
iSelection = editEquipmentForm.lbEquipmentName.ListIndex + 1
aProperties(0, 0) = "Filter Type.................................."
aProperties(0, 1) = Range("userAddedFilters").Offset(iSelection, 1)
aProperties(1, 0) = "Filter Area................................"
aProperties(1, 1) = Range("userAddedFilters").Offset(iSelection, 2) & " " _
& Range("preferenceArea").Offset(0, -1).Value
aProperties(2, 0) = " "
aProperties(2, 1) = " "
aProperties(3, 0) = " "
aProperties(3, 1) = " "
aProperties(4, 0) = " "
aProperties(4, 1) = " "
aProperties(5, 0) = " "
aProperties(5, 1) = " "
aProperties(6, 0) = "Base Equipment Cost...................................."
aProperties(6, 1) = Format(Range("userAddedFilters").Offset(iSelection, 9), ["$\ #,###,###,###"])
aProperties(7, 0) = "Base Bare Module Cost............................."
aProperties(7, 1) = Format(Range("userAddedFilters").Offset(iSelection, 10), ["$\ #,###,###,###"])
editEquipmentForm.lbEquipmentDescription.List = aProperties
End Sub
Sub addSummaryFilter()
Dim rRange
Application.ScreenUpdating = False
Do While Range("costSummary").Offset(iCounter, 0).Value <> ""
iCounter = iCounter + 1
Loop
Range("costSummary").Offset(iCounter, 0).Rows("1:1").EntireRow.insert Shift:=xlDown
Range("costSummary").Offset(iCounter, 0) = "=""Fr-"" & unitNumber + " & Val(iSelection)
iTemp = roundAmount(lCBM * Range("totalModuleFactor") / 397 * Range("CEPCI"))
Range("costSummary").Offset(iCounter, 1) = "=ROUND(" & lCBM & _
"*totalModuleFactor, " & iTemp & ")"
Range("costSummary").Offset(iCounter, 1).Style = "Currency"
Range("costSummary").Offset(iCounter, 1).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Call filterGrassRoots
iTemp = roundAmount((Range("totalModuleFactor") * lCBM _
+ Range("grassRootsFactor") * lCBM0) / 397 * Range("CEPCI"))
Range("costSummary").Offset(iCounter, 3) = "=ROUND((totalModuleFactor*" & lCBM & _
"+ grassRootsFactor*" & lCBM0 & "), " & iTemp & ")"
Range("costSummary").Offset(iCounter, 3).Style = "Currency"
Range("costSummary").Offset(iCounter, 3).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Range("costSummary").Offset(iCounter, 5).ClearFormats
Range("costSummary").Offset(iCounter, 5).HorizontalAlignment = xlCenter
Range("costSummary").Offset(iCounter, 5).Font.Size = 12
Range("costSummary").Offset(iCounter, 5) = "NA"
End Sub
Sub editSummaryFilter()
Dim rRange
Application.ScreenUpdating = False
iCounter = 0
strFilterMOC = "Fr-" & (Range("unitNumber")) + Val(iSelection)
Do While Range("costSummary").Offset(iCounter, 0) <> strFilterMOC
iCounter = iCounter + 1
Loop
Range("costSummary").Offset(iCounter, 0) = "=""Fr-"" & unitNumber + " & Val(iSelection)
iTemp = roundAmount(lCBM * Range("totalModuleFactor") / 397 * Range("CEPCI"))
Range("costSummary").Offset(iCounter, 1) = "=ROUND(" & lCBM & _
"*totalModuleFactor, " & iTemp & ")"
Range("costSummary").Offset(iCounter, 1).Style = "Currency"
Range("costSummary").Offset(iCounter, 1).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Call filterGrassRoots
iTemp = roundAmount((Range("totalModuleFactor") * lCBM _
+ Range("grassRootsFactor") * lCBM0) / 397 * Range("CEPCI"))
Range("costSummary").Offset(iCounter, 3) = "=ROUND((totalModuleFactor*" & lCBM & _
"+ grassRootsFactor*" & lCBM0 & "), " & iTemp & ")"
Range("costSummary").Offset(iCounter, 3).Style = "Currency"
Range("costSummary").Offset(iCounter, 3).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Range("costSummary").Offset(iCounter, 5).ClearFormats
Range("costSummary").Offset(iCounter, 5).HorizontalAlignment = xlCenter
Range("costSummary").Offset(iCounter, 5).Font.Size = 12
Range("costSummary").Offset(iCounter, 5) = "NA"
End Sub
Sub filterGrassRoots()
lCBM0 = lCp
End Sub