Hello everybody, I have some problems with the program CAPCOST 2008 because it give me a runtime error. After I chose an heat exchanger and fill in every request, debug underlines the red part:
iTemp = roundAmount(lCp)
Range("userAddedExchangers").Select
ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
(lCp / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 7).Style = "Currency"
ActiveCell.Offset(iSelection, 7).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
This is the complete code, if can be useful for understanding (I thank you so much in advance for your availability):
Dim iSelection As Integer
Dim aProperties(7, 1)
Dim sK1 As Single
Dim sK2 As Single
Dim sK3 As Single
Dim sC1 As Single
Dim sC2 As Single
Dim sC3 As Single
Dim sB1 As Single
Dim sB2 As Single
Dim sArea As Single
Dim sShellPressure As Single
Dim sTubePressure As Single
Dim sPressure As Single
Dim lCp As Long
Dim lCBM As Long
Dim lCBM0 As Long
Dim sFM As Single
Dim sFP As Single
Dim bQuestion As Boolean
Dim strExchangerInfo As String
Dim strExchangerMOC As String
Dim strExchangerType As String
Dim strGrassRootsSuffix As String
Dim iTemp As Integer
Dim sHolder As Single
'*****************************************************************************
' The three modules below change information in "exchangerForm" depending *
' on whether the action chosen was add or edit. *
'*****************************************************************************
Sub displayAddExchanger()
Unload addEquipmentForm
Load exchangerForm
exchangerForm.Caption = "Add an Exchanger to Unit " & Range("unitNumber").Value
Call prepareExchangerFormUnits
exchangerForm.Show
End Sub
Sub displayEditExchanger()
Load exchangerForm
Call prepareExchangerFormUnits
Call fillExchangerForm
exchangerForm.Caption = "Edit Exchanger E-" & (Val(Range("unitNumber")) + iSelection)
exchangerForm.exchangerFinish.Caption = "Edit"
exchangerForm.exchangerAdd.Enabled = False
exchangerForm.exchangerAdd.Visible = False
exchangerForm.Show
End Sub
Sub prepareExchangerFormUnits()
With exchangerForm.cbPressure
.RowSource = ""
.AddItem "barg"
.AddItem "kPag"
.AddItem "psig"
End With
With exchangerForm.cbArea
.RowSource = ""
.AddItem "square meters"
.AddItem "square feet"
End With
Select Case True
Case Worksheets("User Options").Range("preferencePressure").Offset(0, -1) _
.Value = "barg"
exchangerForm.cbPressure.ListIndex = 0
Case Worksheets("User Options").Range("preferencePressure").Offset(0, -1) _
.Value = "kPag"
exchangerForm.cbPressure.ListIndex = 1
Case Worksheets("User Options").Range("preferencePressure").Offset(0, -1) _
.Value = "psig"
exchangerForm.cbPressure.ListIndex = 2
End Select
Select Case True
Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
.Value = "square meters"
exchangerForm.cbArea.ListIndex = 0
Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
.Value = "square feet"
exchangerForm.cbArea.ListIndex = 1
End Select
End Sub
'**************************************************************************
' The two modules below call different exchanger procedures depending *
' on whether the action chosen was add or edit. *
'**************************************************************************
Sub addExchanger()
If Range("userAddedExchangers").Offset(1, 0).Value = "" Then
Call editEquipment.unhideEquipment("userAddedExchangers")
End If
Call getExchangerData
Call calculateExchangerCosts
Application.ScreenUpdating = False
Call insertRowsExchanger
Call insertExchanger
Application.ScreenUpdating = True
End Sub
Sub editExchanger()
Call getExchangerData
Call calculateExchangerCosts
Application.ScreenUpdating = False
Call insertExchanger
Call listExchangerProperties
Application.ScreenUpdating = True
End Sub
Sub addMultipleExchangers(iCounter As Integer)
Application.ScreenUpdating = False
exchangerForm.tbShells = 1
If Range("userAddedExchangers").Offset(1, 0).Value = "" Then
Call editEquipment.unhideEquipment("userAddedExchangers")
End If
Call getExchangerData
sArea = sArea / iCounter
Call calculateExchangerCosts
sArea = sArea * iCounter
lCp = lCp * iCounter
lCBM = lCBM * iCounter
With exchangerForm
.tbBaseCost.Value = lCp
.tbBaseCost = Format(exchangerForm.tbBaseCost, ["$\ #,###,###,###"])
.tbModuleCost.Value = lCBM
.tbModuleCost = Format(exchangerForm.tbModuleCost, ["$\ #,###,###,###"])
End With
Call insertRowsExchanger
Call insertExchanger
Application.ScreenUpdating = True
End Sub
Sub editMultipleExchangers(iCounter As Integer)
Application.ScreenUpdating = False
exchangerForm.tbShells = 1
Call getExchangerData
sArea = sArea / iCounter
Call calculateExchangerCosts
sArea = sArea * iCounter
lCp = lCp * iCounter
lCBM = lCBM * iCounter
With exchangerForm
.tbBaseCost.Value = lCp
.tbBaseCost = Format(exchangerForm.tbBaseCost, ["$\ #,###,###,###"])
.tbModuleCost.Value = lCBM
.tbModuleCost = Format(exchangerForm.tbModuleCost, ["$\ #,###,###,###"])
End With
Call insertExchanger
Call listExchangerProperties
Application.ScreenUpdating = True
End Sub
' This module takes the data from user form "exchangerForm"
Sub getExchangerData()
sArea = convert(exchangerForm, "area", Val(exchangerForm.tbArea))
sShellPressure = convert(exchangerForm, "pressure", Val(exchangerForm.tbPmaxShell))
sTubePressure = convert(exchangerForm, "pressure", Val(exchangerForm.tbPmaxTube))
Select Case True
Case exchangerForm.obDouble
strExchangerInfo = "exchangerDouble"
strExchangerType = "Double Pipe"
Case exchangerForm.obMultiple
strExchangerInfo = "exchangerMultiple"
strExchangerType = "Multiple Pipe"
Case exchangerForm.obFixed
strExchangerInfo = "exchangerFixed"
strExchangerType = "Fixed, Sheet, or U-Tube"
Case exchangerForm.obFloating
strExchangerInfo = "exchangerFloating"
strExchangerType = "Floating Head"
Case exchangerForm.obBayonet
strExchangerInfo = "exchangerBayonet"
strExchangerType = "Bayonet"
Case exchangerForm.obKettle
strExchangerInfo = "exchangerKettle"
strExchangerType = "Kettle Reboiler"
Case exchangerForm.obScraped
strExchangerInfo = "exchangerScraped"
strExchangerType = "Scraped Wall"
Case exchangerForm.obTeflon
strExchangerInfo = "exchangerTeflon"
strExchangerType = "Teflon Tube"
Case exchangerForm.obAir
strExchangerInfo = "exchangerAir"
strExchangerType = "Air Cooler"
Case exchangerForm.obSpiralTube
strExchangerInfo = "exchangerSpiralTube"
strExchangerType = "Spiral Tube"
Case exchangerForm.obSpiralPlate
strExchangerInfo = "exchangerSpiralPlate"
strExchangerType = "Spiral Plate"
Case exchangerForm.obFlat
strExchangerInfo = "exchangerFlat"
strExchangerType = "Flat Plate"
End Select
sHolder = -99
If sArea < Range(strExchangerInfo & "Amin") Then
sHolder = sArea
sArea = Range(strExchangerInfo & "Amin")
End If
sK1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "K1").Value
sK2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "K2").Value
sK3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "K3").Value
sB1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "B1").Value
sB2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "B2").Value
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C1").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C2").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C3").Value
If exchangerForm.obDouble = True Or exchangerForm.obMultiple = True Or _
exchangerForm.obScraped = True Then
If sTubePressure <= 100 And sTubePressure > 40 Then
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C12").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C22").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C32").Value
End If
If sTubePressure <= 40 Then
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C13").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C23").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C33").Value
End If
End If
If exchangerForm.obFixed = True Or exchangerForm.obFloating = True Or _
exchangerForm.obBayonet = True Or exchangerForm.obKettle = True Then
If sShellPressure <= 5 And sTubePressure > 5 Then
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C12").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C22").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C32").Value
End If
End If
If exchangerForm.obSpiralTube = True Then
If sShellPressure <= 10 And sTubePressure > 10 Then
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C12").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C22").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C32").Value
End If
End If
' New piece of code below, April 1st, 2002
If exchangerForm.obSpiralTube = True Then
If sShellPressure <= 10 And sTubePressure <= 10 Then
sC1 = 0
sC2 = 0
sC3 = 0
End If
End If
Select Case True
Case exchangerForm.obMOCCSCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSCS").Value
strExchangerMOC = "Carbon Steel / Carbon Steel"
Case exchangerForm.obMOCCuCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSCu").Value
strExchangerMOC = "Copper / Carbon Steel"
Case exchangerForm.obMOCCuCu.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCuCu").Value
strExchangerMOC = "Copper / Copper"
Case exchangerForm.obMOCSSCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSSS").Value
strExchangerMOC = "Stainless Steel / Carbon Steel"
Case exchangerForm.obMOCSSSS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMSSSS").Value
strExchangerMOC = "Stainless Steel / Stainless Steel"
Case exchangerForm.obMOCNiCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSNi").Value
strExchangerMOC = "Nickel / Carbon Steel"
Case exchangerForm.obMOCNiNi.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMNiNi").Value
strExchangerMOC = "Nickel / Nickel"
Case exchangerForm.obMOCTiCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSTi").Value
strExchangerMOC = "Titanium / Carbon Steel"
Case exchangerForm.obMOCTiTi.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMTiTi").Value
strExchangerMOC = "Titanium / Titanium"
Case exchangerForm.obMOCCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCS").Value
strExchangerMOC = "Carbon Steel"
Case exchangerForm.obMOCCu.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCu").Value
strExchangerMOC = "Copper"
Case exchangerForm.obMOCSS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMSS").Value
strExchangerMOC = "Stainless Steel"
Case exchangerForm.obMOCNi.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMNi").Value
strExchangerMOC = "Nickel"
Case exchangerForm.obMOCTi.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMTi").Value
strExchangerMOC = "Titanium"
Case exchangerForm.obMOCAl.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMAl").Value
strExchangerMOC = "Aluminum"
Case Else
MsgBox " Exchanger MOC String Concantenation Problem"
End Select
' The case select below performs a linear interpolation of pressure factor values
' Thia module interpolates the data from worksheet "Equipment Cost Data"
If sShellPressure > sTubePressure Then sPressure = sShellPressure _
Else: sPressure = sTubePressure
If sPressure <= 10 Then
sFP = 1
End If
If sPressure > 10 Then
sFP = 10 ^ (sC1 + sC2 * Log(sPressure) / Log(10) + sC3 * (Log(sPressure) / Log(10)) ^ 2)
End If
If exchangerForm.obFixed = True Or exchangerForm.obFloating = True Or _
exchangerForm.obBayonet = True Or exchangerForm.obKettle = True Then
If sPressure > 5 Then
sFP = 10 ^ (sC1 + sC2 * Log(sPressure) / Log(10) + sC3 * (Log(sPressure) / Log(10)) ^ 2)
End If
End If
If sFP < 1 Then
sFP = 1
End If
End Sub
'************************************************************************************
' The module below calculates cost for exchangers according to the correlations *
' given in "Analysis, Synthesis, and Design of Chemical Processes." *
'************************************************************************************
Sub calculateExchangerCosts()
If exchangerForm.tbShells > 1 Then
sArea = sArea / exchangerForm.tbShells
End If
lCp = 10 ^ (sK1 + sK2 * (Log(sArea) / Log(10)) + sK3 * _
((Log(sArea) / Log(10)) ^ 2)) / 397 * Range("CEPCI")
If sHolder <> -99 Then
sArea = sHolder
End If
exchangerForm.tbBaseCost.Value = lCp
exchangerForm.tbBaseCost = Format(exchangerForm.tbBaseCost, ["$\ #,###,###,000"])
If exchangerForm.tbShells > 1 Then
sArea = sArea * exchangerForm.tbShells
lCp = lCp * exchangerForm.tbShells
End If
lCBM = lCp * (sB1 + sB2 * sFM * sFP)
exchangerForm.tbModuleCost.Value = lCBM
exchangerForm.tbModuleCost = Format(exchangerForm.tbModuleCost, ["$\ #,###,###,000"])
End Sub
Sub insertRowsExchanger()
bQuestion = False
iSelection = 0
Range("userAddedExchangers").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 insertExchanger()
Dim iName As Integer
Dim iUnitNumber As Integer
' Exchanger naming procedure
Range("userAddedExchangers").Offset(iSelection, 0).Value _
= "=""E-"" & unitNumber + " & Val(iSelection)
Range("userAddedExchangers").Offset(iSelection, 1).Value = strExchangerType
Range("userAddedExchangers").Offset(iSelection, 5).Value = strExchangerMOC
iTemp = roundAmount(convert(exchangerForm, "area", Val(exchangerForm.tbArea)) _
* Range("preferenceArea"))
Range("userAddedExchangers").Offset(iSelection, 6).Value = "=ROUND(" & _
sArea & "*preferenceArea, " & iTemp & ")"
If exchangerForm.tbPmaxShell.Enabled = True Then
iTemp = roundAmount(sShellPressure * Range("preferencePressure"))
Range("userAddedExchangers").Offset(iSelection, 2).Value = "=ROUND(" & _
(sShellPressure) & "*preferencePressure, " & iTemp & ")"
End If
If exchangerForm.tbPmaxShell.Enabled = False Then
Range("userAddedExchangers").Offset(iSelection, 2).Value = ""
End If
iTemp = roundAmount(sTubePressure * Range("preferencePressure"))
Range("userAddedExchangers").Offset(iSelection, 3).Value = "=ROUND(" & _
(sTubePressure) & "*preferencePressure, " & iTemp & ")"
iTemp = roundAmount(lCp)
Range("userAddedExchangers").Select
ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
(lCp / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 7).Style = "Currency"
ActiveCell.Offset(iSelection, 7).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
iTemp = roundAmount(lCBM)
ActiveCell.Offset(iSelection, 8).Value = "=ROUND(" & _
(lCBM / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 8).Style = "Currency"
ActiveCell.Offset(iSelection, 8).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
End Sub
'***************************************************************************
' This module fills the edit version of exchangerForm with information *
' regarding the exchanger that has been chosen for editing. Both of the *
' modules below are for editing an existing exchanger. *
'***************************************************************************
Sub fillExchangerForm()
Select Case Range("userAddedExchangers").Offset(iSelection, 1).Value
Case "Double Pipe"
exchangerForm.obDouble = True
Case "Multiple Pipe"
exchangerForm.obMultiple = True
Case "Fixed, Sheet, or U-Tube"
exchangerForm.obFixed = True
Case "Floating Head"
exchangerForm.obFloating = True
Case "Bayonet"
exchangerForm.obBayonet = True
Case "Kettle Reboiler"
exchangerForm.obKettle = True
Case "Scraped Wall"
exchangerForm.obScraped = True
Case "Teflon Tube"
exchangerForm.obTeflon = True
Case "Air Cooler"
exchangerForm.obAir = True
Case "Spiral Tube"
exchangerForm.obSpiralTube = True
Case "Spiral Plate"
exchangerForm.obSpiralPlate = True
Case "Flat Plate"
exchangerForm.obFlat = True
End Select
Select Case Range("userAddedExchangers").Offset(iSelection, 5).Value
Case "Carbon Steel / Carbon Steel"
exchangerForm.obMOCCSCS.Value = True
Case "Copper / Carbon Steel"
exchangerForm.obMOCCuCS.Value = True
Case "Copper / Copper"
exchangerForm.obMOCCuCu.Value = True
Case "Stainless Steel / Carbon Steel"
exchangerForm.obMOCSSCS.Value = True
Case "Stainless Steel / Stainless Steel"
exchangerForm.obMOCSSSS.Value = True
Case "Nickel / Carbon Steel"
exchangerForm.obMOCNiCS.Value = True
Case "Nickel / Nickel"
exchangerForm.obMOCNiNi.Value = True
Case "Titanium / Carbon Steel"
exchangerForm.obMOCTiCS.Value = True
Case "Titanium / Titanium"
exchangerForm.obMOCTiTi.Value = True
Case "Carbon Steel"
exchangerForm.obMOCCS.Value = True
Case "Copper"
exchangerForm.obMOCCu.Value = True
Case "Stainless Steel"
exchangerForm.obMOCSS.Value = True
Case "Nickel"
exchangerForm.obMOCNi.Value = True
Case "Titanium"
exchangerForm.obMOCTi.Value = True
Case "Aluminum"
exchangerForm.obMOCAl.Value = True
Case Else
MsgBox " Exchangerr MOC String Concantenation Problem"
End Select
exchangerForm.tbPmaxTube.Value = Range("userAddedExchangers").Offset(iSelection, 3).Value
exchangerForm.tbArea.Value = Range("userAddedExchangers").Offset(iSelection, 6).Value
If exchangerForm.tbPmaxShell.Enabled = True Then
exchangerForm.tbPmaxShell.Value = Range("userAddedExchangers").Offset(iSelection, 2).Value
End If
End Sub
Sub listExchangerProperties()
editEquipmentForm.lbEquipmentDescription.Clear
' This section of code fills an array with the selected exchanegr's properties
' The properties are then displeyd in the edit window
iSelection = editEquipmentForm.lbEquipmentName.ListIndex + 1
aProperties(0, 0) = "Exchanger Type.............................."
aProperties(0, 1) = Range("userAddedExchangers").Offset(iSelection, 1)
aProperties(1, 0) = "Area....................................."
aProperties(1, 1) = Range("userAddedExchangers").Offset(iSelection, 6) & " " _
& Range("preferenceArea").Offset(0, -1).Value
aProperties(2, 0) = "Tube Side Pressure........................"
aProperties(2, 1) = Range("userAddedExchangers").Offset(iSelection, 3) & " " _
& Range("preferencePressure").Offset(0, -1).Value
aProperties(3, 0) = ""
aProperties(3, 1) = ""
If Range("userAddedExchangers").Offset(iSelection, 2) <> "" Then
aProperties(3, 0) = "Shell Side Pressure..........................."
aProperties(3, 1) = Range("userAddedExchangers").Offset(iSelection, 2) & " " _
& Range("preferencePressure").Offset(0, -1).Value
End If
aProperties(4, 0) = "Construction material........................"
aProperties(4, 1) = Range("userAddedExchangers").Offset(iSelection, 5)
aProperties(5, 0) = " "
aProperties(5, 1) = " "
aProperties(6, 0) = "Base Cost...................................."
aProperties(6, 1) = Format(Range("userAddedExchangers").Offset(iSelection, 7), ["$\ #,###,###,###"])
aProperties(7, 0) = "Bare Module Cost............................."
aProperties(7, 1) = Format(Range("userAddedExchangers").Offset(iSelection, 8), ["$\ #,###,###,###"])
editEquipmentForm.lbEquipmentDescription.List = aProperties
End Sub
' The modules below are used for adding the exchangers to the cost summary and updating
' exchangers in the cost summary as they are edited
Sub addSummaryExchanger()
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) = "=""E-"" & unitNumber + " & Val(iSelection)
iTemp = roundAmount(Range("totalModuleFactor") * lCBM0 / 397 * Range("CEPCI"))
Range("costSummary").Offset(iCounter, 1) = "=ROUND(totalModuleFactor*" & lCBM & _
", " & iTemp & ")"
Range("costSummary").Offset(iCounter, 1).Style = "Currency"
Range("costSummary").Offset(iCounter, 1).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Call exchangerGrassRoots
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);_($* ""-""??_);_(@_)"
' Procedure below fills in cell with colors and shading to let user
' know that these cells should be clicked on
Range("costSummary").Offset(iCounter, 5) = "Unspecified"
With Range("costSummary").Offset(iCounter, 5).Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Range("costSummary").Offset(iCounter, 5).Font.ColorIndex = 36
Application.ScreenUpdating = True
End Sub
Sub editSummaryExchanger()
Dim rRange
Application.ScreenUpdating = False
iCounter = 0
strExchangerMOC = "E-" & (Range("unitNumber")) + Val(iSelection)
Do While Range("costSummary").Offset(iCounter, 0) <> strExchangerMOC
iCounter = iCounter + 1
Loop
Range("costSummary").Offset(iCounter, 0) = "=""E-"" & unitNumber + " & Val(iSelection)
iTemp = roundAmount(Range("totalModuleFactor") * lCBM0 / 397 * Range("CEPCI"))
Range("costSummary").Offset(iCounter, 1) = "=ROUND(totalModuleFactor*" & lCBM & _
", " & iTemp & ")"
Range("costSummary").Offset(iCounter, 1).Style = "Currency"
Range("costSummary").Offset(iCounter, 1).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Call exchangerGrassRoots
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);_($* ""-""??_);_(@_)"
Application.ScreenUpdating = True
End Sub
Sub exchangerGrassRoots()
strGrassRootsSuffix = "FMCSCS"
If exchangerForm.obTeflon = True Or exchangerForm.obSpiralPlate = True _
Or exchangerForm.obFlat = True Or exchangerForm.obAir = True Then
strGrassRootsSuffix = "FMCS"
End If
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & strGrassRootsSuffix).Value
lCBM0 = lCp * (sB1 + sB2 * sFM)
End Sub
iTemp = roundAmount(lCp)
Range("userAddedExchangers").Select
ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
(lCp / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 7).Style = "Currency"
ActiveCell.Offset(iSelection, 7).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
This is the complete code, if can be useful for understanding (I thank you so much in advance for your availability):
Dim iSelection As Integer
Dim aProperties(7, 1)
Dim sK1 As Single
Dim sK2 As Single
Dim sK3 As Single
Dim sC1 As Single
Dim sC2 As Single
Dim sC3 As Single
Dim sB1 As Single
Dim sB2 As Single
Dim sArea As Single
Dim sShellPressure As Single
Dim sTubePressure As Single
Dim sPressure As Single
Dim lCp As Long
Dim lCBM As Long
Dim lCBM0 As Long
Dim sFM As Single
Dim sFP As Single
Dim bQuestion As Boolean
Dim strExchangerInfo As String
Dim strExchangerMOC As String
Dim strExchangerType As String
Dim strGrassRootsSuffix As String
Dim iTemp As Integer
Dim sHolder As Single
'*****************************************************************************
' The three modules below change information in "exchangerForm" depending *
' on whether the action chosen was add or edit. *
'*****************************************************************************
Sub displayAddExchanger()
Unload addEquipmentForm
Load exchangerForm
exchangerForm.Caption = "Add an Exchanger to Unit " & Range("unitNumber").Value
Call prepareExchangerFormUnits
exchangerForm.Show
End Sub
Sub displayEditExchanger()
Load exchangerForm
Call prepareExchangerFormUnits
Call fillExchangerForm
exchangerForm.Caption = "Edit Exchanger E-" & (Val(Range("unitNumber")) + iSelection)
exchangerForm.exchangerFinish.Caption = "Edit"
exchangerForm.exchangerAdd.Enabled = False
exchangerForm.exchangerAdd.Visible = False
exchangerForm.Show
End Sub
Sub prepareExchangerFormUnits()
With exchangerForm.cbPressure
.RowSource = ""
.AddItem "barg"
.AddItem "kPag"
.AddItem "psig"
End With
With exchangerForm.cbArea
.RowSource = ""
.AddItem "square meters"
.AddItem "square feet"
End With
Select Case True
Case Worksheets("User Options").Range("preferencePressure").Offset(0, -1) _
.Value = "barg"
exchangerForm.cbPressure.ListIndex = 0
Case Worksheets("User Options").Range("preferencePressure").Offset(0, -1) _
.Value = "kPag"
exchangerForm.cbPressure.ListIndex = 1
Case Worksheets("User Options").Range("preferencePressure").Offset(0, -1) _
.Value = "psig"
exchangerForm.cbPressure.ListIndex = 2
End Select
Select Case True
Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
.Value = "square meters"
exchangerForm.cbArea.ListIndex = 0
Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
.Value = "square feet"
exchangerForm.cbArea.ListIndex = 1
End Select
End Sub
'**************************************************************************
' The two modules below call different exchanger procedures depending *
' on whether the action chosen was add or edit. *
'**************************************************************************
Sub addExchanger()
If Range("userAddedExchangers").Offset(1, 0).Value = "" Then
Call editEquipment.unhideEquipment("userAddedExchangers")
End If
Call getExchangerData
Call calculateExchangerCosts
Application.ScreenUpdating = False
Call insertRowsExchanger
Call insertExchanger
Application.ScreenUpdating = True
End Sub
Sub editExchanger()
Call getExchangerData
Call calculateExchangerCosts
Application.ScreenUpdating = False
Call insertExchanger
Call listExchangerProperties
Application.ScreenUpdating = True
End Sub
Sub addMultipleExchangers(iCounter As Integer)
Application.ScreenUpdating = False
exchangerForm.tbShells = 1
If Range("userAddedExchangers").Offset(1, 0).Value = "" Then
Call editEquipment.unhideEquipment("userAddedExchangers")
End If
Call getExchangerData
sArea = sArea / iCounter
Call calculateExchangerCosts
sArea = sArea * iCounter
lCp = lCp * iCounter
lCBM = lCBM * iCounter
With exchangerForm
.tbBaseCost.Value = lCp
.tbBaseCost = Format(exchangerForm.tbBaseCost, ["$\ #,###,###,###"])
.tbModuleCost.Value = lCBM
.tbModuleCost = Format(exchangerForm.tbModuleCost, ["$\ #,###,###,###"])
End With
Call insertRowsExchanger
Call insertExchanger
Application.ScreenUpdating = True
End Sub
Sub editMultipleExchangers(iCounter As Integer)
Application.ScreenUpdating = False
exchangerForm.tbShells = 1
Call getExchangerData
sArea = sArea / iCounter
Call calculateExchangerCosts
sArea = sArea * iCounter
lCp = lCp * iCounter
lCBM = lCBM * iCounter
With exchangerForm
.tbBaseCost.Value = lCp
.tbBaseCost = Format(exchangerForm.tbBaseCost, ["$\ #,###,###,###"])
.tbModuleCost.Value = lCBM
.tbModuleCost = Format(exchangerForm.tbModuleCost, ["$\ #,###,###,###"])
End With
Call insertExchanger
Call listExchangerProperties
Application.ScreenUpdating = True
End Sub
' This module takes the data from user form "exchangerForm"
Sub getExchangerData()
sArea = convert(exchangerForm, "area", Val(exchangerForm.tbArea))
sShellPressure = convert(exchangerForm, "pressure", Val(exchangerForm.tbPmaxShell))
sTubePressure = convert(exchangerForm, "pressure", Val(exchangerForm.tbPmaxTube))
Select Case True
Case exchangerForm.obDouble
strExchangerInfo = "exchangerDouble"
strExchangerType = "Double Pipe"
Case exchangerForm.obMultiple
strExchangerInfo = "exchangerMultiple"
strExchangerType = "Multiple Pipe"
Case exchangerForm.obFixed
strExchangerInfo = "exchangerFixed"
strExchangerType = "Fixed, Sheet, or U-Tube"
Case exchangerForm.obFloating
strExchangerInfo = "exchangerFloating"
strExchangerType = "Floating Head"
Case exchangerForm.obBayonet
strExchangerInfo = "exchangerBayonet"
strExchangerType = "Bayonet"
Case exchangerForm.obKettle
strExchangerInfo = "exchangerKettle"
strExchangerType = "Kettle Reboiler"
Case exchangerForm.obScraped
strExchangerInfo = "exchangerScraped"
strExchangerType = "Scraped Wall"
Case exchangerForm.obTeflon
strExchangerInfo = "exchangerTeflon"
strExchangerType = "Teflon Tube"
Case exchangerForm.obAir
strExchangerInfo = "exchangerAir"
strExchangerType = "Air Cooler"
Case exchangerForm.obSpiralTube
strExchangerInfo = "exchangerSpiralTube"
strExchangerType = "Spiral Tube"
Case exchangerForm.obSpiralPlate
strExchangerInfo = "exchangerSpiralPlate"
strExchangerType = "Spiral Plate"
Case exchangerForm.obFlat
strExchangerInfo = "exchangerFlat"
strExchangerType = "Flat Plate"
End Select
sHolder = -99
If sArea < Range(strExchangerInfo & "Amin") Then
sHolder = sArea
sArea = Range(strExchangerInfo & "Amin")
End If
sK1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "K1").Value
sK2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "K2").Value
sK3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "K3").Value
sB1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "B1").Value
sB2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "B2").Value
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C1").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C2").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C3").Value
If exchangerForm.obDouble = True Or exchangerForm.obMultiple = True Or _
exchangerForm.obScraped = True Then
If sTubePressure <= 100 And sTubePressure > 40 Then
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C12").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C22").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C32").Value
End If
If sTubePressure <= 40 Then
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C13").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C23").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C33").Value
End If
End If
If exchangerForm.obFixed = True Or exchangerForm.obFloating = True Or _
exchangerForm.obBayonet = True Or exchangerForm.obKettle = True Then
If sShellPressure <= 5 And sTubePressure > 5 Then
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C12").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C22").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C32").Value
End If
End If
If exchangerForm.obSpiralTube = True Then
If sShellPressure <= 10 And sTubePressure > 10 Then
sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C12").Value
sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C22").Value
sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C32").Value
End If
End If
' New piece of code below, April 1st, 2002
If exchangerForm.obSpiralTube = True Then
If sShellPressure <= 10 And sTubePressure <= 10 Then
sC1 = 0
sC2 = 0
sC3 = 0
End If
End If
Select Case True
Case exchangerForm.obMOCCSCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSCS").Value
strExchangerMOC = "Carbon Steel / Carbon Steel"
Case exchangerForm.obMOCCuCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSCu").Value
strExchangerMOC = "Copper / Carbon Steel"
Case exchangerForm.obMOCCuCu.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCuCu").Value
strExchangerMOC = "Copper / Copper"
Case exchangerForm.obMOCSSCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSSS").Value
strExchangerMOC = "Stainless Steel / Carbon Steel"
Case exchangerForm.obMOCSSSS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMSSSS").Value
strExchangerMOC = "Stainless Steel / Stainless Steel"
Case exchangerForm.obMOCNiCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSNi").Value
strExchangerMOC = "Nickel / Carbon Steel"
Case exchangerForm.obMOCNiNi.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMNiNi").Value
strExchangerMOC = "Nickel / Nickel"
Case exchangerForm.obMOCTiCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSTi").Value
strExchangerMOC = "Titanium / Carbon Steel"
Case exchangerForm.obMOCTiTi.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMTiTi").Value
strExchangerMOC = "Titanium / Titanium"
Case exchangerForm.obMOCCS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCS").Value
strExchangerMOC = "Carbon Steel"
Case exchangerForm.obMOCCu.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCu").Value
strExchangerMOC = "Copper"
Case exchangerForm.obMOCSS.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMSS").Value
strExchangerMOC = "Stainless Steel"
Case exchangerForm.obMOCNi.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMNi").Value
strExchangerMOC = "Nickel"
Case exchangerForm.obMOCTi.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMTi").Value
strExchangerMOC = "Titanium"
Case exchangerForm.obMOCAl.Value = True
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMAl").Value
strExchangerMOC = "Aluminum"
Case Else
MsgBox " Exchanger MOC String Concantenation Problem"
End Select
' The case select below performs a linear interpolation of pressure factor values
' Thia module interpolates the data from worksheet "Equipment Cost Data"
If sShellPressure > sTubePressure Then sPressure = sShellPressure _
Else: sPressure = sTubePressure
If sPressure <= 10 Then
sFP = 1
End If
If sPressure > 10 Then
sFP = 10 ^ (sC1 + sC2 * Log(sPressure) / Log(10) + sC3 * (Log(sPressure) / Log(10)) ^ 2)
End If
If exchangerForm.obFixed = True Or exchangerForm.obFloating = True Or _
exchangerForm.obBayonet = True Or exchangerForm.obKettle = True Then
If sPressure > 5 Then
sFP = 10 ^ (sC1 + sC2 * Log(sPressure) / Log(10) + sC3 * (Log(sPressure) / Log(10)) ^ 2)
End If
End If
If sFP < 1 Then
sFP = 1
End If
End Sub
'************************************************************************************
' The module below calculates cost for exchangers according to the correlations *
' given in "Analysis, Synthesis, and Design of Chemical Processes." *
'************************************************************************************
Sub calculateExchangerCosts()
If exchangerForm.tbShells > 1 Then
sArea = sArea / exchangerForm.tbShells
End If
lCp = 10 ^ (sK1 + sK2 * (Log(sArea) / Log(10)) + sK3 * _
((Log(sArea) / Log(10)) ^ 2)) / 397 * Range("CEPCI")
If sHolder <> -99 Then
sArea = sHolder
End If
exchangerForm.tbBaseCost.Value = lCp
exchangerForm.tbBaseCost = Format(exchangerForm.tbBaseCost, ["$\ #,###,###,000"])
If exchangerForm.tbShells > 1 Then
sArea = sArea * exchangerForm.tbShells
lCp = lCp * exchangerForm.tbShells
End If
lCBM = lCp * (sB1 + sB2 * sFM * sFP)
exchangerForm.tbModuleCost.Value = lCBM
exchangerForm.tbModuleCost = Format(exchangerForm.tbModuleCost, ["$\ #,###,###,000"])
End Sub
Sub insertRowsExchanger()
bQuestion = False
iSelection = 0
Range("userAddedExchangers").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 insertExchanger()
Dim iName As Integer
Dim iUnitNumber As Integer
' Exchanger naming procedure
Range("userAddedExchangers").Offset(iSelection, 0).Value _
= "=""E-"" & unitNumber + " & Val(iSelection)
Range("userAddedExchangers").Offset(iSelection, 1).Value = strExchangerType
Range("userAddedExchangers").Offset(iSelection, 5).Value = strExchangerMOC
iTemp = roundAmount(convert(exchangerForm, "area", Val(exchangerForm.tbArea)) _
* Range("preferenceArea"))
Range("userAddedExchangers").Offset(iSelection, 6).Value = "=ROUND(" & _
sArea & "*preferenceArea, " & iTemp & ")"
If exchangerForm.tbPmaxShell.Enabled = True Then
iTemp = roundAmount(sShellPressure * Range("preferencePressure"))
Range("userAddedExchangers").Offset(iSelection, 2).Value = "=ROUND(" & _
(sShellPressure) & "*preferencePressure, " & iTemp & ")"
End If
If exchangerForm.tbPmaxShell.Enabled = False Then
Range("userAddedExchangers").Offset(iSelection, 2).Value = ""
End If
iTemp = roundAmount(sTubePressure * Range("preferencePressure"))
Range("userAddedExchangers").Offset(iSelection, 3).Value = "=ROUND(" & _
(sTubePressure) & "*preferencePressure, " & iTemp & ")"
iTemp = roundAmount(lCp)
Range("userAddedExchangers").Select
ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
(lCp / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 7).Style = "Currency"
ActiveCell.Offset(iSelection, 7).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
iTemp = roundAmount(lCBM)
ActiveCell.Offset(iSelection, 8).Value = "=ROUND(" & _
(lCBM / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
ActiveCell.Offset(iSelection, 8).Style = "Currency"
ActiveCell.Offset(iSelection, 8).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
End Sub
'***************************************************************************
' This module fills the edit version of exchangerForm with information *
' regarding the exchanger that has been chosen for editing. Both of the *
' modules below are for editing an existing exchanger. *
'***************************************************************************
Sub fillExchangerForm()
Select Case Range("userAddedExchangers").Offset(iSelection, 1).Value
Case "Double Pipe"
exchangerForm.obDouble = True
Case "Multiple Pipe"
exchangerForm.obMultiple = True
Case "Fixed, Sheet, or U-Tube"
exchangerForm.obFixed = True
Case "Floating Head"
exchangerForm.obFloating = True
Case "Bayonet"
exchangerForm.obBayonet = True
Case "Kettle Reboiler"
exchangerForm.obKettle = True
Case "Scraped Wall"
exchangerForm.obScraped = True
Case "Teflon Tube"
exchangerForm.obTeflon = True
Case "Air Cooler"
exchangerForm.obAir = True
Case "Spiral Tube"
exchangerForm.obSpiralTube = True
Case "Spiral Plate"
exchangerForm.obSpiralPlate = True
Case "Flat Plate"
exchangerForm.obFlat = True
End Select
Select Case Range("userAddedExchangers").Offset(iSelection, 5).Value
Case "Carbon Steel / Carbon Steel"
exchangerForm.obMOCCSCS.Value = True
Case "Copper / Carbon Steel"
exchangerForm.obMOCCuCS.Value = True
Case "Copper / Copper"
exchangerForm.obMOCCuCu.Value = True
Case "Stainless Steel / Carbon Steel"
exchangerForm.obMOCSSCS.Value = True
Case "Stainless Steel / Stainless Steel"
exchangerForm.obMOCSSSS.Value = True
Case "Nickel / Carbon Steel"
exchangerForm.obMOCNiCS.Value = True
Case "Nickel / Nickel"
exchangerForm.obMOCNiNi.Value = True
Case "Titanium / Carbon Steel"
exchangerForm.obMOCTiCS.Value = True
Case "Titanium / Titanium"
exchangerForm.obMOCTiTi.Value = True
Case "Carbon Steel"
exchangerForm.obMOCCS.Value = True
Case "Copper"
exchangerForm.obMOCCu.Value = True
Case "Stainless Steel"
exchangerForm.obMOCSS.Value = True
Case "Nickel"
exchangerForm.obMOCNi.Value = True
Case "Titanium"
exchangerForm.obMOCTi.Value = True
Case "Aluminum"
exchangerForm.obMOCAl.Value = True
Case Else
MsgBox " Exchangerr MOC String Concantenation Problem"
End Select
exchangerForm.tbPmaxTube.Value = Range("userAddedExchangers").Offset(iSelection, 3).Value
exchangerForm.tbArea.Value = Range("userAddedExchangers").Offset(iSelection, 6).Value
If exchangerForm.tbPmaxShell.Enabled = True Then
exchangerForm.tbPmaxShell.Value = Range("userAddedExchangers").Offset(iSelection, 2).Value
End If
End Sub
Sub listExchangerProperties()
editEquipmentForm.lbEquipmentDescription.Clear
' This section of code fills an array with the selected exchanegr's properties
' The properties are then displeyd in the edit window
iSelection = editEquipmentForm.lbEquipmentName.ListIndex + 1
aProperties(0, 0) = "Exchanger Type.............................."
aProperties(0, 1) = Range("userAddedExchangers").Offset(iSelection, 1)
aProperties(1, 0) = "Area....................................."
aProperties(1, 1) = Range("userAddedExchangers").Offset(iSelection, 6) & " " _
& Range("preferenceArea").Offset(0, -1).Value
aProperties(2, 0) = "Tube Side Pressure........................"
aProperties(2, 1) = Range("userAddedExchangers").Offset(iSelection, 3) & " " _
& Range("preferencePressure").Offset(0, -1).Value
aProperties(3, 0) = ""
aProperties(3, 1) = ""
If Range("userAddedExchangers").Offset(iSelection, 2) <> "" Then
aProperties(3, 0) = "Shell Side Pressure..........................."
aProperties(3, 1) = Range("userAddedExchangers").Offset(iSelection, 2) & " " _
& Range("preferencePressure").Offset(0, -1).Value
End If
aProperties(4, 0) = "Construction material........................"
aProperties(4, 1) = Range("userAddedExchangers").Offset(iSelection, 5)
aProperties(5, 0) = " "
aProperties(5, 1) = " "
aProperties(6, 0) = "Base Cost...................................."
aProperties(6, 1) = Format(Range("userAddedExchangers").Offset(iSelection, 7), ["$\ #,###,###,###"])
aProperties(7, 0) = "Bare Module Cost............................."
aProperties(7, 1) = Format(Range("userAddedExchangers").Offset(iSelection, 8), ["$\ #,###,###,###"])
editEquipmentForm.lbEquipmentDescription.List = aProperties
End Sub
' The modules below are used for adding the exchangers to the cost summary and updating
' exchangers in the cost summary as they are edited
Sub addSummaryExchanger()
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) = "=""E-"" & unitNumber + " & Val(iSelection)
iTemp = roundAmount(Range("totalModuleFactor") * lCBM0 / 397 * Range("CEPCI"))
Range("costSummary").Offset(iCounter, 1) = "=ROUND(totalModuleFactor*" & lCBM & _
", " & iTemp & ")"
Range("costSummary").Offset(iCounter, 1).Style = "Currency"
Range("costSummary").Offset(iCounter, 1).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Call exchangerGrassRoots
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);_($* ""-""??_);_(@_)"
' Procedure below fills in cell with colors and shading to let user
' know that these cells should be clicked on
Range("costSummary").Offset(iCounter, 5) = "Unspecified"
With Range("costSummary").Offset(iCounter, 5).Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Range("costSummary").Offset(iCounter, 5).Font.ColorIndex = 36
Application.ScreenUpdating = True
End Sub
Sub editSummaryExchanger()
Dim rRange
Application.ScreenUpdating = False
iCounter = 0
strExchangerMOC = "E-" & (Range("unitNumber")) + Val(iSelection)
Do While Range("costSummary").Offset(iCounter, 0) <> strExchangerMOC
iCounter = iCounter + 1
Loop
Range("costSummary").Offset(iCounter, 0) = "=""E-"" & unitNumber + " & Val(iSelection)
iTemp = roundAmount(Range("totalModuleFactor") * lCBM0 / 397 * Range("CEPCI"))
Range("costSummary").Offset(iCounter, 1) = "=ROUND(totalModuleFactor*" & lCBM & _
", " & iTemp & ")"
Range("costSummary").Offset(iCounter, 1).Style = "Currency"
Range("costSummary").Offset(iCounter, 1).NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Call exchangerGrassRoots
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);_($* ""-""??_);_(@_)"
Application.ScreenUpdating = True
End Sub
Sub exchangerGrassRoots()
strGrassRootsSuffix = "FMCSCS"
If exchangerForm.obTeflon = True Or exchangerForm.obSpiralPlate = True _
Or exchangerForm.obFlat = True Or exchangerForm.obAir = True Then
strGrassRootsSuffix = "FMCS"
End If
sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & strGrassRootsSuffix).Value
lCBM0 = lCp * (sB1 + sB2 * sFM)
End Sub
