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

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.

On Sat, 30 Apr 2005 07:21:41 GMT, DecapitateSpammers@myspam.com (Wild
Bill) wrote:

>(except when the workbook is loaded)

I believe I should have said "recalculated" instead of "loaded."

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" <jeffwright@cox.net> 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
>
>

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.

Exactly my point when suggesting the latter option.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wild Bill" <DecapitateSpammers@myspam.com> wrote in message
news:427e9ab2.37044812@news.comcast.giganews.com...
> 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.

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%238IxP5aTFHA.544@TK2MSFTNGP15.phx.gbl...
> Exactly my point when suggesting the latter option.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Wild Bill" <DecapitateSpammers@myspam.com> wrote in message
> news:427e9ab2.37044812@news.comcast.giganews.com...
>> 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.
>
>