Procédure trop grande : Comment réduire ?
Bonjour,
Lorsque que je lance ma macro, j'ai un message d'erreur "procédure trop grande". Comment je peux faire pour réduire ma macro ? Je vous la partage ci-dessous :
[code]Sub FINAL()
Application.ScreenUpdating = False
'Calculation for the Open Cooling tower with Axial fan in counterflow
Sheets("TO hélicoidal").Activate
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("TO hélicoidal").Cells(65, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("TO hélicoidal").Cells(65, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("TO hélicoidal").Cells(65, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("TO hélicoidal").Cells(65, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("TO hélicoidal").Cells(65, i - 1)
Next i
For i = 2 To 13
Cells(64, i) = Sheets("Données d'entrées utilisateur").Range("B17")
If Cells(60, i) <= 0 Then
Cells(60, i) = 0.5
End If
Next
For i = 2 To 13
If Cells(64, i) - 2 <= Cells(4, i) Then
Cells(66, i).Interior.ColorIndex = 3
Cells(64, i).Select
Selection.Copy
Cells(67, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Cells(67, i) = Cells(64, i)
Cells(66, i).Interior.ColorIndex = 2
End If
If Cells(64, i) - 2 <= Cells(4, i) Then
Cells(81, i) = 1
Cells(64, i) = Cells(4, i) + 3
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Else
Cells(81, i) = 1
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
End If
Next i
p = 3
For i = 2 To 13
If Cells(60, i) > 4 Then
Do
Cells(81, i) = 1
Cells(60, i) = 0.5
Cells(64, i) = Cells(4, i) + p
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
p = p + 0.01
Loop Until Cells(60, i) <= 3.5
End If
Next i
For i = 2 To 13
Do
Cells(81, i) = Cells(81, i) - 0.0005
Loop Until Cells(87, i) <= 0
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("Calcul Chiller").Activate
For i = 3 To 14
b = Cells(9, i).Value
c = Cells(5, i).Value
Y1 = b >= 10 And b < 12.5
Y2 = b >= 12.5 And b < 15
Y3 = b >= 15 And b < 17.5
Y4 = b >= 17.5 And b < 20
Y5 = b >= 20 And b < 22.5
Y6 = b >= 22.5 And b < 25
Y7 = b >= 25 And b < 27.5
Y8 = b >= 27.5 And b < 30
Y9 = b >= 30 And b < 32.5
Y10 = b >= 32.5 And b < 35
Y11 = b >= 35 And b < 37.5
Y12 = b >= 37.5 And b < 40
Y13 = b >= 40 And b < 60
If Y1 Then
Cells(6, i) = -19.579 * c ^ 3 + 30.073 * c ^ 2 - 11.613 * c + 9.8294
ElseIf Y2 Then
Cells(6, i) = -9.9568 * c ^ 2 + 11.362 * c + 7.0693
ElseIf Y3 Then
Cells(6, i) = 26.895 * c ^ 3 - 57.613 * c ^ 2 + 34.33 * c + 4.6334
ElseIf Y4 Then
Cells(6, i) = 19.11 * c ^ 3 - 42.891 * c ^ 2 + 26.919 * c + 4.8065
ElseIf Y5 Then
Cells(6, i) = 15.46 * c ^ 3 - 34.518 * c ^ 2 + 22.057 * c + 4.7047
ElseIf Y6 Then
Cells(6, i) = 13.855 * c ^ 3 - 31.919 * c ^ 2 + 20.806 * c + 4.1679
ElseIf Y7 Then
Cells(6, i) = 12.25 * c ^ 3 - 29.32 * c ^ 2 + 19.555 * c + 3.6311
ElseIf Y8 Then
Cells(6, i) = 10.32 * c ^ 3 - 25.491 * c ^ 2 + 17.413 * c + 3.4137
ElseIf Y9 Then
Cells(6, i) = 8.3902 * c ^ 3 - 21.663 * c ^ 2 + 15.272 * c + 3.1962
ElseIf Y10 Then
Cells(6, i) = 7.3327 * c ^ 3 - 19.049 * c ^ 2 + 13.628 * c + 2.8743
ElseIf Y11 Then
Cells(6, i) = 6.2753 * c ^ 3 - 16.435 * c ^ 2 + 11.984 * c + 2.5524
ElseIf Y12 Then
Cells(6, i) = 5.6145 * c ^ 3 - 14.571 * c ^ 2 + 10.628 * c + 2.3383
ElseIf Y13 Then
Cells(6, i) = 4.9537 * c ^ 3 - 12.706 * c ^ 2 + 9.2711 * c + 2.1243
Else
Cells(6, i) = "Erreur"
End If
Next
Sheets("Calcul Chiller").Activate
Range("B1:N15").Select
Selection.Copy
Range("A18") = " OCT Axial fan counterflow"
Range("B18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul PCW").Activate
Range("C1:O11").Select
Selection.Copy
Range("B14") = " OCT Axial fan counterflow"
Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Air comprimé").Activate
Range("A1:M69").Select
Selection.Copy
Range("N72") = " OCT Axial fan counterflow"
Range("A72").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Autre").Activate
Range("A1:M6").Select
Selection.Copy
Range("N9") = " OCT Axial fan counterflow"
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul TAR").Activate
Range("A1:M15").Select
Selection.Copy
Range("N18") = " OCT Axial fan counterflow"
Range("A18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("TO hélicoidal").Activate
For i = 2 To 13
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("TO hélicoidal cross").Activate
For t = 1 To 2
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Next i
For i = 2 To 13
Cells(64, i) = Sheets("Données d'entrées utilisateur").Range("B17")
If Cells(64, i) - 2 <= Cells(4, i) Then
Cells(66, i).Interior.ColorIndex = 3
Cells(64, i).Select
Selection.Copy
Cells(67, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Cells(67, i) = Cells(64, i)
Cells(66, i).Interior.ColorIndex = 2
End If
If Cells(64, i) - 2 <= Cells(4, i) Then
Cells(81, i) = 1
Cells(64, i) = Cells(4, i) + 3
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Else
Cells(81, i) = 1
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
End If
Next i
p = 3
For i = 2 To 13
If Cells(60, i) > 4 Then
Do
Cells(81, i) = 1
Cells(60, i) = 0.4
Cells(64, i) = Cells(4, i) + p
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
p = p + 0.01
Loop Until Cells(60, i) <= 3.5
End If
Next i
For i = 2 To 13
Do
Cells(81, i) = Cells(81, i) - 0.0005
Loop Until Cells(87, i) <= 0
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("TO hélicoidal cross").Cells(65, i - 1)
Next i
t = t + 1
Next t
Sheets("Calcul Réseau ER").Activate
For i = 5 To 16
Cells(13, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("Calcul Chiller").Activate
For i = 3 To 14
b = Cells(9, i).Value
c = Cells(5, i).Value
Y1 = b >= 10 And b < 12.5
Y2 = b >= 12.5 And b < 15
Y3 = b >= 15 And b < 17.5
Y4 = b >= 17.5 And b < 20
Y5 = b >= 20 And b < 22.5
Y6 = b >= 22.5 And b < 25
Y7 = b >= 25 And b < 27.5
Y8 = b >= 27.5 And b < 30
Y9 = b >= 30 And b < 32.5
Y10 = b >= 32.5 And b < 35
Y11 = b >= 35 And b < 37.5
Y12 = b >= 37.5 And b < 40
Y13 = b >= 40 And b < 60
If Y1 Then
Cells(6, i) = -19.579 * c ^ 3 + 30.073 * c ^ 2 - 11.613 * c + 9.8294
ElseIf Y2 Then
Cells(6, i) = -9.9568 * c ^ 2 + 11.362 * c + 7.0693
ElseIf Y3 Then
Cells(6, i) = 26.895 * c ^ 3 - 57.613 * c ^ 2 + 34.33 * c + 4.6334
ElseIf Y4 Then
Cells(6, i) = 19.11 * c ^ 3 - 42.891 * c ^ 2 + 26.919 * c + 4.8065
ElseIf Y5 Then
Cells(6, i) = 15.46 * c ^ 3 - 34.518 * c ^ 2 + 22.057 * c + 4.7047
ElseIf Y6 Then
Cells(6, i) = 13.855 * c ^ 3 - 31.919 * c ^ 2 + 20.806 * c + 4.1679
ElseIf Y7 Then
Cells(6, i) = 12.25 * c ^ 3 - 29.32 * c ^ 2 + 19.555 * c + 3.6311
ElseIf Y8 Then
Cells(6, i) = 10.32 * c ^ 3 - 25.491 * c ^ 2 + 17.413 * c + 3.4137
ElseIf Y9 Then
Cells(6, i) = 8.3902 * c ^ 3 - 21.663 * c ^ 2 + 15.272 * c + 3.1962
ElseIf Y10 Then
Cells(6, i) = 7.3327 * c ^ 3 - 19.049 * c ^ 2 + 13.628 * c + 2.8743
ElseIf Y11 Then
Cells(6, i) = 6.2753 * c ^ 3 - 16.435 * c ^ 2 + 11.984 * c + 2.5524
ElseIf Y12 Then
Cells(6, i) = 5.6145 * c ^ 3 - 14.571 * c ^ 2 + 10.628 * c + 2.3383
ElseIf Y13 Then
Cells(6, i) = 4.9537 * c ^ 3 - 12.706 * c ^ 2 + 9.2711 * c + 2.1243
Else
Cells(6, i) = "Erreur"
End If
Next
Sheets("Calcul Chiller").Activate
Range("B1:N15").Select
Selection.Copy
Range("A35") = " OCT Axial fan crossflow"
Range("B35").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul PCW").Activate
Range("C1:O11").Select
Selection.Copy
Range("B27") = " OCT Axial fan crossflow"
Range("C27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Air comprimé").Activate
Range("A1:M69").Select
Selection.Copy
Range("N143") = " OCT Axial fan crossflow"
Range("A143").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Autre").Activate
Range("A1:M6").Select
Selection.Copy
Range("N17") = " OCT Axial fan crossflow"
Range("A17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul TAR").Activate
Range("A1:M15").Select
Selection.Copy
Range("N35") = " OCT Axial fan crossflow"
Range("A35").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("TO hélicoidal cross").Activate
For i = 2 To 13
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("TO centrifuge").Activate
For t = 1 To 2
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("TO centrifuge").Cells(65, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("TO centrifuge").Cells(65, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("TO centrifuge").Cells(65, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("TO centrifuge").Cells(65, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("TO centrifuge").Cells(65, i - 1)
Next i
For i = 2 To 13
Cells(64, i) = Sheets("Données d'entrées utilisateur").Range("B17")
If Cells(64, i) - 1 <= Cells(4, i) Then
Cells(66, i).Interior.ColorIndex = 3
Cells(64, i).Select
Selection.Copy
Cells(67, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Cells(67, i) = Cells(64, i)
Cells(66, i).Interior.ColorIndex = 2
End If
If Cells(64, i) - 1 <= Cells(4, i) Then
Cells(81, i) = 1
Cells(64, i) = Cells(4, i) + 3
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Else
Cells(81, i) = 1
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
End If
Next i
p = 3
For i = 2 To 13
If Cells(60, i) > 4 Then
Do
Cells(81, i) = 1
Cells(60, i) = 0.5
Cells(64, i) = Cells(4, i) + p
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
p = p + 0.01
Loop Until Cells(60, i) <= 3.5
End If
Next i
For i = 2 To 13
Do
Cells(81, i) = Cells(81, i) - 0.0005
Loop Until Cells(87, i) <= 0
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("TO centrifuge").Cells(65, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("TO centrifuge").Cells(65, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("TO centrifuge").Cells(65, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("TO centrifuge").Cells(65, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("TO centrifuge").Cells(65, i - 1)
Next i
t = t + 1
Next t
Sheets("Calcul Réseau ER").Activate
For i = 5 To 16
Cells(13, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("Calcul Chiller").Activate
For i = 3 To 14
b = Cells(9, i).Value
c = Cells(5, i).Value
Y1 = b >= 10 And b < 12.5
Y2 = b >= 12.5 And b < 15
Y3 = b >= 15 And b < 17.5
Y4 = b >= 17.5 And b < 20
Y5 = b >= 20 And b < 22.5
Y6 = b >= 22.5 And b < 25
Y7 = b >= 25 And b < 27.5
Y8 = b >= 27.5 And b < 30
Y9 = b >= 30 And b < 32.5
Y10 = b >= 32.5 And b < 35
Y11 = b >= 35 And b < 37.5
Y12 = b >= 37.5 And b < 40
Y13 = b >= 40 And b < 60
If Y1 Then
Cells(6, i) = -19.579 * c ^ 3 + 30.073 * c ^ 2 - 11.613 * c + 9.8294
ElseIf Y2 Then
Cells(6, i) = -9.9568 * c ^ 2 + 11.362 * c + 7.0693
ElseIf Y3 Then
Cells(6, i) = 26.895 * c ^ 3 - 57.613 * c ^ 2 + 34.33 * c + 4.6334
ElseIf Y4 Then
Cells(6, i) = 19.11 * c ^ 3 - 42.891 * c ^ 2 + 26.919 * c + 4.8065
ElseIf Y5 Then
Cells(6, i) = 15.46 * c ^ 3 - 34.518 * c ^ 2 + 22.057 * c + 4.7047
ElseIf Y6 Then
Cells(6, i) = 13.855 * c ^ 3 - 31.919 * c ^ 2 + 20.806 * c + 4.1679
ElseIf Y7 Then
Cells(6, i) = 12.25 * c ^ 3 - 29.32 * c ^ 2 + 19.555 * c + 3.6311
ElseIf Y8 Then
Cells(6, i) = 10.32 * c ^ 3 - 25.491 * c ^ 2 + 17.413 * c + 3.4137
ElseIf Y9 Then
Cells(6, i) = 8.3902 * c ^ 3 - 21.663 * c ^ 2 + 15.272 * c + 3.1962
ElseIf Y10 Then
Cells(6, i) = 7.3327 * c ^ 3 - 19.049 * c ^ 2 + 13.628 * c + 2.8743
ElseIf Y11 Then
Cells(6, i) = 6.2753 * c ^ 3 - 16.435 * c ^ 2 + 11.984 * c + 2.5524
ElseIf Y12 Then
Cells(6, i) = 5.6145 * c ^ 3 - 14.571 * c ^ 2 + 10.628 * c + 2.3383
ElseIf Y13 Then
Cells(6, i) = 4.9537 * c ^ 3 - 12.706 * c ^ 2 + 9.2711 * c + 2.1243
Else
Cells(6, i) = "Erreur"
End If
Next
Sheets("Calcul Chiller").Activate
Range("B1:N15").Select
Selection.Copy
Range("A52") = " OCT centrifugal fan counterflow"
Range("B52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul PCW").Activate
Range("C1:O11").Select
Selection.Copy
Range("B40") = " OCT Axial fan counterflow"
Range("C40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Air comprimé").Activate
Range("A1:M69").Select
Selection.Copy
Range("N214") = " OCT centrifugal fan counterflow"
Range("A214").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Autre").Activate
Range("A1:M6").Select
Selection.Copy
Range("N25") = " OCT Centrifugal fan counterflow"
Range("A25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul TAR").Activate
Range("A1:M15").Select
Selection.Copy
Range("N52") = " OCT Centrifugal fan counterflow"
Range("A52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("TO centrifuge").Activate
For i = 2 To 13
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("Aéro sec").Activate
For i = 2 To 13
If Cells(74, i) < 1 Then
Cells(74, i) = 0.5
End If
Next
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = 50
Sheets("Calcul PCW").Cells(6, i + 1) = 50
Sheets("Calcul Air comprimé").Cells(20, i - 1) = 50
Sheets("Calcul Autre").Cells(5, i - 1) = 50
Sheets("Calcul TAR").Cells(2, i - 1) = 50
Next i
' equilibre les puissances pour partir sur des bases équilibrées
For i = 2 To 13
Cells(74, i).Interior.ColorIndex = 2 ' Remet tous les fond des cases en blanc
If Cells(60, i) >= Cells(76, i) Then ' Si la température d'entré d'air est supérieur à la température d'eau cible : impossible pour le système d'atteindre cette température ! Donc on bloque le ventilateur à 100%
Cells(74, i) = 1
Else
Cells(57, i) = Cells(60, i) + 7 ' permet d'éviter d'avoir une valeur négative dans le calcul de la température logarithmique moyenne (le LN n'accepte pas de valeur négative
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(74, i) ' égalise la puissance P = K*S*Tlm avec les besoins à refroidir en faisant varier la charge ventilateur (vitesse d'air)
Cells(89, i).GoalSeek goal:=0, changingcell:=Cells(74, i) ' égalise la puissance P = Qm*Cp*(Tfs-Tfe) avec les besoins en faisant varier la charge ventilo (Il s'agit de la pusance sur l'air ici : fluide froid)
End If
If Cells(74, i) > 1 Then ' Si le calcul d'égalisation des puissances ci-dessus dépasse 100% de charge ventilateur, je le redescend à 99% pour la suite du calcul (sinon la suite ne le prendra pas en compte à cause de la conditon "exit do" qui fera sortir de la boucle)
Cells(74, i) = 0.99
End If
If Cells(60, i) >= Cells(59, i) Then ' evite de créer une erreur dans le LN (valeur négative) si la température d'entrée d'air est > à la températrure de sortie d'eau
Cells(57, i) = Cells(60, i) + 5
End If
Next
' fais varier la charge ventilateur jusqu'à atteindre la température cible
' Si la charge dépasse 100% on sort de la boucle
For i = 2 To 13
If Cells(77, i) < 0 Then ' boucle tant que j'attend une différence = 0 entre la température souhaitée et la température de sortie d'eau
Do ' j'égalise la Puissance P = K*S*Tlm en faisant varier la température de sortie d'eau cette fois-ci pour une valeur de charge ventilateur donnée : j'incrémente cette valeur de charge tout les 0.05% jusqu'à a atteindre l'équilibre et la température souhaitée
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) - 0.0001
Loop Until Cells(77, i) >= 0.01
Else
Do ' J'execute cette manip pour les valeurs négative de dépassement et les valeurs positives de dépasement et en conséquence j'augmente la ou diminue la charge ventilo : c'est pour cela qu'on retrouve 2 boucle DO
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) + 0.0001
If Cells(74, i) > 1 Then Exit Do
Loop Until Cells(77, i) <= 0.01
End If
Next
For i = 2 To 13
Cells(67, i).GoalSeek goal:=0, changingcell:=Cells(57, i) 'permet de réduire l'erreur en égalisant les Tlm (avec une égalisation juste des puissances, on obtien une erreur trop grande ! On est plus précis sur les temmpératures...)
If Cells(74, i) > 1 Then
Cells(74, i).Interior.ColorIndex = 3 'permet de colorier en rouge les dépassement de 100%
Else
Cells(74, i).Interior.ColorIndex = 2
End If
Next
For t = 1 To 4
For i = 2 To 13
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Next
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("Aéro sec").Cells(59, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("Aéro sec").Cells(59, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("Aéro sec").Cells(59, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("Aéro sec").Cells(59, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("Aéro sec").Cells(59, i - 1)
Next i
For i = 2 To 13
If Cells(77, i) < 0 Then ' boucle tant que j'attend une différence = 0 entre la température souhaitée et la température de sortie d'eau
Do ' j'égalise la Puissance P = K*S*Tlm en faisant varier la température de sortie d'eau cette fois-ci pour une valeur de charge ventilateur donnée : j'incrémente cette valeur de charge tout les 0.05% jusqu'à a atteindre l'équilibre et la température souhaitée
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) - 0.0001
Loop Until Cells(77, i) >= 0.01
Else
Do ' J'execute cette manip pour les valeurs négative de dépassement et les valeurs positives de dépasement et en conséquence j'augmente la ou diminue la charge ventilo : c'est pour cela qu'on retrouve 2 boucle DO
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) + 0.0001
If Cells(74, i) > 1 Then Exit Do
Loop Until Cells(77, i) <= 0.01
End If
Next
t = t + 1
Next t
Sheets("Calcul Réseau ER").Activate
For i = 5 To 16
Cells(13, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("Calcul Chiller").Activate
For i = 3 To 14
b = Cells(9, i).Value
c = Cells(5, i).Value
Y1 = b >= 10 And b < 12.5
Y2 = b >= 12.5 And b < 15
Y3 = b >= 15 And b < 17.5
Y4 = b >= 17.5 And b < 20
Y5 = b >= 20 And b < 22.5
Y6 = b >= 22.5 And b < 25
Y7 = b >= 25 And b < 27.5
Y8 = b >= 27.5 And b < 30
Y9 = b >= 30 And b < 32.5
Y10 = b >= 32.5 And b < 35
Y11 = b >= 35 And b < 37.5
Y12 = b >= 37.5 And b < 40
Y13 = b >= 40 And b < 60
If Y1 Then
Cells(6, i) = -19.579 * c ^ 3 + 30.073 * c ^ 2 - 11.613 * c + 9.8294
ElseIf Y2 Then
Cells(6, i) = -9.9568 * c ^ 2 + 11.362 * c + 7.0693
ElseIf Y3 Then
Cells(6, i) = 26.895 * c ^ 3 - 57.613 * c ^ 2 + 34.33 * c + 4.6334
ElseIf Y4 Then
Cells(6, i) = 19.11 * c ^ 3 - 42.891 * c ^ 2 + 26.919 * c + 4.8065
ElseIf Y5 Then
Cells(6, i) = 15.46 * c ^ 3 - 34.518 * c ^ 2 + 22.057 * c + 4.7047
ElseIf Y6 Then
Cells(6, i) = 13.855 * c ^ 3 - 31.919 * c ^ 2 + 20.806 * c + 4.1679
ElseIf Y7 Then
Cells(6, i) = 12.25 * c ^ 3 - 29.32 * c ^ 2 + 19.555 * c + 3.6311
ElseIf Y8 Then
Cells(6, i) = 10.32 * c ^ 3 - 25.491 * c ^ 2 + 17.413 * c + 3.4137
ElseIf Y9 Then
Cells(6, i) = 8.3902 * c ^ 3 - 21.663 * c ^ 2 + 15.272 * c + 3.1962
ElseIf Y10 Then
Cells(6, i) = 7.3327 * c ^ 3 - 19.049 * c ^ 2 + 13.628 * c + 2.8743
ElseIf Y11 Then
Cells(6, i) = 6.2753 * c ^ 3 - 16.435 * c ^ 2 + 11.984 * c + 2.5524
ElseIf Y12 Then
Cells(6, i) = 5.6145 * c ^ 3 - 14.571 * c ^ 2 + 10.628 * c + 2.3383
ElseIf Y13 Then
Cells(6, i) = 4.9537 * c ^ 3 - 12.706 * c ^ 2 + 9.2711 * c + 2.1243
Else
Cells(6, i) = "Erreur"
End If
Next
Sheets("Calcul Chiller").Activate
Range("B1:N15").Select
Selection.Copy
Range("A69") = " Dry cooler "
Range("B69").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul PCW").Activate
Range("C1:O11").Select
Selection.Copy
Range("B53") = " Dry cooler "
Range("C53").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Air comprimé").Activate
Range("A1:M69").Select
Selection.Copy
Range("N285") = " Dry cooler "
Range("A285").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Autre").Activate
Range("A1:M6").Select
Selection.Copy
Range("N33") = " Dry cooler "
Range("A33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul TAR").Activate
Range("A1:M15").Select
Selection.Copy
Range("N69") = " Dry cooler "
Range("A69").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Aéro média humide").Activate
For i = 2 To 13
If Cells(74, i) < 1 Then
Cells(74, i) = 0.5
End If
Next
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = 50
Sheets("Calcul PCW").Cells(6, i + 1) = 50
Sheets("Calcul Air comprimé").Cells(20, i - 1) = 50
Sheets("Calcul Autre").Cells(5, i - 1) = 50
Sheets("Calcul TAR").Cells(2, i - 1) = 50
Next i
For i = 2 To 13
Cells(74, i).Interior.ColorIndex = 2
If Cells(60, i) >= Cells(76, i) Then
Cells(74, i) = 1
Else
Cells(57, i) = Cells(60, i) + 6
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(74, i)
Cells(89, i).GoalSeek goal:=0, changingcell:=Cells(74, i)
End If
If Cells(74, i) > 1 Then
Cells(74, i) = 0.99
End If
If Cells(60, i) >= Cells(59, i) Then
Cells(57, i) = Cells(60, i) + 5
End If
Next
For i = 2 To 13
If Cells(77, i) < 0 Then
Do
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) - 0.0001
Loop Until Cells(77, i) >= 0.01
Else
Do
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) + 0.0001
If Cells(74, i) > 1 Then Exit Do
Loop Until Cells(77, i) <= 0.01
End If
Next
For i = 2 To 13
Cells(67, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
If Cells(74, i) > 1 Then
Cells(74, i).Interior.ColorIndex = 3
Else
Cells(74, i).Interior.ColorIndex = 2
End If
Next
For i = 2 To 13
If Cells(84, i) > 3000 Then
Cells(57, i) = Cells(76, i)
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(74, i)
Cells(89, i).GoalSeek goal:=0, changingcell:=Cells(74, i)
Cells(67, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
End If
Next
For t = 1 To 4
For i = 2 To 13
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Next
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("Aéro média humide").Cells(59, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("Aéro média humide").Cells(59, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("Aéro média humide").Cells(59, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("Aéro média humide").Cells(59, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("Aéro média humide").Cells(59, i - 1)
Next i
For i = 2 To 13
If Cells(77, i) < 0 Then
Do
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) - 0.0001
Loop Until Cells(77, i) >= 0.01
Else
Do
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) + 0.0001
If Cells(74, i) > 1 Then Exit Do
Loop Until Cells(77, i) <= 0.01
End If
Next
t = t + 1
Next t
Sheets("Calcul Réseau ER").Activate
For i = 5 To 16
Cells(13, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("Calcul Chiller").Activate
For i = 3 To 14
b = Cells(9, i).Value
c = Cells(5, i).Value
Y1 = b >= 10 And b < 12.5
Y2 = b >= 12.5 And b < 15
Y3 = b >= 15 And b < 17.5
Y4 = b >= 17.5 And b < 20
Y5 = b >= 20 And b < 22.5
Y6 = b >= 22.5 And b < 25
Y7 = b >= 25 And b < 27.5
Y8 = b >= 27.5 And b < 30
Y9 = b >= 30 And b < 32.5
Y10 = b >= 32.5 And b < 35
Y11 = b >= 35 And b < 37.5
Y12 = b >= 37.5 And b < 40
Y13 = b >= 40 And b < 60
If Y1 Then
Cells(6, i) = -19.579 * c ^ 3 + 30.073 * c ^ 2 - 11.613 * c + 9.8294
ElseIf Y2 Then
Cells(6, i) = -9.9568 * c ^ 2 + 11.362 * c + 7.0693
ElseIf Y3 Then
Cells(6, i) = 26.895 * c ^ 3 - 57.613 * c ^ 2 + 34.33 * c + 4.6334
ElseIf Y4 Then
Cells(6, i) = 19.11 * c ^ 3 - 42.891 * c ^ 2 + 26.919 * c + 4.8065
ElseIf Y5 Then
Cells(6, i) = 15.46 * c ^ 3 - 34.518 * c ^ 2 + 22.057 * c + 4.7047
ElseIf Y6 Then
Cells(6, i) = 13.855 * c ^ 3 - 31.919 * c ^ 2 + 20.806 * c + 4.1679
ElseIf Y7 Then
Cells(6, i) = 12.25 * c ^ 3 - 29.32 * c ^ 2 + 19.555 * c + 3.6311
ElseIf Y8 Then
Cells(6, i) = 10.32 * c ^ 3 - 25.491 * c ^ 2 + 17.413 * c + 3.4137
ElseIf Y9 Then
Cells(6, i) = 8.3902 * c ^ 3 - 21.663 * c ^ 2 + 15.272 * c + 3.1962
ElseIf Y10 Then
Cells(6, i) = 7.3327 * c ^ 3 - 19.049 * c ^ 2 + 13.628 * c + 2.8743
ElseIf Y11 Then
Cells(6, i) = 6.2753 * c ^ 3 - 16.435 * c ^ 2 + 11.984 * c + 2.5524
ElseIf Y12 Then
Cells(6, i) = 5.6145 * c ^ 3 - 14.571 * c ^ 2 + 10.628 * c + 2.3383
ElseIf Y13 Then
Cells(6, i) = 4.9537 * c ^ 3 - 12.706 * c ^ 2 + 9.2711 * c + 2.1243
Else
Cells(6, i) = "Erreur"
End If
Next
Sheets("Calcul Chiller").Activate
Range("B1:N15").Select
Selection.Copy
Range("A86") = "Adiabatic Dry cooler "
Range("B86").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul PCW").Activate
Range("C1:O11").Select
Selection.Copy
Range("B66") = "Adiabatic Dry cooler "
Range("C66").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Air comprimé").Activate
Range("A1:M69").Select
Selection.Copy
Range("N356") = "Adiabatic Dry cooler "
Range("A356").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Autre").Activate
Range("A1:M6").Select
Selection.Copy
Range("N41") = "Adiabatic Dry cooler "
Range("A41").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul TAR").Activate
Range("A1:M15").Select
Selection.Copy
Range("N86") = "Adiabatic Dry cooler "
Range("A86").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Aéro spray").Activate
For i = 2 To 13
If Cells(74, i) < 1 Then
Cells(74, i) = 0.5
End If
Next
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = 50
Sheets("Calcul PCW").Cells(6, i + 1) = 50
Sheets("Calcul Air comprimé").Cells(20, i - 1) = 50
Sheets("Calcul Autre").Cells(5, i - 1) = 50
Sheets("Calcul TAR").Cells(2, i - 1) = 50
Next i
'
For i = 2 To 13
Cells(74, i).Interior.ColorIndex = 2
If Cells(60, i) >= Cells(76, i) Then
Cells(74, i) = 1
Else
Cells(57, i) = Cells(60, i) + 6
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(74, i)
Cells(89, i).GoalSeek goal:=0, changingcell:=Cells(74, i)
End If
If Cells(74, i) > 1 Then
Cells(74, i) = 0.99
End If
If Cells(60, i) >= Cells(59, i) Then
Cells(57, i) = Cells(60, i) + 5
End If
Next
For i = 2 To 13
If Cells(77, i) < 0 Then
Do
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) - 0.0001
Loop Until Cells(77, i) >= 0.01
Else
Do
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) + 0.0001
If Cells(74, i) > 1 Then Exit Do
Loop Until Cells(77, i) <= 0.01
End If
Next
For i = 2 To 13
Cells(67, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
If Cells(74, i) > 1 Then
Cells(74, i).Interior.ColorIndex = 3
Else
Cells(74, i).Interior.ColorIndex = 2
End If
Next
For i = 2 To 13
If Cells(84, i) > 3000 Then
Cells(57, i) = Cells(76, i)
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(74, i)
Cells(89, i).GoalSeek goal:=0, changingcell:=Cells(74, i)
Cells(67, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
End If
Next
For t = 1 To 4
For i = 2 To 13
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Next
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("Aéro spray").Cells(59, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("Aéro spray").Cells(59, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("Aéro spray").Cells(59, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("Aéro spray").Cells(59, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("Aéro spray").Cells(59, i - 1)
Next i
For i = 2 To 13
If Cells(77, i) < 0 Then
Do
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) - 0.0001
Loop Until Cells(77, i) >= 0.01
Else
Do
Cells(84, i).GoalSeek goal:=0, changingcell:=Cells(57, i)
Cells(74, i) = Cells(74, i) + 0.0001
If Cells(74, i) > 1 Then Exit Do
Loop Until Cells(77, i) <= 0.01
End If
Next
t = t + 1
Next t
Sheets("Calcul Réseau ER").Activate
For i = 5 To 16
Cells(13, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("Calcul Chiller").Activate
For i = 3 To 14
b = Cells(9, i).Value
c = Cells(5, i).Value
Y1 = b >= 10 And b < 12.5
Y2 = b >= 12.5 And b < 15
Y3 = b >= 15 And b < 17.5
Y4 = b >= 17.5 And b < 20
Y5 = b >= 20 And b < 22.5
Y6 = b >= 22.5 And b < 25
Y7 = b >= 25 And b < 27.5
Y8 = b >= 27.5 And b < 30
Y9 = b >= 30 And b < 32.5
Y10 = b >= 32.5 And b < 35
Y11 = b >= 35 And b < 37.5
Y12 = b >= 37.5 And b < 40
Y13 = b >= 40 And b < 60
If Y1 Then
Cells(6, i) = -19.579 * c ^ 3 + 30.073 * c ^ 2 - 11.613 * c + 9.8294
ElseIf Y2 Then
Cells(6, i) = -9.9568 * c ^ 2 + 11.362 * c + 7.0693
ElseIf Y3 Then
Cells(6, i) = 26.895 * c ^ 3 - 57.613 * c ^ 2 + 34.33 * c + 4.6334
ElseIf Y4 Then
Cells(6, i) = 19.11 * c ^ 3 - 42.891 * c ^ 2 + 26.919 * c + 4.8065
ElseIf Y5 Then
Cells(6, i) = 15.46 * c ^ 3 - 34.518 * c ^ 2 + 22.057 * c + 4.7047
ElseIf Y6 Then
Cells(6, i) = 13.855 * c ^ 3 - 31.919 * c ^ 2 + 20.806 * c + 4.1679
ElseIf Y7 Then
Cells(6, i) = 12.25 * c ^ 3 - 29.32 * c ^ 2 + 19.555 * c + 3.6311
ElseIf Y8 Then
Cells(6, i) = 10.32 * c ^ 3 - 25.491 * c ^ 2 + 17.413 * c + 3.4137
ElseIf Y9 Then
Cells(6, i) = 8.3902 * c ^ 3 - 21.663 * c ^ 2 + 15.272 * c + 3.1962
ElseIf Y10 Then
Cells(6, i) = 7.3327 * c ^ 3 - 19.049 * c ^ 2 + 13.628 * c + 2.8743
ElseIf Y11 Then
Cells(6, i) = 6.2753 * c ^ 3 - 16.435 * c ^ 2 + 11.984 * c + 2.5524
ElseIf Y12 Then
Cells(6, i) = 5.6145 * c ^ 3 - 14.571 * c ^ 2 + 10.628 * c + 2.3383
ElseIf Y13 Then
Cells(6, i) = 4.9537 * c ^ 3 - 12.706 * c ^ 2 + 9.2711 * c + 2.1243
Else
Cells(6, i) = "Erreur"
End If
Next
Sheets("Calcul Chiller").Activate
Range("B1:N15").Select
Selection.Copy
Range("A103") = "Wet-Dry cooler fogging"
Range("B103").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul PCW").Activate
Range("C1:O11").Select
Selection.Copy
Range("B79") = "Wet-Dry cooler fogging"
Range("C79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Air comprimé").Activate
Range("A1:M69").Select
Selection.Copy
Range("N427") = "Wet-Dry cooler fogging"
Range("A427").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Autre").Activate
Range("A1:M6").Select
Selection.Copy
Range("N49") = "Wet-Dry cooler fogging"
Range("A49").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul TAR").Activate
Range("A1:M15").Select
Selection.Copy
Range("N103") = "Wet-Dry cooler fogging"
Range("A103").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("TF hélicoidal Echangeur Accolé").Activate
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("TF hélicoidal Echangeur Accolé").Cells(65, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("TF hélicoidal Echangeur Accolé").Cells(65, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("TF hélicoidal Echangeur Accolé").Cells(65, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("TF hélicoidal Echangeur Accolé").Cells(65, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("TF hélicoidal Echangeur Accolé").Cells(65, i - 1)
Next i
For i = 2 To 13
Cells(64, i) = Sheets("Données d'entrées utilisateur").Range("B17")
If Cells(60, i) <= 0 Then
Cells(60, i) = 0.5
End If
Next
For i = 2 To 13
If Cells(64, i) - 2 <= Cells(4, i) Then
Cells(66, i).Interior.ColorIndex = 3
Cells(64, i).Select
Selection.Copy
Cells(67, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Cells(67, i) = Cells(64, i)
Cells(66, i).Interior.ColorIndex = 2
End If
If Cells(64, i) - 2 <= Cells(4, i) Then
Cells(81, i) = 1
Cells(64, i) = Cells(4, i) + 5
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Else
Cells(81, i) = 1
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
End If
Next i
p = 5
For i = 2 To 13
If Cells(60, i) > 4 Then
Do
Cells(81, i) = 1
Cells(60, i) = 0.5
Cells(64, i) = Cells(4, i) + p
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
p = p + 0.01
Loop Until Cells(60, i) <= 3.5
End If
Next i
For i = 2 To 13
Do
Cells(81, i) = Cells(81, i) - 0.0005
Loop Until Cells(87, i) <= 0
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("Calcul Chiller").Activate
For i = 3 To 14
b = Cells(9, i).Value
c = Cells(5, i).Value
Y1 = b >= 10 And b < 12.5
Y2 = b >= 12.5 And b < 15
Y3 = b >= 15 And b < 17.5
Y4 = b >= 17.5 And b < 20
Y5 = b >= 20 And b < 22.5
Y6 = b >= 22.5 And b < 25
Y7 = b >= 25 And b < 27.5
Y8 = b >= 27.5 And b < 30
Y9 = b >= 30 And b < 32.5
Y10 = b >= 32.5 And b < 35
Y11 = b >= 35 And b < 37.5
Y12 = b >= 37.5 And b < 40
Y13 = b >= 40 And b < 60
If Y1 Then
Cells(6, i) = -19.579 * c ^ 3 + 30.073 * c ^ 2 - 11.613 * c + 9.8294
ElseIf Y2 Then
Cells(6, i) = -9.9568 * c ^ 2 + 11.362 * c + 7.0693
ElseIf Y3 Then
Cells(6, i) = 26.895 * c ^ 3 - 57.613 * c ^ 2 + 34.33 * c + 4.6334
ElseIf Y4 Then
Cells(6, i) = 19.11 * c ^ 3 - 42.891 * c ^ 2 + 26.919 * c + 4.8065
ElseIf Y5 Then
Cells(6, i) = 15.46 * c ^ 3 - 34.518 * c ^ 2 + 22.057 * c + 4.7047
ElseIf Y6 Then
Cells(6, i) = 13.855 * c ^ 3 - 31.919 * c ^ 2 + 20.806 * c + 4.1679
ElseIf Y7 Then
Cells(6, i) = 12.25 * c ^ 3 - 29.32 * c ^ 2 + 19.555 * c + 3.6311
ElseIf Y8 Then
Cells(6, i) = 10.32 * c ^ 3 - 25.491 * c ^ 2 + 17.413 * c + 3.4137
ElseIf Y9 Then
Cells(6, i) = 8.3902 * c ^ 3 - 21.663 * c ^ 2 + 15.272 * c + 3.1962
ElseIf Y10 Then
Cells(6, i) = 7.3327 * c ^ 3 - 19.049 * c ^ 2 + 13.628 * c + 2.8743
ElseIf Y11 Then
Cells(6, i) = 6.2753 * c ^ 3 - 16.435 * c ^ 2 + 11.984 * c + 2.5524
ElseIf Y12 Then
Cells(6, i) = 5.6145 * c ^ 3 - 14.571 * c ^ 2 + 10.628 * c + 2.3383
ElseIf Y13 Then
Cells(6, i) = 4.9537 * c ^ 3 - 12.706 * c ^ 2 + 9.2711 * c + 2.1243
Else
Cells(6, i) = "Erreur"
End If
Next
Sheets("Calcul Chiller").Activate
Range("B1:N15").Select
Selection.Copy
Range("A120") = " CCT Axial fan counterflow"
Range("B120").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul PCW").Activate
Range("C1:O11").Select
Selection.Copy
Range("B92") = " CCT Axial fan counterflow"
Range("C92").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Air comprimé").Activate
Range("A1:M69").Select
Selection.Copy
Range("N498") = " CCT Axial fan counterflow"
Range("A498").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Autre").Activate
Range("A1:M6").Select
Selection.Copy
Range("N57") = " CCT Axial fan counterflow"
Range("A57").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul TAR").Activate
Range("A1:M15").Select
Selection.Copy
Range("N120") = " CCT Axial fan counterflow"
Range("A120").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("TF hélicoidal Echangeur Accolé").Activate
For i = 2 To 13
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("TF centrifuge Echangeur Accolé").Activate
For i = 3 To 14
Sheets("Calcul Chiller").Cells(9, i) = Sheets("TF centrifuge Echangeur Accolé").Cells(65, i - 1)
Sheets("Calcul PCW").Cells(6, i + 1) = Sheets("TF centrifuge Echangeur Accolé").Cells(65, i - 1)
Sheets("Calcul Air comprimé").Cells(20, i - 1) = Sheets("TF centrifuge Echangeur Accolé").Cells(65, i - 1)
Sheets("Calcul Autre").Cells(5, i - 1) = Sheets("TF centrifuge Echangeur Accolé").Cells(65, i - 1)
Sheets("Calcul TAR").Cells(2, i - 1) = Sheets("TF centrifuge Echangeur Accolé").Cells(65, i - 1)
Next i
For i = 2 To 13
Cells(64, i) = Sheets("Données d'entrées utilisateur").Range("B17")
If Cells(60, i) <= 0 Then
Cells(60, i) = 0.5
End If
Next
For i = 2 To 13
If Cells(64, i) - 2 <= Cells(4, i) Then
Cells(66, i).Interior.ColorIndex = 3
Cells(64, i).Select
Selection.Copy
Cells(67, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Cells(67, i) = Cells(64, i)
Cells(66, i).Interior.ColorIndex = 2
End If
If Cells(64, i) - 2 <= Cells(4, i) Then
Cells(81, i) = 1
Cells(64, i) = Cells(4, i) + 5
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Else
Cells(81, i) = 1
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
End If
Next i
p = 5
For i = 2 To 13
If Cells(60, i) > 4 Then
Do
Cells(81, i) = 1
Cells(60, i) = 0.5
Cells(64, i) = Cells(4, i) + p
Cells(79, i).GoalSeek goal:=0, changingcell:=Cells(29, i)
Cells(77, i).GoalSeek goal:=0, changingcell:=Cells(60, i)
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
p = p + 0.01
Loop Until Cells(60, i) <= 3.5
End If
Next i
For i = 2 To 13
Do
Cells(81, i) = Cells(81, i) - 0.0005
Loop Until Cells(87, i) <= 0
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Sheets("Calcul Chiller").Activate
For i = 3 To 14
b = Cells(9, i).Value
c = Cells(5, i).Value
Y1 = b >= 10 And b < 12.5
Y2 = b >= 12.5 And b < 15
Y3 = b >= 15 And b < 17.5
Y4 = b >= 17.5 And b < 20
Y5 = b >= 20 And b < 22.5
Y6 = b >= 22.5 And b < 25
Y7 = b >= 25 And b < 27.5
Y8 = b >= 27.5 And b < 30
Y9 = b >= 30 And b < 32.5
Y10 = b >= 32.5 And b < 35
Y11 = b >= 35 And b < 37.5
Y12 = b >= 37.5 And b < 40
Y13 = b >= 40 And b < 60
If Y1 Then
Cells(6, i) = -19.579 * c ^ 3 + 30.073 * c ^ 2 - 11.613 * c + 9.8294
ElseIf Y2 Then
Cells(6, i) = -9.9568 * c ^ 2 + 11.362 * c + 7.0693
ElseIf Y3 Then
Cells(6, i) = 26.895 * c ^ 3 - 57.613 * c ^ 2 + 34.33 * c + 4.6334
ElseIf Y4 Then
Cells(6, i) = 19.11 * c ^ 3 - 42.891 * c ^ 2 + 26.919 * c + 4.8065
ElseIf Y5 Then
Cells(6, i) = 15.46 * c ^ 3 - 34.518 * c ^ 2 + 22.057 * c + 4.7047
ElseIf Y6 Then
Cells(6, i) = 13.855 * c ^ 3 - 31.919 * c ^ 2 + 20.806 * c + 4.1679
ElseIf Y7 Then
Cells(6, i) = 12.25 * c ^ 3 - 29.32 * c ^ 2 + 19.555 * c + 3.6311
ElseIf Y8 Then
Cells(6, i) = 10.32 * c ^ 3 - 25.491 * c ^ 2 + 17.413 * c + 3.4137
ElseIf Y9 Then
Cells(6, i) = 8.3902 * c ^ 3 - 21.663 * c ^ 2 + 15.272 * c + 3.1962
ElseIf Y10 Then
Cells(6, i) = 7.3327 * c ^ 3 - 19.049 * c ^ 2 + 13.628 * c + 2.8743
ElseIf Y11 Then
Cells(6, i) = 6.2753 * c ^ 3 - 16.435 * c ^ 2 + 11.984 * c + 2.5524
ElseIf Y12 Then
Cells(6, i) = 5.6145 * c ^ 3 - 14.571 * c ^ 2 + 10.628 * c + 2.3383
ElseIf Y13 Then
Cells(6, i) = 4.9537 * c ^ 3 - 12.706 * c ^ 2 + 9.2711 * c + 2.1243
Else
Cells(6, i) = "Erreur"
End If
Next
Sheets("Calcul Chiller").Activate
Range("B1:N15").Select
Selection.Copy
Range("A137") = " CCT centrifugal fan counterflow"
Range("B137").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul PCW").Activate
Range("C1:O11").Select
Selection.Copy
Range("B105") = " CCT centrifugal fan counterflow"
Range("C105").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Air comprimé").Activate
Range("A1:M69").Select
Selection.Copy
Range("N569") = " CCT centrifugal fan counterflow"
Range("A569").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul Autre").Activate
Range("A1:M6").Select
Selection.Copy
Range("N75") = " CCT centrifugal fan counterflow"
Range("A75").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Calcul TAR").Activate
Range("A1:M15").Select
Selection.Copy
Range("N137") = " CCT centrifugal fan counterflow"
Range("A137").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("TF centrifuge Echangeur Accolé").Activate
For i = 2 To 13
Cells(75, i).GoalSeek goal:=0, changingcell:=Cells(12, i)
Next i
Application.ScreenUpdating = True
End Sub
[/code
Bonjour,
au minimum tu coupes ton code à chaque sheet…. . activate ( si changement de feuille alors le code peut venir d'une autre macro)
Tu mes chaque coupe dans des modules différents … Macro1, Macro2, Macro3 ….
Et dans ton Sub Final()
tu indiques l'une en desous de l'autre le nom de tes macros.
Très bien merci !
Bonne fin de journée !
Bonjour,
Pour suivre l'idée d'Xmen j'ai découpé le saucisson en autant de macro que de feuilles activées
Puis je les appelle successivement.
Il est parfaitement inutile de créer pour cela 50 modules tu peux faire cela dans le module d'origine.
Je l'ai d'ailleurs fait pour toi dans le classeur ci joint.
Ensuite j'ai optimisé le code des 3 premières macros. Pourquoi les 3 premières ? Parce que j'ai pas que ça à faire !
Et puis il faut bien que tu apprennes... à optimiser.
Donc TAPUKA faire comme moi et le tour est joué. Enfin si je n'ai pas fait d'erreur dans la découpe et si tu n'as pas fait d'erreur dans l'optimisation.
Quelques précisions : C'est malheureux que tu n'aies fait aucune déclaration. Toi seul peux le faire car tu as une idée très claire des variables à utiliser. Je suppose que des Long et des Single aurait suffit, mébon... je ne me suis pas engagé sur ce terrain là : Pourtant ça aurait encore pu rajouter une bonne petite couche d'optimisation...
Il ne faut pas abuser des bonnes chose Des variables d'une lettre ça va bien dans une macro de 4 lignes mais quand tu veux repérer une erreur quand tu as des a, b, c, p à tous les étages... bonjour les dégâts.
Donc à l'avenir tu as droit qu'a 2 ou 3 variables lettres "i", "k" et "x" à condition de les réserver pour des boucles For... Next et à condition que la macro ne dépasse pas 10 lignes.
Pas de p = Cells(x,y).Value hein ! Sinon j'avoine !
Il n'est pas nécessaire de sélect ni d'activer un feuille ni une sélection de plage avec VBA ; C'est même fortement contre indiqué car cela ralenti considérablement le programme. L'essentiel de mon optimisation a donc été de supprimer les activate et les Selection et de les remplacer par des With... en n'oubliant pas de rajouter un point devant chaque cells de la feuille en question.
Tu feras également attention au copier/Collage Spécial... en respectant bien cette syntaxe.
Moyennant quoi ya même pas besoin de Screenupdating puisque tu n'actives rien. Mébon je l'ai laissé quand même...
Enfin j'ai pas mal amélioré ta ribambelle de IF... et sa cohorte de Y en remplaçant ça par des Select Case plus judicieux. Il y avait d'ailleurs une erreur logique dans le Else car il n'y avait pas de Else possible... Entre Y1 et Y13 tu aurais pu trouver quoi d'autre ???
Bon si tu as des petites difficultés de compréhension tu peux en redemander...
Note bien que pour l'instant le débogueur ne couine pas... Donc YA de l'espoir !
Bon courage !
A+
Bonjour, galopin beau travail
C'est ce que je voulais dire mais je me suis mal exprimé par écrit ... j'aurai du écrire dans des "Macros" différentes au lieu de
dans des "modules" différents.
Bonjour Xmenpl
Ya des fois ou on se demande si c'est bien la peine de se fatiguer !
Bonjour,
Merci pour le temps que vous avez pris ! C'est super, j'en attendez pas autant ! merci beaucoup !!
Je vais tenter de reprendre cela alors ! je suis qu'un débutant en VBA donc merci de ses précieux conseils !
Je vais m'atteler à cette optimisation !
A bientôt !