Consulting

Results 1 to 3 of 3

Thread: Function fails to update values?

  1. #1
    VBAX Regular
    Joined
    Oct 2010
    Posts
    9
    Location

    Function fails to update values?

    I have written the following VBA function

    [vba]Function Eff(CL)
    'Aerodynamic Efficiency
    'Function of CL
    'unitless
    'E=L/D or CL/CD or CL/(CD0+k*CL^2)

    AR = Range("AR").Value
    CD0 = Range("CD0").Value
    e = Range("e").Value
    x = 2
    Pi = Application.Pi()
    k = 1 / (Pi * AR * e)
    Eff = CL / (CD0 + k * CL ^ 2)
    End Function[/vba]
    Where AR, CD0, and e are all assigned values i put in excel using Name Manager.
    The function works and everything the only problem is that if I change the values of AR, CD0, or e in excel, the function does not update itself with the new values and displays the values using the old parameters. Is there a way I can have the function auto recalculate / auto update when the values of AR, CD0, or e have been changed in excel?

    ps.
    I have tried defining AR, CD0, and e in the code itself and changing it from there but it is not as efficient as if I had those values in excel. In excel I can add a scroll button / slide bar to allow for easy changes in those values. So if possible I would still like to keep the values of AR, CD0, and e defined in excel

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Application.Volatile

    e.g.
    [VBA]Function Eff(CL As Double) As Double
    'Aerodynamic Efficiency
    'Function of CL
    'unitless
    'E=L/D or CL/CD or CL/(CD0+k*CL^2)

    Dim AR As Double, CD0 As Double, e As Double, Pi As Double, k As Double
    Application.Volatile
    AR = Range("AR").Value
    CD0 = Range("CD0").Value
    e = Range("e").Value
    Pi = Application.Pi()
    k = 1 / (Pi * AR * e)
    Eff = CL / (CD0 + k * CL ^ 2)
    End Function[/VBA]

  3. #3
    VBAX Regular
    Joined
    Oct 2010
    Posts
    9
    Location
    Wow that was easy!
    Did exactly as I was looking for, Thank you so much Kenneth Hobs!!!!!

Posting Permissions

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