VBA-function: How to avoid recalculations with every change in a worksheet

[ulfd]

Hello,

I'm working with a VBA Function, which shuffels around some function-internal Arrays and calculates a value (XIRR).
Even without having any Application.Volatile in the file this VBA Function is run even if I Input some text in an unused cell.
I have hundreds of calls of the function in my worksheet so recalculation Needs some time I want to avoid.

Is there anything I can do so that the function only runs when the values the function is based on are changed?

Any idea?

Best regards,
Frank

[oeldere]

Non VBA solution could be:

Formula => Countoptions => Manual

After all the changes you could use

Formula => Countoptions => Automatic

[MrShorty]

You will probably have to post the code and spreadsheet so we can see and test what you are doing. I would guess that there is something in the argument list or one of the values being passed to the function that Excel is seeing change with every calculation event (any chance you are passing a volatile function like =NOW() or =TODAY() to this UDF)?

[ulfd]

Hello,

I uploaded a sample sheet with the function. All cal of the function are marked yellow. As I only have about 10 lines with values, the Problem is a bit difficult to see. Maybe you put a breakpoint and then Change something in an empty cell (put in [A6] for example...). Recalculation starts immediately.

Many thanks in advance,

Frank

[MrShorty]

The INDIRECT() function is a volatile function (http://www.decisionmodels.com/calcsecretsi.htm ). The references for each of your UDF calls are built using the INDIRECT() function. Because these INDIRECT() functions are volatile, they are calculated with each calculation event. Subsequently, each incidence of the UDF is also recalculated with each calculation event.

In order to get these to be non-volatile, you will need a different strategy for identifying these ranges that does not use a volatile INDIRECT() or OFFSET() function. It is not clear to me exactly what the logic is behind creating these ranges, but perhaps a mixed absolute/relative reference. something like a$11:a13 (note that row 11 is fixed, but row 13 will adjust as the formula is copied).

[ulfd]

I did not know, that the INDIRECT() function is volatile. Thank you for this.

The Problem I have is that I need the xirr function with "dynamic" ranges. As there can be some Switches of Shares the begin and end of the ranges depend on the date of the Switch. I will think about this and open a separate threat if needed.

Many thanks for your help!