4

I have a ListObject with an external query as the data source, which returns 18 columns. The ListObject has previously had an additional 4 calculated columns added.

Right now, the ListObject has 0 data rows, however, while there are 0 data rows, I don't seem to be able to read the pre-defined formulas of the calculated columns.

If I refresh the data source, and the data source returns at least 1 row, then the formulas for the calculated columns become readable. Likewise, if I manually enter data in one of the non-calculated columns, so that there is at least one row, then the calculated column formulas are readable.

Is there a way to determine what the calculated column formulas are without adding any data to the list object?

ThunderFrame
  • 9,352
  • 2
  • 29
  • 60

1 Answers1

3

Here is a workaround that will work whether the table has rows or not.

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

enter image description here

enter image description here

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
  • Similar to my original workaround "if I manually enter data in one of the non-calculated columns". Clearly the formula exists somewhere, but seemingly the object model doesn't expose it. – ThunderFrame Nov 22 '16 at 06:04
  • 1
    @ThunderFrame Please note that there is one key difference in the approach of this answer when compared to _your original workaround_, and it is the fact that this method does not require any prior knowledge about what `ListObject` Fields (`LisColumns.Items`) are _non-calculated_ and which Fields _`HasFormula`_, as it blindly adds no "data" per se but just one row to the `DataBodyRange` in order to make the _"hidden"_ formulas of the `LisColumns.Items` visible. – EEM Apr 08 '17 at 13:13