Public Sub Callback1(control As IRibbonControl)

    check
End Sub

Public Sub Callback2(control As IRibbonControl)

    Preparation

End Sub

Public Sub Callback3(control As IRibbonControl)

    cancella
End Sub

Public Sub Callback4(control As IRibbonControl)

    
    UserForm1.MultiPage1.Value = 0
    UserForm1.Show
End Sub
Public Sub Callback5(control As IRibbonControl)

    
    SolverCall
End Sub
Public Sub Callback6(control As IRibbonControl)

    
    Grafico
End Sub



Public Sub ConvertLenght()

For Each c In Range(ActiveWorkbook.Names("lenghtprofile"))
 c.Value = modify_lenght(c.Value)
Next
Range(ActiveWorkbook.Names("lcheck")).Value = "m"

End Sub
Public Sub ConvertAltitude()


For Each c In Range(ActiveWorkbook.Names("Altitude"))
 c.Value = modify_altitude(c.Value)
Next
Range(ActiveWorkbook.Names("acheck")).Value = "m"

End Sub
Public Sub ConvertPressure()

b = Range(ActiveWorkbook.Names("pcheck")).Value
For Each c In Range(ActiveWorkbook.Names("PressureProfile"))
Select Case b
    Case "bara", "psia", "KPaa", "Paa", "MPaa"
            c.Value = modify_pressure(c.Value) - Cells(c.Row, c.Column + 2).Value
    Case "barg", "psig", "KPag", "Pag", "MPag"
            c.Value = modify_pressure(c.Value)
End Select
Next
Range(ActiveWorkbook.Names("pcheck")).Value = "barg"

End Sub

Public Function Lconvert(a)

Select Case a

    Case "m"
        Lconvert = 1
    Case "km"
        Lconvert = 1000
    Case "in"
        Lconvert = 0.0254
    Case "ft"
        Lconvert = 0.3048
        
End Select

End Function

Public Function Pconvert(a)

Select Case a

    Case "barg", "bara"
        Pconvert = 1
    Case "psig", "psia"
        Pconvert = 0.0689476
    Case "KPag", "KPaa"
        Pconvert = 0.01
    Case "Pag", "Paa"
        Pconvert = 10 ^ -5
    Case "MPag", "MPaa"
        Pconvert = 10
End Select




End Function


Public Function modify_lenght(a)
lck = Range(ActiveWorkbook.Names("lcheck")).Value
modify_lenght = a * Lconvert(lck)

End Function
Public Function modify_altitude(a)
ack = Range(ActiveWorkbook.Names("acheck")).Value
modify_altitude = a * Lconvert(ack)

End Function

Public Function modify_pressure(a)
pck = Range(ActiveWorkbook.Names("pcheck")).Value
modify_pressure = a * Pconvert(pck)

End Function

Public Sub Area()
 
 Pi = Excel.WorksheetFunction.Pi
 
For Each c In Range(ActiveWorkbook.Names("diameter"))
    
    col = c.Column
    Row = c.Row
    tk = col + 1
    sup = col + 5
    
    Diam = (c.Value - 2 * Cells(Row, tk).Value) / 1000
    Cells(Row, sup).Value = (Pi * Diam ^ 2) / 4

Next
   
End Sub

Public Sub Temperature()

For Each d In Range(ActiveWorkbook.Names("diameter"))
    iniz = d.Column + 2
    fin = d.Column + 3
    rowin = d.Row
    Ttcol = d.Column + 4
    Tt = Cells(rowin, Ttcol).Value
    Viniz = Cells(rowin, iniz).Value
    Vfin = Cells(rowin, fin).Value
        For Each e In Range(ActiveWorkbook.Names("lenghtprofile"))
        Row = e.Row
        col = e.Column
        Tcol = col + 3
        m = e.Value
            If m >= Viniz And m <= Vfin Then
                Cells(Row, Tcol).Value = Tt
            Else
            End If
        Next
    Next
    
End Sub

Public Sub Prif()

R = 1000 * Range(ActiveWorkbook.Names("CgasMW")).Value
CostExp = (9.81 / (R * (273.15 + 20)))

For Each c In Range(ActiveWorkbook.Names("Altitude"))
    col = c.Column
    Row = c.Row
    colP = col + 3
    h = c.Value
    P0 = 1.01325
    Cells(Row, colP).Value = P0 * Exp(-CostExp * h)
Next
End Sub

Public Sub Volume()

For Each d In Range(ActiveWorkbook.Names("diameter"))
    iniz = d.Column + 2
    fin = d.Column + 3
    rowin = d.Row
    Acol = d.Column + 5
    surface = Cells(rowin, Acol).Value
    Viniz = Cells(rowin, iniz).Value
    Vfin = Cells(rowin, fin).Value
        For Each e In Range(ActiveWorkbook.Names("lenghtprofile"))
        Row = e.Row
        col = e.Column
        Vcol = col + 5
        m = e.Value
            If m >= Viniz And m <= Vfin Then
                   Cells(Row, Vcol).Value = surface * Abs((Cells(Row, col).Value - Cells(Row + 1, col)))
            Else
            End If
        Next
    Next
 Range(ActiveWorkbook.Names("Vol")).End(xlDown).Select
 ActiveCell.Delete
End Sub


Public Sub Pequib_iniz()

Range(ActiveWorkbook.Names("PEquilib")).Value = Excel.WorksheetFunction.Average(Range(ActiveWorkbook.Names("PressureProfile")))

End Sub

Public Sub gamma1()

Range(ActiveWorkbook.Names("ggamma1")).Value = Excel.WorksheetFunction.Average(Range(ActiveWorkbook.Names("gamma")))
End Sub

Public Sub Volume_Final_formula()

nc = Range(ActiveWorkbook.Names("Vol")).Count + 5
Range("g6").Formula = "=F6*(EXP(-(PEquilib-C6)/(ggamma1*PEquilib)))"
Range("g6").Select
Selection.AutoFill Destination:=Range("G6:G" & nc)
'Range("g6").Formula = "=F6*(EXP(-(PEquilib-C6)/(gamma*PEquilib)))"

End Sub

Public Sub density_final_formula()

nc = Range(ActiveWorkbook.Names("Vol")).Count + 5
Range("h6").Formula = "=((PEquilib+E6)*100)/(CgasMW*(D6+273.15))"
Range("h6").Select
Selection.AutoFill Destination:=Range("h6:h" & nc)

End Sub


Public Sub work_final_formula()

nc = Range(ActiveWorkbook.Names("Vol")).Count + 5
Range("i6").Formula = "=((PEquilib+E6)*G6-(C6+E6)*F6)/(1-ggamma1)"
Range("i6").Select
Selection.AutoFill Destination:=Range("i6:i" & nc)

End Sub

Public Sub shiftwork_final_formula()

nc = Range(ActiveWorkbook.Names("Vol")).Count + 5
Range("j6").Formula = "=(H6*9.81*ABS(F6-G6)*(B6-B7))"
Range("j6").Select
Selection.AutoFill Destination:=Range("j6:j" & nc)

End Sub

Public Sub delta()

Range(ActiveWorkbook.Names("deltawork")).Formula = "=SUM(Work)+SUM(shiftwork)"
Range(ActiveWorkbook.Names("deltavol")).Formula = "=abs(sum(Vol)-SUM(VOlfinal))"
Range(ActiveWorkbook.Names("deltaperc")).Formula = "=IF(deltavol<>"""",(ABS(SUM(Vol)-SUM(Volfinal))/SUM(Vol)),"""")"

End Sub


Public Sub PressProf()

nc = Range(ActiveWorkbook.Names("lenghtprofile")).Count + 5
Range("k6").Formula = "=PEquilib+(9.81*H6*(0-B6))/100000"
Range("k7").Formula = "=K6+(9.81*H6*(B6-B7)/100000)"
Range("k7").Select
Selection.AutoFill Destination:=Range("k7:k" & nc)

End Sub


Public Sub check()

'CHECK LENGHT DATA

clenght = 0
For Each e In Range(ActiveWorkbook.Names("lenghtprofile"))
If Not IsNumeric(e) Or IsEmpty(e) Then
    With e.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    clenght = clenght + 1
    Else
    With e.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    clenght = clenght + 0
    End If
  Next

'CHECK ALTITUDE DATA

caltitude = 0
For Each f In Range(ActiveWorkbook.Names("Altitude"))
If Not IsNumeric(f) Or IsEmpty(f) Then
    With f.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    caltitude = caltitudet + 1
    Else
    With f.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    caltitude = caltitude + 0
    End If
  Next

'CHECK PRESSURE PROFILE DATA


cpress = 0
For Each g In Range(ActiveWorkbook.Names("PressureProfile"))
If Not IsNumeric(g) Or IsEmpty(g) Then
    With g.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    cpress = cpress + 1
    Else
    With g.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    cpress = cpress + 0
    End If
  Next

'CHECK DIAMETER DATA

n = Range(ActiveWorkbook.Names("diameter")).Count + 5
'Dd = Range("M6", "Q" & n)

cdiam = 0
For Each h In Range("M6", "Q" & n)
If Not IsNumeric(h) Or IsEmpty(h) Then
    With h.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    cdiam = cdiam + 1
    Else
    With h.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    cdiam = cdiam + 0
    End If
  Next

'CHECK GAS DATA

cgasd = 0
For Each i In Range(ActiveWorkbook.Names("gdata"))
If Not IsNumeric(i) Or IsEmpty(i) Then
    With i.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    cgasd = cgasd + 1
    Else
    With i.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    cgasd = cgasd + 0
    End If
  Next

'Messaggi

'Lunghezza

If clenght = 0 Then
    txtL = "OK"
Else
    txtL = "there isare " & clenght & " cell(s) that isare not numbers. Check yellow cells and correct"
End If

'Altitude

If caltitude = 0 Then
    txtA = "OK"
Else
    txtA = "there isare " & clenght & " cell(s) that isare not numbers. Check yellow cells and correct"
End If

'Pressure

If cpress = 0 Then
    txtP = "OK"
Else
    txtP = "there isare " & clenght & " cell(s) that isare not numbers. Check yellow cells and correct"
End If

'Diameter

If cdiam = 0 Then
    txtD = "OK"
Else
    txtD = "there isare " & clenght & " cell(s) that isare not numbers. Check yellow cells and correct"
End If

'Gas
If cgasd = 0 Then
    txtG = "OK"
Else
    txtGD = "there isare " & clenght & " cell(s) that isare not numbers. Check yellow cells and correct"
End If


somma = clenght + caltitude + cpress + cdiam + cgasd

If somma = 0 Then
    txtS = "Check complete. Can proceed with Sheet preparation"
    Range(ActiveWorkbook.Names("start")).Value = 1
Else
    txtS = "Correct and Check again"
    Range(ActiveWorkbook.Names("start")).Value = 0
End If

MsgBox "Lenght: " & txtL & vbNewLine & "Altitude: " & txtA & vbNewLine & "Pressure: " & txtP & vbNewLine & "Pipeline geometry: " & txtD & vbNewLine & "Gas Prop: " & txtG & vbNewLine & txtS



End Sub


Public Sub Preparation()

If Range(ActiveWorkbook.Names("start")).Value = 0 Then
    MsgBox "Before preparing Worksheet check data, then try again"
Else

    ConvertLenght
    ConvertAltitude
    ConvertPressure
    Area
    Temperature
    Prif
    Volume
    Pequib_iniz
    gamma1
    Volume_Final_formula
    density_final_formula
    work_final_formula
    shiftwork_final_formula
    delta
    PressProf


End If
    
MsgBox "Worksheet ready to be used"



End Sub



Public Sub cancella()

mm = Range(ActiveWorkbook.Names("lenghtprofile")).Count + 5
nn = Range(ActiveWorkbook.Names("diameter")).Count + 5
Range("A6", "K" & mm).ClearContents
Range("M6", "R" & nn).ClearContents
Range("U6", "W6").ClearContents
Range(ActiveWorkbook.Names("PEquilib")).ClearContents
Range(ActiveWorkbook.Names("ggamma1")).ClearContents
Range(ActiveWorkbook.Names("deltawork")).ClearContents
Range(ActiveWorkbook.Names("deltavol")).ClearContents
Range(ActiveWorkbook.Names("start")).Value = 0

End Sub



Public Sub SolverCall()
If Range(ActiveWorkbook.Names("start")).Value = 0 Then
    MsgBox "Before solving, check and prepare Worksheet, then try again"
Else



    SolverAdd CellRef:="$F$2", Relation:=1, FormulaText:="0.1"
    SolverOk SetCell:="$E$1", MaxMinVal:=3, ValueOf:=0, ByChange:="$B$1", Engine:=1 _
        , EngineDesc:="GRG Nonlinear"
    
    SolverSolve
    'SolverOk SetCell:="$E$1", MaxMinVal:=3, ValueOf:=0, ByChange:="$B$1", Engine:=1 _
       ' , EngineDesc:="GRG Nonlinear"
   ' SolverAdd CellRef:="$F$2", Relation:=1, FormulaText:="0.1"
    
    'SolverSolve


End If
    



End Sub



Sub Grafico()
'
'
'
Dim sh As Worksheet

'
Application.DisplayAlerts = False
For i = 1 To Worksheets.Count
    If ActiveWorkbook.Sheets(i).Name = "SD Pressure" Then
    a = ActiveWorkbook.Sheets(i).Name
        ActiveWorkbook.Sheets(i).Delete
    End If
Next i
    
    
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
    ActiveChart.SetSourceData Source:=Range( _
        "lenghtprofile,PressureProfile,Peqprofile")
        
With ActiveChart
     'chart name
    .HasTitle = True
    .ChartTitle.Characters.Text = "Profilo Pressione"
     'X axis name
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Lenght (m)"
     'y-axis name
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Pressure (barg)"
    .SetElement (msoElementLegendTop)
    .SeriesCollection(1).Name = "Pressure"
    .SeriesCollection(2).Name = "SD Pressure"
    .Location Where:=xlLocationAsNewSheet, Name:="SD Pressure"
End With
        
    
End Sub