Suggestions to improve UDF

M

MrShorty

I have coded the following function for my own use. The vast majorit
of the time, the function will be called from a worksheet cell rathe
than from another VBA procedure. The function works just fine a
written. As a self-taught programmer, I'm wondering if there are som
standard programming practices that could improve the code.
1st priority is to minimize round-off error. For example, is there
better way to write the third to last line (change the order o
operations or something), to make the calculation more accurate?
2nd priority is improve efficiency.

Thanks in advance for any suggestions.

Typical values for the input arguments:
T as temperature: 100<T<1000
T as pressure: can vary anywhere from 1 to 10E6, typical might b
~100000
A~100
B~-10000
C~-10
D~1E-18
E=6

Function RIEDEL(T As Double, A As Double, B As Double, C As Double,
As Double, E As Integer, _
Optional slvfrT As Boolean = False, Optional Ti As Double = 300) A
Variant
'calculates P0 or T0 using the Riedel equation
'Default, if optional parameters omitted, is to caclulate P0
'If slvfrT is true, then it will find the equilibrium temperatur
corresponding to a given vapor pressure
'input as T. Ti is an optional initial guess. uses the Newton-Raphso
method.
Dim P As Double, T2 As Double, T1 As Double, f As Double, df As Double
itcount As Integer
If slvfrT Then
P = T
T2 = Ti
itcount = 0
Do
itcount = itcount + 1
T1 = T2
f = A + B / T1 + C * Log(T1) + D * T1 ^ E - Log(P)
df = -B / T1 ^ 2 + C / T1 + E * D * T1 ^ (E - 1)
T2 = T1 - f / df
Loop Until Abs(T2 - T1) < 0.000001 Or itcount > 100
If itcount > 100 Then
RIEDEL = CVErr(xlErrNum)
'returns #num error if loop doesn't converge
Else
RIEDEL = T2
End If
Else
RIEDEL = Exp(A + B / T + C * Log(T) + D * T ^ E)
End If
End Functio
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top