Search code examples
excellistobjectexcel-tablesvba

Add/Modify/delete calculated column formula in Excel Listobject/Table via VBA

If I manually enter a formula into a column in an Excel table (i.e. ListObject), AutoCorrect applies this formula to the whole column.

Is there any way to control the this behavior via VBA, i.e. can I somehow modify/delete/add this formula?

I know I can simply change the formula of the ListObject.ListColumns(1).DataBodyRange object - but this will overwrite any manually values entered before - while changing the formula in the UI will leave this untouched...

Solution

  • Thanks to Doug's and bonCodigos comments/answers, I found the simple answer:

    ListObject.ListColumns("Column name").DataBodyRange.FormulaR1C1 = "new formula"

    This will overwrite any manual value (just as the normal behavior with AutoCorrect).