问题描述:

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:

Sub PipeData()

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

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 Sub

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.

Any ideas?

网友答案:

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

=PipeData(A3)

Where "A3" is your ideal pressure drop number

相关阅读:
Top