macro.bas

Private oListener as Object
Private CellRng as Object

Sub AddListener
Dim Doc, Sheet as Object

Doc = ThisComponent
Sheet = Doc.Sheets(0)

CellRng = Sheet.getCellRangeByName("C1:C6")

oListener = createUnoListener("Modify_", "com.sun.star.util.XModifyListener")
CellRng.addModifyListener(oListener)

End Sub

Sub Modify_disposing(oEv)
End Sub

Sub RmvListener
CellRng.removeModifyListener(oListener)
End Sub

Sub Modify_modified(oEv)

oDoc = ThisComponent
Sheet0 = oDoc.Sheets(0)
EngineSheet = oDoc.Sheets(1)
FuelTankSheet = oDoc.Sheets(2)
SRBSheet = oDoc.Sheets(3)

RowOffset = 7
ColOffset = 7

Sheet0.getCellRangeByName("A8:ZZ9999").clearContents(23)
Sheet0.getCellByPosition(0, RowOffset).setString("Engine Type")
Sheet0.getCellByPosition(1, RowOffset).setString("Total Mass (t)")
Sheet0.getCellByPosition(2, RowOffset).setString("# Engines")
Sheet0.getCellByPosition(3, RowOffset).setString("TWR")
Sheet0.getCellByPosition(4, RowOffset).setString("Delta V (m/s)")
Sheet0.getCellByPosition(5, RowOffset).setString("Constraint1")
Sheet0.getCellByPosition(6, RowOffset).setString("Constraint2")
Sheet0.getCellByPosition(7, RowOffset).setString("# Parts")

NumFuelTankTypes = FuelTankSheet.getCellByPosition(1, 0).getValue()

For FuelTankIdx = 1 To NumFuelTankTypes
  HeaderCell = Sheet0.getCellByPosition(ColOffset+FuelTankIdx, RowOffset)
  NameCell = FuelTankSheet.getCellByPosition(0, 2+FuelTankIdx)
  HeaderCell.setString("# " & NameCell.getString())
Next FuelTankIdx

smgr = GetProcessServiceManager()
solv = smgr.createInstance("com.sun.star.comp.Calc.Solver")
solv.Document = oDoc

Dim Variables(NumFuelTankTypes) as Object
Dim Constraints(4+NumFuelTankTypes) as Object
Dim ThisConstraint as New com.sun.star.sheet.SolverConstraint

ConstrOperatorLE = com.sun.star.sheet.SolverConstraintOperator.LESS_EQUAL
ConstrOperatorGE = com.sun.star.sheet.SolverConstraintOperator.GREATER_EQUAL

NumEngineTypes = EngineSheet.getCellByPosition(1, 0).getValue()
BestEngineIdx = 0

ColNames = Sheet0.getColumns.ElementNames

For EngineIdx = 1 To NumEngineTypes
  RowIdx = RowOffset+EngineIdx
  
  HeaderCell = Sheet0.getCellByPosition(0, RowIdx)
  NameCell = EngineSheet.getCellByPosition(0, 2+EngineIdx)
  HeaderCell.setString(NameCell.getString())
  
  MassCell = Sheet0.getCellByPosition(1, RowIdx)
  Mass = "=C$1+Engines.B" & 3+EngineIdx & "*C" & RowIdx+1
  For FuelTankIdx = 1 To NumFuelTankTypes
    Mass = Mass & "+FuelTanks.B$" & 3+FuelTankIdx & "*"
    Mass = Mass & ColNames(ColOffset+FuelTankIdx) & RowIdx+1
  Next FuelTankIdx
  MassCell.setFormula(Mass)
  
  TWRCell = Sheet0.getCellByPosition(3, RowIdx)
  Thrust = "=Engines.C" & 3+EngineIdx & "*C" & RowIdx+1
  Weight = "9.81*B" & RowIdx+1
  TWRCell.setFormula(Thrust & "/(" & Weight & ")")
  TWRConstraintCell = Sheet0.getCellByPosition(5, RowIdx)
  TWRConstraintCell.setFormula(Thrust & "-C$2*" & Weight)
  
  DVCell = Sheet0.getCellByPosition(4, RowIdx)
  Ve = "9.82*(C$4*Engines.D" & 3+EngineIdx
  Ve = Ve & "+(1-C$4)*Engines.E" & 3+EngineIdx & ")"
  FuelMass = "(0"
  For FuelTankIdx = 1 To NumFuelTankTypes
    FuelMass = FuelMass & "+FuelTanks.D$" & 3+FuelTankIdx & "*"
    FuelMass = FuelMass & ColNames(ColOffset+FuelTankIdx) & RowIdx+1
  Next FuelTankIdx
  FuelMass = FuelMass & ")"
  DV = Ve & "*LN(B" & RowIdx+1 & "/(B" & RowIdx+1 & "-" & FuelMass & "))"
  DVCell.setFormula("=" & DV)
  DVConstraintCell = Sheet0.getCellByPosition(6, RowIdx)
  DVConstraint = "=B" & RowIdx+1 & "*(1-EXP(C$3/(" & Ve & ")))+"
  DVConstraint = DVConstraint & "EXP(C$3/(" & Ve & "))*" & FuelMass
  DVConstraintCell.setFormula(DVConstraint)
  
  NumPartsCell = Sheet0.getCellByPosition(7, RowIdx)
  NumParts = "=C" & RowIdx+1
  For FuelTankIdx = 1 To NumFuelTankTypes
    NumParts = NumParts & "+" & ColNames(ColOffset+FuelTankIdx) & RowIdx+1
  Next FuelTankIdx
  NumPartsCell.setFormula(NumParts)
  
  solv.Objective = MassCell.CellAddress
  Variables(0) = Sheet0.getCellByPosition(2, RowIdx).CellAddress
  For FuelTankIdx = 1 To NumFuelTankTypes
    VariCell = Sheet0.getCellByPosition(ColOffset+FuelTankIdx, RowIdx)
    Variables(FuelTankIdx) = VariCell.CellAddress
  Next FuelTankIdx
  solv.Variables = Variables()
  
  ThisConstraint.Left = Variables(0)
  ThisConstraint.Operator = ConstrOperatorGE
  ThisConstraint.Right = 1
  Constraints(0) = ThisConstraint
  
  ThisConstraint.Left = Variables(0)
  ThisConstraint.Operator = ConstrOperatorLE
  ThisConstraint.Right = Sheet0.getCellByPosition(2, 4).CellAddress
  Constraints(1) = ThisConstraint
  
  ThisConstraint.Left = TWRConstraintCell.CellAddress
  ThisConstraint.Operator = ConstrOperatorGE
  ThisConstraint.Right = 0
  Constraints(2) = ThisConstraint
  
  ThisConstraint.Left = DVConstraintCell.CellAddress
  ThisConstraint.Operator = ConstrOperatorGE
  ThisConstraint.Right = 0
  Constraints(3) = ThisConstraint

  ThisConstraint.Left = NumPartsCell.CellAddress
  ThisConstraint.Operator = ConstrOperatorLE
  ThisConstraint.Right = Sheet0.getCellByPosition(2, 5).CellAddress
  Constraints(4) = ThisConstraint
  
  For FuelTankIdx = 1 To NumFuelTankTypes
    ThisConstraint.Left = Variables(FuelTankIdx)
    ThisConstraint.Operator = ConstrOperatorLE
    MaxNumTanksCell = FuelTankSheet.getCellByPosition(4, 2+FuelTankIdx)
    ThisConstraint.Right = MaxNumTanksCell.CellAddress
    Constraints(4+FuelTankIdx) = ThisConstraint 
  Next FuelTankIdx  
  
  solv.Constraints = Constraints()
  solv.Maximize = False
  solv.NonNegative = True
  solv.Integer = True
  solv.Solve()
  
  If solv.Success And solv.ResultValue < 1e308 Then
    Sheet0.getCellByPosition(2, RowIdx).setValue(solv.Solution(0))
    For FuelTankIdx = 1 To NumFuelTankTypes
      NumTanksCell = Sheet0.getCellByPosition(ColOffset+FuelTankIdx, RowIdx)
      NumTanksCell.setValue(solv.Solution(FuelTankIdx))
    Next FuelTankIdx
    
    If BestEngineIdx = 0 Or solv.ResultValue <= BestEngineMass Then
      BestEngineIdx = EngineIdx
      BestEngineMass = solv.ResultValue
    End If
  Else
    Sheet0.getCellByPosition(2, RowIdx).setFormula("=1/0")
  End If
Next EngineIdx

PayloadMass = Sheet0.getCellByPosition(2, 0).getValue()
MinTWR = Sheet0.getCellByPosition(2, 1).getValue()
MinDV = Sheet0.getCellByPosition(2, 2).getValue()
AtmPressure = Sheet0.getCellByPosition(2, 3).getValue()
MaxNumEngines = Sheet0.getCellByPosition(2, 4).getValue()
MaxNumParts = Sheet0.getCellByPosition(2, 5).getValue()

NumSRBTypes = SRBSheet.getCellByPosition(1, 0).getValue()

For EngineIdx = NumEngineTypes+1 To NumEngineTypes+NumSRBTypes
  RowIdx = RowOffset+EngineIdx
  SRBIdx = EngineIdx-NumEngineTypes
  
  HeaderCell = Sheet0.getCellByPosition(0, RowIdx)
  NameCell = SRBSheet.getCellByPosition(0, 2+SRBIdx)
  HeaderCell.setString(NameCell.getString())
  
  MassCell = Sheet0.getCellByPosition(1, RowIdx)
  MassCell.setFormula("=C$1+SRBs.B" & 3+SRBIdx & "*C" & RowIdx+1)
  
  TWRCell = Sheet0.getCellByPosition(3, RowIdx)
  Thrust = "=SRBs.D" & 3+SRBIdx & "*C" & RowIdx+1
  TWRCell.setFormula(Thrust & "/(9.81*B" & RowIdx+1 & ")")
  
  DVCell = Sheet0.getCellByPosition(4, RowIdx)
  Ve = "9.82*(C$4*SRBs.E" & 3+SRBIdx & "+(1-C$4)*SRBs.F" & 3+SRBIdx & ")"
  DryMass = "(C$1+SRBs.C" & 3+SRBIdx & "*C" & RowIdx+1 & ")"
  DVCell.setFormula("=" & Ve & "*LN(B" & RowIdx+1 & "/" & DryMass & ")")
  
  SRBFullMass = SRBSheet.getCellByPosition(1, 2+SRBIdx).getValue()
  SRBDryMass = SRBSheet.getCellByPosition(2, 2+SRBIdx).getValue()
  SRBThrust = SRBSheet.getCellByPosition(3, 2+SRBIdx).getValue()
  SRBAtmIsp = SRBSheet.getCellByPosition(4, 2+SRBIdx).getValue()
  SRBVacIsp = SRBSheet.getCellByPosition(5, 2+SRBIdx).getValue()
  
  SRBIsp = AtmPressure*SRBAtmIsp + (1-AtmPressure)*SRBVacIsp
  If SRBIsp = 0 Then
    MassRatio = 999
  Else
    MassRatio = Exp(MinDV/(9.82*SRBIsp))
  End If
  
  If SRBThrust > 9.81*MinTWR*SRBFullMass Then
    NumSRBs = 9.81*MinTWR*PayloadMass/(SRBThrust-9.81*MinTWR*SRBFullMass)
  Else
    NumSRBs = -1
  End If
  
  If SRBFullMass > SRBDryMass*MassRatio Then
    NumSRBs2 = PayloadMass*(MassRatio-1)/(SRBFullMass-SRBDryMass*MassRatio)
    If NumSRBs2 > NumSRBs And Not(NumSRBs = -1) Then
      NumSRBs = NumSRBs2
    End If
  Else
    NumSRBs = -1
  End If
  
  If NumSRBs = 0 Or NumSRBs > Int(NumSRBs) Then
    REM round up fractional numbers of SRBs
    NumSRBs = Int(NumSRBs) + 1
  End If
  
  If NumSRBs > MaxNumEngines Or NumSRBs > MaxNumParts Then
    NumSRBs = -1
  End If
  
  If NumSRBs > 0 Then
    Sheet0.getCellByPosition(2, RowIdx).setValue(NumSRBs)
    TotalMass = PayloadMass + SRBFullMass*NumSRBs
    If BestEngineIdx = 0 Or TotalMass <= BestEngineMass Then
      BestEngineIdx = EngineIdx
      BestEngineMass = TotalMass
    End If
  Else
    Sheet0.getCellByPosition(2, RowIdx).setFormula("=1/0")
  End If
Next EngineIdx

If BestEngineIdx = 0 Then
  Result = "Conditions are infeasible"
Else
  Result = Sheet0.getCellByPosition(0, RowOffset+BestEngineIdx).getString
  Result = Result & " is mass-optimal engine type"
End If
RowIdx = RowOffset+NumEngineTypes+NumSRBTypes
Sheet0.getCellByPosition(0, RowIdx+1).setString("")
Sheet0.getCellByPosition(0, RowIdx+2).setString(Result)
End Sub