Prevent functions from running while macro is running

My worksheet contains several custom functions. The functions calculate, as usual, when data is entered. How do I prevent the functions from recalculating while a macro is running? The macro carries out calculations as well, and each time data is entered by the macro the functions recalculate. I have tried

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

but they do not stop the functions. Are there any suggestions that may help.
Thankyou
Peter

Hi Peter,

It's not particularly elegant or high-tech, but the following approach might be suitable for preventing your user-defined functions from recalculating while your macro is executing:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thanks for the reply Greg. I can't make it work so could you give me an explanation of the process
Thanks
Peter

Hi again,

The statement:

Please Login or Register  to view this content.

must be inserted in EACH of the user-defined functions which you wish to prevent from calculating while the MyMacro routine is executing.

The variable bMacroRunning is defined as a module-level variable (i.e. at the top of the Code Window) - this means that it is available to all routines contained in the module.

As soon as the MyMacro routine is executed, the bMacroRunning variable is set to TRUE. Whenever your user-defined functions are called (from the worksheet), the above If statement causes control to exit immediately from the user-defined function.

When the MyMacro routine has executed, the bMacroRunning variable is set to FALSE - this means that user-defined functions will be calculated, as the result of the above If statement will be FALSE.

Hope this helps - please let me know how you get on.

Regards,

Greg M