Hello All,
When I run this particular script with small values on one side of the equation it normally takes a lot longer to process than usual seeing as the right side of the equation has a 5th power in it. I recently switched from Excel 2007 where I codes this to Excel 2010 on my new computer, so if there is something hindering it with that change please let me know. The problem I am getting is that Excel stalls when I have a small value on the left and I get a "Run-time error 1004. Method 'Range' of object '_Worksheet' failed" error. I am assuming that reporting the values after every iteration the way it does is the culprit behind this, or just the sheer computing power. Any help would be greatly appreciated.
Sub Compute()
Dim d_computed As Double
Dim d_actual As Double
Dim d_pipesize As Double
Dim eqn_rhs As Double
Dim f_factor As Double
Dim idx As Integer
precision = 0.001 'find d within 0.1%
e_rough = 0.00015 'ft - commercial steel piping roughness assumed
Worksheets("Calculator").Range("B19:B21").ClearContents
L = Worksheets("Calculator").Cells(3, 2)
Cr = Worksheets("Calculator").Range("B4")
Po = Worksheets("Calculator").Range("B6") 'defining variable taken from sheet
P2 = Worksheets("Calculator").Range("B7")
d_computed = Worksheets("Calculator").Cells(11, 2)
sch = Worksheets("Calculator").Range("B12")
If sch <> 40 And sch <> 80 Then
MsgBox ("Please enter either 40 or 80 for desired pipe schedule")
Exit Sub
End If
Do
'friction factor calculation
f_factor = 1 / (-2 * Application.WorksheetFunction.Log(12 * e_rough / d_computed / 3.7)) ^ 2
eqn_rhs = 0.214 * d_computed ^ 5 * (Po ^ 2 - P2 ^ 2) / (f_factor * Cr ^ 2) - d_computed * Log(Po / P2) / (6 * f_factor) 'right hand side of the equation
'unacceptable result adjustment to the (1/5)th power
If (Abs(L / eqn_rhs) <= (1 - precision) Or Abs(L / eqn_rhs) >= (1 + precision)) Then 'if the fraction of length over result is outside precision...
d_computed = d_computed * Abs(L / eqn_rhs) ^ (1 / 5) 'adjust first iteration
Worksheets("Calculator").Cells(19, 2) = Abs(d_computed) 'once the result passes the requirements place value on the sheet
Else
Exit Do
End If
Loop
It seems to be choking on this line of code:
Worksheets("Calculator").Cells(19, 2) = Abs(d_computed)
Thank you all again for your help!
DarkF1ame
Bookmarks