How to Refresh VB Functions Automatically in Excel?

P

Phillip Nolan

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?
 
J

Jim Thomlinson

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.
 
N

Niek Otten

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
 
T

Tushar Mehta

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
 
N

Niek Otten

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.
 
T

Tushar Mehta

Hi Niek,

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.
If the function uses information that XL doesn't know about it's just
bad design and using App.Volatile is a band-aid. Far better to design
the UDF so that cells used in it are passed as arguments.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top