VBA: cannot automatically recalculate Excel formula after updating it -- needs manual interaction

Question

In VBA, I am updating the formula in a cell (which works ok), but automatic recalculation does not work:

updated_formula = "=COUNT(Sheet1!A3:A" & nr_points & ")"
Cells(x, y).Formula = updated_formula
ActiveWorkbook.Save
Cells(x, y).Calculate

The formula simply counts the number of existing rows in another sheet. When I run the macro, the cell's value in the function textfield is correct, but in the cell itself I have "#NAME?" and I need to press ENTER in the function to recalculate the formula.

Am I expecting too much of Excel? Or am I doing something wrong?

EDIT: Screenshot of the situation -- this is what I see after running the macro. (Sorry for the black censoring, have to maintain anonimity for the client company)

Answer 1

After seeing your screenshot, it became clear.

Change

Cells(x, y).Formula = updated_formula

to

Cells(x, y).FormulaLocal = updated_formula