Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I prevent a cell, once calculated from being recalculated? | Excel Discussion (Misc queries) | |||
How do i call a macro from a cell | Excel Programming | |||
How do I make a macro call on different macros | Excel Programming | |||
Call Macro when Cell within Range Changes | Excel Programming | |||
Event to call before a opened workbook is recalculated | Excel Programming |