Prevent VBA UDF to get updated if row/col is inserted in excel worksheet-CodePudding

I have a function defined e.g.

Public Function calc_x(ByVal x As Integer)

...do some stuff

calc_x = x x

End Function

This function gets called within the excel sheet from a cell lets say (A2), with a "pointer" to A1 which contains a value 20:

content cell A1: "20"

content cell A2: "=calc_c(A1)"

However, everytime I insert new rows or columns in excel (even after row A or after col 2) the function gets recalculated. Is there a way to prevent that?

CodePudding user response：

You can use Application.Caller.Text*(1) to reference the cell's original output.
Next, you can put a boolean "On/Off Switch Cell" somewhere.
Your UDF logic will go like: "If the value of boolean cell is TRUE, then do calc, otherwise output CallerCell text"

Below example works and is the roughly the simplest form.

Function Test_Not_Calc(x, y, b)
    Application.Volatile
    If b.Value = True Then
        Test_Not_Calc = x * y
    Else
        Test_Not_Calc = Application.Caller.Text
    End If
End Function

*Reference(1) - S/O - excel vba preserve original value if UDF formula fails

CodePudding user response：

In the Formulas ribbon, you can set calculation options to manual.
You can set this in VBA using Application.Calculation = xlManual.

That will keep the formula from calculating with every new addition to the sheet.