Excel VBA: как проверить вычисляемый столбец?

Это дает подходящие ответы для вашего примера.

К сожалению, у него есть несколько потенциально фатальных недостатков, в зависимости от ситуации. Во-первых, это вызывает повторный расчет, поэтому случайные числа, сгенерированные с помощью =RAND() формул в вашем примере, изменяются.

Второй недостаток заключается в том, что он изменяет рабочий лист, чтобы получить ответы (он удаляет внесенные изменения, но все же изменяется). Я могу придумать несколько лишь частично полезных обходных путей: (а) выполняйте эту операцию настолько редко, насколько это необходимо, и кешируйте результаты для всех столбцов, и (б) скопируйте таблицу в новую книгу и запустите процедуру (и удалите новую книгу) . Хотя последний позволит избежать недостатка модификации, он по-прежнему вызывает повторный расчет для исходной книги (и в остальном имеет свои недостатки). Чтобы добавить к этому, копирование таблицы в новую книгу теряет table / ListObject, если вы не копируете весь диапазон (а не только заголовки); тогда это также, кажется, продвигает 4-й столбец (формулы, не согласованные с расчетом), чтобы быть вычисляемым. К сожалению, такая акция случается и при копировании всего листа.

Что ж, 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

Я не смотрел на это раньше, но похоже, что это свойство диапазона столбцов ListObject следующим образом:

Dim wks As Worksheet
Set wks = ActiveSheet
Dim li As ListObject
Set li = wks.ListObjects(1)
Dim col As ListColumn
Set col = li.ListColumns(2)    ' assuming column 2 of the table has a calculated formula
Dim r As Range
Set r = col.DataBodyRange
Let b = Not IsNull(r.FormulaArray)
if b then
    Let b = Len(r.FormulaArray) > 0 ' case where r.FormulaArray = "", suspect it's not a calculated column
End If
MsgBox b

если IsNull (r.FormulaArray), то у него нет вычисляемого столбца, в противном случае он есть.

hth

Хорошо, поиграем с этим немного, и я вижу, что объект диапазона, полученный с помощью приведенного выше, отличается от объекта диапазона для любой данной ячейки, поэтому, если у вас есть данная ячейка, я думаю, вам нужно будет получить соответствующий диапазон ListColumn через .DataBodyRange.

(Например, если вы вставите Set r = r.Cells(1,1) в указанное выше, тогда тест IsNull(r.FormulaArray) больше не работает для проверки вычисляемого столбца, а вместо этого просто говорит, есть ли в диапазоне формула, но ее можно вычислить или нет.)

Кроме того, в то время как r.FormulaArray кажется строкой при вычислении столбца, если это не так (вычисляемый столбец), то .FormulaArray возвращает null, что не является допустимым строковым значением (что затрудняет использование в что для захвата значения вы должны использовать вариант, а не логическое значение); Я обнаружил, что IsNull (r.FormulaArray), похоже, работает нормально.

Если я добавлю столбец справа от уже рассчитанного столбца, тогда r.FormulaArray = "" для этого нового добавленного столбца. Если вы поместите значение в одну из его ячеек, массив формул немедленно вернется к более ожидаемому NULL. Итак, я добавил тест на то, что считаю ложноположительным.