Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Refresh VB Functions Automatically in Excel?
I can define a Visual Basic function (via Tools - Macro - Visual
Basic Editor) and the function is indeed called for all cells referencing it the first time that I select "Close and Return to Microsoft Excel." The problem is that when I re-edit the same function and "Close and Return to Microsoft Excel" the cells are not updated automatically. How can I get Excel to refresh my entire page automatically upon saving my VB function and returning to Excel? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Refresh VB Functions Automatically in Excel?
Add Application.Volitile at the beginning of the function. Doing this will
force the cells to recalculate every time that the sheet recalculates. Add this line. Go back to the sheet and hit F9 to recalculate. If application.volitile is not required as a regular part of the function then comment it out when you are finished modifying. -- HTH... Jim Thomlinson "Phillip Nolan" wrote: I can define a Visual Basic function (via Tools - Macro - Visual Basic Editor) and the function is indeed called for all cells referencing it the first time that I select "Close and Return to Microsoft Excel." The problem is that when I re-edit the same function and "Close and Return to Microsoft Excel" the cells are not updated automatically. How can I get Excel to refresh my entire page automatically upon saving my VB function and returning to Excel? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Refresh VB Functions Automatically in Excel?
This may work, but there is no guarantee that it will calculate the cells in
the correct order. Also they will be recalculated no matter if that's necessary or not, which may or may not be a problem from a performance point of view. The only correct way is to include all input to the function in the argument list, just like in "real" math. That's the concept of functions. Never access precedent cells (or even others) directly from within your functions. Kind regards, Niek Otten "Phillip Nolan" wrote in message ups.com... I can define a Visual Basic function (via Tools - Macro - Visual Basic Editor) and the function is indeed called for all cells referencing it the first time that I select "Close and Return to Microsoft Excel." The problem is that when I re-edit the same function and "Close and Return to Microsoft Excel" the cells are not updated automatically. How can I get Excel to refresh my entire page automatically upon saving my VB function and returning to Excel? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Refresh VB Functions Automatically in Excel?
<This may work
was meant to be a reply to Jim's answer "Niek Otten" wrote in message ... This may work, but there is no guarantee that it will calculate the cells in the correct order. Also they will be recalculated no matter if that's necessary or not, which may or may not be a problem from a performance point of view. The only correct way is to include all input to the function in the argument list, just like in "real" math. That's the concept of functions. Never access precedent cells (or even others) directly from within your functions. Kind regards, Niek Otten "Phillip Nolan" wrote in message ups.com... I can define a Visual Basic function (via Tools - Macro - Visual Basic Editor) and the function is indeed called for all cells referencing it the first time that I select "Close and Return to Microsoft Excel." The problem is that when I re-edit the same function and "Close and Return to Microsoft Excel" the cells are not updated automatically. How can I get Excel to refresh my entire page automatically upon saving my VB function and returning to Excel? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Refresh VB Functions Automatically in Excel?
Just use CTRL+SHIFT+ALT+F9 which is the keyboard shortcut for "Force recalculate everything in sight and then some more." {grin} No need for something as volatile as Application.Volatile. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article . com, says... I can define a Visual Basic function (via Tools - Macro - Visual Basic Editor) and the function is indeed called for all cells referencing it the first time that I select "Close and Return to Microsoft Excel." The problem is that when I re-edit the same function and "Close and Return to Microsoft Excel" the cells are not updated automatically. How can I get Excel to refresh my entire page automatically upon saving my VB function and returning to Excel? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Refresh VB Functions Automatically in Excel?
Hi Tushar,
Even then, if (some of) the input cells weren't in the argument list, there is no guarantee that the formulas will be calculated in the correct order and will remain to do so in future versions of Excel; this is not part of Excel's specifications. -- Kind regards, Niek Otten "Tushar Mehta" wrote in message om... Just use CTRL+SHIFT+ALT+F9 which is the keyboard shortcut for "Force recalculate everything in sight and then some more." {grin} No need for something as volatile as Application.Volatile. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article . com, says... I can define a Visual Basic function (via Tools - Macro - Visual Basic Editor) and the function is indeed called for all cells referencing it the first time that I select "Close and Return to Microsoft Excel." The problem is that when I re-edit the same function and "Close and Return to Microsoft Excel" the cells are not updated automatically. How can I get Excel to refresh my entire page automatically upon saving my VB function and returning to Excel? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Excel functions formula and auto refresh refresh | Excel Worksheet Functions | |||
How can I Automatically refresh the NOW() Display in EXCEL? | Excel Worksheet Functions | |||
Excel will not automatically refresh external data on open | Excel Discussion (Misc queries) | |||
Refresh workbook automatically | Excel Worksheet Functions | |||
Force refresh of custom functions | Excel Worksheet Functions |