Excel VBA: how to check for calculated column?

Question

Is there any way to check in VBA if a specific column of a table (ListObject) in Excel is a calculated column (as in https://support.microsoft.com/en-us/office/use-calculated-columns-in-an-excel-table-873fbac6-7110-4300-8f6f-aafa2ea11ce8)?

Note that calculated columns will not only have the same R1C1 formula set for each row but will also auto-expand when new rows are added (it will even repopulate if you delete the entire data body range and then recreate some new rows). So, checking for columns with consistent formulas is not the same as checking for a calculated formula.

It's even possible for a column to be calculated, but to have one of the rows overwritten with some other formula or value and have the auto-expand features persist.

So, I'm pretty convinced this has to be some property of the column, I'm just not sure where I can access it through VBA. If it's not exposed through the VBA object model, is there some workaround to get this info?

Thanks in advance, Carlos

EDIT: I did some digging on the Excel Office Open XML files, and it turns out that what I'm looking for is the <calculatedColumnFormula> element on the <tableColumn> definition of the xl\tables\table*.xml files. Is there any way to get to that through VBA?

EDIT2: Here is an example file with the test cases that I could come up with. The VBA should signal these columns 1, 2 and 3 are calculated columns, and columns 4 and 5 aren't.

Answer 1

This one gives appropriate answers for your example.

Unfortunately, it has a few potentially fatal drawbacks, depending one's situation. For one, it causes a recalc, so the random numbers generated via =RAND() formulas in your sample are changed.

The second drawback is that it modifies the worksheet in order to obtain the answers (it removes the modification that it makes, but still it gets modified). I can think of a few only partially helpful workarounds: (a) do this operation as seldom as needed and cache results for all columns, and (b) copy the table to a new workbook and run the routine (and delete the new workbook). While the latter would avoid the modification drawback, it still triggers a recalc for the original workbook (and otherwise has its own drawbacks). To add to that, copying the table to a new workbook looses the table/ListObject unless you copy the whole range (not just headers); then it also appears to promote the 4th column (non-calc consistent formulas) to being a calculated one. Sadly, this promotion also happens when copying the whole sheet.

Well, FWIW:

Sub TestTable()
    Dim ans As String
    Let ans = ""

    Dim li As ListObject
    Set li = ActiveSheet.ListObjects(1)

    Dim rowCountBefore As Long
    Let rowCountBefore = li.ListRows.Count

    Dim lr As ListRow
    Set lr = Nothing

    On Error Resume Next
    Set lr = li.ListRows.Add(AlwaysInsert:=True)
    On Error GoTo 0

    Dim rowCountAfter As Long
    Let rowCountAfter = li.ListRows.Count

    If Not (lr Is Nothing) And rowCountAfter = rowCountBefore + 1 Then
        Dim c As Long
        For c = 1 To li.DataBodyRange.Columns.Count
            Dim b As Boolean
            Let b = lr.Range.Cells(1, c).HasFormula
            ans = ans & "col " & c & ": " & b & ";  "
        Next
        li.ListRows(rowCountAfter).Delete
    End If

    MsgBox ans
End Sub