使用目标搜索
使用目标搜索
提问于 2015-11-05 21:28:04
我正在拆解我写的一个非常旧的电子表格，并试图使用VBA将其重新组合在一起。到目前为止，我做到了这一点，这似乎是有效的：
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此处列出的一些常量(例如密度、管道尺寸等)我最终打算从工作表中读取或自动计算，但现在我是一步一步地进行。
现在我已经很满意了，我已经通过输出生成的数字进行了检查，我想使用Goal Seek在某个预定义的流率下找到流速值。
所以我想做的是让VBA循环不同的流速值，直到达到所需的压降值。我将在Excel工作表的单元格中告诉VBA所需的压降。我希望这个计算完全存在于VBA中，没有任何工作表公式。
因此，我得到了，非常简单的术语，如下：
(1)起始流速(我想这应该在VBA代码中定义，否则目标搜索将没有起点)
(2)一些计算
(3)由此产生的压降。
(4)如果结果压降不等于预定义的值(位于单元格G3中)，则应调整(1)中的流速值，并再次运行计算。
(5)当结果压降等于预定义的值时，告诉我用来计算的流速值是多少。
有什么想法吗？
回答 1
Stack Overflow用户
发布于 2015-11-06 01:38:43
好的，我尝试了this..there可能是一种更好的方法，这假设了一个直接的关系(而不是逆关系)，..i把你的一些变量移动到常量中，把压力计算器放在一个函数中，并将数据类型改为双精度。它是可在工作表中使用的UDF。
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现在可以在工作表中使用以下命令进行引用
=PipeData(A3)其中"A3“是理想的压降数值
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33545804
复制相关文章
相似问题
腾讯云开发者
