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

Question

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?

Is it because it's not performing well and annoying you in that regard? — Skin, Nov 21 '22 at 00:58

score 0 · Answer 1 · answered Nov 21 '22 at 21:40

0

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.

answered Nov 21 '22 at 21:40

virpericulosus

61
6

score 0 · Accepted Answer · answered Nov 21 '22 at 22:20

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

Thanks, this is a suitable solution – Bernd Nov 21 '22 at 23:04 — Bernd, Nov 21 '22 at 23:04

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

2 Answers2