Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Function question
Greetings!
I have a simple function, shown below: Function Testing(X) Dim Computed As Currency Computed = (X + Range("A1")) Testing = Computed End Function Let's say range A1 has a value of 10. Now, in my worksheet, I type into cell B1: =testing(5) Cell B1 now correctly reflects the value of 15. However, if I change the value of cell A1 from 10 to any other number, cell B1 will remain 15. Why does the value of cell B1 not change? Conversely, I will obtain a correct answer if I change the argument in cell B1. Confused in Tucson, Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Function question
You probably want to learn about volatile functions at
http://www.decisionmodels.com/calcsecretsi.htm and http://www.decisionmodels.com/calcsecretsj.htm AAR Excel doesn't have a dependency on A1 in B1 - check tools/auditing/dependents and precedents on your cells. Excel "itself" doesn't realize that your function needs to be run (except when the workbook is loaded), so it doesn't try to recalc B1 when A1 changes. Only your code does. The links above should clear things up. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Function question
On Sat, 30 Apr 2005 07:21:41 GMT, (Wild
Bill) wrote: (except when the workbook is loaded) I believe I should have said "recalculated" instead of "loaded." |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Function question
The problem is that there is no link between the cell with the function in
it, and cell A1, in the function or anything referred to by the function. The VBA does not count. You can overcome it by using Application.Volatile at the start of your function, which will cause the function to be executed for any recalculation change on the worksheet (wasteful), or by referring to A1 as an argument in the function, rather than explicitly in the VBA, such as Function Testing(X, rng As Range) Dim Computed As Currency Computed = (X + rng) Testing = Computed End Function and then call with =Testing(5,A1) -- HTH RP (remove nothere from the email address if mailing direct) "Jeff Wright" wrote in message news:o9Gce.529$tQ.432@fed1read06... Greetings! I have a simple function, shown below: Function Testing(X) Dim Computed As Currency Computed = (X + Range("A1")) Testing = Computed End Function Let's say range A1 has a value of 10. Now, in my worksheet, I type into cell B1: =testing(5) Cell B1 now correctly reflects the value of 15. However, if I change the value of cell A1 from 10 to any other number, cell B1 will remain 15. Why does the value of cell B1 not change? Conversely, I will obtain a correct answer if I change the argument in cell B1. Confused in Tucson, Jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Function question
Charles discusses it extensively in "User-Defined Volatile Functions" on
http://www.decisionmodels.com/calcsecretsj.htm including a recommendation to avoid application.volatile when possible. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Function question
Exactly my point when suggesting the latter option.
-- HTH RP (remove nothere from the email address if mailing direct) "Wild Bill" wrote in message .. . Charles discusses it extensively in "User-Defined Volatile Functions" on http://www.decisionmodels.com/calcsecretsj.htm including a recommendation to avoid application.volatile when possible. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Function question
Thanks Bob and Wild Bill for your replies! Instead of using "volatile," I
opted to expand my function to use two additional arguments (solving the linkage problem), and now it works well. Thanks again for your help! Jeff Wright "Bob Phillips" wrote in message ... Exactly my point when suggesting the latter option. -- HTH RP (remove nothere from the email address if mailing direct) "Wild Bill" wrote in message .. . Charles discusses it extensively in "User-Defined Volatile Functions" on http://www.decisionmodels.com/calcsecretsj.htm including a recommendation to avoid application.volatile when possible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple Sum function question | Excel Worksheet Functions | |||
Probably simple question on AVG function | Excel Discussion (Misc queries) | |||
Simple Function question? | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) |