Consulting

Results 1 to 9 of 9

Thread: How to do loop in the function?

  1. #1
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    4
    Location

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



    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
    Last edited by Paul_Hossler; 02-18-2018 at 07:32 AM. Reason: Added CODE tags and moved outside of TextBox

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Last edited by Paul_Hossler; 02-18-2018 at 07:58 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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

  4. #4
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    4
    Location
    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

    T
    ADegC=Den20

    End Function



    Cheers
    Abilisus

    Quote Originally Posted by Paul_Hossler View Post
    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
    Last edited by abilisus; 02-18-2018 at 09:32 AM.

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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 ?


    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


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    4
    Location
    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 View Post
    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 ?


    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
    Attached Files Attached Files
    Last edited by abilisus; 02-18-2018 at 11:52 PM.

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    VBAX Newbie
    Joined
    Feb 2018
    Posts
    4
    Location
    Hi Paul:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •