Hi there,

I'm created a function using macros, and i want to know how to make it so
that when ever something on the worksheet changes the function will
recalculate and update itself. How do I go about doing this?

thanks in advance

Ryan

enter the word volitile at the begining of the macro

"Brite" wrote:

Hi there,

I'm created a function using macros, and i want to know how to make it so
that when ever something on the worksheet changes the function will
recalculate and update itself. How do I go about doing this?

thanks in advance

Ryan

The best you can do is make your function recalculate when it's supposed to.

For that to happen, you want to make sure that any reference in your UDF is
passed to it through the formula:

If your UDF depends on A1 and B1, then using this:

Function myFunc(rng1 as range, rng2 as range) as double
'no validation at all
myfunc = rng1.value + rng2.value
end function

and in the worksheet:
=myfunc(a1,b1)

Is much better than:

Function DontUseThis(rng1 as range) as double
dontusethis = rng1.value + rng1.offset(0,1).value
end function

and in the worksheet:
=dontusethis(a1)

If B1 changes, the excel doesn't know that it needs to recalc that cell with the
formula.

You could use this (still Don't use it!)

Function DontUseThis2(rng1 as range) as double
application.volatile
dontusethis2 = rng1.value + rng1.offset(0,1).value
end function

and in the worksheet:
=dontusethis2(a1)

But that means that the cell with the function could be one calculation behind.
If you change B1 and that doesn't cause excel to recalc, then your cell with the
formula will look pretty, but it'll be wrong.


Brite wrote:

Hi there,

I'm created a function using macros, and i want to know how to make it so
that when ever something on the worksheet changes the function will
recalculate and update itself. How do I go about doing this?

thanks in advance

Ryan

--

Dave Peterson