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

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!

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

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


--
Kaak
------------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
View this thread: http://www.excelforum.com/showthread...hreadid=557271

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

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 wrote in
message
...
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!

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

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!