I'm taking apart a very old spreadsheet I wrote and trying to put it back together using VBA. So far I've this, which seems to work:
Dim FlowRate As Single
Dim Density As Single
Dim DynamicViscosity As Single
Dim PipeSize As Single
Dim Pi As Single
Dim ReynoldsNumber As Single
Dim Lamda As Single
Dim EquivalentRoughness As Single
Dim RelativeRoughness As Single
Dim Velocity As Single
Dim PressureDrop As Single
Density = 977.8
DynamicViscosity = 0.0004
PipeSize = 36.1
Pi = WorksheetFunction.Pi()
EquivalentRoughness = 0.046
RelativeRoughness = EquivalentRoughness / PipeSize
FlowRate = Cells(2, 7)
ReynoldsNumber = (4 * FlowRate) / (DynamicViscosity * Pi * (PipeSize / 1000))
If ReynoldsNumber < 2000 Then
Lamda = 64 / ReynoldsNumber
Lamda = ((1 / (-1.8 * WorksheetFunction.Log((6.9 / ReynoldsNumber) + ((RelativeRoughness / 3.71) ^ 1.11)))) ^ 2)
Velocity = ((4 * FlowRate) / (Pi * Density * ((PipeSize / 1000) ^ 2)))
PressureDrop = ((Lamda * Density) * (Velocity ^ 2)) / (2 * (PipeSize / 1000))
Some of the constants listed here (for example density, pipe size, etc.) I eventually intend to read from a worksheet or automatically calculate but for now I'm proceeding one step at a time.
Now that I'm satisfied that this works, which I've checked by outputting the numbers generated, I want to use Goal Seek to find the flow rate value at a certain pre-defined flow rate.
So what I want to do is have VBA cycle through different flow rate values until the desired pressure drop value is reached. I will tell VBA the desired pressure drop in a cell in the Excel sheet. I want this calculation to exist entirely inside VBA without any worksheet formulas.
So I've got, in very simplified terms, the following:
(1) A starting flow rate (I guess this should be defined in the VBA code otherwise Goal Seek won't have a starting point)
(2) Some calculations
(3) A resulting pressure drop.
(4) If the resulting pressure drop is not equal to a pre-defined value (located in cell G3) the flow rate value in (1) should be adjusted and the calculations run again.
(5) When the resulting pressure drop equals the pre-defined value tell me what the flow rate value used to calculate this is.
OK, I took a crack at this..there may be a better way and this assumes a direct relationship (not inverse)..i moved some of your variables into constants and put the pressure calc in a function, and changed data types to double. It is a UDF with you can use in the worksheet.
Const Density As Double = 977.8 Const DynamicViscosity As Double = 0.0004 Const PipeSize As Double = 36.1 Const Pi As Double = 3.14159265358979 Const EquivalentRoughness As Double = 0.046 Const RelativeRoughness As Double = EquivalentRoughness / PipeSize Const Sig As Double = 0.0000000001 'this indicates how accurate you want your answer Dim FlowRate As Double Dim ReynoldsNumber As Double Dim Lamda As Double Dim Velocity As Double Function PipeData(IdealPressureDrop As Long) FlowRate = 1000 + Sig Stepper = 100 If PressureDrop(FlowRate) > IdealPressureDrop Then FlowRateGoal = GoalSeek(FlowRate, Stepper, -1, IdealPressureDrop) Else FlowRateGoal = GoalSeek(FlowRate, Stepper, 1, IdealPressureDrop) End If PipeData = FlowRateGoal End Function Function GoalSeek(FlowRate, Stepper, Direction, IdealPressureDrop) calcagain: Select Case Direction Case 1 Do While PressureDrop(FlowRate) < IdealPressureDrop oFR = FlowRate FlowRate = FlowRate + Stepper Loop Case -1 Do While PressureDrop(FlowRate) > IdealPressureDrop oFR = FlowRate FlowRate = FlowRate - Stepper Loop End Select Stepper = Stepper / 10 If Stepper < Sig Then GoTo getout FlowRate = oFR GoTo calcagain getout: GoalSeek = FlowRate End Function Function PressureDrop(FlowRate) ReynoldsNumber = (4 * FlowRate) / (DynamicViscosity * Pi * (PipeSize / 1000)) If ReynoldsNumber < 2000 Then Lamda = 64 / ReynoldsNumber Else Lamda = ((1 / (-1.8 * WorksheetFunction.Log((6.9 / ReynoldsNumber) + ((RelativeRoughness / 3.71) ^ 1.11)))) ^ 2) End If Velocity = ((4 * FlowRate) / (Pi * Density * ((PipeSize / 1000) ^ 2))) PressureDrop = ((Lamda * Density) * (Velocity ^ 2)) / (2 * (PipeSize / 1000)) End Function
This can now be referenced in the worksheet with
Where "A3" is your ideal pressure drop number