Only manually calculate user defined function

Place this code in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Save the file, close it and then re-open it. Each time you open the file, the first macro will set calculation to manual so that calculations are not refreshed. When you want to refresh the calculations, click 'Calculate now'. When you close the file, the second macro will set calculation back to automatic.

Please Login or Register  to view this content.

The one problem is that while this file remains open, all calculations are also not refreshed in any other workbooks that may be open until the file is closed.

Are you only considering some kind of "hot key" arrangement? I would probably pass an additional boolean argument to the function and use that to toggle between calculate this function and don't calculate this function. Something like:

Please Login or Register  to view this content.

Pass TRUE or any non-zero number to the function and it returns "waiting..." and skips over the rest of the function code. Pass FALSE or 0, and it will perform the rest of the calculations.

Hi MrShorty,

Something like that could work if the outputs from the previous calculation were still shown. With your suggestion the cells output is "Waiting...", which messes up the remaining formulas in the worksheet.
So is it possible to store the current cell values and show give this as funciton output.

Any ideas?

ByteMarks solution can work, but, in my tests in my version of Excel, Excel generated a "circular reference" warning. I expect that solutions like ByteMarks will require you to enable iteration in Excel settings. Are you allowed to change that setting?

If I were doing it, rather than use Application.Caller to get the current value, I would prefer to pass the value as another argument to the UDF

Please Login or Register  to view this content.

. If the UDF is called from B1, then the function call looks like =MYUDF(arguments...,TRUE,B1).