How to read calculated column formulas in Excel / ListObject table without any data rows

Question

I have a ListObject with an external query as a data source that returns 18 columns. In the ListObject element, 4 more calculated columns were previously added.

ListObject currently has 0 rows of data, however, although there are 0 rows of data, I can't seem to read the predefined formulas of the computed columns.

If I update the data source and the data source returns at least one row, then the formulas for the computed columns become readable. Similarly, if I manually enter data into one of the uncomputed columns, so that at least one row exists, then the calculated formulas of the columns are read.

Is there a way to determine which formulas of a computed column do not contain any data in a list object?

Answer 1

Below is a workaround that will work if the table has rows or not.

getListColumnFormulae- Adds a row to the table - Fills an array of 1-dimensional base 1 with formulas for all ListColumns - Deletes a row - Return an array

---

---

Function getListColumnFormulae(tbl As ListObject)
    Dim Formulae
    On Error Resume Next
    With tbl.ListRows.Add
        Formulae = Application.Transpose(.Range.Formula)
        Formulae = Application.Transpose(Formulae)
        getListColumnFormulae = Formulae
        .Delete
    End With
    On Error GoTo 0
End Function

Sub FormulaeMessage()
    Dim Data
    Dim tbl As ListObject
    Set tbl = Worksheets("Sheet2").ListObjects(1)
    Data = getListColumnFormulae(tbl)

End Sub