PDA

View Full Version : Function fails to update values?

dtms1
10-27-2010, 04:29 PM
I have written the following VBA function

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

Kenneth Hobs
10-27-2010, 05:09 PM
Application.Volatile

e.g.
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

dtms1
10-27-2010, 05:13 PM
Wow that was easy!
Did exactly as I was looking for, Thank you so much Kenneth Hobs!!!!!