How to make a cell containing a macro call to be recalculated?

[Luis Piñeiro]

I have developed a marco function in VBA for Excell Office 2003.
The macro contains references to cell values as part of its calculations.
I set another cell C so its value is the one returned by the macro:
"=MyMacro()"
If I change any of the cells values to which the macro makes a reference,
the cell C does not update automatically. I have to edit the cell and press
OK (that is, doing no real editorial changes) to force the cell value to be
re-calculated.
Pressing F9 did not work.

Do you know how to make such recalculation automatic?

Thanks in advance!

[Kaak]

Youo can create a function for example

Function Test(A, B) As Integer

Test = A * B

End Function

And use in cell A1: "=Test(B1,C1"

If you change B1 or C1, A1 will update

[Chip Pearson]

Since your macro doesn't have any range arguments, Excel has no
reason to believe it needs to be recalculated. Instead of using
getting the range values inside the procedure, make the relevant
ranges parameters to the function. E.g.,

=MyMarco(A1,A2)

Since Excel sees that your macro is dependent upon A1 and A2, it
will properly calculate the result when either A1 or A2 changes.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Luis Piñeiro" <Luis Piñ[email protected]> wrote in
message
news:[email protected]...
>I have developed a marco function in VBA for Excell Office 2003.
> The macro contains references to cell values as part of its
> calculations.
> I set another cell C so its value is the one returned by the
> macro:
> "=MyMacro()"
> If I change any of the cells values to which the macro makes a
> reference,
> the cell C does not update automatically. I have to edit the
> cell and press
> OK (that is, doing no real editorial changes) to force the cell
> value to be
> re-calculated.
> Pressing F9 did not work.
>
> Do you know how to make such recalculation automatic?
>
> Thanks in advance!

[FourBlades]

Type In Application.Volatile at the top of your sub.
For More Info look up "Volatile" in the IDE help section.

Be carefull, If you write a large number of userdefined functions Excel will
slow to a crawl.

"Luis Piñeiro" wrote:

> I have developed a marco function in VBA for Excell Office 2003.
> The macro contains references to cell values as part of its calculations.
> I set another cell C so its value is the one returned by the macro:
> "=MyMacro()"
> If I change any of the cells values to which the macro makes a reference,
> the cell C does not update automatically. I have to edit the cell and press
> OK (that is, doing no real editorial changes) to force the cell value to be
> re-calculated.
> Pressing F9 did not work.
>
> Do you know how to make such recalculation automatic?
>
> Thanks in advance!