LinkBack URL
About LinkBacks
Hi all,
I am in desperate need of help regarding a problem with combinational optimization. I have tried many ways but i just cannot get the required results. May i seek your help in this?
The excel VBA code below is supposed to generate all relevant combinations (and ONLY the relevant combinations) that satisfy the equation Qsys = Q1 + Q2 + Q3 + Q4 + Q5. Q1 to Q5 have maximum and minimum values. Qsys is a fixed constant that is determined before running code.
However, the code fails to do as required. I believe it's a logical problem but i just cannot figure out.
Because my actual code has up to Q10, i need to generate ONLY relevant combinations that satisfy equation, so that the runtime will not take too long.
Many thanks for your kind attention and i await your reply in anticipation!
--------------------------------------------------------------------------------------------------------
Sub PumpFlowCombinationVer1()
' This Macro generates all possible combinations of flow distribution
' between pumps to meet required System Flow.
' Divisions of 0.1m(^3)are used.
' Please feed in the necessary parameters before running the Macro.
' Good Day!
'''''''''''''''''''''''' Declarations '''''''''''''''''''''''''''''''''''''''''''
Dim x As Double
Dim Qsummax As Double
Dim Qsys, Q1, Q2, Q3, Q4, Q5 As Double
' Upper boundary conditions for flows
Dim Q1max, Q2max, Q3max, Q4max, Q5max As Double
' Lower boundary conditions for flows
Dim Q1min, Q2min, Q3min, Q4min, Q5min As Double
' Counters
Dim b, c, d, e, f As Double
' Temporary variables
Dim Qtemp As Double
'''''''''''''''''''''''' Initialization '''''''''''''''''''''''''''''''''''''''''''
' Assigning System Flow
Qsys = Round(Range("Interface!B6"), 1)
''''''''''''''''''''''''''''''''''''''''''''''''' TO BE REMOVED - TESTING ONLY
Qsys = 0.6
' Assigning Boundary Flow Values
Q1max = Range("Input!B10")
Q2max = Range("Input!C10")
Q3max = Range("Input!D10")
Q4max = Range("Input!E10")
Q5max = Range("Input!F10")
Q1min = Range("Input!B11")
Q2min = Range("Input!C11")
Q3min = Range("Input!D11")
Q4min = Range("Input!E11")
Q5min = Range("Input!F11")
' Assigning Counter Initial Values (To be placed after Qmin initialization)
b = Q1min
c = Q2min
d = Q3min
e = Q4min
f = Q5min
x = 4
'''''''''''''''''''''''' Main Body ''''''''''''''''''''''''''''''''''''''''''''''''
Qsummax = Q1max + Q2max + Q3max + Q4max + Q5max
If (Qsys > Qsummax) Then
MsgBox "System Flow requirements exceed total pump capacity. Please run all pumps at full speed. Insufficient System Flow is being provided at present."
Else
Do While Application.Round(b, 1) <= Q1max
If (Qsys - b - c - d - e - f >= 0) Then
Do While Application.Round(c, 1) <= Q2max
If (Qsys - b - c - d - e - f >= 0) Then
Do While Application.Round(d, 1) <= Q3max
If (Qsys - b - c - d - e - f >= 0) Then
Do While Application.Round(e, 1) <= Q4max
If (Qsys - b - c - d - e - f >= 0) Then
Do While Application.Round(f, 1) <= Q5max
If (Qsys - b - c - d - e - f >= 0) And (b + c + d + e + f = Qsys) Then
Cells(x, 1) = b
Cells(x, 2) = c
Cells(x, 3) = d
Cells(x, 4) = e
Cells(x, 5) = f
Cells(x, 7) = b + c + d + e + f
Debug.Print x, b, c, d, e, f, b + c + d + e + f
x = x + 1
Else
End If
f = f + 0.1
Loop
f = Q5min
Else
End If
e = e + 0.1
Loop
e = Q4min
Else
End If
d = d + 0.1
Loop
d = Q3min
Else
End If
c = c + 0.1
Loop
c = Q2min
Else
End If
b = b + 0.1
Loop
b = Q1min
End If
End Sub
Register To Reply
Bookmarks