How to do loop in the function?

I am trying to create a function however unable to progress as i'm stuck with the iteration part. Below are my codes. How do i go about creating the loop? I have tried many options but to no avail. At times, the system stalled on me. :((

Code:

---

Function TADegC(DEN, Temp As Double)
Application.Volatile

T15 = temp - 15
RHO = DEN
RH15 = RHO * 1000

Do
RH15 = RHO15

K0 = 613.9723
K1 = 0
A = 0

alpha15 = (K0 / (RH15 ^ 2)) + (K1 / RH15) + A
VCF15 = Exp((-alpha15 * T15) * (1 + 0.8 * alpha15 * T15))
rho15 = RH15 / VCF15
Error = Abs(rho15 - RH15)

Loop until error <= 0.05

TADegC = rho15


End Function

---

Not sure about the algorithm, but it looks like you re-initialized the inputs with in the Do/Loop Until (esp alpha15)

Try something like this (no promises ) :)

BTW, I like to use Option Explicit and unless a variable is explicitly Dim-ed, it's a Variant so in (DEN, Temp As Double), the DEN is a Variant

Code:

---

Option Explicit

Function TADegC(DEN As Double, Temp As Double) As Double
    Dim T15 As Double, RHO As Double, RH15 As Double, RHO15 As Double
    Dim K0 As Double, K1 As Double, A As Double
    Dim alpha15 As Double, Error As Double, VCF15 As Double
   
    Application.Volatile
   
    T15 = Temp - 15#
    RHO = DEN
    RH15 = RHO * 1000
       
    K0 = 613.9723
    K1 = 0#
    A = 0#
       
    RH15 = RHO15
    alpha15 = (K0 / (RH15 ^ 2)) + (K1 / RH15) + A
   
   
    Do
        VCF15 = Exp((-alpha15 * T15) * (1 + 0.8 * alpha15 * T15))
        RHO15 = RH15 / VCF15
        Error = Abs(RHO15 - RH15)
    Loop Until Error <= 0.05
   
    TADegC = RHO15
End Function

---

Can U use Error as a variable? Maybe...

Code:

---

Dim Result as Double
Do
        VCF15 = Exp((-alpha15 * T15) * (1 + 0.8 * alpha15 * T15))
        RHO15 = RH15 / VCF15
        Result = Abs(RHO15 - RH15)
    Loop Until Result <= 0.05

---

Dave

Hi Paul:
Thanks for your pointers. Maybe I didn't express well for the function. Below are the spreadsheet which I am trying to loop.

Function TADegC (Den, Temp as double)

T15=Temp-15
DenT=Den*1000
Den1=DenT
K0=613.9723
K1=0
K2=0
Item	Iteration1	Interation2
Den1=	DenT	Den15
alpha15=	(K0/Den1^2)+(K1/Den1)+A	(K0/Den1^2)+(K1/Den1)+A
VCF15=	exp((-alpha15*T15)*(1+0.8*alpha15*T15))	exp((-alpha15*T15)*(1+0.8*alpha15*T15))
Den15=	DenT/VCF15	DenT/VCF15
Diff=	abs(Den15-Den1)	abs(Den15-Den1)

How do i complete the loop? Den1=Den15? or DenT=Den15?

Once I obtain the desired Den15 how can i forward the result to complete my function.

Den15 (the desired value)
T20=Temp-20
alpha20=(K0/Den15^2)+(K1/Den15)+A
VCF20=exp((-alpha20*T20)-(8*(alpha20^2)*T20)-(0.8*(alpha20^2)*(T20^2))
Den20=DenT/VCF20

TADegC=Den20

End Function


Cheers:)
Abilisus

Quote:

---

Originally Posted by Paul_Hossler

Not sure about the algorithm, but it looks like you re-initialized the inputs with in the Do/Loop Until (esp alpha15)

Try something like this (no promises ) :)

BTW, I like to use Option Explicit and unless a variable is explicitly Dim-ed, it's a Variant so in (DEN, Temp As Double), the DEN is a Variant

Code:

---

Option Explicit

Function TADegC(DEN As Double, Temp As Double) As Double
    Dim T15 As Double, RHO As Double, RH15 As Double, RHO15 As Double
    Dim K0 As Double, K1 As Double, A As Double
    Dim alpha15 As Double, Error As Double, VCF15 As Double
   
    Application.Volatile
   
    T15 = Temp - 15#
    RHO = DEN
    RH15 = RHO * 1000
       
    K0 = 613.9723
    K1 = 0#
    A = 0#
       
    RH15 = RHO15
    alpha15 = (K0 / (RH15 ^ 2)) + (K1 / RH15) + A
   
   
    Do
        VCF15 = Exp((-alpha15 * T15) * (1 + 0.8 * alpha15 * T15))
        RHO15 = RH15 / VCF15
        Error = Abs(RHO15 - RH15)
    Loop Until Error <= 0.05
   
    TADegC = RHO15
End Function

---

---

https://stackoverflow.com/questions/...or-my-function

Please read the FAQ about multi-posting





Are you sure your algorithm is correct? (My first macro was a little off since RH15 does change within the Do Loop)

RH15, VCF15 and RHO15 are the only variables that change

T15, RHO, K0, K1, and A don't change, so they can be outside of the Do Loop


On the marked line, RHO15 on the first time is = 0 since it's never assigned a value, so RH15 also = 0

I think that would cause a divide / 0 on the line alpha15 with K1 / RH15

When the loop finishes, it should return the last value of rho15 after the error <= 0.05

What is the algorithm for, and do you have any test data with correct answers ?

Code:

---

Function TADegC(DEN, Temp As Double)
Application.Volatile

T15 = temp - 15
RHO = DEN
RH15 = RHO * 1000

Do
RH15 = RHO15  '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

K0 = 613.9723
K1 = 0
A = 0

alpha15 = (K0 / (RH15 ^ 2)) + (K1 / RH15) + A
VCF15 = Exp((-alpha15 * T15) * (1 + 0.8 * alpha15 * T15))
rho15 = RH15 / VCF15
Error = Abs(rho15 - RH15)

Loop until error <= 0.05

TADegC = rho15


End Function

---

Hi Paul:
Attached is the worked excel spreadsheet. It will show you the iterations. Unfortunately, I cant loop them. Would appreciate your feedback on the appropriate method for the iterations.
Thanks.
Abilisus

Quote:

---

Originally Posted by Paul_Hossler

https://stackoverflow.com/questions/...or-my-function

Please read the FAQ about multi-posting





Are you sure your algorithm is correct? (My first macro was a little off since RH15 does change within the Do Loop)

RH15, VCF15 and RHO15 are the only variables that change

T15, RHO, K0, K1, and A don't change, so they can be outside of the Do Loop


On the marked line, RHO15 on the first time is = 0 since it's never assigned a value, so RH15 also = 0

I think that would cause a divide / 0 on the line alpha15 with K1 / RH15

When the loop finishes, it should return the last value of rho15 after the error <= 0.05

What is the algorithm for, and do you have any test data with correct answers ?

Code:

---

Function TADegC(DEN, Temp As Double)
Application.Volatile

T15 = temp - 15
RHO = DEN
RH15 = RHO * 1000

Do
RH15 = RHO15  '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

K0 = 613.9723
K1 = 0
A = 0

alpha15 = (K0 / (RH15 ^ 2)) + (K1 / RH15) + A
VCF15 = Exp((-alpha15 * T15) * (1 + 0.8 * alpha15 * T15))
rho15 = RH15 / VCF15
Error = Abs(rho15 - RH15)

Loop until error <= 0.05

TADegC = rho15


End Function

---

---

I changed the variable names so I could keep them straight

I added a loop counter just as a safety measure in case of non-closing data

I put K0, K1, and A in the call - you can put back in the function, but since they were inputs I decided to have them in the call

The 15 and 20 case answers in D25 and D28 in attachment agree with your worksheet

Code:

---

Option Explicit

Function TADegC(Density As Double, HYC As Double, Temp As Double, K0 As Double, K1 As Double, A As Double) As Double
    Dim DensityInitial As Double, DensityPrevious As Double, DensityCurrent As Double
    Dim LoopCounter As Long
    Dim Alpha As Double, Error As Double, VCF As Double
   
    Application.Volatile
   
    DensityInitial = Density * 1000# * HYC
   
    DensityPrevious = DensityInitial
    Do
        LoopCounter = LoopCounter + 1
   
        Alpha = (K0 / (DensityPrevious ^ 2)) + (K1 / DensityPrevious) + A
        VCF = Exp((-Alpha * Temp) * (1 + 0.8 * Alpha * Temp))
        DensityCurrent = DensityInitial / VCF
        Error = Abs(DensityCurrent - DensityPrevious)
   
        If Error > 0.05 Then DensityPrevious = DensityCurrent
   
    Loop Until Error <= 0.05 Or LoopCounter > 100
   
    If LoopCounter > 100 Then
        TADegC = 999.999
    Else
        TADegC = DensityCurrent
    End If
End Function

---

Your code

Code:

---

Do
RH15 = RHO15 'RHO15 always = 0

K0 = 613.9723
K1 = 0
A = 0

alpha15 = (K0 / (RH15 ^ 2)) + (K1 / RH15) + A
VCF15 = Exp((-alpha15 * T15) * (1 + 0.8 * alpha15 * T15))
rho15 = RH15 / VCF15
Error = Abs(rho15 - RH15) 'Cannot use "error" as a bariable

Loop until error <= 0.05

---

Your code with a proper loop structure. It has several divide by zeros functions

Code:

---

Do While Abs(rho15 - RH15) > 0.05
RH15 = RHO15 'RHO15 will always = 0. IOW, a Constant

K0 = 613.9723 'a Constant
K1 = 0 'a Constant
A = 0 'a Constant

alpha15 = (K0 / (RH15 ^ 2)) + (K1 / RH15) + A 'Also a Constant
'K0 / (RH15 ^ 2) is the same as 613.9723/0^2
'(K1 / RH15) + A is the same as (0/0)+0

VCF15 = Exp((-alpha15 * T15) * (1 + 0.8 * alpha15 * T15))
rho15 = RH15 / VCF15
'rho15 = RH15 / VCF15 is the same as 0/VCF15
Loop

---

Hi Paul:
:bow::bow::bow::bow:
Thank you for the assistance. Now I can use your guided looping for my other similar looping functions.
Cheers
Abilisus