How to do loop in the function?

[abilisus]

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. :((









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

[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





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

[Dave]

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

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

[abilisus]

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


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





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

[Paul_Hossler]

https://stackoverflow.com/questions/48851185/i-cant-do-looping-for-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 ?




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

[abilisus]

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





https://stackoverflow.com/questions/48851185/i-cant-do-looping-for-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 ?




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

[Paul_Hossler]

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






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

[SamT]

Your 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
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

[abilisus]

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