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

Question

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...

Deiform

I'm not clear on what behavior you want. And regarding "changing the formula in the UI will leave this untouched," that's not my experience. If I've got a table column with the same formula throughout except for one hard-coded cell, and I then change the formula, I get the little prompt to "overwrite all cells with this formula." If I choose yes, the hard-coded cell is overwritten, if no then the formula remains applied only to the cell I've changed. This is Excel 2010. – Suborbital

@Doug - thanks for clarifying this. I wrongly thought that cells that deviate from the calculated column formula are kept when the formula is updated! I see now that this is not the case, which pretty much voids the question! All I need to do to modify the calculated column formula is to modify listObject.ListColumns("Column name").DatabodyRange.FormulaR1C1... – Deiform

Answer 1

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).

Deiform

Will this also set the default formula so that when new rows are added, the formula is added as well? – Growler

@Growler - yes, it will – Deiform

I am suddenly getting an "Invalid Use of Property" when using this...any tips? – Lineament

@Lineament are you sure the column name is correct? Also, does your table have data - if not, DataBodyRange will be Nothing and you'll get this error. – Deiform

@PeterAlbert I think this may be the reason its happening as I am pulling in data from a query...what's the best way to wait for the query to finish loading before running this to ensure there is data there? – Lineament

Answer 2

Best would be if you could show us a screen shot of your sheet. Based on that we would have articulated the answer.

Here is with the general assumption. That you have one list object to dump some data to a column and rest of the cells in that column are manually interacted.

You could try the following manually first and see if it works for you. Still the manual one is being overtaken by the code level, then you do this in code level. :)

The main action here is to Stop automatic corrections

Go To -> Tools menu -> Click AutoCorrect Options -> In the AutoCorrect Tab ->

1- To prevent ALL automatic corrections

Clear the check box for Replace Text as you type

2- To prevent SPECIFIC corrections

Clear the corresponding check box for the option. I believe you are more interested in the latter - specific data range that you just dump via a listobject.

---

Now here is the code for disabling this feature at code level.

When working with Excel Tables (ListObjects) there are two AutoCorrect options to consider: You can read about those two in details.

* Apply new rows and columns in table 
  (VBA AutoCorrect.AutoExpandListRange Property)
* Fill formulas in tables to create calculated columns 
 (VBA AutoCorrect.AutoFillFormulasInLists Property) 

Code you may want to use at the top of our listobject is,

Application.AutoCorrect.AutoFillFormulasInLists = False

And totally agree that it would be so much more useful if Application.AutoCorrect. AutoFillFormulasInLists controlled on a table by table basis at the ListObject level. So here is a workaround.

So one way is to clear your table data each time. And when you clear data you can make sure for TABLE TO FORGET forumulae and formatting. So it will clear the contents of the data body range before deleting table contents.

Sub forgetMe()
   With Sheet1.ListObjects("myTable")
      If Not .DataBodyRange Is Nothing Then
         .DataBodyRange.ClearContents
         .DataBodyRange.Delete
      End If
   End With

End Sub

When you input the data, start with auto stuff off.

Crossruff